Google Analytics 4 (GA4) represents a significant shift in how marketers collect, analyze, and utilize data. Unlike its predecessor, Universal Analytics, GA4 is entirely event-based, which provides more flexibility—but also a steeper learning curve. When connected to Google BigQuery, GA4 unleashes a world of opportunity for marketers who want deeper insights, more advanced reporting, and automated workflows.
This article introduces practical and easy-to-implement BigQuery recipes for marketers using GA4. Whether you’re trying to understand customer journeys, calculate lifetime value, or optimize campaign performance, these recipes will help you derive actionable insights without having to be a data scientist.
Why Use BigQuery with GA4?
GA4 provides basic dashboards, but the depth of data available in BigQuery is unmatched. By exporting your GA4 data to BigQuery, you get:
- Full-fidelity data: Unlike sampled data in GA4’s UI, BigQuery offers raw, unsampled data.
- Event-level information: Analyze individual user actions to discover insights hidden by aggregated reports.
- Custom analysis: Build reports and segments based on your business logic.
- Advanced integrations: Combine GA4 data with CRM or ad data for full-funnel attribution.
Before we dive into the actual queries, make sure you’ve already linked your GA4 property to BigQuery—a straightforward process from the GA4 admin panel.
1. User Lifetime Value (LTV)
Understanding how much revenue a user generates over a specific time period helps inform marketing spend decisions. Here’s a basic recipe for calculating LTV over 90 days from the first interaction:
SELECT
user_pseudo_id,
MIN(event_timestamp) AS first_touch,
SUM(ecommerce.purchase_revenue) AS total_revenue_90_days
FROM
`my_project.analytics_XXXXXX.events_*`
WHERE
event_name = 'purchase'
AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
user_pseudo_id
Customize the time window or logic as needed. For example, you can tag users from specific campaigns to evaluate campaign ROI.
2. Session Reconstruction
GA4 no longer uses traditional sessions the way Universal Analytics did. If your business still relies on session-level metrics, you can re-create them with this recipe:
SELECT user_pseudo_id, event_bundle_sequence_id, MIN(event_timestamp) AS session_start, MAX(event_timestamp) AS session_end, COUNT(*) AS total_events FROM `my_project.analytics_XXXXXX.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' GROUP BY user_pseudo_id, event_bundle_sequence_id
This approximation uses a combination of event_bundle_sequence_id, which often correlates with sessions, and user ID. For stricter definitions, you might want to include ga_session_id if available.
3. Funnel Drop-off Analysis
Funnels are useful but limited in the GA4 interface. With BigQuery, you can generate them dynamically based on your needs. Let’s simulate a simple eCommerce funnel:
- View Product
- Add to Cart
- Start Checkout
- Purchase
WITH funnel AS (
SELECT
user_pseudo_id,
ARRAY_AGG(event_name ORDER BY event_timestamp) AS events
FROM
`my_project.analytics_XXXXXX.events_*`
WHERE
event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
user_pseudo_id
)
SELECT
COUNTIF('view_item' IN UNNEST(events)) AS viewed_product,
COUNTIF('add_to_cart' IN UNNEST(events)) AS added_to_cart,
COUNTIF('begin_checkout' IN UNNEST(events)) AS started_checkout,
COUNTIF('purchase' IN UNNEST(events)) AS purchased
FROM
funnel
Visualizing this data in a funnel chart can help determine where users are dropping off and where improvements can be made.
4. Acquisition Channel Performance
Breaking down performance by source/medium is crucial for marketers. Here’s how you can get revenue per acquisition source:
SELECT traffic_source.source, traffic_source.medium, COUNT(DISTINCT user_pseudo_id) AS users, COUNTIF(event_name = 'purchase') AS purchases, SUM(ecommerce.purchase_revenue) AS total_revenue FROM `my_project.analytics_XXXXXX.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' GROUP BY traffic_source.source, traffic_source.medium ORDER BY total_revenue DESC
This recipe helps you answer questions like “Which channel drives the most high-value users?” and “Which source has a high conversion rate but low revenue?”
5. Identifying High-Intent Users
Some behaviors signal a higher purchase intent. Combining events can help identify high-quality leads. Here’s how you can create a list of users who viewed a product, added it to cart, and returned within 3 days.
WITH events AS (
SELECT
user_pseudo_id,
event_name,
TIMESTAMP_MICROS(event_timestamp) AS event_time
FROM
`my_project.analytics_XXXXXX.events_*`
WHERE
event_name IN ('view_item', 'add_to_cart', 'session_start')
AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
),
behavioral AS (
SELECT
user_pseudo_id,
MIN(IF(event_name = 'view_item', event_time, NULL)) AS view_time,
MIN(IF(event_name = 'add_to_cart', event_time, NULL)) AS cart_time,
MIN(IF(event_name = 'session_start', event_time, NULL)) AS return_time
FROM events
GROUP BY user_pseudo_id
)
SELECT *
FROM behavioral
WHERE
view_time IS NOT NULL
AND cart_time IS NOT NULL
AND return_time > cart_time
AND TIMESTAMP_DIFF(return_time, cart_time, DAY) <= 3
These insights can feed into remarketing strategies or a high-priority lead list for sales teams.
6. Custom Audiences for Ads
Once you’ve identified important user segments, such as high-intent shoppers or loyal customers, you can export these to Google Ads for intelligent targeting. Here’s an example of extracting emails (hashed) for recent purchasers:
SELECT user_pseudo_id, user_properties.value.string_value AS email FROM `my_project.analytics_XXXXXX.events_*` WHERE event_name = 'purchase' AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' AND user_properties.key = 'email'
Don’t forget to hash emails (SHA256) as per Google Ads requirements. You can use BigQuery’s built-in TO_HEX(SHA256(TO_BYTES(email))) function for that.
Tips on Optimizing BigQuery Queries
Here are some optimization tips that will save time and cost:
- Use partitioning: Always filter on _TABLE_SUFFIX to load only needed data.
- Select only necessary columns: Avoid SELECT * to reduce processing cost.
- Schedule queries: Automate reporting with scheduled queries and export results to Sheets or Data Studio.
- Monitor usage: Keep an eye on the query execution data using the BigQuery UI or billing dashboard.
Final Thoughts
Putting GA4 and BigQuery together is like giving your marketing toolkit X-ray vision. You get data with rich context, historical depth, and the flexibility to build exactly the reports you need. Whether you’re automating weekly performance dashboards or drilling into customer behavior at a granular level, BigQuery turns your GA4 data into an engine for better decision-making.
Start with the recipes shared here, experiment with your business rules, and iterate. The more you explore, the more value you’ll uncover. Happy querying!