Marketing Analytics with SQL: 6 GA4 BigQuery Queries for Marketers
GA4 reports are fine for quick answers, but they sample, threshold and hide the raw events. Once GA4 is linked to BigQuery, you can query every event yourself with SQL. That is where the most useful marketing analysis happens: data quality checks, product-level revenue and joins with your store or CRM.
These six queries are a practical starting point. Replace your_project.analytics_123456789 with your own project and dataset name.
Before you start
- In GA4, go to Admin → Product links → BigQuery links and link a project. Daily export creates one
events_YYYYMMDDtable per day. - Each row is one event. Event parameters sit in a repeated
event_paramsfield, and e-commerce items in anitemsarray, so many queries useUNNEST. - The export only starts from the day you link it; it does not backfill older data.
1. Event health check: what is actually being collected?
SELECT
event_name,
COUNT(*) AS events,
COUNT(DISTINCT user_pseudo_id) AS users
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261019'
GROUP BY event_name
ORDER BY events DESC;
Run this first on any new property. Look for misspelled duplicates (Purchase and purchase), events you didn’t expect, and key events with suspiciously low counts.
2. Daily purchases and revenue
SELECT
PARSE_DATE('%Y%m%d', event_date) AS day,
COUNT(*) AS purchases,
ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261019'
AND event_name = 'purchase'
GROUP BY day
ORDER BY day;
Compare these totals with your store admin for the same dates and time zone. A consistent gap points to blocked browser events; a sudden drop points to a broken tag.
3. Find duplicate transactions
SELECT
ecommerce.transaction_id,
COUNT(*) AS purchase_events
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261019'
AND event_name = 'purchase'
GROUP BY ecommerce.transaction_id
HAVING COUNT(*) > 1
ORDER BY purchase_events DESC;
Any transaction ID listed here was sent more than once, usually from thank-you page reloads or two purchase tags. A NULL transaction ID in the results means some purchases are sent without one, which also needs fixing.
4. Revenue by product
SELECT
item.item_name,
SUM(item.quantity) AS units,
ROUND(SUM(item.item_revenue), 2) AS item_revenue
FROM `your_project.analytics_123456789.events_*`,
UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261019'
AND event_name = 'purchase'
GROUP BY item.item_name
ORDER BY item_revenue DESC
LIMIT 50;
This is the foundation of product-level analysis. Join it with a cost or margin table to see which products actually make money.
5. New users by first acquisition source
SELECT
traffic_source.source,
traffic_source.medium,
COUNT(DISTINCT user_pseudo_id) AS new_users
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261019'
AND event_name = 'first_visit'
GROUP BY 1, 2
ORDER BY new_users DESC;
The traffic_source fields describe how the user was first acquired, not the source of each session. Use this for acquisition questions, and session-level source fields when you need per-session attribution.
6. A simple purchase funnel
WITH steps AS (
SELECT
user_pseudo_id,
COUNTIF(event_name = 'view_item') > 0 AS viewed,
COUNTIF(event_name = 'add_to_cart') > 0 AS added,
COUNTIF(event_name = 'begin_checkout') > 0 AS checkout,
COUNTIF(event_name = 'purchase') > 0 AS purchased
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261019'
GROUP BY user_pseudo_id
)
SELECT
COUNTIF(viewed) AS viewed_item,
COUNTIF(viewed AND added) AS added_to_cart,
COUNTIF(viewed AND added AND checkout) AS started_checkout,
COUNTIF(viewed AND added AND checkout AND purchased) AS purchased
FROM steps;
This counts users who completed each step within the period. It doesn’t enforce the order of steps, but it is enough to show where the biggest drop-off is.
Keep costs under control
BigQuery charges for the data each query scans. Always filter with _TABLE_SUFFIX, select only the columns you need, and preview the estimated bytes before running large queries.
From queries to decisions
The real value comes when GA4 data is joined with your store orders and CRM outcomes: revenue per customer, lifetime value by channel, and which campaigns bring leads that close. That is what I build in SQL marketing analytics and BI dashboards, on top of tracking you can trust. If the numbers in query 2 or 3 look wrong, start with a tracking audit.
Need this implemented, audited, or white-labeled?
Whether you are an eCommerce brand owner fixing signal loss or a digital marketing agency needing a senior analytics engineer under strict NDA, I deliver 24–48h SLA turnaround with 8.5+ EMQ match rates.

