BigQuery for Marketing Analytics

BigQuery for Marketing Analytics: Cohorts, LTV, and Attribution Across Your Actual Stack

Nobody wakes up wanting a data warehouse. What happens is that somebody asks a question the reporting interface cannot structurally answer, and then asks it again the following quarter.

Are the customers we acquired in Q1 still buying? Which channel brings people worth something twelve months later? Why does Google Ads report 340 conversions when the CRM shows 190 real deals?

None of those are dashboard problems. They are join problems, and joins need somewhere to happen.

Why a warehouse and not another dashboard

GA4 makes the argument for you, and it makes it at the worst possible moment.

Standard properties keep event-level and user-level data for two months by default, extendable to a maximum of fourteen. That setting governs Explorations: funnels, path analysis, segment overlap, cohort reports. Standard aggregated reports are not affected by it, which is exactly why the limit stays invisible. Open your default reports and last year's revenue is still sitting there, so nothing looks broken.

Then someone builds a year-over-year comparison in Explore and finds last year's event-level data no longer exists. Fourteen months gives you a two-month overlap with the same period last year, at best. Not enough for a retention curve, and barely enough for one seasonal cycle.

The setting is enforced by deletion. Raising it from two months to fourteen protects the future, not the past, and support cannot recover what expired.

The three connections

A marketing warehouse worth having is three pipes, not thirty. Behaviour, spend, outcome. Everything after that is refinement.

Behaviour: the GA4 export

Native link, configured in GA4 Admin under Product Links. You get one date-partitioned table per day, events_YYYYMMDD, one row per event, with event_params, user_properties and items as nested repeated records.

The constraint to know is the daily cap. Google's documentation is direct about it: standard properties are limited to one million events per day on the daily batch export, and if a property consistently exceeds it the export is paused and previous days are not reprocessed. That is a cliff, not a throttle.

If you are near the cap there are two moves before an Analytics 360 conversation. Filter low-value events out of the export in the BigQuery link settings, or add streaming export, which has no event limit but bills at 0.05 USD per gigabyte, roughly 600,000 events depending on event size.

Spend: the Google Ads Data Transfer Service

Also native, and free at the connector level. Google Ads is an Alphabet source, so there is no transfer orchestration charge. You pay for storage and queries only. Transfers run daily with a default seven-day refresh window, configurable up to thirty.

Widen that window if your sales cycle is long. Google Ads logs conversions against the date of the ad click, not the date the conversion happened, so a purchase landing three weeks after the click rewrites a partition you already loaded. A seven-day window will never see it, and your warehouse will quietly disagree with the Google Ads interface forever.

One backfill constraint to plan around: the Click Performance Report cannot be retrieved more than 90 days back. If you want gclid history, the transfer needs to be running before you need it. Same shape of problem as the GA4 export, different number.

Outcome: the CRM

This is the one people assume needs a paid ETL tool, and increasingly it does not. BigQuery's Data Transfer Service now carries generally available connectors for Salesforce, ServiceNow, Salesforce Marketing Cloud, Facebook Ads, MySQL, PostgreSQL and Oracle, with Stripe, PayPal, HubSpot, Klaviyo and SQL Server in preview. Preview connectors are not billed until they reach general availability.

Those are paid connectors once live, billed on slot-hours rather than rows. Google's own planning guidance is a conservative ceiling of 20 slot-hours per hour of transfer run time, which works out at roughly 1.20 USD per hour of run time in us-central1. For a nightly CRM sync that is not a meaningful line item.

If your system is not on that list, a scheduled export to Cloud Storage and a batch load into BigQuery is free and boring, which is what you want from a pipeline.

The build, in order

The sequence matters. Steps one and four are the ones that get postponed, and both are painful or impossible to retrofit once traffic is flowing.

1. Enable the GA4 export today, before anything else

   Admin, Product Links, BigQuery Links. You need Editor or above on the GA4 property and OWNER access on the BigQuery project. Data takes 24 to 48 hours to appear, and a dataset named analytics_<property_id> is your confirmation the link works.

   Do this even if the rest of the project is three months out. Every day you wait is a day of history that does not exist later.

2. Attach billing, and leave the sandbox

   You can use BigQuery without a credit card, but the sandbox expires tables and has no intraday import of GA4 events. It is fine for reading the schema. It is not where you build something you intend to keep.

3. Set data retention to 14 months anyway

   Admin, Data Settings, Data Retention. The export is not governed by this setting, but Explorations are, and your team will keep using them. Turn on reset on new activity while you are in there.

4. Pick one region, and mean it

   Datasets in different locations cannot be joined afterwards, and a dataset's location cannot be changed once created. Decide EU or US on day one based on where the data needs to live, then create every dataset there. This is the only mistake on the list with no recovery path.

5. Start the Google Ads transfer, and widen the refresh window

   Default is seven days. Set it to match your click-to-conversion cycle, up to thirty. If you need historical spend, the Data Transfer Service caps each backfill request at 180 days, so longer periods need sequential backfills, and ClickStats stops at 90 regardless.

6. Bring in the CRM, and agree the key first

   The connector is the easy half. The hard half is a durable identifier linking a web session to a CRM record: an order ID written into the purchase event, a hashed email, a user ID set on login. If that key does not exist, fix the tracking before you build the pipeline. This is where most projects stall, and it is a measurement problem wearing a data engineering costume.

7. Set cost controls before anyone writes a query

   Project-level and user-level custom cost controls, plus a maximum bytes billed cap. Then make the expensive mistake fail loudly rather than silently.

   Force a date filter on your largest tables
   ALTER TABLE `project.dataset.events` SET OPTIONS ( require_partition_filter = TRUE );

   On two years of daily tables, a query with no date filter scans all of them. This turns that into an error instead of an invoice.

8. Model the tables, then point the BI layer at them

   Sessions, users, orders, spend. Build them as scheduled queries or Dataform models, then connect Looker Studio to those rather than to raw events. Dashboards reading modelled tables are cheap. Dashboards reading raw events on an hourly refresh are how people get surprise bills.

Three analyses that pay for it

Cohorts

A cohort is a group defined by when they arrived, measured by what they did afterwards. GA4 has a cohort exploration and it is fine for a look, but it inherits the fourteen-month ceiling and it cannot cohort on anything outside GA4.

In BigQuery you can. Cohort by acquisition month and split by first-touch channel, which is what traffic_source is actually for: it holds the first source that acquired the user and never changes afterwards, which makes it wrong for session reporting and exactly right for cohort definitions.

The version that changes decisions joins that cohort table to CRM revenue rather than to GA4 events. Then you are looking at repeat purchase rate by acquisition channel over 24 months, and the channel that looked cheapest on cost per acquisition frequently stops looking cheapest.

Lifetime value

LTV is the analysis that most obviously cannot live in an ad platform. The platform knows the conversion. It does not know the refund, the second order, the cancelled subscription, or the margin.

Start descriptive, not predictive. Realised revenue per acquisition cohort at 30, 90 and 365 days, split by channel, is more useful to a mid-sized business than any model, and it is a straightforward join between the cohort table and CRM order history.

If you go predictive later, BigQuery ML runs inside the warehouse in SQL rather than as a separate stack. Watch the pricing asymmetry: creating a linear or logistic regression, k-means, PCA or time-series model bills at 312.50 USD per TiB processed on-demand, while evaluation and prediction bill at the standard 6.25 USD per TiB. Training rescans the dataset on every run, so an untuned retraining schedule is where the surprise arrives.

Attribution

This is where BigQuery earns its place, and also where the marketing around it is most misleading.

The export gives you raw collected events. It does not give you GA4's modelled output. Google's own developer documentation is explicit that the export contains data sent from the client without most of the value additions applied before data reaches standard reporting surfaces, and those additions include Google Signals, modelling, traffic attribution and predictions.

So the export will not reconcile exactly with the interface. Expecting it to is the single most common cause of a stalled BigQuery project, and it is worth saying out loud in the kickoff meeting rather than discovering it in week three.

What you get instead is the raw material to build your own model, across three traffic source structures that people routinely confuse.

Before July 2024, auto-tagged Google Ads traffic frequently landed in the export as google / organic, because auto-tagged links carry a gclid and no UTM parameters, leaving nothing in the URL to parse. The session-level record fixed it going forward. The fix for your history is the ClickStats join above.

One more structural point that catches people. There is no session table in the GA4 export. ga_session_id sits inside event_params as an integer, and it is the Unix timestamp of session start, so it is only unique when combined with the user. Sessions are something you build, not something you select.

Sending the answer back to Google Ads

An analysis nobody acts on is a report. The reason to build this in BigQuery specifically, rather than in a BI tool sitting on exports, is that BigQuery is a supported source for pushing data back into Google Ads.

Google Ads Data Manager connects to BigQuery directly, and one connection can serve several destinations: offline conversion import, enhanced conversions for leads, and Customer Match. The same warehouse table that tells you which leads closed can also tell Smart Bidding.

The rest of the practical detail, because these are the things that make a connection fail silently.

• Establishing the connection needs GCP owner access on the dataset and Google Ads admin access. The Ads admin access can be revoked once the connection exists.
• Views must be fully authorized, and the view's source cannot include external tables.
• Google Ads does not support IP address matching for end users in the EEA, the UK or Switzerland. Build that exclusion into the query rather than trusting the destination to handle it.
• If your click-to-close cycle runs longer than fourteen days, the warehouse import window is a design constraint, not an implementation detail.

Two things this does not change, and I want to be exact because vendor copy is loose here. Google Ads matches on GCLID, GBRAID, WBRAID, or hashed user-provided data. It does not accept an arbitrary first-party identifier you invented, however clean your warehouse is. And an offline conversion import improves what the bidding algorithm optimises toward. It does not recover attribution for clicks whose identifier you never captured.

What it costs, without the sales gloss

The pricing is genuinely simple, which is unusual enough to state plainly. Figures below are US multi-region on-demand rates, and rates vary by region.

A site collecting tens of thousands of events a month sits inside the free tier, with storage measured in cents per year. The bill only becomes interesting when queries are careless, and there are four ways that happens, in the order they bite.

• Missing partition filters. On two years of daily tables, a query without a date filter scans all of them. Set require_partition_filter and the mistake becomes an error.
• Assuming LIMIT reduces cost. It does not. You are billed on bytes scanned in the columns you select, regardless of rows returned. Select columns, never *.
• Dashboards on a short refresh. Identical queries against unchanged tables serve from cache for 24 hours at no compute charge, so a dashboard whose date range shifts slightly on every load never benefits. Materialise the aggregate and point the dashboard at that.
• Retraining ML models on a schedule. The 312.50 USD per TiB creation rate applies on every run, not once.

There is a floor worth knowing too. Every query is billed a minimum of 10 MB per table referenced. Queries touching dozens of small lookup tables are not as free as they look.

What it will not fix

• Collection. BigQuery stores what GA4 received. If purchase events fire twice or values arrive as zero, you now have that problem in two places and a longer history of it.
• Consented-away data. Events denied ad and analytics storage are not resurrected by a warehouse. Rows that were never collected are not in the export.
• Reconciliation with the GA4 interface. The gap is architectural: probabilistic cardinality counting in reports, Signals deduplication, behavioural modelling, late data arriving within roughly 72 hours. A small persistent difference is expected, and chasing it to zero costs a quarter.
• Google Ads identifier matching. GCLID, GBRAID, WBRAID, or hashed user-provided data. That constraint sits at the platform, not at your warehouse.
• Maintenance. GA4 changes its schema and Google deprecates API surfaces. A pipeline nobody owns quietly stops being correct while the dashboards keep rendering.

Pre-flight checklist

Work through these before anyone writes a query. Most failed warehouse projects fail here, not in SQL.