How to replicate the 'Conversions | Goals | Reverse Goal Path' report (UA)
A query to generate the Google Analytics Reverse Goal Path report in BigQuery. In this report you'll find data about goal completions, segmented by goal completion location and previous location steps 1, 2 and 3.

As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.
Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.
Reverse Goal Path report
In the Conversions | Goals | Reverse Goal Path report you'll find data about goal completions, segmented by goal completion location and previous location steps 1, 2 and 3.
Let's query!
select
goal_completion_location,
goal_previous_step_1,
goal_previous_step_2,
goal_previous_step_3,
count(distinct case when regexp_contains(goal_completion_location, r'/ordercompleted') then session_id else null end) as goal_1_completions,
from
(
select
hits.page.pagepath as goal_completion_location,
lag(hits.page.pagepath, 1) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as goal_previous_step_1,
lag(hits.page.pagepath, 2) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as goal_previous_step_2,
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
)
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 goal_1_completions >= 1
order by
goal_1_completions desc
Enjoying this post? Subscribe to the GA4BigQuery newsletter!
You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.