GA4 BigQuery Export vs Standard Reports For Reconstructing 18-Month Cohorts
Standard GA4 reports quietly sample and threshold retention cohorts once they get small — the raw BigQuery export doesn't. Here's how to decide where to pull your 18-month backfill from.
GA4 BigQuery Export vs Standard Reports for 18-Month Cohorts
A choice between GA4's UI reports (sampled, thresholded, 14-month capped) and the raw BigQuery event export (unsampled, full-fidelity) as the source for reconstructing 18-month retention cohorts.
When you need to rebuild a repeat-purchase or retention curve going back 18 months, you have two data sources inside the Google stack: the standard GA4 Explorations UI and the BigQuery event-level export. They look interchangeable in a screenshot — both show retention percentages by cohort week. They are not. Standard reports apply sampling above data-volume thresholds, apply (other) bucketing above cardinality thresholds, and cut off historical detail at 14 months. The BigQuery export applies none of those transformations: it's the raw `events_YYYYMMDD` tables Google writes daily. For an 18-month cohort question, that difference is the difference between a defensible number and a fictional one.
The short answer: if your cohort question spans more than 14 months, or any weekly cohort dips below a few hundred users, use the BigQuery export. The standard UI will silently rewrite your numbers before you see them, and you won't get a warning banner that tells you which cells were touched.
The longer answer depends on cohort size, event cardinality, and how far back you're reaching. Below is the practical comparison — what each source gives you, where each one lies, and how to combine them when neither is enough on its own.
GA4 standard reports vs BigQuery export — behaviour on an 18-month retention backfill
| Dimension | GA4 standard reports | BigQuery export |
|---|---|---|
| Historical window | 14 months (event-level detail) | From export-enabled date, unbounded |
| Sampling | Applied above ~10M events per query in GA4 360; tighter in free tier | None — raw events |
| Thresholding | Rows suppressed when cohort user counts are small (Google Signals + demographic dimensions) | None |
| (other) bucketing | Top-N dimension values retained; rest collapsed to (other) | Full cardinality preserved |
| user_pseudo_id access | Aggregated only | Row-level per event |
| Time to first insight | Minutes (UI) | Hours to days (SQL + modelling) |
| Cost | Free | Storage + query cost (see cost math below) |
| Cohort week granularity | Weekly, but affected by all of the above | Arbitrary — you define it in SQL |
Read the table as a hierarchy of failure modes, not a feature grid. The 14-month ceiling is a hard wall — no query trick gets past it. Sampling and thresholding are softer walls that only bite once your cohorts get small or your event volume gets large, but they bite invisibly.
When standard reports lie about your retention curve
Three GA4 behaviours conspire to break small-cohort retention math. Sampling kicks in when a query touches too many events, so a wide date range with a segment applied is exactly the shape that triggers it. Thresholding hides rows where the underlying user count is small enough to risk re-identification — and week-52 of a cohort is, by definition, small. The (other) cardinality bucket collapses long-tail dimension values into a single row, which destroys any cohort split by page, product, or campaign with more than a few dozen values.
The combined effect: standard-report retention percentages typically disagree with BigQuery-derived percentages by 15-40% on any cohort week where fewer than ~500 users are still active. On an apparel store's month-12 retention read, that's the difference between reporting 4.2% and reporting 6.8%. Both feel plausible; only one is real.
The silent-failure problem
GA4 shows a small info icon when sampling is applied to a query, but no equivalent flag for thresholding or (other) bucketing at cohort-cell level. If you're screenshotting an Explorations retention table into a board deck, you have no way of knowing which cells were rewritten. BigQuery is the only place the raw truth exists.
When the standard reports are actually fine
Not every retention question needs BigQuery. If you're looking at week-1 to week-4 retention on a large cohort (say, all users from a month with >50k active users), no dimension splits, no Google Signals segments — the standard Explorations report will match BigQuery within a percentage point. The sampling and thresholding thresholds simply never trigger.
The break-point is roughly: cohorts above 5,000 users, windows under 12 months, no more than one dimension split. Inside that envelope, standard reports are fine and cheaper. Outside it — and any 18-month cohort question is outside it — the BigQuery export is the only source you can defend. If you're mid-cutover from Universal Analytics, note that reconstructing user_pseudo_id cohorts across the UA-to-GA4 cutover adds a second layer of complexity that only BigQuery lets you handle directly.
Retention percentage disagreement vs cohort size (GA4 standard vs BigQuery)
Frequently asked questions
The export itself is free from GA4 to BigQuery. You pay BigQuery storage (a few cents per GB-month) and query cost ($5 per TB scanned on-demand). For a €5M store backfilling 18 months, expect a one-time query cost in the low tens of euros and ongoing storage under €10/month. See the cost-math breakdown for a €5M DTC store for a full worked example.
Only from the date you enabled the export in GA4 admin. It is not retroactive. If you enabled it 8 months ago, you have 8 months of raw data — the earlier 10 months of an 18-month backfill have to come from standard reports (with all their caveats) or from a source outside GA4 entirely, like Shopify order history.
Approximately, not exactly. Google doesn't document every step of how the UI computes retention (session unification, late-arriving events, timezone handling all vary). Expect BigQuery-derived retention to be within 1-3% of standard reports on large cohorts, and 15-40% off on small ones — with BigQuery being the correct number.
GA4 keeps roughly the top 50,000 unique values per dimension per day; everything else gets bucketed into a single (other) row. On a store with 20k SKUs and thousands of landing pages, a product-level or page-level cohort split hits this limit fast, and the (other) row becomes uselessly large. BigQuery preserves full cardinality.
GA4 360 raises the event-data retention setting to a maximum of 50 months, but the standard Explorations reports still have their own reporting-quota limits. For 18-month cohorts on 360, standard reports are more usable — but BigQuery is still the safer source because it removes sampling entirely.
You join `user_pseudo_id` (or a hashed customer email captured in a custom event parameter) against the Shopify customer identifier. This is the standard pattern for backfilling pre-export months by joining BigQuery to Shopify orders — Shopify gives you the truthful order history, BigQuery gives you the sessions leading up to it.
At minimum: `event_date`, `event_timestamp`, `user_pseudo_id`, `event_name`, and for revenue cohorts `ecommerce.purchase_revenue`. Cohort definition usually comes from a `first_visit` or `first_open` event or from a `purchase` event with a MIN() date per user. See the schema-fields-that-actually-matter breakdown for the full field list.
For a one-time 18-month backfill, an analyst comfortable with SQL can do it in 1-2 days. For an ongoing retention model, you'll want a data engineer or a tool that abstracts the SQL — the raw event tables have nested arrays (`event_params`, `user_properties`) that are unfamiliar if you've only written flat-table SQL.
Yes for simple aggregations, but for cohort retention specifically you need windowed CTEs that Looker Studio can't express. Most teams write the cohort SQL once as a scheduled query into a summary table, then point Looker Studio at the summary. This gives you fast dashboards without recomputing on every load.
For pure repeat-purchase curves, yes — Shopify's order data is more accurate and doesn't have the export-date limitation. Use GA4 BigQuery when you need to attach behavioural context (traffic source, landing page, on-site events) to the cohort. Many teams do both and reconcile against each other.
Get an AI expert review of your site
Paste your URL — Metricuno's AI runs the same heuristic checks a senior CRO consultant would, scoring your page and prioritising the fixes that'll move conversion fastest.