How to get the monthly 7-day active users?

Question

In my database I have two fields that are used to identify a user, timestamp and instance_id. I want to be able to get the monthly 7-day active users from this data. I have tried the following query but it just returns the same timestamp and 1 for every row.

SELECT 
    FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(date)) as target,
    SUM(CASE WHEN period = 7  THEN users END) as days_07
    # SUM(CASE WHEN period = 14 THEN users END) as days_14,
    # SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
    SELECT 
        activity.date as date,
        periods.period as period,
        COUNT(DISTINCT user) as users
    FROM (
        SELECT
            event.timestamp_micros as date, 
            user_dim.app_info.app_instance_id as user
        FROM `hidden.*` 
        CROSS JOIN 
            UNNEST(event_dim) as event  
    ) as activity
    CROSS JOIN (
        SELECT 
            event.timestamp_micros  as date
        FROM `hidden.*` 
        CROSS JOIN 
            UNNEST(event_dim) as event 
        GROUP BY event.timestamp_micros
    ) as dates
    CROSS JOIN (
        SELECT period 
        FROM 
            (
                SELECT 7 as period 
                # UNION ALL 
                # SELECT 14 as period 
                # UNION ALL
                # SELECT 30 as period
            )
    ) as periods
    WHERE 
        dates.date >= activity.date 
    AND 
        SAFE_CAST(FLOOR(TIMESTAMP_DIFF(TIMESTAMP_MICROS(dates.date), TIMESTAMP_MICROS(activity.date), DAY)/periods.period) AS INT64) = 0
    GROUP BY 1,2
)
GROUP BY date
ORDER BY date DESC

I'm not too sure where to go from here and it's quite challenging to me because I'm not the best with SQL. Any assistance at all would be great. Thanks!

I should also mention that these queries are going to be run within BigQuery and the data is being exported to BigQuery from Firebase.

Mikhail Berlyant · Accepted Answer · 2017-01-19T19:50:52.340

2

Try below

SELECT 
    DATE,
    SUM(CASE WHEN period = 7  THEN users END) AS days_07,
    SUM(CASE WHEN period = 14 THEN users END) AS days_14,
    SUM(CASE WHEN period = 30 THEN users END) AS days_30
FROM (
    SELECT 
        activity.date AS DATE,
        periods.period AS period,
        COUNT(DISTINCT user) AS users
    FROM (
      SELECT DISTINCT
          DATE(TIMESTAMP_MICROS(event.timestamp_micros))  AS DATE, 
          user_dim.app_info.app_instance_id AS user
      FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607` 
      CROSS JOIN UNNEST(event_dim) AS event  
    ) AS activity
    CROSS JOIN (
        SELECT DATE(TIMESTAMP_MICROS(event.timestamp_micros))  AS DATE
        FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`
        CROSS JOIN UNNEST(event_dim) AS event 
        GROUP BY 1
    ) AS dates
    CROSS JOIN (
        SELECT period FROM 
            (SELECT 7 AS period UNION ALL 
            SELECT 14 AS period UNION ALL
            SELECT 30 AS period)
    ) AS periods
    WHERE dates.date >= activity.date 
    AND SAFE_CAST(FLOOR(DATE_DIFF(dates.date, activity.date, DAY)/periods.period) AS INT64) = 0
    GROUP BY 1,2
)
GROUP BY DATE
ORDER BY DATE DESC

edited Jan 19 '17 at 19:50

answered Jan 19 '17 at 19:23

Mikhail Berlyant

165,386
8
154
230

That works great! Since you seem to be the main BigQuery guy around here, could you quickly explain what the billing tiers are? I've tried reading the documentation but it's quite confusing and doesn't make much sense. This query in particular requires tier 8. – Joe Scotto Jan 19 '17 at 19:28
you can read about billing tier in documentation for [High-Compute queries](https://cloud.google.com/bigquery/pricing#high-compute). it gives quite a detail picture on subject – Mikhail Berlyant Jan 19 '17 at 19:42
Thank you, I didn't see this link before. – Joe Scotto Jan 19 '17 at 19:42
i think I missed "fix" in one place and it still did grouping by micros vs. date in dates sub-query - please try and let us know if billing tier is still 8 – Mikhail Berlyant Jan 19 '17 at 19:52
I remove the 14 and 30 day queries, now it's asking for tier 3 which I don't think is too bad. – Joe Scotto Jan 19 '17 at 19:52

How to get the monthly 7-day active users?

1 Answers1