GA4

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_YYYYMMDD table per day.
  • Each row is one event. Event parameters sit in a repeated event_params field, and e-commerce items in an items array, so many queries use UNNEST.
  • 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.

Direct Implementation White-Label for Agencies

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.

✓ 24–48h Emergency SLA ✓ Strict Non-Solicitation & NDA ✓ 8.5+ Event Match Quality
Jobair Mahmud

Jobair Mahmud

AI-Driven Performance Marketing & Web Analytics Consultant

Helping eCommerce brands and marketing agencies trust their ads data, scale Smart Bidding, and eliminate wasted ad spend through bulletproof server-side tracking and PPC management.