BigQuery Firebase Event Logs Grouped By User Id

Question

Question: I want to pull a stream of Firebase event-data grouped by user id and ordered by time of occurrence for my android users. I created the two scripts below, but unfortunately in both I can't seem to get the last part correct, which is to, successfully group all the app_instance_ids first irregardless of timestamp. Should I perhaps look at using distinct user_ids instead?

Unsuccessful attempt 1:

SELECT
  d.userid,
  c.ev_timestamp,
  c.ev_name
FROM (SELECT 
        user_dim.app_info.app_instance_id as userid
      FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
      WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      GROUP BY 1) AS d
  LEFT JOIN (SELECT user_dim.app_info.app_instance_id as userid,
            event.timestamp_micros as ev_timestamp,
            event.name as ev_name
            FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
            WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
            AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000) AS c
  ON d.userid = c.userid
ORDER BY 2 ASC
LIMIT 1000;

Unsuccessful attempt 2:

SELECT
  d.userid,
  d.ev_timestamp,
  c.ev_name
FROM (SELECT 
        user_dim.app_info.app_instance_id as userid,
        event.timestamp_micros as ev_timestamp
      FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
      WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      GROUP BY 1,2
      ORDER BY 2 ASC) AS d
  LEFT JOIN (SELECT user_dim.app_info.app_instance_id as userid,
            event.timestamp_micros as ev_timestamp,
            event.name as ev_name
            FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
            WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
            AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000) AS c
  ON d.userid = c.userid AND d.ev_timestamp = c.ev_timestamp
#ORDER BY 2 ASC
LIMIT 1000;

Correct answer (Amod's answer converted to New Export Schema):

SELECT user_pseudo_id, event_timestamp, event_name
FROM `xxxx.analytics_xxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20180630' AND '20180702'
AND user_first_touch_timestamp BETWEEN 1530453600000000 AND 1530468000000000
AND platform = "ANDROID"
ORDER BY 1,2 ASC
LIMIT 1000

if you remove c.ev_name from query does it return correct order? — Amod Gokhale, Jul 31 '18 at 05:21
Yes, it does. But how am I joining it wrong, because I need that column in my data-set. — d_-, Jul 31 '18 at 07:45
try this - in first script add GROUP BY 2 after last ORDER BY 2 ASC ( reason chk answered query - https://stackoverflow.com/questions/5355585/how-to-sort-order-of-left-join-in-sql-query ) — Amod Gokhale, Jul 31 '18 at 09:51
You can't run a GROUP BY after ORDER BY in this script, neither can I run the GROUP BY in front of the last "ORDER BY 2 ASC" of Script 1, because you have to group by all three selected fields at the top of Script 1. Have you tried running the script 1 yourself? I used a publicly accessible data-set if you want to give it run :) — d_-, Jul 31 '18 at 15:22

Amod Gokhale · Accepted Answer · 2018-07-31T16:47:13.553

try this

    SELECT user_dim.app_info.app_instance_id as userid, X.timestamp_micros,X.name as ev_name,X
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS X
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      and user_dim.app_info.app_instance_id like 'C4%'
      order by 1,2 ASC

or

SELECT user_dim.app_info.app_instance_id as userid, X.timestamp_micros,X.name as ev_name
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS X
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      order by 1,2 ASC

BigQuery Firebase Event Logs Grouped By User Id

1 Answers1