BigQuery & Firebase - how to determine weekly growth

Question

I'm trying to build a dashboard for following key metrics of my Android application. To do so, I am using Firebase analytics backed by BigQuery.

I'm trying to get weekly growth of first_open event count and ratio for

the current week
the previous week
the best week ever

I'm able to get the current week and previous week first_open event count as separate queries in BigQuery (such as the following as an example for the previous week query) :

SELECT
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday,
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
  COUNT(*) as counter,
  h.name as event
FROM `com_package_app_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) 
AND 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY))
AND 
 h.name='first_open'
GROUP BY event
ORDER BY counter DESC

But I'm unable to get a ratio by combining the 2 queries (for current week vs previous week), and also, I'm unable to get the best ever week first_open count.

mnort9 mnort9 · Accepted Answer · 2017-01-18T17:46:02

To get the "best week ever", you can group the events by week and order by count. Something like this...

SELECT 
    DATE_TRUNC(event_date, WEEK) AS week,
    COUNT(*) AS count
FROM BQ_TABLE
WHERE BQ_TABLE.name = 'first_open'
GROUP BY week
ORDER BY count DESC

To get the current/previous week, you can turn the above query into a subquery and filter it by your target weeks.

Note that the "best week ever" query will always be a full table scan and could get expensive depending on the number of events and how frequently you need to perform the query.

BigQuery & Firebase - how to determine weekly growth

1 Answers