bigquery
/bigquery
Query the GA4 BigQuery export (events_* tables) through a BigQuery MCP server and answer questions from raw event data. Use when asked to run or write SQL against GA4 export tables, unnest event_params or items, build sessions, landing pages or session-level source/medium from raw events, reconcile BigQuery numbers with the GA4 UI, estimate or cap query cost with a dry run, or find which dataset holds a property's export. Do NOT use for live GA4 reports via the Data API or for setting up the BigQuery link and export - use google-analytics; for BigQuery work unrelated to GA4 data, use Google's
- Author
- henkisdabro ↗
- Repository
- wookstar-claude-plugins ↗
- Licence
- MIT
- Last repository change
- 04.10.2026
Assessment and security
- Files in this package
- 2
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 1
No signals detected. Repository changed on 04.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
Query the GA4 BigQuery export (events_* tables) through a BigQuery MCP server and answer questions from raw event data. Use when asked to run or write SQL against GA4 export tables, unnest event_params or items, build sessions, landing pages or session-level source/medium from raw events, reconcile BigQuery numbers with the GA4 UI, estimate or cap query cost with a dry run, or find which dataset holds a property's export. Do NOT use for live GA4 reports via the Data API or for setting up the BigQuery link and export - use google-analytics; for BigQuery work unrelated to GA4 data, use Google's
Arguments
/bigquery [question about GA4 export data]
Original author description
Query the GA4 BigQuery export (events_* tables) through a BigQuery MCP server and answer questions from raw event data. Use when asked to run or write SQL against GA4 export tables, unnest event_params or items, build sessions, landing pages or session-level source/medium from raw events, reconcile BigQuery numbers with the GA4 UI, estimate or cap query cost with a dry run, or find which dataset holds a property's export. Do NOT use for live GA4 reports via the Data API or for setting up the BigQuery link and export - use google-analytics; for BigQuery work unrelated to GA4 data, use Google's
How it works
- Step 1 - Confirm a BigQuery MCP server
- Step 2 - Locate the export
- Step 3 - Write the query with the cost rules
- Step 4 - Dry run, then run
- Step 5 - Present
- Sessionisation
- Time and table gotchas
- UI vs BigQuery
- Access and troubleshooting
SKILL.md preview
---
name: bigquery
description: Query the GA4 BigQuery export (events_* tables) through a BigQuery MCP server and answer questions from raw event data. Use when asked to run or write SQL against GA4 export tables, unnest event_params or items, build sessions, landing pages or session-level source/medium from raw events, reconcile BigQuery numbers with the GA4 UI, estimate or cap query cost with a dry run, or find which dataset holds a property's export. Do NOT use for live GA4 reports via the Data API or for setting up the BigQuery link and export - use google-analytics; for BigQuery work unrelated to GA4 data, use Google's bigquery-data-analytics plugin directly.
argument-hint: "[question about GA4 export data]"
---
# GA4 BigQuery Export Querying
The request: **$ARGUMENTS** - treat it as the query brief. With no arguments, ask what the user wants to know and over which dates.
The export's setup, table naming and full schema live in the google-analytics skill: read `${CLAUDE_SKILL_DIR}/../google-analytics/references/bigquery.md` whenever you need a column, record or event_params key you have not already confirmed. Ready-made SQL (sessions, landing pages, channels, ecommerce, funnels) is in `${CLAUDE_SKILL_DIR}/references/query-patterns.md` - start from it rather than writing from scratch.
## Step 1 - Confirm a BigQuery MCP server
Look for BigQuery tools (`execute_sql`, `list_dataset_ids`, `get_table_info`). Two official Google options supply them:
| Server | How | Cost controls |
|--------|-----|---------------|
| `bigquery-data-analytics` plugin (Google, MCP Toolbox over stdio, Application Default Credentials) | `/plugin install bigquery-data-analytics@claude-plugins-official` | `execute_sql` accepts `dry_run: true`; env `BIGQUERY_MAXIMUM_BYTES_BILLED` sets a hard cap; results return 50 rows unless `BIGQUERY_MAX_QUERY_RESULT_ROWS` is raised |
| Remote BigQuery MCP server `https://bigquery.googleapis.com/mcp` (OAuth) | `claude mcp add --transport http` - see the plugin README | No dry-run parameter; prefer `execute_sql_readonly`; 3-minute timeout, 3,000-row cap |
Done when a BigQuery tool is callable. If none is, point the user to the plugin README's "BigQuery querying" section and stop.
## Step 2 - Locate the export
GA4 datasets are named `analytics_<property_id>`. Use `list_dataset_ids` (pass a project ID if the export lives outside the server's default project), then `list_table_ids` to find the first and last `events_` shard and whether `events_intraday_` tables exist. Always write fully qualified names: `` `project-id.analytics_123456789.events_*` ``.
Done when you know the project, dataset, the date range the shards cover, and the property time zone if timing matters (`get_table_info`, or ask).
## Step 3 - Write the query with the cost rules
The export is **date-sharded** (one table per day), not partitioned, so the shard filter is the partition filter. Every query against `events_*`:
1. Filters `_TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'`. A filter on `event_date` alone scans every shard.
2. Selects named columns only - `SELECT *` reads every record, including `items` and `event_params`.
3. Filters `event_name` as early as possible.
4. Extracts parameters with a scalar subquery: `(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')`. Use `CROSS JOIN UNNEST(...)` only when you need one row per parameter or item.
5. Reads the right value column: `ga_session_id`, `engagement_time_msec`, `entrances` are `int_value`; URLs, titles and UTMs are `string_value`; `session_engaged` can be either, so `COALESCE(value.string_value, CAST(value.int_value AS STRING))`. A wrong column returns NULL silently, never an error.
Done when the SQL meets all five rules.
## Step 4 - Dry run, then run
Dry-run first: `execute_sql` with `dry_run: true` (Toolbox), or `bq query --use_legacy_sql=false --dry_run '<sql>'` when only the remote server is available. Report bytes processed and the approximate on-demand cost (bytes / 2^40 x US$6.25 - confirm current pricing if the user is cost-sensitive).
- Under ~10 GB: run it.
- Over ~10 GB, or above any limit the user set: show the estimate and ask before running, offering a narrower date range or fewer columns.
- Explore on one or two days with `LIMIT 100` before widening the range.
Done when the query has run and you have checked the row count against the row cap of the server (Step 1) - a result of exactly 50 or 3,000 rows is truncated, so aggregate further or raise the cap.
## Step 5 - Present
Lead with the answer, then a compact table, then the SQL in a fenced block so the user can rerun it. State the date range, the time zone assumption and anything excluded (intraday data, consent-denied traffic).
## Sessionisation
A session is `user_pseudo_id` + `ga_session_id`. `ga_session_id` alone repeats across users, and `CONCAT` needs strings:
```sql
CONCAT(user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key
```
- **Session counts**: `COUNT(DISTINCT session_key)` across the whole range. Sessions crossing midnight appear in two daily shards, so summing per-day counts overcounts.
- **Engaged sessions**: a session where any event has `session_engaged = '1'`; engagement time is `SUM(engagement_time_msec)` per session.
- **Landing page**: `page_location` of the `page_view` with `entrances = 1`, or the session's first `page_view` by `event_timestamp` (break ties with `batch_page_id`, `batch_ordering_id`, `batch_event_index`).
- **Session source/medium**: `session_traffic_source_last_click.cross_channel_campaign` matches GA4's session attribution where the column exists. For older shards, take `collected_traffic_source` (or the `source`/`medium` params) from the session's first event, and treat NULL as `(direct) / (none)`.
- **`traffic_source`** is the user's first-ever acquisition - use it for user acquisition, never for sessions.
## Time and table gotchas
- `event_date` is in the property's time zone; `event_timestamp` is UTC microseconds. Convert with `DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Area/City')`.
- `events_*` also matches `events_intraday_*`, but those suffixes start with `intraday_`, so a `BETWEEN` on digits excludes them. To include today, `UNION ALL` the intraday table explicitly - and leave acquisition out, since intraday lacks new-session traffic source data.
- Daily shards can be rewritten for about 72 hours after the day ends; flag figures for the last three days as provisional.
## UI vs BigQuery
Expect BigQuery counts to differ from the GA4 UI by a few percent and explain why rather than forcing a match: the UI estimates users and sessions with HyperLogLog++, applies thresholding and consent-mode modelling, and may use Google signals; the export holds only observed, consented events, exactly counted. Large gaps usually mean a time zone mismatch, an intraday/daily mix, or a different user definition (`user_id` vs `user_pseudo_id`).
## Access and troubleshooting
Both servers authenticate as the user. Query jobs run in, and are billed to, the server's default project (`BIGQUERY_PROJECT` for the plugin), which may differ from the project that holds the export.
| Symptom | Fix |
|---------|-----|
| `Reauthentication is needed` / `RefreshError` | `gcloud auth application-default login`, then restart MCP servers with `/mcp` |
| Quota-project warning or `USER_PROJECT_DENIED` | `gcloud auth application-default set-quota-project YOUR_PROJECT_ID` with a project you can bill to |
| `403 Access Denied` running a query | Need BigQuery Job User (`roles/bigquery.jobUser`) on the billing project and BigQuery Data Viewer (`roles/bigquery.dataViewer`) on the export dataset; the remote server also needs MCP Tool User (`roles/mcp.toolUser`) |
| `403` reading `__TABLES__` | Expected for some roles - use `list_table_ids` |
| `Unrecognized name` on a column | The column predates or postdates the shard range (for example `session_traffic_source_last_click`) - check the schema with `get_table_info` on the earliest shard |
Files in this package (2)
- SKILL.md 8 KB
- references/query-patterns.md 7 KB
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · henkisdabro/wookstar-claude-plugins | 100 | MIT |
| costory-io/costory-finops-mcp-skills | 100 | Apache-2.0 |
| builderio/agent-native | 100 | MIT |
| builderio/agent-native | 100 | MIT |
| wraith-proxy/wraith-skills | 100 | MIT |
| costory-io/costory-finops-mcp-skills | 100 | Apache-2.0 |
| G1Joshi/Agent-Skills | 100 | MIT |
| jpantsjoha/googlecloud-plugin | 100 | MIT |
Database snapshot: 13.11.2026 · package version 5f430552c1eb (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 90ga4
Write correct BigQuery SQL against the Google Analytics 4 (GA4) BigQuery export (analytics_<id>.events_YYYYMMDD tables). Use when the user asks about GA4, Google Analytics 4, or an analytics_ dataset in BigQuery - sessions, users, engagement, conversions, funnels, traffic… - 95google-bigquery
Google BigQuery API integration with managed OAuth. Run SQL queries, manage datasets and tables, and analyze data at scale. Use this skill when users want to query BigQuery data, create or manage datasets/tables, run analytics jobs, or work with BigQuery resources. For other… - 100ga4-bigquery-query-writer
Writes, explains, fixes and runs BigQuery SQL on the GA4 export for sessions, page paths, ordered funnels, multi-touch attribution, cohort LTV and Google Ads cost per purchase. Use when asked for GA4 BigQuery queries or why BigQuery and GA4 numbers differ. - 92write-script-bigquery
MUST use when writing BigQuery queries. - 100bigquery-basics
Manages datasets, tables, and jobs in BigQuery, and integrates with BigQuery ML and Gemini for advanced data analytics and AI-driven insights. Use for SQL queries, resource management, data ingestion, or AI applications on BigQuery. - 100bigquery-basics
Manages datasets, tables, and jobs in BigQuery, and integrates with BigQuery ML and Gemini for advanced data analytics and AI-driven insights. Use when you need to interact with BigQuery, run SQL queries, manage BigQuery resources, or leverage BigQuery's built-in ML…