BigQuery + Looker for Personalization Analytics: Insights vs Actions

TL;DR.

• BigQuery + Looker for personalization analytics is a cold-path stack. It tells you what your personalization did. It does not, and should not, decide what to do next.
• GA4 has a native BigQuery export. The streaming events_intraday_YYYYMMDD table lands events within seconds; the daily events_YYYYMMDD table can lag up to 72 hours, with "Daily Fresh" landing in roughly 30 to 60 minutes.
• BigQuery BI Engine is an in-memory cache that gives Looker dashboards sub-second response on top of warehouse-scale data. It is the right serving layer for analysts. It is the wrong serving layer for users.
• The two warehouse jobs that matter for personalization are cohort decay (does last week's recommended set still convert this week?) and holdout vs treatment lift (did personalization actually move revenue?).
• The warehouse stops where the request path starts. Once the answer needs to land in <100 ms for a user, you are out of BigQuery territory and into knowledge graph or feature store territory.

If you are building a personalization system in 2026, the most common architectural mistake is asking BigQuery to decide things. It is excellent at counting and joining. It is not built to answer "what should we show this user, right now, in 30 milliseconds." This post is about getting the warehouse's job right: what to compute, how to visualize it in Looker, and the line where the analytics stack hands off to the action stack.

The split: BigQuery and Looker do insights, not actions

A personalization system has two clocks. The hot clock measures the request path — a user shows up, the system has tens of milliseconds to decide what to render. The cold clock measures the analytics path — every decision, click, dwell, and conversion lands in the warehouse so we can ask, days later, whether the system is working.

BigQuery personalization and Looker personalization belong on the cold clock. BigQuery is a columnar, scan-oriented analytics warehouse — it is designed for queries that touch billions of rows once, not for single-row reads on a per-user budget. Looker is the semantic layer on top: it translates BigQuery tables into LookML models that analysts and product managers can explore without writing SQL. Together they are the best personalization analytics warehouse Google sells. They are also, by design, the wrong place to make decisions.

The right way to read every BigQuery + Looker marketing page is: this is what you use to measure your personalization, not what you use to run it. The marketing copy often blurs the line because it makes the story bigger. The architecture forces the split anyway.

GA4 → BigQuery export: the pattern everyone starts with

The most common entry point into BigQuery personalization analytics is the GA4 BigQuery export. It is free for standard GA4 properties, ships event-level data into your project, and is the foundation of nearly every Looker dashboard a marketing team builds.

The export has three flavors:

• Streaming (events_intraday_YYYYMMDD). Best-effort, near-real-time. Events typically land within seconds of being recorded. Useful for live ops dashboards and for kicking off pipelines that need a quick read of the last hour.
• Daily (events_YYYYMMDD). Backfilled with full attribution and computed fields. Drops between roughly 8 AM and 11 AM local time after a multi-hour processing window; the official documentation allows for up to 72 hours of lag in edge cases.
• Daily Fresh (newer option). A middle tier — tables typically available within 30 to 60 minutes after midnight, with most of the daily-table completeness.

A few facts that bite teams the first time:

• The intraday table does not populate traffic_source, user_ltv, or is_active_user. If your personalization analytics needs attribution, you must wait for the daily table or join against another source.
• Event params and user properties are stored as repeated RECORD fields. You will write a lot of UNNEST(event_params) in your Looker personalization LookML, which is a known footgun for new analysts.
• The free tier (1 TB of query and 10 GB of storage per month) covers small sites but not anyone running real volume. Plan for the bill.

For the architecture pattern around how this fits a larger stack, see our reference architecture for real-time personalization — the warehouse sits on the cold side of that diagram, fed by the same event bus that feeds the action layer.

Cohort decay: the SQL job the warehouse is built for

The first BigQuery query every personalization team ends up writing is some flavor of cohort decay. You group users by the date or action that put them into a cohort (first session, first purchase, first time they saw a personalized module) and you track a metric forward in time — retention, repeat conversion, revenue per user, click-through rate on the recommended set.

The reason this is a warehouse job is that it touches every event the user has ever generated, joined against the cohort definition, windowed across weeks or months. A serving database cannot answer this question in any reasonable time. BigQuery can answer it in a single query because it is built for exactly this shape of work — scan a partition, group by a derived cohort key, aggregate.

In practice, the cohort-decay query for personalization analytics looks something like this:

WITH cohorts AS (
  SELECT
    user_pseudo_id,
    DATE(MIN(TIMESTAMP_MICROS(event_timestamp))) AS cohort_date
  FROM `project.analytics_NNNNNN.events_*`
  WHERE event_name = 'first_personalized_session'
  GROUP BY user_pseudo_id
)
SELECT
  cohort_date,
  DATE_DIFF(event_date, cohort_date, DAY) AS day_since_cohort,
  COUNT(DISTINCT e.user_pseudo_id) AS active_users,
  SUM(IF(event_name = 'purchase', 1, 0)) AS conversions
FROM cohorts c
JOIN `project.analytics_NNNNNN.events_*` e
  USING (user_pseudo_id)
GROUP BY 1, 2
ORDER BY 1, 2;

The output is a triangle table that a Looker explore turns into a decay curve. The interesting reading is not the absolute conversion rate — it is whether the curve for cohorts who got personalization decays slower than the curve for those who did not. That comparison is your model health signal, and it is what the warehouse is here to give you.

Holdout vs treatment lift: the question only the warehouse can answer

The second canonical job is measuring whether personalization actually moved revenue. The pattern is a holdout group — a randomized slice of users who get a non-personalized baseline experience while everyone else gets the treatment. Bluecore's engineering team has a good public write-up on this pattern and they run the same kind of holdout queries against BigQuery that you will.

The mechanics live in BigQuery because they require joining a randomly-assigned cohort table to the full event stream and aggregating by treatment status:

SELECT
  treatment_arm,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(revenue) AS total_revenue,
  SUM(revenue) / COUNT(DISTINCT user_pseudo_id) AS arpu
FROM `project.analytics_NNNNNN.events_*` e
JOIN `project.experiment.holdout_assignments` h
  USING (user_pseudo_id)
WHERE event_date BETWEEN @start AND @end
GROUP BY treatment_arm;

The lift is (arpu_treatment - arpu_holdout) / arpu_holdout. You can extend the same query to compute confidence intervals using BigQuery's built-in statistical functions, or push the read into a notebook for a more careful test. Either way, the source of truth lives in the warehouse, not in the serving layer.

This is also the question your CFO will ask. If your personalization analytics warehouse cannot answer it cleanly, your renewal conversation gets harder. We wrote more about the experiment design itself in A/B testing personalization at scale.

Looker as the semantic layer over BigQuery

Once the SQL is written, you want analysts and product managers exploring on top of it without re-writing queries every time. That is Looker's job. Per the Google Cloud Looker + BigQuery documentation, Looker's semantic model (LookML) translates BigQuery's column-level structure into named dimensions, measures, and explores that a non-SQL user can pivot on.

For Looker personalization work, the typical model looks like:

• A users view keyed on user_pseudo_id, with derived dimensions for cohort, signup channel, and assigned treatment arm.
• An events view exposing event_name, derived is_personalized_impression, revenue, and a junction back to users.
• A cohort_decay derived table that pre-aggregates the SQL above into a (cohort_date, day_since_cohort) grain.
• A holdout_lift explore that joins treatment assignments to events and exposes arpu, conversion_rate, and incremental_revenue as measures.

The acceleration story matters here. BigQuery BI Engine is an in-memory, vectorized cache that sits in front of BigQuery. When a Looker query matches a BI Engine reservation, the query bypasses BigQuery storage and reads from cache — Google publishes sub-second response on common dashboard queries, which is the difference between a Looker dashboard people actually use and one nobody opens. BI Engine works with both Looker and Looker Studio with no application changes, provided the reservation lives in the same region and project.

A reasonable default: put your most-used Looker dashboards (the ones product looks at weekly) behind a BI Engine reservation, and let the long-tail explores fall back to standard BigQuery. The cost of the reservation is small relative to what you spend on analyst time when dashboards are slow.

Where the warehouse stops: the request path needs something else

The line is sharp, and it shows up the moment a user hits your site. A personalization analytics warehouse lives on the cold path. The action layer lives on the hot path. They share an event bus, not a query engine.

A request that needs to land in <100 ms is not a BigQuery query. Even with BI Engine, you are looking at sub-second response — fast for a dashboard, far too slow for a recommendation API. The hot path needs a different shape of system: a feature store, a vector index, or a knowledge graph that can hop from a user node to a set of candidate items in a constant number of edge traversals.

This is the distinction we go into in recommendation engine vs personalization layer and in knowledge graphs vs vector embeddings. The short version: when an analyst asks "did it work," the answer lives in BigQuery. When a user asks "what should I see next," the answer needs to live somewhere that returns in tens of milliseconds. Two systems, two clocks.

Reverse ETL — Hightouch, Census, Polytomic, or hand-rolled — is how the warehouse signals back to the serving layer. Features computed in BigQuery (lifetime value, cohort, predicted churn) get synced down to an online store the action layer can read in single-digit milliseconds. The warehouse computes, the action layer serves, the event bus closes the loop.

How ×marble fits in

BigQuery and Looker are where you measure ×marble; ×marble is what generates the events you measure. We run the action layer — a knowledge graph that decides, per request, what each user should see in tens of milliseconds — and we emit a stream of decision events (impressions, treatment arm, candidate set, ranked output) into your warehouse so your existing BigQuery Looker personalization analytics stack can answer the lift question without rewriting how your analysts work.

You keep your GA4 export, your LookML, your CFO's dashboard. We add the request-path layer that GA4 and Looker were never designed to be, and feed your warehouse the data it needs to prove it is working. See timesmarble.com for the umbrella, video.timesmarble.com for the YouTube product, and the marketing engineer's personalization stack post for where ×marble sits in a typical reference diagram.

FAQ

Can BigQuery serve real-time personalization?

Not for the request path. BigQuery is optimized for analytical scans at scale and has query latency floors closer to hundreds of milliseconds than to the <30 ms budget a real-time recommendation API needs. BI Engine pushes some queries into sub-second range, which is great for dashboards but still too slow to sit inside a user request. Use BigQuery to measure personalization; use a feature store or knowledge graph to serve it.

What's the difference between GA4 daily and streaming export to BigQuery?

The streaming export (events_intraday_YYYYMMDD) lands events within seconds but is best-effort and missing some attribution fields like traffic_source and user_ltv. The daily export (events_YYYYMMDD) backfills the day with full attribution but can lag up to 72 hours. There is a newer Daily Fresh tier that targets 30 to 60 minutes for most of the daily-table completeness.

Do I need BI Engine for Looker on BigQuery?

Not strictly. Looker works against BigQuery without a BI Engine reservation. You want BI Engine on top of your most-used dashboards — the ones product managers and execs look at weekly — because the in-memory cache turns multi-second queries into sub-second ones. For long-tail explores written by analysts, native BigQuery is usually fine.

How does BigQuery + Looker compare to a personalization platform?

They are different layers of the same stack, not alternatives. BigQuery + Looker is your personalization analytics warehouse — the cold path where you measure, attribute, and report. A personalization platform is the action layer that decides what each user sees in real time. You need both. See personalization platforms in 2026 for a tour of the action-layer options.

Where does the warehouse stop and the action layer start?

At the request path. The moment a user is waiting on a response and the answer has to land in <100 ms, you are out of warehouse territory. Anything that requires scanning history, joining cohorts, or windowing across weeks belongs in BigQuery. Anything that has to return inside a user-facing request belongs in a knowledge graph or feature store.

Further reading

• Reference architecture for real-time personalization — where the warehouse sits in the larger picture.
• Recommendation engine vs personalization layer — why the action layer is its own system.
• Knowledge graphs vs vector embeddings — what fills the gap when you leave BigQuery.
• A/B testing personalization at scale — the experiment design behind the holdout queries above.
• Marketing engineer's personalization stack — a full reference stack with BigQuery + Looker in their proper place.
• Google Cloud — Looker + BigQuery solutions — the canonical doc on the semantic layer story.
• Google Cloud — Introduction to BI Engine — sub-second dashboards on BigQuery.
• Bluecore engineering — Holdout groups to quantify campaign lift — the canonical pattern this post's holdout SQL follows.