{"id":459,"date":"2026-09-11T08:30:00","date_gmt":"2026-09-11T06:30:00","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/?p=459"},"modified":"2026-10-26T11:59:00","modified_gmt":"2026-10-26T10:59:00","slug":"automated-data-quality-alerts-for-ga4-with-looker-studio-bigquery","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/digital-analytics\/tutorials\/automated-data-quality-alerts-for-ga4-with-looker-studio-bigquery\/","title":{"rendered":"Automated Data Quality Alerts for GA4 with Looker Studio & BigQuery"},"content":{"rendered":"

A tracking failure can remain unnoticed while the website still works. A daily volume check provides a clue: how many events were exported, and how far does the count differ from a comparable weekday? BigQuery calculates the comparison; Looker Studio displays it. Notifications require a separately configured alert rule.<\/p>\n

The example checks four event names. It does not validate every parameter or the entire measurement pipeline. Campaigns, holidays and consent changes can also explain a flagged result.<\/p>\n

1. Data availability before event volume<\/h2>\n

The target is four days ago, compared with the same weekday in the previous eight weeks. The target is excluded from the reference mean. This delay allows time for late exports but does not certify completeness. GA4 has no universally guaranteed daily export time; 05:30 UTC is not a safe completion boundary.<\/p>\n

A missing daily table remains an unknown value. An absent event becomes zero only when its daily table exists. An existing table can still be incomplete. Here, session_start<\/code> counts that event; it does not recreate the GA4 sessions metric.<\/p>\n

\"Availability,
Availability, comparison and delivery need separate checks. A change from a constant baseline must not pass as an unremarkable result.<\/figcaption><\/figure>\n

2. SQL example with separate failure states<\/h2>\n

The project, dataset and every occurrence of the reporting timezone need replacement with the intended settings. The query expects GA4 daily tables with compatible schemas. If all matching tables or access permissions are missing, the job fails; separate operational monitoring must cover that case. A BigQuery trial run and cost estimate are required before scheduling.<\/p>\n

-- GoogleSQL; replace project, dataset and reporting timezone.\nWITH dates AS (\n  SELECT d AS metric_date,\n    DATE_SUB(CURRENT_DATE('Europe\/Warsaw'), INTERVAL 4 DAY) AS target_date\n  FROM UNNEST(GENERATE_DATE_ARRAY(\n    DATE_SUB(CURRENT_DATE('Europe\/Warsaw'), INTERVAL 60 DAY),\n    DATE_SUB(CURRENT_DATE('Europe\/Warsaw'), INTERVAL 4 DAY),\n    INTERVAL 7 DAY)) AS d\n),\navailable AS (\n  SELECT table_name\n  FROM `project_id.analytics_XXXXXXXXX.INFORMATION_SCHEMA.TABLES`\n  WHERE table_type = 'BASE TABLE'\n),\ncounts AS (\n  SELECT PARSE_DATE('%Y%m%d', event_date) AS metric_date,\n    event_name, COUNT(*) AS event_count\n  FROM `project_id.analytics_XXXXXXXXX.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN\n    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Europe\/Warsaw'), INTERVAL 60 DAY))\n    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Europe\/Warsaw'), INTERVAL 4 DAY))\n    AND event_name IN ('purchase', 'add_to_cart', 'begin_checkout', 'session_start')\n  GROUP BY 1, 2\n),\ngrid AS (\n  SELECT d.metric_date, d.target_date, n AS event_name,\n    IF(a.table_name IS NULL, NULL, COALESCE(c.event_count, 0)) AS event_count\n  FROM dates AS d\n  CROSS JOIN UNNEST(['purchase', 'add_to_cart', 'begin_checkout', 'session_start']) AS n\n  LEFT JOIN available AS a\n    ON a.table_name = CONCAT('events_', FORMAT_DATE('%Y%m%d', d.metric_date))\n  LEFT JOIN counts AS c ON c.metric_date = d.metric_date AND c.event_name = n\n),\nstats AS (\n  SELECT target_date AS metric_date, event_name,\n    MAX(IF(metric_date = target_date, event_count, NULL)) AS event_count,\n    COUNT(IF(metric_date < target_date, event_count, NULL)) AS reference_days,\n    AVG(IF(metric_date < target_date, event_count, NULL)) AS expected_avg,\n    STDDEV_SAMP(IF(metric_date < target_date, event_count, NULL)) AS reference_sd\n  FROM grid\n  GROUP BY target_date, event_name\n),\nscored AS (\n  SELECT *,\n    IF(reference_days = 8,\n      SAFE_DIVIDE(event_count - expected_avg, reference_sd), NULL) AS z_score\n  FROM stats\n),\nclassified AS (\n  SELECT *,\n    CASE\n      WHEN event_count IS NULL THEN 'MISSING_EXPORT'\n      WHEN reference_days != 8 THEN 'INCOMPLETE_BASELINE'\n      WHEN reference_sd = 0 AND event_count != expected_avg THEN 'CONSTANT_BASELINE_CHANGE'\n      WHEN reference_sd = 0 THEN 'NO_CHANGE'\n      WHEN z_score < -2.0 THEN 'DROP'\n      WHEN z_score > 3.0 THEN 'SPIKE'\n      ELSE 'WITHIN_RULE'\n    END AS quality_status\n  FROM scored\n)\nSELECT *, CURRENT_TIMESTAMP() AS evaluated_at,\n  IF(quality_status IN ('NO_CHANGE', 'WITHIN_RULE'), 0, 1) AS alert_flag\nFROM classified\nORDER BY event_name;\n<\/code><\/pre>\n
reference_days<\/code> must equal eight. A missing reference produces INCOMPLETE_BASELINE<\/code>; a missing target produces MISSING_EXPORT<\/code>. A constant baseline cannot support division by its standard deviation: eight values of 100 followed by zero therefore produce CONSTANT_BASELINE_CHANGE<\/code>. Eight values of 100 followed by 100 produce NO_CHANGE<\/code>.<\/p>\n
With a varying baseline, the z-score describes distance from the mean relative to the variation in the eight reference values. For example, a mean of 100, a sample standard deviation of 10 and a new value of 70 give (70 \u2212 100) \/ 10 = \u22123. The standard deviation measures how spread out the reference values are. The \u22122 and +3 thresholds are investigation rules, not calibrated significance tests. Eight observations provide limited evidence; multiple checks and low event volumes can produce misleading flags. WITHIN_RULE<\/code> only means that this rule did not trigger.<\/p>\n
3. Result storage and alert delivery<\/h2>\n
    \n
  1. A monitoring dataset is created in a suitable BigQuery region. The account running the query needs checked read and write permissions.<\/li>\n
  2. A manual first run is expected to return four rows including evaluated_at<\/code> and alert_flag<\/code>. The cost estimate needs review: the table range spans 57 days even though only nine comparison dates are needed.<\/li>\n
  3. Scheduling follows observed export availability. The current-result destination table can be replaced after each successful run. A separate history requires run identifiers and a retry-deduplication rule; blind appends create duplicates.<\/li>\n
  4. Looker Studio displays target date, event, count, reference mean, status and evaluation time. Pro chart alerts support email notifications and use default filters. Sending a report to Google Chat is a different feature from a conditional data alert.<\/li>\n
  5. Cloud Monitoring uses a separate query returning only alert rows. The automatic bigquerydatatransfer.googleapis.com\/transfer_config\/last_execution_job_rows<\/code> metric counts result rows, not z-scores. The rule filters to the correct config_id<\/code> and alerts above zero.<\/li>\n<\/ol>\n
    The alert query must follow a successful calculation and check freshness, the expected target date and all four result rows. An old healthy table must not conceal a failed new run. Job failures and missing runs need separate monitoring: the last row-count value can persist after a failure. Notification channels need verification with controlled cases before delivery can be relied on.<\/p>\n
    4. Acceptance checks before daily operation<\/h2>\n