BigQuery + GTM: Real-Time Tag Data Export for Custom Analytics Dashboards

Why BigQuery for Tag Data

Pre-built dashboards answer pre-built questions. Your questions are specific to your business. “Which tag causes the most LCP degradation on our top 10 landing pages?” “What is the consent denial rate by country, by week, for the last 6 months?” “Which GTM container version introduced the most tag failures?” No pre-built dashboard answers these. BigQuery does.

TagDrishti streams every tag event to your BigQuery dataset in real time. Every tag fire, every network request, every consent state change, every performance measurement — all available as SQL-queryable rows within seconds of the event occurring in the user’s browser.

Schema: Five Tables, 70+ Columns

The export writes to five tables in your BigQuery dataset:

tag_events

One row per tag fire. Key columns:

Column	Type	Description
event_id	STRING	Unique event identifier
session_id	STRING	Session identifier
timestamp	TIMESTAMP	Event time (microsecond precision)
tag_name	STRING	Tag name from GTM or auto-detected
tag_vendor	STRING	Vendor (google_analytics, meta, tiktok, etc.)
tag_type	STRING	Tag type (pageview, event, conversion)
page_url	STRING	Full page URL
page_path	STRING	URL path only
fire_status	STRING	success, blocked, error, timeout
http_status	INT64	Response status code (200, 403, 500, etc.)
load_time_ms	FLOAT64	Tag script load time in milliseconds
exec_time_ms	FLOAT64	Tag execution time in milliseconds
consent_state	STRING	Consent state at time of fire
country	STRING	User country (ISO 3166-1)
device_type	STRING	desktop, mobile, tablet
browser	STRING	Browser name and version

network_requests

One row per outbound HTTP request from a tag. Captures URL, method, status, response time, and payload size.

consent_events

One row per consent state change. Captures the previous state, new state, trigger (banner interaction, API call), and timestamp.

performance_metrics

One row per page load with CWV data. Captures LCP, INP, CLS, and per-tag contribution to each metric.

container_changes

One row per GTM container version publish. Captures version number, publish timestamp, user who published, and a summary of changes (tags added, modified, removed).

Ten SQL Queries for Real Business Questions

1. Tag failure rate by vendor, last 7 days:

SELECT tag_vendor,
       COUNTIF(fire_status != 'success') / COUNT(*) AS failure_rate
FROM tag_events
WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY tag_vendor
ORDER BY failure_rate DESC;

2. Top 10 pages by tag errors:

SELECT page_path, COUNT(*) AS error_count
FROM tag_events
WHERE fire_status = 'error'
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY page_path
ORDER BY error_count DESC
LIMIT 10;

3. Consent denial rate by country:

SELECT country,
       COUNTIF(new_state = 'denied') / COUNT(*) AS denial_rate
FROM consent_events
WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY country
ORDER BY denial_rate DESC;

4. Per-tag LCP impact (P75):

SELECT tag_name,
       APPROX_QUANTILES(load_time_ms, 100)[OFFSET(75)] AS p75_load_ms
FROM tag_events
WHERE fire_status = 'success'
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY tag_name
ORDER BY p75_load_ms DESC;

5. Ad blocker rate by browser:

SELECT browser,
       COUNTIF(fire_status = 'blocked') / COUNT(*) AS block_rate
FROM tag_events
WHERE tag_vendor = 'meta'
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY browser
ORDER BY block_rate DESC;

6. Purchase event data completeness:

SELECT DATE(timestamp) AS event_date,
       COUNTIF(transaction_id IS NOT NULL AND value > 0
               AND currency IS NOT NULL) / COUNT(*) AS complete_rate
FROM tag_events
WHERE tag_type = 'purchase'
GROUP BY event_date
ORDER BY event_date DESC;

7. GTM version publish impact on tag health:

SELECT cc.version_number,
       cc.publish_timestamp,
       AVG(CASE WHEN te.fire_status = 'error' THEN 1 ELSE 0 END) AS error_rate_after
FROM container_changes cc
JOIN tag_events te
  ON te.timestamp BETWEEN cc.publish_timestamp
     AND TIMESTAMP_ADD(cc.publish_timestamp, INTERVAL 2 HOUR)
GROUP BY cc.version_number, cc.publish_timestamp
ORDER BY error_rate_after DESC;

8. Tags firing without consent:

SELECT tag_name, tag_vendor, COUNT(*) AS violations
FROM tag_events
WHERE consent_state = 'denied'
  AND fire_status = 'success'
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY tag_name, tag_vendor
ORDER BY violations DESC;

9. Hourly tag fire volume (anomaly detection baseline):

SELECT tag_name,
       EXTRACT(HOUR FROM timestamp) AS hour_of_day,
       COUNT(*) AS fires
FROM tag_events
WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY tag_name, hour_of_day
ORDER BY tag_name, hour_of_day;

10. Mobile vs desktop tag performance:

SELECT device_type, tag_vendor,
       AVG(exec_time_ms) AS avg_exec_ms,
       APPROX_QUANTILES(exec_time_ms, 100)[OFFSET(75)] AS p75_exec_ms
FROM tag_events
WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY device_type, tag_vendor
ORDER BY p75_exec_ms DESC;

BI Tool Connections

Once data is in BigQuery, connect any BI tool directly: Looker Studio (free, native BigQuery connector), Looker, Tableau, Power BI, Metabase, or Grafana. Create custom dashboards that combine tag health data with your business metrics. Overlay tag failure rates on revenue graphs to quantify the business impact of tracking issues.