BigQuery Monthly Active Users?

Question

I'm currently working off a query from this post. That query is written in Legacy SQL and will not work in my environment. I've modified the query to use the modern SQL functions and updated the SELECT date as date to use timestamp_micros.

I should also mention that the rows I'm trying to select are coming in from Firebase Analytics.

My Query:

SELECT 
    FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(event.timestamp_micros)) as 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
        FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(event.timestamp_micros)) as date,
        periods.period as period,
        COUNT(DISTINCT user_dim.app_info.app_instance_id) as users
    FROM `com_sidearm_fanapp_uiowa_IOS.*` as activity
    CROSS JOIN
        UNNEST(event_dim) as event  
    CROSS JOIN (
        SELECT 
            FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(event.timestamp_micros)) as date
        FROM `com_sidearm_fanapp_uiowa_IOS.*`
        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(dates.date, activity.date, DAY)/periods.period) AS INT64) = 0
    GROUP BY 1,2
)
CROSS JOIN 
    UNNEST(event_dim) as event
GROUP BY date
ORDER BY date DESC

For others visiting this post, the BQ export schema is defined here. — Kato

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-01-19T14:44:31

Column name period is ambiguous at [24:13] error.

to fix this particular error - you should fix below

    CROSS JOIN (
        SELECT 
            period 
        FROM 
            (SELECT 7 as period), 
            (SELECT 14 as period), 
            (SELECT 30 as period)
    ) as periods

so it should look like:

    CROSS JOIN (
        SELECT 
            period 
        FROM 
            (SELECT 7 as period UNION ALL
            SELECT 14 as period UNION ALL
            SELECT 30 as period)
    ) as periods

Answer on your updated question

Try below. I didn't have chance to test it but hope it can help you fix your query

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
          event.timestamp_micros as date, 
           user_dim.app_info.app_instance_id as user
      FROM `yourTable` CROSS JOIN UNNEST(event_dim) as event  
    ) as activity
    CROSS JOIN (
        SELECT 
            event.timestamp_micros  as date
        FROM `yourTable` 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

BigQuery Monthly Active Users?

1 Answers