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.
Seiten-Tracking-Dimensionen
- hostname
- page
- previous page path
- page path level 1
- page path level 2
- page path level 3
- page path level 4
- page title
- landing page
- second page
- exit page
Seiten-Tracking-Metriken
- entrances
- pageviews
- unique pageviews
- pages / session
- exits
- % exit
- avg. time on page
Beispiel-Abfrage
select
hostname,
page,
previous_page,
page_path_level_1,
page_path_level_2,
page_path_level_3,
page_path_level_4,
page_title,
landing_page,
second_page,
exit_page,
-- entrances (metric)
countif(isentrance = true) as entrances,
-- pageviews (metric)
count(*) as pageviews,
-- pages per session (metric)
count(*) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as pages_per_session,
-- exits (metric)
countif(isexit = true) as exits,
-- exit rate (metric)
countif(isexit = true) / count(*) as exit_rate
from (
select
-- hostname (dimension)
hits.page.hostname as hostname,
-- page (dimension)
hits.page.pagepath as page,
-- previous page (dimension)
lag(hits.page.pagepath, 1) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as previous_page,
-- page path level 1 (dimension)
hits.page.pagepathlevel1 as page_path_level_1,
-- page path level 2 (dimension)
nullif(hits.page.pagepathlevel2,'') as page_path_level_2,
-- page path level 3 (dimension)
nullif(hits.page.pagepathlevel3,'') as page_path_level_3,
-- page path level 4 (dimension)
nullif(hits.page.pagepathlevel4,'') as page_path_level_4,
-- page title (dimension)
hits.page.pagetitle as page_title,
-- landing page (dimension)
case when hits.isentrance = true then hits.page.pagepath else null end as landing_page,
-- second page (dimension)
case when hits.isentrance = true then (lead(hits.page.pagepath, 1) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc)) else null end as second_page,
-- exit page (dimension)
case when hits.isexit = true then hits.page.pagepath else null end as exit_page,
hits.isentrance,
fullvisitorid,
visitstarttime,
hits.isexit
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
unnest(hits) as hits
where
totals.visits = 1
and hits.type = 'PAGE')
group by
hostname,
page,
previous_page,
page_path_level_1,
page_path_level_2,
page_path_level_3,
page_path_level_4,
page_title,
landing_page,
second_page,
exit_page
order by
pageviews desc
Beispiel-Abfrage: Unique Pageviews
select
page,
-- unique pageviews (metric)
sum(unique_pageviews) as unique_pageviews
from (
select
-- page (dimension)
hits.page.pagepath as page,
concat(hits.page.pagepath,hits.page.pagetitle) as page_concat,
count(distinct concat(cast(fullvisitorid as string), cast(visitstarttime as string))) as unique_pageviews
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
unnest(hits) as hits
where
totals.visits = 1
and hits.type = 'PAGE'
group by
page,
page_concat)
group by
page
order by
unique_pageviews desc
Beispiel-Abfrage: Average Time On Page
select
pagepath as page,
case when pageviews = exits then 0 else total_time_on_page / (pageviews - exits) end as avg_time_on_page
from (
select
pagepath,
count(*) as pageviews,
countif(isexit is not null) as exits,
sum(time_on_page) as total_time_on_page
from (
select
fullvisitorid,
visitstarttime,
pagepath,
pagetitle,
hit_time,
type,
isexit,
case when isexit is not null then last_interaction - hit_time else next_pageview - hit_time end as time_on_page
from (
select
fullvisitorid,
visitstarttime,
hits.page.pagepath,
hits.page.pagetitle,
hits.time / 1000 as hit_time,
hits.type,
hits.isexit,
max(if(hits.isinteraction = true,hits.time / 1000,0)) over (partition by fullvisitorid, visitstarttime) as last_interaction,
lead(hits.time / 1000) over (partition by fullvisitorid, visitstarttime order by hits.time / 1000) as next_pageview
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
unnest(hits) as hits
where
hits.type = 'PAGE'
and totals.visits = 1))
group by
pagepath)
order by
avg_time_on_page desc