BigQuery and Data Studio – Free solution for reporting

Data Studio is a Google solution for data visualization. One of its advantages is price – as the tool itself is free. The other is connection possibilities.

 

Data Studio link with Google Analytics 4

Both systems are made by Google so its link will be very easy. And it is!
But you are fairly limited regarding types of reports. The main thing for flexibility is a connection between Data Studio and BigQuery. With that, you can create any report and show results in Data Studio.

 

Where DataStudio is crucial

You need to have in mind that DataStudio is not an analytical tool by itself. It is a tool to show data. So for a company, it is a good approach to find out what are the main KPI’s which are valuable to measure. Especially for the management team which may be not familiar with Google Analytics, and with Google Analytics 4 especially.

 

Tableau vs DataStudio with BigQuery

But there is more. Why would you limit yourself to Google Analytics 4 data? Why don’t your company upload (and create it as a recurring process) business data into Google Cloud? With such data using SQL, you will be able to create any reporting and show the data in DataStudio. DataStudio can replace Tableau! Ok, hold your horses. I am not saying that if your company uses Tableau you should terminate the contract today. However, if your company considers acquiring Tableau, maybe it is a better (definitely cheaper) solution to try DataStudio and consider if it does not fulfill your needs. As there is a quote saying “What one IT guy can accomplish in one month, two IT guys can accomplish in two months”. Maybe for you, it would be the same – preparation for Tableau taking weeks, where you can hire someone to set DataStudio reports in days 😉

 

Reporting possibilities linking BigQuery with DataStudio seem limitless. The main limitations are:

• your SQL skills
• data which you have in Google Cloud (BigQuery)

 

First steps – linking BigQuery with DataStudio

The results should be a report with a data range checker to measure people who made a scroll in the data range. First of all, let’s write a simple query in BigQuery to get the number of users:

 

#Start Date
DECLARE
a DATE DEFAULT "2021-03-17";
#End date
DECLARE
b DATE DEFAULT "2021-03-17";

SELECT
count(distinct(user_pseudo_id)) as CookiesCount
FROM
`ga4dronsomcom.analytics_263819657.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',a)
AND FORMAT_DATE('%Y%m%d',b)
AND event_name="scroll"

 

To add it to DataStudio you need to create a new connection in data studio:

Choose BigQuery as your source:

Don’t forget to change:

FORMAT_DATE(‘%Y%m%d’,a) -> @DS_START_DATE

FORMAT_DATE(‘%Y%m%d’,b) -> @DS_END_DATE

First report in data studio:

 

But it would be great to have data range control:

 

Additionals for the beginning of Data Studio link with BigQuery

Ok. What else for the beginning? Measurement of:
count(distinct(user_pseudo_id)) as CookiesCount
Is not a good one, as cookies can visit a site on different days and if the same 100 cookies visit a page on day 1 and day 2 the result (100) will be the same for 3-time ranges which you could set:

• only day 1
• only day 2
• day 1 and 2

To fix it you can for example concat user_pseudo_id with event_date:

count(distinct(concat(user_pseudo_id,"__",event_date))) as CookiesCount

This way if the same cookie visits (makes a scroll) on your page on two different dates it will be counted separately.

 

@DS_START_DATE – moving the date

If you need in your query to have a different date than dates chosen in the DataStudio date range it is also doable. You can use such phrase:

replace (cast (DATE_add(PARSE_DATE('%Y%m%d', @DS_START_DATE), INTERVAL -5 DAY) as string),"-","")

With that, the date will be -5 days from your start date. It works 🙂 With that, you can make a report choosing the only user_id of users who added to the cart on your mobile app, in the previous week and check how many of them purchased on desktop.

 

TL;DR

DataStudio with BigQuery is quite a robust solution. You just need 3 things:

• image of what reports you would like to have in Data Studio
• provide data into Google Cloud
• create proper SQL queries

It’s doable!

 

 

I created a video for those first steps described here: