Explorer les logs avancés sur Google BigQuery (GBQ)

SELECT *
FROM `<your-dataset>.tagging_outgoing_requests`
WHERE TIMESTAMP_TRUNC(request_at, DAY) = TIMESTAMP("2026-04-20")   -- replace with the date you want
  AND response_status_code >= 400
LIMIT 1000;

SELECT
  request_at,
  JSON_VALUE(request_body, "$.data[0].event_name") AS event_name,
  JSON_VALUE(request_body, "$.data[0].event_source_url") AS event_source_url,
  JSON_VALUE(request_body, "$.data[0].custom_data.currency") AS currency,
  JSON_VALUE(request_body, "$.data[0].custom_data.value") AS value
FROM `<your-dataset>.tagging_outgoing_requests`
WHERE host LIKE "%facebook%"                                      -- replace with the vendor you investigate
  AND TIMESTAMP_TRUNC(request_at, DAY) = TIMESTAMP("2026-04-20")  -- replace with the date you want
  AND response_status_code >= 400
LIMIT 1000;

SELECT
  TIMESTAMP_TRUNC(request_at, DAY) AS time_bucket,
  COUNTIF(response_status_code = 200) AS success_count,
  COUNTIF(response_status_code >= 400) AS error_count,
  SAFE_DIVIDE(COUNTIF(response_status_code >= 400), COUNT(*)) * 100 AS error_rate_pct
FROM `<your-dataset>.tagging_outgoing_requests`
WHERE request_at >= "2026-04-20"   -- replace with your start date
  AND host LIKE "%facebook%"       -- replace with the vendor you investigate
GROUP BY time_bucket
ORDER BY time_bucket ASC;

SELECT
  TIMESTAMP_TRUNC(event_timestamp, DAY) AS day,
  SUM(IF(consent_settings LIKE '%G111%', 1, 0)) AS consent_G111,
  SUM(IF(consent_settings LIKE '%G110%', 1, 0)) AS consent_G110,
  SUM(IF(consent_settings LIKE '%G101%', 1, 0)) AS consent_G101,
  SUM(IF(consent_settings LIKE '%G100%', 1, 0)) AS consent_G100,
  SUM(IF(consent_settings = "" OR consent_settings IS NULL, 1, 0)) AS consent_undefined,
  ROUND(SUM(IF(consent_settings LIKE '%G111%', 1, 0)) / COUNT(*) * 100, 2) AS rate_G111,
  ROUND(SUM(IF(consent_settings LIKE '%G100%', 1, 0)) / COUNT(*) * 100, 2) AS rate_G100
  -- add rate columns for G110 / G101 / undefined the same way if needed
FROM `<your-dataset>.gtm_monitoring`
WHERE TIMESTAMP_TRUNC(event_timestamp, DAY) BETWEEN TIMESTAMP('2026-04-01') AND TIMESTAMP('2026-04-30')   -- replace with your period
  AND event_name = "purchase"        -- replace with the event you want
GROUP BY day
ORDER BY day;

-- Requires the full incoming request logs (tagging_requests); ask support to activate them.
SELECT
  JSON_VALUE(request_headers, "$.user-agent") AS user_agent,
  JSON_VALUE(request_headers, "$.x-forwarded-for") AS x_forwarded_for,
  MIN(request_at) AS first_seen,
  MAX(request_at) AS last_seen,
  COUNT(*) AS hits
FROM `<your-dataset>.tagging_requests`
WHERE TIMESTAMP_TRUNC(request_at, DAY) BETWEEN TIMESTAMP("2026-04-01") AND TIMESTAMP("2026-04-30")   -- replace with your period
GROUP BY user_agent, x_forwarded_for
ORDER BY hits DESC;

SELECT tag_id, name, is_paused, template_name
FROM `<your-dataset>.gtm_tags`
WHERE CAST(version_id AS INT64) = (
  SELECT MAX(CAST(version_id AS INT64))
  FROM `<your-dataset>.gtm_tags`
)
AND name LIKE "%Meta%CAPI%"   -- replace with (part of) your tag name
ORDER BY name;

-- Requires the full incoming request logs (tagging_requests); ask support to activate them.
SELECT
  request_at,
  REGEXP_EXTRACT(query, r'(?:^|&)tid=([^&]+)') AS tid,
  REGEXP_EXTRACT(IF(request_body > "", request_body, query), r'(?:^|&)en=([^&]+)') AS event_name,
  REGEXP_EXTRACT(IF(request_body > "", request_body, query), r'(?:^|&)ep\.transaction_id=([^&]+)') AS transaction_id,
  REGEXP_EXTRACT(IF(request_body > "", request_body, query), r'(?:^|&)epn\.value=([^&]+)') AS value,
  REGEXP_EXTRACT(query, r'(?:^|&)cu=([^&]+)') AS currency,
  REGEXP_EXTRACT(query, r'(?:^|&)gcs=([^&]+)') AS gcs
FROM `<your-dataset>.tagging_requests`
WHERE TIMESTAMP_TRUNC(request_at, DAY) = TIMESTAMP("2026-04-20")               -- replace with the date you want
  AND (request_body LIKE "%en=purchase%" OR query LIKE "%en=purchase%")        -- replace purchase with your event
ORDER BY request_at;

-- Extract a transaction_id from the body
REGEXP_EXTRACT(request_body, r'ep\.transaction_id=([^&]+)') AS transaction_id
 
-- Extract a header from the request_headers JSON column
JSON_VALUE(request_headers, "$.user-agent") AS user_agent
JSON_VALUE(request_headers, "$.x-forwarded-for") AS x_forwarded_for