ga4-bigquery-export

Wire GA4 → BigQuery for unsampled, queryable event-level data. Covers the one-time export setup, the events_YYYYMMDD table schema, partitioning + clustering, and the SQL patterns for the reports the Data API can't do well (true cohort retention, custom-event attribution, large date ranges). Trigger with "GA4 BigQuery", "GA4 to BQ", "event-level GA4 data", "unsampled GA4", "GA4 export setup", "GA4 SQL".

Ir para a instalação

Repositório: jeremylongshore/tons-of-skills-marketplace
Última atividade na origem: 24 de agosto de 2026 às 03:47
Idioma detectado do SKILL.md: inglês
Estrelas: 2.677
Forks: 392

Opções de instalação

Por padrão, está selecionado o prompt que primeiro revisa a origem. Você pode mudar para um comando direto ou baixar uma cópia local.

Revise os arquivos de origem

Leia o SKILL.md e os arquivos complementares exibidos pelo SkillsMP antes de decidir se vai instalar.

Voltar à pesquisa Voltar à instalação

name	ga4-bigquery-export
description	Wire GA4 → BigQuery for unsampled, queryable event-level data. Covers the one-time export setup, the events_YYYYMMDD table schema, partitioning + clustering, and the SQL patterns for the reports the Data API can't do well (true cohort retention, custom-event attribution, large date ranges). Trigger with "GA4 BigQuery", "GA4 to BQ", "event-level GA4 data", "unsampled GA4", "GA4 export setup", "GA4 SQL".
allowed-tools	Bash(bq:), Bash(gcloud:)
version	1.3.0
license	MIT
author	Jeremy Longshore <jeremy@intentsolutions.io>
tags	["saas","analytics","google-analytics","ga4","bigquery"]
compatibility	Designed for Claude Code

GA4 → BigQuery Export

Overview

The Data API is good but bounded — sampled past a threshold, capped at ~150 dimensions, no cohort joins. BigQuery export gives you the raw event stream as SQL-queryable tables, free at the standard GA4 tier (up to 1M events/day), with no sampling and full event payloads.

This skill: one-time setup, then the SQL recipes for what the Data API can't do well.

Prerequisites

A GA4 property for which you can create a BigQuery link.
A Google Cloud project with BigQuery enabled and permission to grant project IAM roles.
The bq and gcloud CLIs authenticated to that project; use the service account created by ga4-auth-setup for unattended queries.

Instructions

Setup — one time

1. Link your GA4 property to a GCP project

In https://analytics.google.com/:

Admin → Property column → BigQuery Links
Create a link → pick your GCP project
Data location: pick the BQ region for the export tables (US multi-region is fine for most cases; EU if you need data residency)
Export type:
- Daily — single events_YYYYMMDD table per day, written ~24h after midnight. Fine for most reporting.
- Streaming — events_intraday_YYYYMMDD table written ~near-real-time. Costs more, useful for hot ops dashboards.
- Most setups: enable both. Streaming for "today", daily for everything else.
Include advertising identifiers — uncheck unless you specifically need device-graph data (most don't)
Save

The first daily table lands within 24h. The first streaming table is near-immediate. After that you have a new events_YYYYMMDD every day forever, no maintenance needed.

2. Verify the export is working

PROJECT=your-gcp-project
DATASET=analytics_123456789       # auto-named after the property ID

bq ls "$PROJECT:$DATASET" 2>&1 | head -10
# Expect: events_YYYYMMDD tables + events_intraday_YYYYMMDD if streaming enabled

If you see no dataset, the link is configured but the first export hasn't fired yet — wait 24h.

SA_EMAIL=ga4-reader@your-project.iam.gserviceaccount.com
gcloud projects add-iam-policy-binding "$PROJECT" \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.dataViewer"
gcloud projects add-iam-policy-binding "$PROJECT" \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.jobUser"

Column	Type	Notes
`event_date`	`STRING`	`'YYYYMMDD'`
`event_timestamp`	`INT64`	Microseconds since epoch
`event_name`	`STRING`	`page_view`, `session_start`, `purchase`, custom event names
`event_params`	`ARRAY<STRUCT<key, value>>`	All event parameters; value is itself a union STRUCT (`string_value`, `int_value`, `float_value`, `double_value`)
`user_pseudo_id`	`STRING`	GA4's cookie-based user ID (anonymous unless `user_id` is set)
`user_id`	`STRING`	If you set `user_id` via `gtag('set', {user_id: '...'})`
`user_properties`	`ARRAY<STRUCT<key, value>>`	Same shape as event_params
`device.*`	`STRUCT`	category / os / browser / model
`geo.*`	`STRUCT`	country / region / city
`traffic_source.*`	`STRUCT`	source / medium / campaign of FIRST session (not current)
`session_traffic_source_last_click.*`	`STRUCT`	source / medium of CURRENT session — what you usually want
`ga_session_id` (param)	`INT64`	Pulled via `(SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id')`
`ga_session_number` (param)	`INT64`	Same idiom — 1 = first session, 2 = second, etc.

-- Pull the page_location for every page_view
SELECT
  TIMESTAMP_MICROS(event_timestamp) AS ts,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  user_pseudo_id
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260513' AND '20260520'
  AND event_name = 'page_view'
LIMIT 100;

WITH first_seen AS (
  SELECT
    user_pseudo_id,
    DATE(MIN(TIMESTAMP_MICROS(event_timestamp))) AS first_date
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260401' AND '20260520'
  GROUP BY user_pseudo_id
),
activity AS (
  SELECT
    user_pseudo_id,
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS active_date
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260401' AND '20260520'
  GROUP BY user_pseudo_id, active_date
)
SELECT
  DATE_TRUNC(f.first_date, WEEK) AS cohort_week,
  DATE_DIFF(a.active_date, f.first_date, WEEK) AS weeks_since,
  COUNT(DISTINCT a.user_pseudo_id) AS active_users
FROM first_seen f
JOIN activity a USING (user_pseudo_id)
GROUP BY cohort_week, weeks_since
ORDER BY cohort_week, weeks_since;

WITH sessions AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
    MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
    COUNT(*) AS event_count,
    COUNTIF(event_name = 'page_view') AS pageviews,
    ANY_VALUE(device.category) AS device,
    ANY_VALUE(geo.country) AS country,
    ANY_VALUE(session_traffic_source_last_click.manual_campaign.source) AS source,
    ANY_VALUE(session_traffic_source_last_click.manual_campaign.medium) AS medium,
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260513' AND '20260520'
  GROUP BY user_pseudo_id, session_id
  HAVING session_id IS NOT NULL
)
SELECT * FROM sessions
ORDER BY session_start DESC
LIMIT 100;

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  session_traffic_source_last_click.manual_campaign.source AS source,
  COUNT(*) AS pageviews,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260513' AND '20260520'
  AND event_name = 'page_view'
GROUP BY page, source
HAVING pageviews > 10        -- filter the long tail
ORDER BY pageviews DESC
LIMIT 50;

Scenario	~TB / query
Small site, 1k events/day, 30-day window	< 1 GB
Medium site, 100k events/day, 30-day window	~10 GB
Large site, 1M events/day, 30-day window	~100 GB
Same scenarios but querying the full 14-month history	12-15x the above

Table prefix	When
`events_YYYYMMDD`	Stable historical data. Use for reports, analysis.
`events_intraday_YYYYMMDD`	"Today" data, near-realtime. Schema is identical, but rows may not be deduped yet.
`events_*` (wildcard)	When the date range crosses both. BQ will scan both transparently.

Issue	Why
Numbers don't match the GA4 UI exactly	UI uses different identity-stitching for cross-device users; raw events are pre-stitching. Off by a few % is expected.
`event_params` UNNEST returns NULL	The key doesn't exist on that event. Always wrap in `(SELECT ... LIMIT 1)` so missing-key events return NULL instead of erroring.
Query scans way more than expected	Missing `_TABLE_SUFFIX` filter, OR using `events_*` without a `_TABLE_SUFFIX BETWEEN` clause
`events_intraday_*` has 2x the rows you'd expect	Intraday tables aren't deduped; the same event may appear twice if it was buffered + retried. The daily rollup dedupes.
No `user_id` even though I set it on the front-end	`user_id` is the explicit identifier; `user_pseudo_id` is the auto-generated cookie ID. If `user_id` is consistently NULL, the `gtag('set', {user_id: ...})` call is firing AFTER the event you're checking, or it's set on a property that the export doesn't pull.