Conversion rate feels like it should be a number you look up, but in the GA4 export it is a number you compute. Once you fix the denominator, the SQL is short. Here is how to get it right.

Decide what you're dividing

Conversion rate is only meaningful once you pin down both halves of the fraction. The session-based version, the most common, is:

conversion rate = sessions with a key event ÷ total sessions

You could also compute a user-based rate by swapping sessions for users. The mistake to avoid is mixing them – counting key events in the numerator but sessions in the denominator inflates the number.

Build both counts from events

Because the export has no session rows, both halves come from counting distinct session keys, filtered for the numerator:

WITH sessions AS (
  SELECT
    CONCAT(user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id')
    ) AS session_key,
    MAX(IF(event_name = 'purchase', 1, 0)) AS converted
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  GROUP BY session_key
)
SELECT
  COUNT(*) AS total_sessions,
  SUM(converted) AS converting_sessions,
  ROUND(SUM(converted) / COUNT(*) * 100, 2) AS conversion_rate_pct
FROM sessions

Grouping by session key first collapses events into sessions, and MAX(IF(...)) marks a session as converted if it contained the key event at all.

Choose the right key event

Swap 'purchase' for whichever event you marked as a key event – generate_lead, sign_up, or your own. For several key events, widen the condition with an IN list. Keep the event name exactly as GA4 records it, since a casing mismatch silently counts zero.

Watch the denominator's scope

The rate changes with what you count, so be explicit:

  • Segment by channel or device by adding that dimension to the GROUP BY.
  • Restrict to a landing page by filtering the first event of each session.
  • Keep the date window tight with _TABLE_SUFFIX so you scan only what you need.

Conversion rate looked like something to read off a dashboard, but in the export it is something you define and compute. Fix the denominator, collapse events into sessions, mark the ones that converted, and keep the key-event name exact. Do that and the number you compute is one you can actually trust, because you know precisely what went into it.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.