Als Google Analytics-Benutzer sind Sie wahrscheinlich ziemlich an die Standardberichte in der Benutzeroberfläche von Universal Analytics gebunden. Es kann schwierig sein, den Sinn der Daten in den BigQuery-Exporttabellen zu verstehen.
Ich möchte Sie in die Lage versetzen, die Berichte, mit denen Sie vertraut sind, zu replizieren.
Bericht Zielpfad umkehren
Im Bericht Zielpfad umkehren finden Sie Daten zu Zielabschlüssen, aufgeschlüsselt nach dem Ort des Zielabschlusses und den vorherigen Schritten 1, 2 und 3.
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