Common Queries

Replace project.analytics_PROPERTY_ID with your actual BigQuery project and dataset throughout all queries. Always set _TABLE_SUFFIX date bounds to avoid scanning unnecessary data.

Sessions and users

Daily sessions and users

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id, '-',
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
  )) AS sessions
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY event_date
ORDER BY event_date

New users (first visits)

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS new_users
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE event_name = 'first_visit'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY event_date
ORDER BY event_date

Session calculation with composite key

The canonical session identifier is user_pseudo_id + ga_session_id. This query builds a full sessions table with timing and engagement:

SELECT
  CONCAT(user_pseudo_id, '-',
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
  ) AS session_id,
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
  MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
  MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
  COUNT(*) AS event_count,
  SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 AS engagement_seconds
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY 1, 2, 3, 4

User identification (client ID + user ID)

SELECT
  user_pseudo_id,  -- Always present (device/browser ID)
  user_id,         -- Only if you set it (logged-in user ID)
  COUNT(DISTINCT
    CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )
  ) AS sessions
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY 1, 2

Sessions by channel (session-level attribution)

Use collected_traffic_source for session-level source/medium rather than the top-level traffic_source (which is first-touch, user-level):

SELECT
  collected_traffic_source.manual_source AS source,
  collected_traffic_source.manual_medium AS medium,
  collected_traffic_source.manual_campaign_name AS campaign,
  collected_traffic_source.gclid,
  COUNT(DISTINCT
    CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )
  ) AS sessions
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') = 1
GROUP BY 1, 2, 3, 4
ORDER BY sessions DESC

First-touch attribution

The top-level traffic_source struct contains the user’s original acquisition source:

SELECT
  traffic_source.source,
  traffic_source.medium,
  traffic_source.name AS campaign,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY 1, 2, 3
ORDER BY users DESC

Tip

traffic_source is user-level (first-touch). collected_traffic_source is session-level with full UTM parameters and click IDs (gclid, dclid, srsltid). Use the right one for your attribution model.

Engagement rate

SELECT
  event_date,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
  )) AS total_sessions,
  COUNT(DISTINCT CASE
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1'
    THEN CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))
  END) AS engaged_sessions,
  ROUND(
    COUNT(DISTINCT CASE
      WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1'
      THEN CONCAT(user_pseudo_id, '-',
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))
    END) /
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )) * 100, 2
  ) AS engagement_rate_pct
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY event_date
ORDER BY event_date

Page analysis

Page analytics with entrances and engagement

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS title,
  COUNT(*) AS pageviews,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT
    CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )
  ) AS sessions,
  -- Entrances = session starts on this page
  COUNTIF((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') = 1) AS entrances,
  -- Average engagement time
  AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 AS avg_engagement_sec
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name = 'page_view'
GROUP BY 1, 2
ORDER BY pageviews DESC
LIMIT 50

Top landing pages (entry pages)

WITH session_entries AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS entry_page,
    ROW_NUMBER() OVER (
      PARTITION BY user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      ORDER BY event_timestamp
    ) AS event_rank
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE event_name = 'page_view'
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

SELECT
  entry_page,
  COUNT(*) AS landing_sessions
FROM session_entries
WHERE event_rank = 1
GROUP BY entry_page
ORDER BY landing_sessions DESC
LIMIT 50

Event analysis

All events with counts

SELECT
  event_name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY event_name
ORDER BY event_count DESC

Custom event parameters

-- Distribution of search terms
SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term') AS search_term,
  COUNT(*) AS searches,
  COUNT(DISTINCT user_pseudo_id) AS unique_searchers
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE event_name = 'search'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY search_term
ORDER BY searches DESC
LIMIT 100

Ecommerce

Daily revenue

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), 'America/New_York') AS date,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions,
  SUM(ecommerce.purchase_revenue) AS revenue,
  SUM(ecommerce.purchase_revenue_in_usd) AS revenue_usd,
  AVG(ecommerce.purchase_revenue) AS avg_order_value,
  SUM(ecommerce.total_item_quantity) AS items_sold
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name = 'purchase'
GROUP BY 1
ORDER BY 1

Tip

Use ecommerce.transaction_id with COUNT(DISTINCT ...) to avoid double-counting purchases when duplicate events exist. Replace the timezone string with your property’s timezone.

Ecommerce funnel with conversion rates

WITH funnel AS (
  SELECT
    user_pseudo_id,
    CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    ) AS session_id,
    MAX(IF(event_name = 'view_item', 1, 0)) AS viewed_product,
    MAX(IF(event_name = 'add_to_cart', 1, 0)) AS added_to_cart,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS began_checkout,
    MAX(IF(event_name = 'purchase', 1, 0)) AS purchased
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY 1, 2
)
SELECT
  COUNT(DISTINCT session_id) AS total_sessions,
  SUM(viewed_product) AS viewed_product,
  SUM(added_to_cart) AS added_to_cart,
  SUM(began_checkout) AS began_checkout,
  SUM(purchased) AS purchased,
  -- Step-by-step conversion rates
  SAFE_DIVIDE(SUM(added_to_cart), SUM(viewed_product)) AS view_to_cart_rate,
  SAFE_DIVIDE(SUM(began_checkout), SUM(added_to_cart)) AS cart_to_checkout_rate,
  SAFE_DIVIDE(SUM(purchased), SUM(began_checkout)) AS checkout_to_purchase_rate
FROM funnel

Product performance

SELECT
  items.item_id,
  items.item_name,
  items.item_category,
  items.item_brand,
  COUNT(*) AS views,
  SUM(IF(event_name = 'add_to_cart', 1, 0)) AS add_to_carts,
  SUM(IF(event_name = 'purchase', items.quantity, 0)) AS units_sold,
  SUM(IF(event_name = 'purchase', items.item_revenue, 0)) AS revenue
FROM `project.analytics_PROPERTY_ID.events_*`,
  UNNEST(items) AS items
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name IN ('view_item', 'add_to_cart', 'purchase')
GROUP BY 1, 2, 3, 4
ORDER BY revenue DESC
LIMIT 50

Add-to-cart to purchase conversion rate

WITH cart_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    COUNTIF(event_name = 'add_to_cart') AS cart_adds,
    COUNTIF(event_name = 'purchase') AS purchases
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE event_name IN ('add_to_cart', 'purchase')
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY user_pseudo_id, session_id
)

SELECT
  COUNTIF(cart_adds > 0) AS sessions_with_cart_add,
  COUNTIF(purchases > 0) AS sessions_with_purchase,
  COUNTIF(cart_adds > 0 AND purchases > 0) AS converted_sessions,
  ROUND(
    COUNTIF(cart_adds > 0 AND purchases > 0) / COUNTIF(cart_adds > 0) * 100, 2
  ) AS cart_to_purchase_rate_pct
FROM cart_events

Conversions and goal analysis

Conversions by source/medium

WITH session_sources AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    traffic_source.source AS source,
    traffic_source.medium AS medium
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE event_name = 'session_start'
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
),
conversions AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE event_name = 'purchase'  -- replace with your conversion event
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

SELECT
  s.source,
  s.medium,
  COUNT(DISTINCT CONCAT(s.user_pseudo_id, '-', CAST(s.session_id AS STRING))) AS sessions,
  COUNT(DISTINCT CONCAT(c.user_pseudo_id, '-', CAST(c.session_id AS STRING))) AS conversions,
  ROUND(
    COUNT(DISTINCT CONCAT(c.user_pseudo_id, '-', CAST(c.session_id AS STRING))) /
    COUNT(DISTINCT CONCAT(s.user_pseudo_id, '-', CAST(s.session_id AS STRING))) * 100, 2
  ) AS conversion_rate_pct
FROM session_sources s
LEFT JOIN conversions c USING (user_pseudo_id, session_id)
GROUP BY s.source, s.medium
HAVING sessions > 50
ORDER BY conversions DESC

Device and geography

Sessions by device category

SELECT
  device.category AS device_type,
  device.operating_system,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
  )) AS sessions,
  ROUND(
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )) / SUM(COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    ))) OVER () * 100, 1
  ) AS pct_of_total
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE event_name = 'session_start'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY device_type, device.operating_system
ORDER BY sessions DESC

Top countries

SELECT
  geo.country,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(*) AS events
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY geo.country
ORDER BY users DESC
LIMIT 30

User behavior

Repeat purchaser analysis

SELECT
  purchase_count,
  COUNT(*) AS users,
  SUM(total_revenue) AS revenue_from_segment
FROM (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id')) AS purchase_count,
    SUM(ecommerce.purchase_revenue) AS total_revenue
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
  GROUP BY user_pseudo_id
)
GROUP BY purchase_count
ORDER BY purchase_count

Days between first visit and first purchase

WITH user_first_events AS (
  SELECT
    user_pseudo_id,
    MIN(CASE WHEN event_name = 'first_visit' THEN PARSE_DATE('%Y%m%d', event_date) END) AS first_visit_date,
    MIN(CASE WHEN event_name = 'purchase' THEN PARSE_DATE('%Y%m%d', event_date) END) AS first_purchase_date
  FROM `project.analytics_PROPERTY_ID.events_*`
  WHERE event_name IN ('first_visit', 'purchase')
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY user_pseudo_id
)

SELECT
  DATE_DIFF(first_purchase_date, first_visit_date, DAY) AS days_to_first_purchase,
  COUNT(*) AS users
FROM user_first_events
WHERE first_purchase_date IS NOT NULL
  AND first_visit_date IS NOT NULL
GROUP BY days_to_first_purchase
ORDER BY days_to_first_purchase

Custom event analysis

Analyze any custom event by extracting its parameters with the subquery pattern:

-- Example: Analyze a custom 'signup_complete' event
SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), 'America/New_York') AS date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'signup_method') AS signup_method,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'plan_type') AS plan_type,
  COUNT(*) AS signups,
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `project.analytics_PROPERTY_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name = 'signup_complete'
GROUP BY 1, 2, 3
ORDER BY 1, signups DESC

Incremental load with MERGE

For scheduled queries that incrementally update a derived sessions table:

MERGE `project.analytics_derived.sessions` T
USING (
  SELECT
    session_id,
    user_pseudo_id,
    session_start,
    session_end,
    event_count,
    engaged
  FROM (
    SELECT
      CONCAT(user_pseudo_id, '-',
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
      ) AS session_id,
      user_pseudo_id,
      MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
      MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
      COUNT(*) AS event_count,
      MAX((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_engaged')) AS engaged
    FROM `project.analytics_PROPERTY_ID.events_20240115`
    GROUP BY 1, 2
  )
) S
ON T.session_id = S.session_id
WHEN MATCHED THEN UPDATE SET
  session_end = S.session_end,
  event_count = S.event_count,
  engaged = S.engaged
WHEN NOT MATCHED THEN INSERT ROW

Tip

The MERGE pattern is ideal for daily scheduled queries that update a sessions table. It handles late-arriving data and cross-day sessions by updating existing rows and inserting new ones.

Common mistakes

Not using _TABLE_SUFFIX for date filtering

Without WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131', BigQuery scans your entire dataset. On a 2-year history, that is 730 tables. Always include date bounds.

Counting session_start events for session count

Session start events may not exist for all sessions (due to identity stitching or collection issues). Use COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING))) for reliable session counts.

Not handling NULL from UNNEST

If an event does not have a parameter, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'my_param') returns NULL — which is correct. Just handle NULLs appropriately in your downstream logic.

Community Query Resources

The queries in this article are solid starting points, but the GA4 + BigQuery community has built extensive libraries of production-tested SQL. Before writing a complex query from scratch, check these resources — they likely have something close to what you need.

GitHub repositories:

• aliasoblomov/Bigquery-GA4-Queries — 65+ curated queries covering sessions, users, events, ecommerce, funnels, and attribution. Well-documented with expected results and parameter notes.

No-code query generators:

• GA4SQL.com — Build BigQuery queries without writing SQL. Select your dataset, choose analysis type, configure filters — the tool generates valid SQL.
• GA4BQ.com — Another no-code builder with a different UI if you prefer it.

Community analytics resources:

• GA4BigQuery.com — Johan van de Werken’s resource site with query examples and BigQuery best practices.
• Tanelytics.com — Taneli Salonen’s site focused on GA4 and BigQuery integration, with analysis patterns and query tutorials.

---

Related articles

Unnesting Patterns Deep dive into working with nested ARRAY and STRUCT fields.

Session Reconstruction Rebuild full sessions with source attribution in SQL.

Cost Optimization Structure queries to minimize BigQuery costs.