# How to Use Firebase Analytics with BigQuery

- Tool: Firebase
- Difficulty: Intermediate
- Time required: 15-20 min
- Compatibility: Firebase Blaze plan required, BigQuery free tier (1 TB queries/month)
- Last updated: March 2026

## TL;DR

To use Firebase Analytics with BigQuery, enable the BigQuery link in your Firebase project settings, which creates a daily export of raw event data into BigQuery tables. Each day generates an events_ table containing every event with its parameters, user properties, and device info. You can query this data with standard SQL to build custom reports, funnels, and cohort analyses that go far beyond the Firebase Console dashboards.

## Connecting Firebase Analytics to BigQuery for Advanced Analysis

The Firebase Analytics Console provides useful dashboards, but custom analysis requires raw data access. BigQuery integration exports every Analytics event with full parameter detail into BigQuery, where you can run SQL queries for funnel analysis, cohort retention, custom dimensions, and data joins with other sources. This tutorial covers enabling the export, understanding the table schema, writing practical queries, and keeping BigQuery costs under control.

## Before you start

- A Firebase project on the Blaze (pay-as-you-go) plan
- Firebase Analytics initialized and collecting events
- A Google Cloud project linked to your Firebase project
- Basic knowledge of SQL queries

## Step-by-step guide

### 1. Enable the BigQuery link in Firebase

Open the Firebase Console, go to Project Settings (gear icon), and click the Integrations tab. Find BigQuery and click Link. Select which data you want to export — at minimum, enable Google Analytics. Choose whether to include advertising identifiers. Firebase creates a BigQuery dataset named analytics_{project_number} and begins daily exports. The first export appears within 24 hours.

**Expected result:** A BigQuery dataset is created and daily Analytics event exports begin within 24 hours.

### 2. Understand the events_ table schema

Firebase creates one table per day with the naming pattern events_YYYYMMDD. Each row represents a single event. Key columns include event_name (the event type), event_timestamp (microseconds since epoch), user_pseudo_id (anonymous user ID), event_params (REPEATED RECORD of key-value pairs), user_properties (REPEATED RECORD), device (struct with category, os, browser), and geo (struct with country, region, city).

```
-- View the schema of an events table
SELECT column_name, data_type
FROM `your_project.analytics_123456789.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'events_20260327'
ORDER BY ordinal_position;

-- Key columns:
-- event_name          STRING      (e.g., 'page_view', 'purchase')
-- event_timestamp     INTEGER     (microseconds since epoch)
-- user_pseudo_id      STRING      (anonymous user identifier)
-- event_params        RECORD[]    (key-value parameters)
-- user_properties     RECORD[]    (user attributes)
-- device              RECORD      (category, os_version, browser)
-- geo                 RECORD      (country, region, city)
-- traffic_source      RECORD      (source, medium, campaign)
```

**Expected result:** You understand the table structure and can identify which columns to query for your analysis.

### 3. Query event counts and extract parameters

Event parameters are stored as a REPEATED RECORD, which means you need to UNNEST them to access specific values. Use a subquery or CROSS JOIN UNNEST to extract parameter values by their key. This is the most common pattern when working with Firebase Analytics in BigQuery.

```
-- Count events by type for the last 7 days
SELECT
  event_name,
  COUNT(*) AS event_count
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY event_name
ORDER BY event_count DESC
LIMIT 20;

-- Extract a specific parameter value from events
SELECT
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_url,
  COUNT(*) AS views
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'page_view'
  AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY event_name, page_url
ORDER BY views DESC
LIMIT 10;
```

**Expected result:** A table showing top events by count and top pages by page views for the specified date range.

### 4. Build a simple conversion funnel

Funnel analysis shows how many users complete a sequence of steps. Count distinct users at each step to see where drop-off occurs. This example tracks users from page_view to sign_up to purchase over the last 30 days.

```
WITH events AS (
  SELECT
    user_pseudo_id,
    event_name,
    event_timestamp
  FROM `your_project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name IN ('page_view', 'sign_up', 'purchase')
)
SELECT
  'Step 1: Page View' AS step,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM events WHERE event_name = 'page_view'
UNION ALL
SELECT
  'Step 2: Sign Up',
  COUNT(DISTINCT user_pseudo_id)
FROM events WHERE event_name = 'sign_up'
UNION ALL
SELECT
  'Step 3: Purchase',
  COUNT(DISTINCT user_pseudo_id)
FROM events WHERE event_name = 'purchase'
ORDER BY step;
```

**Expected result:** A three-row table showing the number of unique users at each funnel step, revealing the drop-off rate between steps.

### 5. Analyze user retention by cohort

Cohort retention shows what percentage of users return after their first visit. Group users by their first_visit date (cohort) and check which days they returned. This is one of the most valuable analyses for measuring product stickiness.

```
WITH first_visits AS (
  SELECT
    user_pseudo_id,
    DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS cohort_date
  FROM `your_project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260201' AND '20260328'
    AND event_name = 'first_visit'
  GROUP BY user_pseudo_id
),
return_visits AS (
  SELECT DISTINCT
    e.user_pseudo_id,
    DATE(TIMESTAMP_MICROS(e.event_timestamp)) AS visit_date
  FROM `your_project.analytics_123456789.events_*` e
  WHERE _TABLE_SUFFIX BETWEEN '20260201' AND '20260328'
    AND e.event_name = 'session_start'
)
SELECT
  fv.cohort_date,
  COUNT(DISTINCT fv.user_pseudo_id) AS cohort_size,
  COUNT(DISTINCT CASE
    WHEN DATE_DIFF(rv.visit_date, fv.cohort_date, DAY) = 1
    THEN rv.user_pseudo_id END) AS day_1_retained,
  COUNT(DISTINCT CASE
    WHEN DATE_DIFF(rv.visit_date, fv.cohort_date, DAY) = 7
    THEN rv.user_pseudo_id END) AS day_7_retained
FROM first_visits fv
LEFT JOIN return_visits rv USING (user_pseudo_id)
GROUP BY fv.cohort_date
ORDER BY fv.cohort_date;
```

**Expected result:** A table showing each daily cohort, its size, and how many users returned on day 1 and day 7.

### 6. Manage BigQuery costs

BigQuery charges per byte scanned (first 1 TB/month free, then $5/TB). Firebase Analytics tables can grow large quickly. Always filter by _TABLE_SUFFIX to limit the date range, select only the columns you need, and use the BigQuery dry-run feature to preview costs before running expensive queries.

```
-- Check table sizes to estimate costs
SELECT
  table_id,
  ROUND(size_bytes / (1024 * 1024 * 1024), 2) AS size_gb,
  row_count
FROM `your_project.analytics_123456789.__TABLES__`
ORDER BY size_bytes DESC
LIMIT 10;

-- Use dry run to estimate query cost before executing
-- In BigQuery Console: click "More" > "Estimate" before running
-- Or use bq CLI: bq query --dry_run "SELECT ..."
```

**Expected result:** You can estimate and control BigQuery costs by filtering date ranges and previewing query sizes before execution.

## Complete code example

File: `bigquery-analytics-queries.sql`

```sql
-- Firebase Analytics BigQuery Queries
-- Replace 'your_project.analytics_123456789' with your dataset

-- 1. Top events in the last 7 days
SELECT
  event_name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY event_name
ORDER BY event_count DESC
LIMIT 20;

-- 2. Extract page_view URLs with counts
SELECT
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_url,
  COUNT(*) AS views,
  COUNT(DISTINCT user_pseudo_id) AS unique_visitors
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'page_view'
  AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY page_url
ORDER BY views DESC
LIMIT 20;

-- 3. Daily active users over the last 30 days
SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
  COUNT(DISTINCT user_pseudo_id) AS daily_active_users
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY event_date
ORDER BY event_date;

-- 4. Users by country
SELECT
  geo.country AS country,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY country
ORDER BY users DESC
LIMIT 10;
```

## Common mistakes

- **Querying events_* without a _TABLE_SUFFIX filter, scanning all historical data and incurring high costs** — undefined Fix: Always add a WHERE _TABLE_SUFFIX BETWEEN clause to limit the date range. Use dry-run to estimate costs before executing.
- **Trying to access event parameters directly as columns instead of using UNNEST** — undefined Fix: Event parameters are stored as REPEATED RECORDs. Use (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'param_name') to extract values.
- **Expecting data in BigQuery immediately after enabling the link** — undefined Fix: The first daily export appears within 24 hours after linking. Historical data is not backfilled — only new events from the link date onward are exported.
- **Not being on the Blaze plan when trying to enable the BigQuery link** — undefined Fix: BigQuery integration requires the Firebase Blaze plan. Upgrade in the Firebase Console under the billing section before linking.

## Best practices

- Always filter by _TABLE_SUFFIX when using the wildcard events_* table to control query costs
- Select only the columns you need instead of SELECT * to reduce bytes scanned
- Use BigQuery dry-run to estimate query cost before executing expensive analyses
- Start with daily export only — enable streaming export only if you need near-real-time data
- Create saved queries or views in BigQuery for reports you run frequently
- Use UNNEST with a subquery to extract specific event parameters cleanly
- Schedule recurring queries with BigQuery Scheduled Queries to automate daily reports
- Monitor your BigQuery usage in the Google Cloud Console to stay within the free tier

## Frequently asked questions

### Does the Firebase BigQuery integration cost money?

The Firebase side is free on the Blaze plan. BigQuery charges for storage (first 10 GB/month free) and queries (first 1 TB/month free). For most moderate-traffic apps, the free tier is sufficient.

### Can I backfill historical Analytics data into BigQuery?

No. Only events from the date you enable the BigQuery link onward are exported. Enable the link as early as possible to avoid missing data.

### What is the difference between daily and streaming export?

Daily export creates one complete table per day (events_YYYYMMDD) with a 24-hour delay. Streaming export writes events to an intraday table (events_intraday_YYYYMMDD) in near-real-time but costs more in BigQuery storage.

### How do I access event parameter values in BigQuery?

Event parameters are stored as REPEATED RECORDs. Use a subquery with UNNEST: (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'your_param_key').

### Can I join Firebase Analytics data with other BigQuery datasets?

Yes. One of the biggest advantages of BigQuery is joining Analytics data with CRM data, advertising data, or backend databases for cross-source analysis.

### How long is Analytics data retained in BigQuery?

Data is retained indefinitely in BigQuery unless you set a table expiration policy. You can set partition expiration to automatically delete data older than a specified number of days.

### Can RapidDev help set up BigQuery analytics and build custom dashboards?

Yes. RapidDev can configure the BigQuery integration, write optimized SQL queries for your specific metrics, and build dashboards with Looker Studio or custom visualizations.

---

Source: https://www.rapidevelopers.com/firebase-tutorial/how-to-use-firebase-analytics-with-bigquery
© RapidDev — https://www.rapidevelopers.com/firebase-tutorial/how-to-use-firebase-analytics-with-bigquery