AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
1.5K members online now
1.5K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Monthly active users from BigQuery

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

In Google Data Studio I have a connected BigQuery Data source that receives data from Firebase. There is a Metric called Unique Users with values for each day. This would make it a daily Active Users Metric. I need to display a 30-day Active users metric.

 

Can I achieve this aggregation over a date range with Custom Formula based Calculated field?

Or any other way?

Monthly active users from BigQuery

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi Martin,

 

You should be able to do this by making a new dimension in Data Studio, duplication your daily date dimension but changing the type to MM (Month), you'll then be able to group your data by this dimension. While this isn't quite 30 days, hopefully it's close enough?

 

Ben

Monthly active users from BigQuery

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Ben,

 

Well actually I want to group daily by date but count the unique users for the last 30 days (Month would be fine here). If I understand correctly the Dimension then needs to be Date, but the Metric needs to sum across multiple tables.

Makes sense?

/Martin

Monthly active users from BigQuery

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

Hi Martin,

 

I haven't done this directly in DataStudio, but you could add a new data source with a customer query which just looks at the last 30 days tables, then pull your aggregation from here?

 

Ben

Monthly active users from BigQuery

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Ben,

 

That sounds great.

Do you know how to do that sum over the last 30 tables as a custom query?

Even better, do you know how to make that into a time series with a sliding window style summing the last 30 tables for each day? It's fine if the sliding window approach don't work, but it would be the absolutely best solution.

 

/Martin

Re: Monthly active users from BigQuery

Explorer ✭ ✭ ✭
# 6
Explorer ✭ ✭ ✭

Hi Martin,

 

Here's a sample BQ query which you can edit to suit your data, this is based on GA data tables in BQ and is for a rolling last 30 days. Replace the * with your project ID:

 

 

SELECT date, totals.visits
FROM (TABLE_DATE_RANGE([*.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))

Hope that helps,

Ben

 

Monthly active users from BigQuery

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi Ben

 

Is there a way to run the SQL  so that I can populate the monthly number below daily on a rolling basis?

 

SELECT date, totals.visits
FROM (TABLE_DATE_RANGE([*.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))

For example the above will give me monthly users for yesterday's date but if I want the equivalent number on a daily basis for the last three months. Can I do this in one go?

 

Thanks

 

Lucas  

 

Monthly active users from BigQuery

Explorer ✭ ✭ ✭
# 8
Explorer ✭ ✭ ✭

Hi @Hament B

 

Sure, you'll need to use a window function, see here:

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#analytic-funct...

 

Hope that helps,

Ben