Basic SQL Queries for GA4 Data in BigQuery

Query 1: Events by Name (Last 30 Days)

SELECT
  event_name,
  COUNT(*) as total_events,
  COUNT(DISTINCT user_pseudo_id) as unique_users
FROM `project-id.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY event_name
ORDER BY total_events DESC

Query 2: Page Views by URL (Yesterday)

SELECT
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_url,
  COUNT(*) as page_views
FROM `project-id.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND event_name = 'page_view'
GROUP BY page_url
ORDER BY page_views DESC
LIMIT 20

Query 3: Daily Purchase Revenue

SELECT
  event_date,
  COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
    WHERE key = 'transaction_id')) as transactions,
  SUM((SELECT value.double_value FROM UNNEST(event_params)
    WHERE key = 'value')) as revenue
FROM `project-id.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY event_date
ORDER BY event_date

Query 4: Top Traffic Sources

SELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id')
    AS STRING))) as sessions
FROM `project-id.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY traffic_source.source, traffic_source.medium
ORDER BY sessions DESC
LIMIT 20

Query 5: Conversion Rate by Device

SELECT
  device.category,
  COUNT(DISTINCT IF(event_name='purchase', user_pseudo_id, NULL)) as buyers,
  COUNT(DISTINCT user_pseudo_id) as all_users,
  ROUND(COUNT(DISTINCT IF(event_name='purchase', user_pseudo_id, NULL)) * 100.0
    / COUNT(DISTINCT user_pseudo_id), 2) as conversion_rate_pct
FROM `project-id.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY device.category

BigQuery Query Tips

• Always use _TABLE_SUFFIX date filters — this limits which daily tables BigQuery scans, dramatically reducing query cost
• Use FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL N DAY)) for dynamic date ranges
• Replace project-id.analytics_PROPERTY with your actual project and property dataset ID
• Use the BigQuery console's "Query settings" to see estimated bytes scanned before running

Summary

These five query patterns — event counts, page views, purchase revenue, traffic sources, and device conversion rates — handle the majority of standard analytics questions. All use the date shard wildcard pattern (events_* with _TABLE_SUFFIX filter) which is the correct and cost-efficient way to query GA4 BigQuery exports. Substitute your project ID and property dataset name, and set date ranges to match your analysis period.

See our BigQuery Setup service for query development and reporting.

Need help writing BigQuery analytics queries? Contact Adslytics.