Diese Beispielabfrage enthält alle folgenden Google Analytics-Nutzerdimensionen und Metriken. Wenn Sie nur eine Dimension oder Metrik benötigen, sehen Sie sich die — Kommentare in der Beispielabfrage an und kopieren Sie den Teil, den Sie benötigen, aus der Select-Klausel. Stellen Sie sicher, dass Sie auch alle zusätzlichen Bedingungen (in der from-, where-, group by- und order by-Klausel) hinzufügen, die zur korrekten Berechnung der Ergebnisse erforderlich sind.
Zielvorhaben-Conversion Dimensionen
- goal completion location
- goal previous step 1
- goal previous step 2
- goal previous step 3
Zielvorhaben-Conversion Metriken
- goal xx completions
- goal xx conversion rate
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