Compute Active Custom Metrics for Firebase Analytics in BigQuery

Question

I recently came across this post to calculate MAU in BigQuery (Firebase Analytics data). I worked on this request to answer DAU, WAU and MAU for my project, but I saw some slight discrepancies in the calculations of MAU and WAU. The goal is to compute active user metrics that I can compare with the Firebase Dashboard here .

1) MAU calculation

#standardSQL
SELECT
  FORMAT_TIMESTAMP(
      '%Y-%m',
      TIMESTAMP_MICROS(user_dim.first_open_timestamp_micros)) AS year_and_month, 
  COUNT(DISTINCT user_dim.app_info.app_instance_id) AS monthly_visitors
FROM `<<project-id>>.app_events_*`
WHERE (_TABLE_SUFFIX BETWEEN '20170101' AND '20170731')
------ Inclusive for both the start-date and end-date
AND user_dim.first_open_timestamp_micros BETWEEN 1483228800000000 AND 1501545599000000
GROUP BY year_and_month 
ORDER BY year_and_month DESC;

# 1483228800000000   Sunday, January 1, 2017 12:00:00 AM
# 1501545599000000   Monday, July 31, 2017 11:59:59 PM
# https://www.epochconverter.com/

Questions:

• The output is 30% lower than what I saw in the Firebase Console.
• "user_dim.first_open_timestamp_micros" ? , , , - . , ( )?

• , " " - WHERE , . , , :

  

• , ? "AND user_dim.first_open_timestamp_micros BETWEEN 1483228800000000 1501545599000000" -clause , .

2) WAU

#standardSQL
SELECT
  FORMAT_TIMESTAMP(
      '%Y-%W',
      TIMESTAMP_MICROS(user_dim.first_open_timestamp_micros)) AS year_and_week, 
  COUNT(DISTINCT user_dim.app_info.app_instance_id) AS weekly_visitors 
FROM `<<project-id>>.app_events_*`
WHERE (_TABLE_SUFFIX BETWEEN '20170306' AND '20170917')
------ Inclusive for both the start-date and end-date
AND user_dim.first_open_timestamp_micros BETWEEN 1488758400000000 AND 1505692799000000
GROUP BY year_and_week 
ORDER BY year_and_week DESC;

# 1488758400000000    Monday, March 6, 2017 12:00:00 AM
# 1505692799000000    Sunday, September 17, 2017 11:59:59 PM

• 50% , Firebase Console. , 10% - , 50% .
• "% Y-% W" , "% Y-% w" (, "% Y-% m" MAU), ?

3) DAU

, . , first_open firebase .

    #standardSQL
    SELECT
      FORMAT_TIMESTAMP(
          '%D',
          TIMESTAMP_MICROS(user_dim.first_open_timestamp_micros)) AS year_and_day, 
      COUNT(DISTINCT user_dim.app_info.app_instance_id) AS day_visitors 
    FROM `<<project-id>>.app_events_*`
    WHERE (_TABLE_SUFFIX = '20170917')
    ------ Inclusive for both the start-date and end-date
    AND user_dim.first_open_timestamp_micros BETWEEN 1505606400000000 AND 1505692799000000 
    GROUP BY year_and_day
    ORDER BY year_and_day;

# 1505606400000000  Sunday, September 17, 2017 12:00:00 AM
# 1505692799000000  Sunday, September 17, 2017 11:59:59 PM

:

• DAU, , , 17 .

Answer 1

:

SELECT
date,
SUM(CASE WHEN period = 1 THEN users END) as days_01,
SUM(CASE WHEN period = 7 THEN users END) as days_07,
SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
SELECT
dates.date as date,
periods.period as period,
COUNT(DISTINCT user_pseudo_id) as users
FROM 'firebase_project_table.events_*'
CROSS JOIN (SELECT event_date as date FROM 'firebase_project_table.events_*' WHERE event_name = 'user_engagement' GROUP BY date) as dates
CROSS JOIN (SELECT period FROM (SELECT 1 as period) UNION ALL
(SELECT 7 as period) UNION ALL (SELECT 30 as period)) as periods
WHERE dates.date >= event_date
AND SAFE_CAST(FLOOR(DATE_DIFF(PARSE_DATE("%Y%m%d",dates.date), PARSE_DATE("%Y%m%d",event_date), DAY)/periods.period) AS INT64) = 0
GROUP BY 1,2
)

GROUP BY date
ORDER By date