{"id":"e50b7b4d-badd-4ee0-8db4-84f5f4faaadb","shortId":"KyFtDw","kind":"skill","title":"ga4-bigquery-schema","tagline":"GA4 BigQuery Export Schema Reference — complete field reference, nested structures, query patterns, and performance tips","description":"# GA4 BigQuery Export Schema Reference\n\nComplete reference guide to the Google Analytics 4 BigQuery export schema, including table structure, nested fields, common queries, and data processing best practices.\n\nFull docs: https://cogny.com/docs/ga4-bigquery-export-schema\n\n## Usage\n\n```\n/ga4-bigquery-schema                      # Show full schema overview\n/ga4-bigquery-schema event_params         # Explain event_params structure\n/ga4-bigquery-schema conversion funnel    # Show conversion funnel query pattern\n/ga4-bigquery-schema ecommerce            # Explain ecommerce fields\n```\n\n## Instructions\n\nYou are a GA4 BigQuery schema expert. Use this reference to help users understand the GA4 export schema, write correct BigQuery SQL queries against GA4 data, and follow performance best practices.\n\nWhen the user asks a question, find the relevant section below and provide precise, actionable answers with ready-to-use SQL examples.\n\nIf the user provides a specific topic as an argument, focus on that area. Otherwise, provide an overview of the schema structure.\n\n---\n\n## Overview\n\nGoogle Analytics 4 exports raw event data to BigQuery in a nested, denormalized format.\n\n**Daily Tables:** `analytics_PROPERTY_ID.events_YYYYMMDD`\n**Intraday Tables:** `analytics_PROPERTY_ID.events_intraday_YYYYMMDD`\n\nEach row represents a single event with nested fields for event parameters, user properties, and e-commerce data.\n\n```sql\n-- View table schema\nSELECT\n  column_name,\n  data_type,\n  description\nFROM `project.analytics_123456789.INFORMATION_SCHEMA.COLUMNS`\nWHERE table_name LIKE 'events_%'\nORDER BY ordinal_position\n```\n\n## Top-Level Fields\n\n| Field | Type | Description |\n|-------|------|-------------|\n| `event_date` | STRING | Date when the event was logged (YYYYMMDD format) |\n| `event_timestamp` | INTEGER | Time when the event was logged (microseconds since Unix epoch) |\n| `event_name` | STRING | Name of the event (e.g., 'page_view', 'purchase') |\n| `event_params` | ARRAY\\<STRUCT\\> | Array of event parameters |\n| `event_previous_timestamp` | INTEGER | Timestamp of previous event by this user |\n| `event_value_in_usd` | FLOAT | Value of the event in USD |\n| `event_bundle_sequence_id` | INTEGER | Sequential ID of the event bundle |\n| `event_server_timestamp_offset` | INTEGER | Timestamp offset between collection and server |\n| `user_id` | STRING | User ID set via setUserId API |\n| `user_pseudo_id` | STRING | Pseudonymous ID for the user (cookie-based) |\n| `user_properties` | ARRAY\\<STRUCT\\> | Array of user properties |\n| `user_first_touch_timestamp` | INTEGER | First time user visited (microseconds) |\n| `user_ltv` | STRUCT | User lifetime value information |\n| `device` | STRUCT | Device information |\n| `geo` | STRUCT | Geographic information |\n| `app_info` | STRUCT | App information (mobile apps) |\n| `traffic_source` | STRUCT | Traffic source information |\n| `stream_id` | STRING | Numeric ID of the data stream |\n| `platform` | STRING | Platform (web, ios, android) |\n| `ecommerce` | STRUCT | E-commerce transaction data |\n| `items` | ARRAY\\<STRUCT\\> | Array of item (product) details |\n\n## Nested Structures\n\n### event_params\n\nEvent parameters stored as key-value pairs:\n\n```sql\nSTRUCT<\n  key STRING,\n  value STRUCT<\n    string_value STRING,\n    int_value INT64,\n    float_value FLOAT64,\n    double_value FLOAT64\n  >\n>\n```\n\n**Common event parameters:**\n\n| Key | Type | Description |\n|-----|------|-------------|\n| `page_location` | string | Full URL of the page |\n| `page_title` | string | Title of the page |\n| `page_referrer` | string | Referrer URL |\n| `engagement_time_msec` | int | Engagement time in milliseconds |\n| `session_engaged` | int | Whether session was engaged (1/0) |\n| `ga_session_id` | int | Session ID |\n| `ga_session_number` | int | Session number for user |\n\n**Example — extract event params:**\n\n```sql\nSELECT\n  event_name,\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_location,\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') as page_title,\n  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') as engagement_time\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  AND event_name = 'page_view'\nLIMIT 100\n```\n\n### user_properties\n\nUser properties stored as key-value pairs:\n\n```sql\nSTRUCT<\n  key STRING,\n  value STRUCT<\n    string_value STRING,\n    int_value INT64,\n    float_value FLOAT64,\n    double_value FLOAT64,\n    set_timestamp_micros INT64\n  >\n>\n```\n\n**Example — extract user properties:**\n\n```sql\nSELECT\n  user_pseudo_id,\n  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_type') as user_type,\n  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'plan_level') as plan_level\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\nGROUP BY user_pseudo_id, user_type, plan_level\nLIMIT 100\n```\n\n### device\n\n```sql\nSTRUCT<\n  category STRING,              -- desktop, mobile, tablet\n  mobile_brand_name STRING,     -- Apple, Samsung, etc.\n  mobile_model_name STRING,     -- iPhone 12, Galaxy S21, etc.\n  mobile_marketing_name STRING,\n  mobile_os_hardware_model STRING,\n  operating_system STRING,      -- iOS, Android, Windows, macOS\n  operating_system_version STRING,\n  vendor_id STRING,\n  advertising_id STRING,\n  language STRING,              -- en-us, fr-fr, etc.\n  is_limited_ad_tracking STRING,\n  time_zone_offset_seconds INT64,\n  browser STRING,               -- Chrome, Safari, Firefox\n  browser_version STRING,\n  web_info STRUCT<\n    browser STRING,\n    browser_version STRING,\n    hostname STRING\n  >\n>\n```\n\n**Example — device breakdown:**\n\n```sql\nSELECT\n  device.category as device_category,\n  device.operating_system,\n  device.browser,\n  COUNT(*) as event_count,\n  COUNT(DISTINCT user_pseudo_id) as users\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\nGROUP BY 1, 2, 3\nORDER BY event_count DESC\n```\n\n### geo\n\n```sql\nSTRUCT<\n  continent STRING,     -- Americas, Europe, Asia, etc.\n  sub_continent STRING, -- Northern Europe, Western Asia, etc.\n  country STRING,       -- United States, United Kingdom, etc.\n  region STRING,        -- California, England, etc.\n  metro STRING,         -- Metro area\n  city STRING\n>\n```\n\n**Example — geographic breakdown:**\n\n```sql\nSELECT\n  geo.country,\n  geo.city,\n  COUNT(DISTINCT user_pseudo_id) as users,\n  COUNT(*) as events\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\nGROUP BY 1, 2\nORDER BY users DESC\nLIMIT 100\n```\n\n### traffic_source\n\n```sql\nSTRUCT<\n  name STRING,   -- Traffic source name (google, facebook, etc.)\n  medium STRING, -- Traffic medium (organic, cpc, referral, etc.)\n  source STRING  -- Traffic source (google, facebook.com, etc.)\n>\n```\n\n**Example — traffic source performance:**\n\n```sql\nSELECT\n  traffic_source.source,\n  traffic_source.medium,\n  COUNT(DISTINCT user_pseudo_id) as users,\n  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) as purchasers,\n  SAFE_DIVIDE(\n    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END),\n    COUNT(DISTINCT user_pseudo_id)\n  ) as conversion_rate\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\nGROUP BY 1, 2\nORDER BY users DESC\n```\n\n### ecommerce\n\n```sql\nSTRUCT<\n  total_item_quantity INT64,\n  purchase_revenue_in_usd FLOAT64,\n  purchase_revenue FLOAT64,\n  refund_value_in_usd FLOAT64,\n  refund_value FLOAT64,\n  shipping_value_in_usd FLOAT64,\n  shipping_value FLOAT64,\n  tax_value_in_usd FLOAT64,\n  tax_value FLOAT64,\n  unique_items INT64,\n  transaction_id STRING\n>\n```\n\n**Example — revenue analysis:**\n\n```sql\nSELECT\n  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,\n  COUNT(DISTINCT ecommerce.transaction_id) as transactions,\n  SUM(ecommerce.purchase_revenue_in_usd) as revenue,\n  AVG(ecommerce.purchase_revenue_in_usd) as avg_order_value\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))\n                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  AND event_name = 'purchase'\n  AND ecommerce.transaction_id IS NOT NULL\nGROUP BY 1\nORDER BY 1 DESC\n```\n\n### items\n\n```sql\nSTRUCT<\n  item_id STRING,\n  item_name STRING,\n  item_brand STRING,\n  item_variant STRING,\n  item_category STRING,\n  item_category2 STRING,\n  item_category3 STRING,\n  item_category4 STRING,\n  item_category5 STRING,\n  price_in_usd FLOAT64,\n  price FLOAT64,\n  quantity INT64,\n  item_revenue_in_usd FLOAT64,\n  item_revenue FLOAT64,\n  item_refund_in_usd FLOAT64,\n  item_refund FLOAT64,\n  coupon STRING,\n  affiliation STRING,\n  location_id STRING,\n  item_list_id STRING,\n  item_list_name STRING,\n  item_list_index STRING,\n  promotion_id STRING,\n  promotion_name STRING,\n  creative_name STRING,\n  creative_slot STRING\n>\n```\n\n**Example — product performance:**\n\n```sql\nSELECT\n  item.item_name,\n  item.item_category,\n  SUM(item.quantity) as total_quantity,\n  SUM(item.item_revenue_in_usd) as total_revenue,\n  COUNT(DISTINCT ecommerce.transaction_id) as transactions\nFROM `project.analytics_123456789.events_*`,\n  UNNEST(items) as item\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  AND event_name = 'purchase'\nGROUP BY 1, 2\nORDER BY total_revenue DESC\nLIMIT 100\n```\n\n## Common Query Patterns\n\n### Sessions and Users\n\n```sql\nSELECT\n  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,\n  COUNT(DISTINCT user_pseudo_id) as users,\n  COUNT(DISTINCT CONCAT(user_pseudo_id,\n    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) as sessions,\n  SAFE_DIVIDE(\n    COUNT(DISTINCT CONCAT(user_pseudo_id,\n      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))),\n    COUNT(DISTINCT user_pseudo_id)\n  ) as sessions_per_user\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))\n                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\nGROUP BY 1\nORDER BY 1 DESC\n```\n\n### Page Views\n\n```sql\nSELECT\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_location,\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') as page_title,\n  COUNT(*) as page_views,\n  COUNT(DISTINCT user_pseudo_id) as unique_users,\n  AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 as avg_engagement_seconds\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  AND event_name = 'page_view'\nGROUP BY 1, 2\nORDER BY page_views DESC\nLIMIT 100\n```\n\n### Conversion Funnel\n\n```sql\nWITH funnel_steps AS (\n  SELECT\n    user_pseudo_id,\n    COUNTIF(event_name = 'page_view') as step_1_landing,\n    COUNTIF(event_name = 'view_item') as step_2_product_view,\n    COUNTIF(event_name = 'add_to_cart') as step_3_add_to_cart,\n    COUNTIF(event_name = 'begin_checkout') as step_4_checkout,\n    COUNTIF(event_name = 'purchase') as step_5_purchase\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  GROUP BY user_pseudo_id\n)\n\nSELECT\n  'Landing' as step, 1 as step_number,\n  COUNT(DISTINCT CASE WHEN step_1_landing > 0 THEN user_pseudo_id END) as users,\n  1.0 as conversion_rate\nFROM funnel_steps\nUNION ALL\nSELECT 'Product View', 2,\n  COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END),\n  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END),\n              COUNT(DISTINCT CASE WHEN step_1_landing > 0 THEN user_pseudo_id END))\nFROM funnel_steps\nUNION ALL\nSELECT 'Add to Cart', 3,\n  COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END),\n  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END),\n              COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END))\nFROM funnel_steps\nUNION ALL\nSELECT 'Checkout', 4,\n  COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END),\n  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END),\n              COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END))\nFROM funnel_steps\nUNION ALL\nSELECT 'Purchase', 5,\n  COUNT(DISTINCT CASE WHEN step_5_purchase > 0 THEN user_pseudo_id END),\n  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_5_purchase > 0 THEN user_pseudo_id END),\n              COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END))\nFROM funnel_steps\nORDER BY step_number\n```\n\n### User Acquisition\n\n```sql\nWITH first_visit AS (\n  SELECT\n    user_pseudo_id,\n    MIN(event_timestamp) as first_visit_timestamp,\n    ARRAY_AGG(\n      STRUCT(traffic_source.source, traffic_source.medium, traffic_source.name)\n      ORDER BY event_timestamp LIMIT 1\n    )[OFFSET(0)] as first_traffic_source\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n    AND event_name = 'first_visit'\n  GROUP BY user_pseudo_id\n)\n\nSELECT\n  first_traffic_source.source as acquisition_source,\n  first_traffic_source.medium as acquisition_medium,\n  COUNT(DISTINCT user_pseudo_id) as new_users,\n  COUNT(DISTINCT CASE WHEN purchase_count > 0 THEN user_pseudo_id END) as purchasers,\n  SAFE_DIVIDE(\n    COUNT(DISTINCT CASE WHEN purchase_count > 0 THEN user_pseudo_id END),\n    COUNT(DISTINCT user_pseudo_id)\n  ) as conversion_rate\nFROM first_visit\nLEFT JOIN (\n  SELECT user_pseudo_id, COUNT(*) as purchase_count\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n    AND event_name = 'purchase'\n  GROUP BY user_pseudo_id\n) purchases USING(user_pseudo_id)\nGROUP BY 1, 2\nORDER BY new_users DESC\n```\n\n### Cohort Retention\n\n```sql\nWITH cohorts AS (\n  SELECT\n    user_pseudo_id,\n    DATE(TIMESTAMP_MICROS(MIN(user_first_touch_timestamp))) as cohort_date\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  GROUP BY user_pseudo_id\n),\nuser_activity AS (\n  SELECT user_pseudo_id, DATE(TIMESTAMP_MICROS(event_timestamp)) as activity_date\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  GROUP BY user_pseudo_id, activity_date\n)\n\nSELECT\n  cohort_date,\n  DATE_DIFF(activity_date, cohort_date, DAY) as days_since_cohort,\n  COUNT(DISTINCT c.user_pseudo_id) as cohort_size,\n  COUNT(DISTINCT a.user_pseudo_id) as active_users,\n  SAFE_DIVIDE(COUNT(DISTINCT a.user_pseudo_id), COUNT(DISTINCT c.user_pseudo_id)) as retention_rate\nFROM cohorts c\nLEFT JOIN user_activity a USING(user_pseudo_id)\nWHERE DATE_DIFF(activity_date, cohort_date, DAY) IN (0, 1, 7, 14, 30, 60, 90)\nGROUP BY 1, 2\nORDER BY 1 DESC, 2\n```\n\n## Performance Best Practices\n\n### Partition Pruning — always use _TABLE_SUFFIX\n\n```sql\n-- GOOD: Uses partition pruning\nWHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'\n\n-- BAD: Scans all partitions\nWHERE event_date BETWEEN '20250101' AND '20250131'\n```\n\n### Clustering — filter by event_name\n\nGA4 tables are clustered by `event_name`. Always include an `event_name` filter when possible.\n\n### Materialized Views\n\nCreate materialized views for frequently accessed aggregations:\n\n```sql\nCREATE MATERIALIZED VIEW `project.analytics_123456789.daily_summary`\nAS\nSELECT\n  event_date,\n  traffic_source.source,\n  traffic_source.medium,\n  device.category,\n  COUNT(DISTINCT user_pseudo_id) as users,\n  COUNT(*) as events,\n  COUNTIF(event_name = 'purchase') as purchases,\n  SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue_in_usd END) as revenue\nFROM `project.analytics_123456789.events_*`\nWHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))\nGROUP BY 1, 2, 3, 4\n```\n\n## Data Quality Checks\n\n### Check for missing export dates\n\n```sql\nWITH date_range AS (\n  SELECT date\n  FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY), CURRENT_DATE() - 1)) as date\n),\nexported_dates AS (\n  SELECT DISTINCT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) as date\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n)\nSELECT dr.date, 'Missing' as status\nFROM date_range dr\nLEFT JOIN exported_dates ed USING(date)\nWHERE ed.date IS NULL\nORDER BY dr.date DESC\n```\n\n### Validate event count consistency\n\n```sql\nWITH daily_counts AS (\n  SELECT\n    DATE(TIMESTAMP_MICROS(event_timestamp)) as date,\n    COUNT(*) as event_count,\n    COUNT(DISTINCT user_pseudo_id) as user_count\n  FROM `project.analytics_123456789.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))\n                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)\n  GROUP BY 1\n)\nSELECT\n  date, event_count, user_count,\n  AVG(event_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) as avg_7day,\n  event_count / NULLIF(AVG(event_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING), 0) as variance_ratio\nFROM daily_counts\nORDER BY date DESC\n```\n\n## Resources\n\n- **GA4 BigQuery Export Schema:** https://support.google.com/analytics/answer/7029846\n- **BigQuery Best Practices:** https://cloud.google.com/bigquery/docs/best-practices\n- **GA4 Event Reference:** https://developers.google.com/analytics/devguides/collection/ga4/reference/events\n- **Full Cogny Docs:** https://cogny.com/docs/ga4-bigquery-export-schema","tags":["ga4","bigquery","schema","claude","code","marketing","skills","cognyai","agent-skills","ai-agents","claude-code","claude-skills"],"capabilities":["skill","source-cognyai","skill-ga4-bigquery-schema","topic-agent-skills","topic-ai-agents","topic-claude-code","topic-claude-skills","topic-cluade-mcp","topic-cursor","topic-geo","topic-growth-hacking","topic-llm","topic-marketing","topic-mcp","topic-seo"],"categories":["claude-code-marketing-skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/cognyai/claude-code-marketing-skills/ga4-bigquery-schema","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add cognyai/claude-code-marketing-skills","source_repo":"https://github.com/cognyai/claude-code-marketing-skills","install_from":"skills.sh"}},"qualityScore":"0.471","qualityRationale":"deterministic score 0.47 from registry signals: · indexed on github topic:agent-skills · 42 github stars · SKILL.md body (18,285 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-01T06:56:26.319Z","embedding":null,"createdAt":"2026-04-18T22:23:32.843Z","updatedAt":"2026-05-01T06:56:26.319Z","lastSeenAt":"2026-05-01T06:56:26.319Z","tsv":"'/analytics/answer/7029846':2479 '/analytics/devguides/collection/ga4/reference/events':2491 '/bigquery/docs/best-practices':2485 '/docs/ga4-bigquery-export-schema':52,2497 '/ga4-bigquery-schema':54,59,66,74 '0':1538,1567,1583,1596,1621,1638,1652,1673,1688,1703,1724,1739,1752,1796,1861,1877,2122,2461 '1':560,649,783,786,858,861,959,962,1073,1086,1089,1220,1227,1333,1336,1339,1415,1423,1450,1517,1527,1536,1594,1794,1827,1930,1947,2001,2048,2123,2131,2135,2261,2294,2337,2417,2420,2439,2459 '1.0':1546 '1/0':482 '100':567,660,868,1235,1431 '1000':1398 '12':681 '14':2125 '2':787,862,963,1228,1424,1459,1558,1564,1580,1649,1948,2132,2137,2262 '20250101':2156,2167 '20250131':2158,2169 '3':788,1470,1611,1617,1634,1699,2263 '30':1063,1323,1817,1920,2126,2290,2327,2407 '365':2257 '4':32,159,1481,1665,1671,1686,1750,2264 '5':1489,1716,1722,1737 '60':2127 '7':1507,2124,2436,2456 '7day':2443 '90':1991,2038,2128 'a.user':2080,2090 'access':2197 'acquisit':1766,1841,1845 'action':125 'activ':2008,2020,2054,2061,2084,2107,2116 'ad':722 'add':1465,1471,1608,1618,1635,1700 'advertis':708 'affili':1147 'agg':1784 'aggreg':2198 'alway':2143,2182 'america':799 'analysi':1015 'analyt':31,158 'analytics_property_id.events':173,177 'android':395,698 'answer':126 'api':322 'app':368,371,374 'appl':673 'area':147,826 'argument':143 'array':264,266,337,339,404,406,1783,2284 'asia':801,809 'ask':114 'avg':1038,1044,1385,1400,2427,2442,2447 'bad':2159 'base':334 'begin':1477 'best':46,109,2139,2481 'bigqueri':3,6,21,33,84,100,165,2474,2480 'brand':670,1101 'breakdown':750,831 'browser':730,735,741,743 'bundl':293,302 'c':2103 'c.user':2072,2095 'california':820 'cart':1467,1473,1610,1620,1637,1702 'case':913,929,1533,1561,1577,1591,1614,1631,1646,1668,1683,1696,1719,1734,1747,1857,1873,2229 'categori':664,756,1107,1184 'category2':1110 'category3':1113 'category4':1116 'category5':1119 'check':2267,2268 'checkout':1478,1482,1664,1672,1687,1751 'chrome':732 'citi':827 'cloud.google.com':2484 'cloud.google.com/bigquery/docs/best-practices':2483 'cluster':2170,2178 'cogni':2493 'cogny.com':51,2496 'cogny.com/docs/ga4-bigquery-export-schema':50,2495 'cohort':1954,1958,1973,2057,2063,2069,2076,2102,2118 'collect':311 'column':204 'commerc':197,400 'common':41,441,1236 'complet':10,25 'concat':1260,1282 'consist':2365 'contin':797,804 'convers':67,70,945,1432,1548,1889 'cooki':333 'cookie-bas':332 'correct':99 'count':760,763,764,792,836,843,904,911,927,939,1025,1198,1251,1258,1280,1298,1373,1377,1531,1559,1575,1589,1612,1629,1644,1666,1681,1694,1717,1732,1745,1847,1855,1860,1871,1876,1883,1900,1903,2070,2078,2088,2093,2212,2219,2364,2369,2379,2382,2383,2390,2424,2426,2429,2445,2449,2467 'countif':1443,1452,1462,1474,1483,2222 'countri':811 'coupon':1145 'cpc':886 'creat':2192,2200 'creativ':1170,1173 'current':558,647,781,856,957,1060,1071,1218,1320,1331,1413,1504,1515,1814,1825,1917,1928,1988,1999,2035,2046,2254,2287,2292,2324,2335,2404,2415 'd':557,646,780,855,956,1057,1070,1217,1317,1330,1412,1501,1514,1811,1824,1914,1927,1985,1998,2032,2045,2251,2306,2321,2334,2401,2414 'daili':171,2368,2466 'data':44,105,163,198,206,388,402,2265 'date':228,230,554,559,643,648,777,782,852,857,953,958,1018,1024,1054,1058,1061,1067,1072,1214,1219,1244,1250,1314,1318,1321,1327,1332,1409,1414,1498,1502,1505,1511,1516,1808,1812,1815,1821,1826,1911,1915,1918,1924,1929,1964,1974,1982,1986,1989,1995,2000,2014,2021,2029,2033,2036,2042,2047,2055,2058,2059,2062,2064,2114,2117,2119,2165,2208,2248,2252,2255,2272,2275,2279,2283,2285,2288,2293,2296,2298,2303,2310,2318,2322,2325,2331,2336,2344,2350,2353,2372,2378,2398,2402,2405,2411,2416,2422,2433,2453,2470 'day':1064,1324,1508,1818,1921,1992,2039,2065,2067,2120,2258,2291,2328,2408 'denorm':169 'desc':793,866,967,1090,1233,1340,1429,1953,2136,2361,2471 'descript':208,226,446 'desktop':666 'detail':410 'developers.google.com':2490 'developers.google.com/analytics/devguides/collection/ga4/reference/events':2489 'devic':360,362,661,749,755 'device.browser':759 'device.category':753,2211 'device.operating':757 'diff':2060,2115 'distinct':765,837,905,912,928,940,1026,1199,1252,1259,1281,1299,1378,1532,1560,1576,1590,1613,1630,1645,1667,1682,1695,1718,1733,1746,1848,1856,1872,1884,2071,2079,2089,2094,2213,2301,2384 'divid':926,1279,1574,1628,1680,1731,1870,2087 'doc':49,2494 'doubl':438,593 'dr':2346 'dr.date':2339,2360 'e':196,399 'e-commerc':195,398 'e.g':258 'ecommerc':75,77,396,968 'ecommerce.purchase':1032,1039,2235 'ecommerce.transaction':1027,1079,1200 'ed':2351 'ed.date':2355 'en':714 'en-us':713 'end':922,938,1543,1572,1588,1601,1626,1643,1657,1678,1693,1708,1729,1744,1757,1866,1882,2239 'engag':467,471,476,481,542,546,1395,1401 'england':821 'epoch':250 'etc':675,684,719,802,810,817,822,880,888,895 'europ':800,807 'event':60,63,162,185,190,215,227,233,238,244,251,257,262,268,270,277,281,289,292,301,303,413,415,442,499,503,510,524,538,562,762,791,845,915,931,1021,1075,1222,1247,1269,1291,1350,1364,1391,1417,1444,1453,1463,1475,1484,1777,1791,1829,1932,2017,2164,2173,2180,2185,2207,2221,2223,2231,2363,2375,2381,2423,2428,2444,2448,2487 'exampl':133,497,600,748,829,896,1013,1176 'expert':86 'explain':62,76 'export':7,22,34,96,160,2271,2297,2349,2475 'extract':498,601 'facebook':879 'facebook.com':894 'field':11,40,78,188,223,224 'filter':2171,2187 'find':117 'firefox':734 'first':344,348,1769,1780,1798,1831,1892,1969 'first_traffic_source.medium':1843 'first_traffic_source.source':1839 'float':285,435,590 'float64':437,440,592,595,979,982,987,990,995,998,1003,1006,1124,1126,1133,1136,1141,1144 'focus':144 'follow':107 'format':170,237,553,642,776,851,952,1053,1066,1213,1313,1326,1408,1497,1510,1807,1820,1910,1923,1981,1994,2028,2041,2247,2317,2330,2397,2410 'fr':717,718 'fr-fr':716 'frequent':2196 'full':48,56,450,2492 'funnel':68,71,1433,1436,1551,1603,1659,1710,1759 'ga':483,489,1273,1295 'ga4':2,5,20,83,95,104,2175,2473,2486 'ga4-bigquery-schema':1 'galaxi':682 'generat':2282 'geo':364,794 'geo.city':835 'geo.country':834 'geograph':366,830 'good':2148 'googl':30,157,878,893 'group':650,784,859,960,1084,1225,1334,1421,1518,1833,1935,1945,2002,2049,2129,2259,2418 'guid':27 'hardwar':691 'help':91 'hostnam':746 'id':295,298,315,318,325,328,382,385,485,488,608,654,706,709,768,840,908,921,937,943,1011,1028,1080,1095,1150,1154,1165,1201,1255,1263,1275,1285,1297,1302,1381,1442,1522,1542,1571,1587,1600,1625,1642,1656,1677,1692,1707,1728,1743,1756,1775,1837,1851,1865,1881,1887,1899,1939,1944,1963,2006,2013,2053,2074,2082,2092,2097,2112,2216,2387 'includ':36,2183 'index':1162 'info':369,739 'inform':359,363,367,372,380 'instruct':79 'int':432,470,477,486,492,587 'int64':434,589,599,729,974,1009,1128 'integ':240,273,296,307,347 'interv':1062,1322,1506,1816,1919,1990,2037,2256,2289,2326,2406 'intraday':175,178 'io':394,697 'iphon':680 'item':403,408,972,1008,1091,1094,1097,1100,1103,1106,1109,1112,1115,1118,1129,1134,1137,1142,1152,1156,1160,1207,1209,1456 'item.item':1181,1183,1191 'item.quantity':1186 'join':1895,2105,2348 'key':420,425,444,513,527,541,575,580,617,631,1272,1294,1353,1367,1394 'key-valu':419,574 'kingdom':816 'land':1451,1524,1537,1595 'languag':711 'left':1894,2104,2347 'level':222,633,636,658 'lifetim':357 'like':214 'limit':566,659,721,867,1234,1430,1793 'list':1153,1157,1161 'locat':448,515,518,1149,1355,1358 'log':235,246 'ltv':354 'm':556,645,779,854,955,1056,1069,1216,1316,1329,1411,1500,1513,1810,1823,1913,1926,1984,1997,2031,2044,2250,2305,2320,2333,2400,2413 'maco':700 'market':686 'materi':2190,2193,2201 'medium':881,884,1846 'metro':823,825 'micro':598,1020,1246,1966,2016,2374 'microsecond':247,352 'millisecond':474 'min':1776,1967 'miss':2270,2340 'mobil':373,667,669,676,685,689 'model':677,692 'msec':469,544,1397 'name':205,213,252,254,504,563,671,678,687,873,877,916,932,1076,1098,1158,1168,1171,1182,1223,1418,1445,1454,1464,1476,1485,1830,1933,2174,2181,2186,2224,2232 'nest':13,39,168,187,411 'new':1853,1951 'northern':806 'null':1083,2357 'nullif':2446 'number':491,494,1530,1764 'numer':384 'offset':306,309,727,1795 'oper':694,701 'order':216,789,863,964,1045,1087,1229,1337,1425,1761,1789,1949,2133,2358,2431,2451,2468 'ordin':218 'organ':885 'os':690 'otherwis':148 'overview':58,151,156 'page':259,447,454,455,461,462,514,517,528,531,564,1341,1354,1357,1368,1371,1375,1419,1427,1446 'pair':422,577 'param':61,64,263,414,500,511,525,539,1270,1292,1351,1365,1392 'paramet':191,269,416,443 'pars':2302 'partit':2141,2150,2162 'pattern':16,73,1238 'per':1305 'perform':18,108,899,1178,2138 'plan':632,635,657 'platform':390,392 'posit':219 'possibl':2189 'practic':47,110,2140,2482 'preced':2437,2440,2457,2460 'precis':124 'previous':271,276 'price':1121,1125 'process':45 'product':409,1177,1460,1556,1565,1581,1650 'project.analytics_123456789.daily':2203 'project.analytics_123456789.events':549,638,772,847,948,1048,1205,1308,1404,1492,1802,1905,1976,2023,2243,2312,2392 'project.analytics_123456789.information_schema.columns':210 'promot':1164,1167 'properti':193,336,342,569,571,603,615,629 'provid':123,137,149 'prune':2142,2151 'pseudo':324,607,653,767,839,907,920,936,942,1254,1262,1284,1301,1380,1441,1521,1541,1570,1586,1599,1624,1641,1655,1676,1691,1706,1727,1742,1755,1774,1836,1850,1864,1880,1886,1898,1938,1943,1962,2005,2012,2052,2073,2081,2091,2096,2111,2215,2386 'pseudonym':327 'purchas':261,917,924,933,975,980,1077,1224,1486,1490,1715,1723,1738,1859,1868,1875,1902,1934,1940,2225,2227,2233 'qualiti':2266 'quantiti':973,1127,1189 'queri':15,42,72,102,1237 'question':116 'rang':2276,2345 'rate':946,1549,1890,2100 'ratio':2464 'raw':161 'readi':129 'ready-to-us':128 'refer':9,12,24,26,89,2488 'referr':463,465,887 'refund':983,988,1138,1143 'region':818 'relev':119 'repres':182 'resourc':2472 'retent':1955,2099 'revenu':976,981,1014,1033,1037,1040,1130,1135,1192,1197,1232,2236,2241 'row':181,2434,2454 's21':683 'safari':733 'safe':925,1278,1573,1627,1679,1730,1869,2086 'samsung':674 'scan':2160 'schema':4,8,23,35,57,85,97,154,202,2476 'second':728,1402 'section':120 'select':203,502,505,519,533,605,609,623,752,833,901,1017,1180,1243,1264,1286,1344,1345,1359,1386,1439,1523,1555,1607,1663,1714,1772,1838,1896,1960,2010,2056,2206,2278,2300,2338,2371,2421 'sequenc':294 'sequenti':297 'server':304,313 'session':475,479,484,487,490,493,1239,1274,1277,1296,1304 'set':319,596 'setuserid':321 'ship':991,996 'show':55,69 'sinc':248,2068 'singl':184 'size':2077 'skill' 'skill-ga4-bigquery-schema' 'slot':1174 'sourc':376,379,870,876,889,892,898,1800,1842 'source-cognyai' 'specif':139 'sql':101,132,199,423,501,578,604,662,751,795,832,871,900,969,1016,1092,1179,1242,1343,1434,1767,1956,2147,2199,2273,2366 'state':814 'status':2342 'step':1437,1449,1458,1469,1480,1488,1526,1529,1535,1552,1563,1579,1593,1604,1616,1633,1648,1660,1670,1685,1698,1711,1721,1736,1749,1760,1763 'store':417,572 'stream':381,389 'string':229,253,316,326,383,391,426,429,431,449,457,464,581,584,586,665,672,679,688,693,696,704,707,710,712,724,731,737,742,745,747,798,805,812,819,824,828,874,882,890,1012,1096,1099,1102,1105,1108,1111,1114,1117,1120,1146,1148,1151,1155,1159,1163,1166,1169,1172,1175 'struct':265,338,355,361,365,370,377,397,405,424,428,579,583,663,740,796,872,970,1093,1785 'structur':14,38,65,155,412 'sub':803,1059,1319,1503,1813,1916,1987,2034,2253,2286,2323,2403 'suffix':552,641,775,850,951,1051,1212,1311,1407,1495,1805,1908,1979,2026,2146,2154,2246,2308,2315,2395 'sum':1031,1185,1190,2228 'summari':2204 'support.google.com':2478 'support.google.com/analytics/answer/7029846':2477 'system':695,702,758 'tabl':37,172,176,201,212,551,640,774,849,950,1050,1211,1310,1406,1494,1804,1907,1978,2025,2145,2153,2176,2245,2307,2314,2394 'tablet':668 'tax':999,1004 'time':241,349,468,472,543,547,725,1396 'timestamp':239,272,274,305,308,346,597,1019,1022,1245,1248,1778,1782,1792,1965,1971,2015,2018,2373,2376 'tip':19 'titl':456,458,529,532,1369,1372 'top':221 'top-level':220 'topic':140 'topic-agent-skills' 'topic-ai-agents' 'topic-claude-code' 'topic-claude-skills' 'topic-cluade-mcp' 'topic-cursor' 'topic-geo' 'topic-growth-hacking' 'topic-llm' 'topic-marketing' 'topic-mcp' 'topic-seo' 'total':971,1188,1196,1231 'touch':345,1970 'track':723 'traffic':375,378,869,875,883,891,897,1799 'traffic_source.medium':903,1787,2210 'traffic_source.name':1788 'traffic_source.source':902,1786,2209 'transact':401,1010,1030,1203 'type':207,225,445,619,622,656 'understand':93 'union':1553,1605,1661,1712 'uniqu':1007,1383 'unit':813,815 'unix':249 'unnest':509,523,537,613,627,1206,1268,1290,1349,1363,1390,2281 'url':451,466 'us':715 'usag':53 'usd':284,291,978,986,994,1002,1035,1042,1123,1132,1140,1194,2238 'use':87,131,1941,2109,2144,2149,2352 'user':92,113,136,192,280,314,317,323,331,335,341,343,350,353,356,496,568,570,602,606,614,618,621,628,652,655,766,770,838,842,865,906,910,919,935,941,966,1241,1253,1257,1261,1283,1300,1306,1379,1384,1440,1520,1540,1545,1569,1585,1598,1623,1640,1654,1675,1690,1705,1726,1741,1754,1765,1773,1835,1849,1854,1863,1879,1885,1897,1937,1942,1952,1961,1968,2004,2007,2011,2051,2085,2106,2110,2214,2218,2385,2389,2425 'valid':2362 'valu':282,286,358,421,427,430,433,436,439,507,521,535,576,582,585,588,591,594,611,625,984,989,992,997,1000,1005,1046,1266,1288,1347,1361,1388 'value.int':534,1265,1287,1387 'value.string':506,520,610,624,1346,1360 'varianc':2463 'variant':1104 'vendor':705 'version':703,736,744 'via':320 'view':200,260,565,1342,1376,1420,1428,1447,1455,1461,1557,1566,1582,1651,2191,2194,2202 'visit':351,1770,1781,1832,1893 'web':393,738 'western':808 'whether':478 'window':699 'write':98 'y':555,644,778,853,954,1055,1068,1215,1315,1328,1410,1499,1512,1809,1822,1912,1925,1983,1996,2030,2043,2249,2304,2319,2332,2399,2412 'yyyymmdd':174,179,236 'zone':726","prices":[{"id":"7aecea88-ccce-4294-9ac7-5fab0d08dc2d","listingId":"e50b7b4d-badd-4ee0-8db4-84f5f4faaadb","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"cognyai","category":"claude-code-marketing-skills","install_from":"skills.sh"},"createdAt":"2026-04-18T22:23:32.843Z"}],"sources":[{"listingId":"e50b7b4d-badd-4ee0-8db4-84f5f4faaadb","source":"github","sourceId":"cognyai/claude-code-marketing-skills/ga4-bigquery-schema","sourceUrl":"https://github.com/cognyai/claude-code-marketing-skills/tree/main/skills/ga4-bigquery-schema","isPrimary":false,"firstSeenAt":"2026-04-18T22:23:32.843Z","lastSeenAt":"2026-05-01T06:56:26.319Z"}],"details":{"listingId":"e50b7b4d-badd-4ee0-8db4-84f5f4faaadb","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"cognyai","slug":"ga4-bigquery-schema","github":{"repo":"cognyai/claude-code-marketing-skills","stars":42,"topics":["agent-skills","ai-agents","claude-code","claude-skills","cluade-mcp","cursor","geo","growth-hacking","llm","marketing","mcp","seo","vibe","windsurf"],"license":null,"html_url":"https://github.com/cognyai/claude-code-marketing-skills","pushed_at":"2026-04-29T13:43:50Z","description":"Marketing skills for Claude Code — SEO audits and implementation, ad analysis, ad optimization. Free skills need no account. $9/mo for live Search Console, Bing & LinkedIn data.","skill_md_sha":"87a687340d1b1872de138a4b311de807d3a7b629","skill_md_path":"skills/ga4-bigquery-schema/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/cognyai/claude-code-marketing-skills/tree/main/skills/ga4-bigquery-schema"},"layout":"multi","source":"github","category":"claude-code-marketing-skills","frontmatter":{"name":"ga4-bigquery-schema","description":"GA4 BigQuery Export Schema Reference — complete field reference, nested structures, query patterns, and performance tips"},"skills_sh_url":"https://skills.sh/cognyai/claude-code-marketing-skills/ga4-bigquery-schema"},"updatedAt":"2026-05-01T06:56:26.319Z"}}