The query lists, for the last 28 full days of the GA4 export’s daily events_* tables, every planned event and how often it fired, every planned parameter and how often it arrived, and every event that fired but is not in the plan. It follows the plan above as you change it. Paste it into the BigQuery console and replace the table with your own export.
-- GA4 tracking plan check: E-commerce, 18 events.
-- Plan: https://lp-data.cloud/tools/ga4-tracking-plan-generator
-- For the last 28 full days: did each planned event fire, did each planned
-- parameter arrive as the planned type, and which events fired that are not in
-- the plan. Reads event_name, event_params, items and privacy_info only.
-- Replace the table with your own GA4 export: <project>.analytics_<property id>.
WITH plan AS (
SELECT * FROM UNNEST([
STRUCT('view_item_list' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('item_list_id', 'string'), ('item_list_name', 'string'), ('items', 'items')] AS params),
STRUCT('select_item' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('item_list_id', 'string'), ('item_list_name', 'string'), ('items', 'items')] AS params),
STRUCT('view_item' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('items', 'items')] AS params),
STRUCT('search' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('search_term', 'string')] AS params),
STRUCT('add_to_wishlist' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('items', 'items')] AS params),
STRUCT('add_to_cart' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('items', 'items')] AS params),
STRUCT('remove_from_cart' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('items', 'items')] AS params),
STRUCT('view_cart' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('items', 'items')] AS params),
STRUCT('begin_checkout' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('coupon', 'string'), ('items', 'items')] AS params),
STRUCT('add_shipping_info' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('shipping_tier', 'string'), ('items', 'items')] AS params),
STRUCT('add_payment_info' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('currency', 'string'), ('value', 'number'), ('payment_type', 'string'), ('items', 'items')] AS params),
STRUCT('purchase' AS event_name, TRUE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('transaction_id', 'string'), ('currency', 'string'), ('value', 'number'), ('tax', 'number'), ('shipping', 'number'), ('coupon', 'string'), ('items', 'items')] AS params),
STRUCT('refund' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('transaction_id', 'string'), ('currency', 'string'), ('value', 'number'), ('items', 'items')] AS params),
STRUCT('view_promotion' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('promotion_id', 'string'), ('promotion_name', 'string'), ('creative_slot', 'string'), ('items', 'items')] AS params),
STRUCT('select_promotion' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('promotion_id', 'string'), ('promotion_name', 'string'), ('creative_slot', 'string'), ('items', 'items')] AS params),
STRUCT('sign_up' AS event_name, TRUE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('method', 'string')] AS params),
STRUCT('login' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('method', 'string')] AS params),
STRUCT('newsletter_signup' AS event_name, FALSE AS key_event, ARRAY<STRUCT<name STRING, type STRING>>[('form_location', 'string')] AS params)
])
),
ev AS (
SELECT event_name, event_params, items, privacy_info.analytics_storage AS analytics_storage
FROM `your-project.analytics_123456789.events_*`
-- Daily tables only: the events_intraday_ suffixes sort after the dates.
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),
event_counts AS (
SELECT
event_name,
COUNT(*) AS events,
COUNTIF(analytics_storage = 'No') AS consent_denied,
COUNTIF(ARRAY_LENGTH(items) > 0) AS with_items
FROM ev
GROUP BY event_name
),
param_counts AS (
SELECT
e.event_name,
ep.key AS param,
COUNT(*) AS events_with_param,
COUNTIF(ep.value.string_value IS NOT NULL) AS as_string,
COUNTIF(ep.value.int_value IS NOT NULL OR ep.value.double_value IS NOT NULL OR ep.value.float_value IS NOT NULL) AS as_number
FROM ev AS e, UNNEST(e.event_params) AS ep
WHERE e.event_name IN (SELECT event_name FROM plan)
GROUP BY 1, 2
),
checks AS (
SELECT
1 AS sort_order,
'planned event' AS row_type,
p.event_name,
CAST(NULL AS STRING) AS parameter,
p.key_event,
IFNULL(c.events, 0) AS events_28d,
IFNULL(c.consent_denied, 0) AS consent_denied_28d,
IF(c.events IS NULL, 'MISSING: never fired', 'ok') AS status
FROM plan AS p
LEFT JOIN event_counts AS c USING (event_name)
UNION ALL
SELECT
2,
'planned parameter',
p.event_name,
prm.name,
p.key_event,
IFNULL(IF(prm.type = 'items', c.with_items, pc.events_with_param), 0),
NULL,
CASE
WHEN c.events IS NULL THEN 'event never fired'
WHEN prm.type = 'items' AND c.with_items = 0 THEN 'MISSING: items always empty'
WHEN prm.type = 'items' AND c.with_items < c.events THEN FORMAT('PARTIAL: on %.0f%% of events', 100 * c.with_items / c.events)
WHEN prm.type = 'items' THEN 'ok'
WHEN pc.events_with_param IS NULL THEN 'MISSING: never sent'
WHEN prm.type = 'number' AND pc.as_number < pc.events_with_param THEN 'WRONG TYPE: sent as text on some events'
WHEN prm.type = 'string' AND pc.as_string < pc.events_with_param THEN 'WRONG TYPE: sent as a number on some events'
WHEN pc.events_with_param < c.events THEN FORMAT('PARTIAL: on %.0f%% of events', 100 * pc.events_with_param / c.events)
ELSE 'ok'
END
FROM plan AS p
CROSS JOIN UNNEST(p.params) AS prm
LEFT JOIN event_counts AS c ON c.event_name = p.event_name
LEFT JOIN param_counts AS pc ON pc.event_name = p.event_name AND pc.param = prm.name
UNION ALL
SELECT
IF(c.event_name IN UNNEST(automatic), 4, 3),
IF(c.event_name IN UNNEST(automatic), 'automatic event', 'not in plan'),
c.event_name,
NULL,
NULL,
c.events,
c.consent_denied,
IF(c.event_name IN UNNEST(automatic), 'collected by GA4 itself', 'FIRED BUT NOT IN THE PLAN')
FROM event_counts AS c
CROSS JOIN (SELECT ['click', 'file_download', 'first_visit', 'form_start', 'form_submit', 'page_view', 'scroll', 'session_start', 'user_engagement', 'video_complete', 'video_progress', 'video_start', 'view_search_results'] AS automatic)
WHERE c.event_name NOT IN (SELECT event_name FROM plan)
)
SELECT * EXCEPT (sort_order)
FROM checks
ORDER BY sort_order, event_name, parameterReading the result
- MISSING: never fired on a planned event: it is not implemented, or it fires on a page or app the export does not cover.
- MISSING: never sent on a parameter: the event fires without it. Ecommerce events without an
itemsarray do not reach the ecommerce reports. - WRONG TYPE: a number sent as text (a value of "49.00" in quotes) or an ID sent as a number. GA4 stores them in different columns, so sums and joins quietly break.
- PARTIAL: the parameter arrives on some of the events only, usually because one of several places that fire the event was missed.
- FIRED BUT NOT IN THE PLAN: an old event, a typo of a planned one, or something a tag manager added. Each one is either planned or removed.
consent_denied_28dcounts events sent withanalytics_storagedenied under advanced consent mode. Events from automatic collection and enhanced measurement are labelled separately (Google’s list).
It reads four columns over 28 days, so it scans a fraction of the export; the BigQuery console shows the bytes before you run it. If a parameter you know you send shows as missing, check whether GA4 stored it in a column of its own instead, such as ecommerce.transaction_id.