BigQuery Abfrage für Google Analytics (UA): Zielvorhaben-Conversions: Dimensionen und Metriken

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