You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Goal conversions: dimensions & metrics

Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in
💡
This article is about GA3 - Universal Analytics

This example query contains all following Google Analytics goal conversions dimensions and metrics. If you only need one dimension or metric, look at the -- comments in the example query and copy the part you need from the select clause. Make sure that you also add any additional conditions (in the from, where, group by and order by) that are necessary to calculate the results correctly.

Note: see also this tutorial on how to calculate goals from Google Analytics in BigQuery.

Goal conversions dimensions

  • goal completion location
  • goal previous step 1
  • goal previous step 2
  • goal previous step 3

Goal conversions metrics

  • goal xx completions
  • goal xx conversion rate

Example query

select
  goal_completion_location,
  goal_previous_step_1,
  goal_previous_step_2,
  goal_previous_step_3,
  -- goalxx completions (metric)
  count(distinct(if(regexp_contains(goal_completion_location, '/ordercompleted'),session_id,null))) as goalxx_completions,
  -- goalxy completions (metric)
  count(distinct(if(regexp_contains(goal_completion_location,'/basket.html'),session_id,null))) as goalxy_completions
from (
  select
    -- goal completion location (dimension)
    hits.page.pagepath as goal_completion_location,
    -- goal previous step 1 (dimension)
    lag(hits.page.pagepath, 1) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as goal_previous_step_1,
    -- goal previous step 2 (dimension)
    lag(hits.page.pagepath, 2) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as goal_previous_step_2,
    -- goal previous step 3 (dimension)
    lag(hits.page.pagepath, 3) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as goal_previous_step_3,
    concat(cast(fullvisitorid as string),cast(visitstarttime as string)) as session_id
  from
    `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
    unnest(hits) as hits
  where
    totals.visits = 1 )
group by
  goal_completion_location,
  goal_previous_step_1,
  goal_previous_step_2,
  goal_previous_step_3
having
  goal_completion_location not in (goal_previous_step_1,goal_previous_step_2,goal_previous_step_3)
  and goalxx_completions >= 1 or goalxy_completions >= 1
order by
  goalxx_completions desc,
  goalxy_completions desc