Why Claude gets your GA4 data wrong (and the free skill that fixes it)

Claude can write GA4 BigQuery SQL that runs on the first try and returns the wrong number. We ran the usual mistakes against Google's public GA4 sample: one undercounts engaged sessions by 39%, another reports revenue at 4.4x the real figure. Then we packaged the fixes as a free Claude skill that is tested against the same data.

A recent r/bigquery thread asked whether anyone connected GA4 to BigQuery and then barely used it. Plenty had. The export gets switched on, the tables grow every day, and the reporting stays in the GA4 UI because the SQL is painful: UNNEST on every query, and a bill that grows if you query it carelessly. One reply said the thing that changed it for them was connecting BigQuery to Claude Code.

That works, with a catch. Claude knows the GA4 export in general terms. It doesn't know that half the parameters in your event_params array change type between events, and nothing in the schema tells it. The query looks right and the number is off.

Trap 1: the same parameter arrives as a string and as an integer

Every GA4 event carries an event_params array. Each entry has a key and four possible value columns: string_value, int_value, float_value, double_value. You'd expect a parameter to pick one and stick with it. In the public sample, several don't:

Both types show up on the same days, so this isn't one old tracking change you can filter out. Here is the engaged-sessions query most people write first, because a quick look at the data shows session_engaged as the string '1':

-- December 2020: 72,239 engaged sessions
COUNT(DISTINCT IF(
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'session_engaged') = '1',
  session_key, NULL))

And the version that reads both columns:

-- December 2020: 117,633 engaged sessions
COUNT(DISTINCT IF(
  (SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING))
   FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1',
  session_key, NULL))

The first query misses 45,394 engaged sessions, 39% of the real count, and nothing about it looks broken. Revenue has the same problem. Summing the value parameter from double_value gives $110,042.55 for December purchases. Coalescing the int and double columns gives $160,559.55. The typed ecommerce.purchase_revenue column says $160,555.00, which is why the skill tells Claude to prefer it.

Trap 2: unnesting items multiplies your revenue

Product data sits in another array, items, one entry per product in the event. The moment you UNNEST(items), every event-level column repeats once per product. So this, which looks like a reasonable "revenue by product" starting point, is wrong in an expensive way:

-- 711,567.00
SELECT SUM(ecommerce.purchase_revenue)
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items)
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231'
  AND event_name = 'purchase'

December's real revenue is $160,555. The query reports 4.4x that. The fix is simple once you know: sum the order-level column without unnesting, or sum item.item_revenue after unnesting when you actually want product-level revenue. A related trap sits right next to it: December has 2,434 purchase events but only 2,311 distinct transaction IDs, so counting purchase events overstates orders too.

Trap 3: the date filter that scans everything

The GA4 export is one table per day (events_20201201, events_20201202, ...), queried with a wildcard. It is sharded, not partitioned, so a filter on the event_date column does nothing to limit what BigQuery reads. Only _TABLE_SUFFIX does. Dry runs for the same week and the same two columns:

That's 12x on a sample with three months of data. On a property with three years of exports, the event_date version reads every table you have, every time. The answer is the same, and you only notice when the bill comes.

The rest of the list

Those three cost the most. The skill covers the others too, each tested or sourced:

• ga_session_id alone is not a session. It's a start time in seconds and collides across users: 129,376 "sessions" vs 133,368 real ones.
• traffic_source is the user's first touch and never changes. For "sessions by source", you need the session-level parameters. They disagree for about 20% of December sessions.
• event_timestamp is UTC, but tables and event_date follow the property's time zone. Shifting the sample's December events by the Pacific offset moves 34% of them to a different day, which is the size of the error when a US property is grouped by UTC day.
• Intraday tables are staging tables that Google deletes once the daily table is complete. Don't union them with daily data for the same date.
• The export has no history from before you linked it. GA4's own 14-month retention limits explorations, so if you need event-level history, link now.
• Daily tables keep updating for about two days, and whole days can go missing (standard properties over the 1 million events a day limit get their export paused). A missing day looks exactly like a traffic drop.
• Your numbers won't match the GA4 UI exactly. The UI estimates users with HyperLogLog++, thresholds small rows, and includes modeled consent-mode data the export doesn't have.

What's in the skill

A Claude skill is a folder with a SKILL.md file that tells Claude when to use it, plus whatever reference files it needs. Claude reads the short version up front and opens the rest when the question calls for it. The GA4 pack has:

1. SKILL.md with the eight rules above that matter most, so Claude applies them before writing any SQL.
2. schema.md, including which value column each common parameter uses.
3. gotchas.md, every trap with wrong and right SQL and the sample numbers.
4. Fifteen queries: sessions, new vs returning users, engagement rate, an ordered purchase funnel, first-touch and session-level traffic, top pages, landing pages, revenue and AOV, top products, device and country, DAU/WAU/MAU, weekly retention cohorts, and an export-health check.

Every query file says which dataset it ran on, the date, how many rows came back, and the first few rows of the result. The repo has a verify.sh that re-runs every query against BigQuery and fails on any error or empty result, so "tested" is something you can check yourself. Point it at your own dataset with DATASET=your-project.analytics_123 ./verify.sh ga4.

Get it from github.com/contextflo/verified-claude-skills. In Claude Code, copy the ga4 folder into ~/.claude/skills/ or your project's .claude/skills/. In claude.ai, zip the folder and upload it as a skill.

Where it falls short

The public sample is from late 2020, and Google has obfuscated it: plenty of sources read <Other> or (data deleted), and newer columns like session_traffic_source_last_click and is_active_user don't exist in it. The queries run on a current export, but the sample numbers aren't benchmarks for your store. Your own custom parameters can switch types too, and the skill can only tell Claude to check, with a query that shows which columns each key fills.

A skill is also a set of files on one person's machine. It doesn't know that your team counts revenue net of refunds or that your checkout stopped sending transaction_id in March. Keeping that kind of context current and shared across a team is the part we build Contextflo for.

The GA4 export is the most complete copy of your analytics you will ever have, and the first number Claude pulls from it will be quoted in a meeting before anyone checks it.

FAQ

Why does Claude get GA4 BigQuery queries wrong? The GA4 export stores most fields inside a nested event_params array, and the schema does not say which value column each parameter uses. Some parameters switch between string_value, int_value and double_value from one event to the next, so SQL that reads one column runs fine and silently drops part of the data.

How do I count sessions in the GA4 BigQuery export? Count distinct user_pseudo_id plus ga_session_id. ga_session_id is the session start time in seconds, so on its own it collides across users. In Google's public GA4 sample, ga_session_id alone gives 129,376 sessions for December 2020 and the correct pair gives 133,368.

Why don't my BigQuery numbers match the GA4 UI? GA4 reports estimate users and sessions with HyperLogLog++, can withhold thresholded rows, include consent-mode modeled data and Google signals that the export does not have, and dedupe users by reporting identity. BigQuery counts raw events exactly, so a few percent difference on users and sessions is expected.

Does the GA4 BigQuery export include data from before I linked it? No. The export starts within about a day of linking the property and there is no way to export earlier dates. GA4's own 2 or 14 month retention setting only limits explorations, so the export is the only place event-level history lasts.

How do I install the GA4 skill for Claude? In Claude Code, copy the ga4 folder from the verified-claude-skills repo into ~/.claude/skills/ or your project's .claude/skills/. In claude.ai, zip the folder and upload it as a skill.