GA4 BigQuery ML Sales Forecast

⚠️ Pre-release. This pipeline has been built carefully and validated on synthetic data and small production datasets. Real-world testing on large datasets is exactly what we need next — see Contributing if you'd like to help.

A pure-SQL pipeline that turns your GA4 BigQuery export into a 30-day daily revenue forecast, with custom holidays, automatic seasonality, ad-spend regressors, and seasonal sub-forecasts for upstream funnel metrics. Output is a single view you connect directly to Data Studio.

📚 Related writeups

Long-form posts on this project at hugonissar.github.io:

• A pure-SQL pipeline that turns your GA4 BigQuery export into a 30-day daily revenue forecast

Overview

Two phases:

• Build (first run, then weekly retrain) — aggregate GA4 events and ad metrics into a daily fact table; train one ARIMA_PLUS_XREG model on revenue plus four small ARIMA_PLUS sub-models on the funnel metrics.
• Forecast (daily refresh, live on every query) — the sub-forecasts predict tomorrow's funnel volume; the trained XREG model uses those plus your planned ad spend to predict tomorrow's revenue. A view unions forecast with history for Data Studio to consume.

What you'll have in BigQuery

After running the script you'll have 11 objects in your dataset:

• 3 tables — aggregated daily data, your custom occasions, the future-regressor inputs
• 6 models — one production XREG model, one transient eval model, four univariate sub-forecast models
• 2 views — the main forecast view (your Data Studio data source) and a diagnostic explainability view

Pros and cons

👍 Pros	👎 Cons
Pure SQL — no Python, Vertex, or external infrastructure	Daily granularity only — no intraday forecasts
Native to the GA4 → BigQuery export pipeline	Auto-ARIMA's choice isn't always optimal; tuning recourse is limited
Automatic weekly + yearly seasonality detection	Requires future values for every regressor (handled by sub-forecasts)
Built-in holiday regions + your own custom occasions	Handles structural breaks poorly (pricing changes, new markets, viral moments)
Regressor weights enable scenario planning ("what if I double spend?")	One forecast at a time — no multi-output (separate SKUs need separate models)
ML.EXPLAIN_FORECAST decomposes every prediction	2+ years of clean data strongly preferred — 13 months minimum

Prerequisites

• BigQuery project with billing enabled
• GA4 BigQuery export enabled with at least 13 months of history (2–3 years strongly preferred)
• Ad data in BigQuery: a daily table with columns day, spend, impressions, clicks
• A Data Studio account (free)

Input tables

Three tables are inputs that you control (everything else is built by the pipeline). One is required, two are optional but useful.

YOUR_ADS_TABLE — required

Your existing daily ad metrics, whatever the source (Google Ads export, Meta Ads export, your own attribution table). The pipeline only needs these four columns:

Column	Type	Example	What it is
day	DATE	2026-05-12	One row per day
spend	FLOAT64	1240.00	Total ad spend that day, in your currency
impressions	INT64	42180	Total impressions that day
clicks	INT64	920	Total clicks that day

If your source table has extra columns (campaign, channel, country, etc.) that's fine — the pipeline aggregates everything to a daily total. If your columns are named differently, edit the ads CTE in §1 of the SQL to do the renaming.

custom_holidays — created by §2

Tells the main model when your brand-specific occasions happen (campaigns, sales, launches, anniversaries). The model learns a separate "lift" for each named occasion and applies it to future occurrences.

Column	Type	Example	What it is
region	STRING	'US'	Region code. Match this to your main model's holiday_region setting
holiday_name	STRING	'BlackFridayWeek'	The name of the occasion. Use the same name for every yearly occurrence
primary_date	DATE	2025-11-28	The actual date of the occasion
preholiday_days	INT64	4	How many days before the date are also part of the event window
postholiday_days	INT64	3	How many days after the date are also part of the event window

Example row reads: "In the US, the BlackFridayWeek occasion happened on Nov 28, 2025; treat the 4 days before and the 3 days after as part of the same event window."

Rules of thumb when adding a new occasion:

• Add at least 2–3 past occurrences so the model has something to learn from. One row is not enough.
• Add future occurrences that fall in your 30-day forecast horizon, otherwise the model can't apply the lift.
• Use the same holiday_name string across years — that's how the model groups them.
• preholiday_days + postholiday_days define the effect window. For a flash sale that's mostly a single day, use 0, 0. For a week-long event, expand the window.

media_plan — optional, for production media plan integration

A small table holding your planned future spend, used to override the trailing-average defaults in future_regressors (§6). Only useful if you want the forecast to react to a specific upcoming campaign.

Column	Type	Example	What it is
day	DATE	2026-05-20	A future date you're planning for
planned_spend	FLOAT64	2500.00	Planned ad spend for that day
planned_impressions	INT64	75000	Expected impressions
planned_clicks	INT64	1650	Expected clicks

Only fill in the days you actually have a plan for — the pipeline falls back to trailing 28-day averages for everything else via COALESCE. See the "MEDIA PLAN INTEGRATION" note in §6 of the SQL for the join pattern.

IAM roles

Three principals need access. Grant roles at the lowest scope that works — dataset-level over project-level where possible.

Setup user (you)

Role	Granted on	Purpose
roles/bigquery.dataEditor	Target dataset (YOUR_DATASET)	Create + modify tables, models, views
roles/bigquery.dataViewer	GA4 export dataset (YOUR_GA4_PROP)	Read raw events
roles/bigquery.jobUser	Project	Submit queries and training jobs

Scheduled-query service account

If you create scheduled queries from the BigQuery console, GCP provisions a service account on your behalf — grant it the same access as above so it can refresh and retrain unattended.

Role	Granted on	Purpose
roles/bigquery.dataEditor	Target dataset (YOUR_DATASET)	Rebuild tables on schedule
roles/bigquery.dataViewer	GA4 export dataset (YOUR_GA4_PROP)	Read events daily
roles/bigquery.jobUser	Project	Run scheduled queries

Data Studio viewer

Role	Granted on	Purpose
roles/bigquery.dataViewer	Target dataset (YOUR_DATASET)	Read sales_forecast_view
roles/bigquery.jobUser	Project	Submit queries from Data Studio

If your GA4 export lives in a different GCP project than your forecast dataset, both the setup user and the scheduled service account need cross-project dataViewer on the GA4 dataset.

Step-by-step setup

1. Configure placeholders

Open ga4_sales_forecast_bqml.sql and find/replace these tokens:

Placeholder	Replace with
YOUR_PROJECT	Your GCP project ID
YOUR_DATASET	The dataset where the pipeline will live (create it if it doesn't exist)
YOUR_GA4_PROP	Your GA4 export dataset, e.g. analytics_123456789
YOUR_ADS_TABLE	Your daily ad metrics table
YOUR_TIMEZONE	Your GA4 property timezone, e.g. Europe/Stockholm, America/New_York, UTC

2. First run (manual, top to bottom)

Paste the SQL into the BigQuery console and run each section in order:

Step	Section	What it does	Typical runtime
1	§1	Build daily_features from GA4 + ads	30 s – 2 min
2	§2	Populate custom_holidays (edit the list first to add your own occasions)	< 1 s
3	§3	Train the production XREG model	1 – 5 min
4	§4a–d	Train the eval model and run ML.EVALUATE	1 – 5 min
5	§4e	Write down the MAPE, then uncomment and run the DROP MODEL line	< 1 s
6	§5	Train the four ARIMA_PLUS sub-forecast models	30 s each
7	§6	Build future_regressors	< 10 s
8	§7	Create the forecast view	< 1 s
9	§8	(Optional) Create the explainability view	< 1 s

A healthy MAPE for stable ecommerce with clean data is 8–15%. If you're above 20%, see troubleshooting.

3. Schedule the recurring jobs

In the BigQuery console (BigQuery → Scheduled queries → Create scheduled query), create two schedules:

Name	Schedule	SQL to include	What it does
ga4_forecast_daily_refresh	Every day at 06:00 in YOUR_TIMEZONE	Sections §1 and §6	Refresh raw data and roll the 30-day forecast window forward
ga4_forecast_weekly_retrain	Mondays at 02:00	Sections §3 and §5	Retrain the main XREG model and the four sub-forecast models

Both should run as the same service account with the IAM roles listed above. The forecast view (§7) doesn't need a schedule — it re-evaluates on every query.

4. Connect Data Studio

1. In Data Studio: Add data → BigQuery → YOUR_PROJECT.YOUR_DATASET.sales_forecast_view.
2. Create a time-series chart with:
   • Dimension: day
   • Metrics: actual_revenue, forecast_revenue
   • Optional ranges (for the confidence band): lower_bound, upper_bound
3. Optional: add series_type as a breakdown dimension to style history vs forecast differently.

Ongoing maintenance

Cadence	What to do
Daily (automated)	Scheduled _daily_refresh job — no action needed
Weekly (automated)	Scheduled _weekly_retrain job — no action needed
When you add a new occasion	Edit custom_holidays (§2), then re-run §3 to retrain
Quarterly (manual)	Re-run §4 to spot-check accuracy; drop the eval model when done
When forecasts look off	Query sales_forecast_explained (§8) and look at attribution_* columns to see which signal is dominating

Troubleshooting

MAPE above 20%. Likely one of: (a) less than ~13 months of training data, (b) a structural break in your business that the model can't reach back over, (c) high day-to-day volatility from small order volume. Check sales_forecast_explained to see whether any single regressor is dominating — if so, that's a candidate for removal.

Forecast values look flat. The sub-forecasts may not have enough data to learn weekday seasonality. Confirm daily_features has at least 13 months and that view_item/add_to_cart aren't all zero (it happens — GA4 ecommerce parameters require enhanced ecommerce to be configured).

Future regressors table is missing column X error from ML.FORECAST. The schema of future_regressors (§6) must exactly match the regressor columns from the training query in §3. If you add or remove a regressor in one place, mirror it in the other.

Data Studio shows no forecast rows. Re-run §6 to rebuild future_regressors; the view depends on it.

Contributing

We're looking for production testers. The pipeline is solid on synthetic and small datasets, but real-world ecommerce data has quirks that only show up at scale, and we want to harden it before tagging a 1.0.

Ideal tester profile

• 2+ years of GA4 BigQuery export with stable schema
• 500+ daily orders or 50k+ daily sessions (so seasonality is statistically clean)
• Willing to share MAPE / MAE numbers publicly or privately (no underlying data needed)
• Bonus if you're in one of these less-tested setups:
  • Subscription / recurring revenue
  • Heavy promotional calendars (multiple major sales per year)
  • Non-US holiday regions

How to contribute

1. Run the pipeline against your data following the steps above.
2. Open an issue with:
   • Years × daily orders (or sessions)
   • Business type
   • MAPE from §4
   • Anything that broke, surprised you, or required tweaking
3. If you find a bug or have a fix, PRs welcome. Please include a brief description of what was wrong, what your data looked like that triggered it, and how you verified the fix.

What would be especially helpful

• Benchmark MAPE numbers across different business profiles so we can publish realistic expectations
• Alternative ad-data integrations (Meta, LinkedIn, TikTok, in-house attribution)
• Battle-tested media plan integration — the README pattern is sketched but unproven at scale

License

MIT (placeholder — will finalize for 1.0).