How to Use Firebase Analytics with BigQuery

1-- View the schema of an events table
2SELECT column_name, data_type
3FROM `your_project.analytics_123456789.INFORMATION_SCHEMA.COLUMNS`
4WHERE table_name = 'events_20260327'
5ORDER BY ordinal_position;
6
7-- Key columns:
8-- event_name          STRING      (e.g., 'page_view', 'purchase')
9-- event_timestamp     INTEGER     (microseconds since epoch)
10-- user_pseudo_id      STRING      (anonymous user identifier)
11-- event_params        RECORD[]    (key-value parameters)
12-- user_properties     RECORD[]    (user attributes)
13-- device              RECORD      (category, os_version, browser)
14-- geo                 RECORD      (country, region, city)
15-- traffic_source      RECORD      (source, medium, campaign)

1-- Count events by type for the last 7 days
2SELECT
3  event_name,
4  COUNT(*) AS event_count
5FROM `your_project.analytics_123456789.events_*`
6WHERE _TABLE_SUFFIX BETWEEN
7  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
8  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
9GROUP BY event_name
10ORDER BY event_count DESC
11LIMIT 20;
12
13-- Extract a specific parameter value from events
14SELECT
15  event_name,
16  (SELECT value.string_value FROM UNNEST(event_params)
17   WHERE key = 'page_location') AS page_url,
18  COUNT(*) AS views
19FROM `your_project.analytics_123456789.events_*`
20WHERE event_name = 'page_view'
21  AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
22GROUP BY event_name, page_url
23ORDER BY views DESC
24LIMIT 10;

1WITH events AS (
2  SELECT
3    user_pseudo_id,
4    event_name,
5    event_timestamp
6  FROM `your_project.analytics_123456789.events_*`
7  WHERE _TABLE_SUFFIX BETWEEN
8    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
9    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
10  AND event_name IN ('page_view', 'sign_up', 'purchase')
11)
12SELECT
13  'Step 1: Page View' AS step,
14  COUNT(DISTINCT user_pseudo_id) AS users
15FROM events WHERE event_name = 'page_view'
16UNION ALL
17SELECT
18  'Step 2: Sign Up',
19  COUNT(DISTINCT user_pseudo_id)
20FROM events WHERE event_name = 'sign_up'
21UNION ALL
22SELECT
23  'Step 3: Purchase',
24  COUNT(DISTINCT user_pseudo_id)
25FROM events WHERE event_name = 'purchase'
26ORDER BY step;

1WITH first_visits AS (
2  SELECT
3    user_pseudo_id,
4    DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS cohort_date
5  FROM `your_project.analytics_123456789.events_*`
6  WHERE _TABLE_SUFFIX BETWEEN '20260201' AND '20260328'
7    AND event_name = 'first_visit'
8  GROUP BY user_pseudo_id
9),
10return_visits AS (
11  SELECT DISTINCT
12    e.user_pseudo_id,
13    DATE(TIMESTAMP_MICROS(e.event_timestamp)) AS visit_date
14  FROM `your_project.analytics_123456789.events_*` e
15  WHERE _TABLE_SUFFIX BETWEEN '20260201' AND '20260328'
16    AND e.event_name = 'session_start'
17)
18SELECT
19  fv.cohort_date,
20  COUNT(DISTINCT fv.user_pseudo_id) AS cohort_size,
21  COUNT(DISTINCT CASE
22    WHEN DATE_DIFF(rv.visit_date, fv.cohort_date, DAY) = 1
23    THEN rv.user_pseudo_id END) AS day_1_retained,
24  COUNT(DISTINCT CASE
25    WHEN DATE_DIFF(rv.visit_date, fv.cohort_date, DAY) = 7
26    THEN rv.user_pseudo_id END) AS day_7_retained
27FROM first_visits fv
28LEFT JOIN return_visits rv USING (user_pseudo_id)
29GROUP BY fv.cohort_date
30ORDER BY fv.cohort_date;

1-- Check table sizes to estimate costs
2SELECT
3  table_id,
4  ROUND(size_bytes / (1024 * 1024 * 1024), 2) AS size_gb,
5  row_count
6FROM `your_project.analytics_123456789.__TABLES__`
7ORDER BY size_bytes DESC
8LIMIT 10;
9
10-- Use dry run to estimate query cost before executing
11-- In BigQuery Console: click "More" > "Estimate" before running
12-- Or use bq CLI: bq query --dry_run "SELECT ..."