Viele Daten von Google Analytics 4 können direkt aus den Tabellen abgeleitet werden, ohne dass Berechnungen mit den Daten durchgeführt werden müssen. Mit Hilfe von Unnest und Parse für Datums- oder Zeitstempel können wir diesen Trick anwenden.
Andere Dimensionen und Metriken, die Sie von Ihren Google Analytics-Analysen und -Berichten gewohnt sind, sind etwas schwieriger zugänglich. Sie müssen sie mit Ihren eigenen Abfragen berechnen.
Da ich bereits versucht habe, die am häufigsten verwendeten Dimensionen und Metriken herauszufiltern, hoffe ich, Ihnen mit den folgenden Beispielabfragen behilflich sein zu können.
Wenn Sie nur eine Dimension oder Metrik benötigen, schauen Sie in den — Kommentaren in der Beispielabfrage nach Namen und Definitionen und kopieren Sie den Teil, den Sie benötigen, aus der SELECT-Klausel. Stellen Sie sicher, dass Sie auch alle zusätzlichen Bedingungen (z.B. with, from, where, group by, having und order by) hinzufügen, die zur korrekten Berechnung der Ergebnisse erforderlich sind.
Standard Nutzer-Dimensionen
- user_id
- user_pseudo_id
- user_first_touch_timestamp
- user_properties.key
- user_properties.value.string_value
- user_properties.value.set_timestamp_micros
- user_ltv.currency
Standard Nutzer-Metriken
- user_properties.value.int_value
- user_properties.value.float_value
- user_properties.value.double_value
- user_ltv.revenue
Beispielabfrage
select
-- user_id (dimension | the user id set via the setUserId api)
user_id,
-- user_pseudo_id (dimension | the pseudonymous id (e.g., app instance id) for the user)
user_pseudo_id,
-- user_first_touch_timestamp (dimension | the time (in microseconds) at which the user first opened the app/website)
timestamp_micros(user_first_touch_timestamp) as user_first_touch_timestamp,
-- user_properties.key (dimension | the name of the user property | replace <insert key> with a parameter key or delete where clause to select all)
(select key from unnest(user_properties) where key = '<insert key>') as user_properties_key,
-- user_properties.value.string_value (dimension | the string value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
(select value.string_value from unnest(user_properties) where key = '<insert key>') as user_string_value,
-- user_properties.value.int_value (metric | the integer value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
(select value.int_value from unnest(user_properties) where key = '<insert key>') as user_int_value,
-- user_properties.value.float_value (metric | the float value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
(select value.float_value from unnest(user_properties) where key = '<insert key>') as user_float_value,
-- user_properties.value.double_value (metric | the double value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
(select value.double_value from unnest(user_properties) where key = '<insert key>') as user_double_value,
-- user_properties.value.set_timestamp_micros (dimension | the time (in microseconds) at which the user property was last set | replace <insert key> with a parameter key or delete where clause to select all)
timestamp_micros((select value.set_timestamp_micros from unnest(user_properties) where key = '<insert key>')) as user_set_timestamp_micros,
-- user_ltv.revenue (metric | the lifetime value (revenue) of the user)
user_ltv.revenue as user_ltv_revenue,
-- user_ltv.currency (dimension | the lifetime value (currency) of the user)
user_ltv.currency as user_ltv_currency
from
-- Hier Ihre GA4 Export nehmen
`privat-327611.analytics_266663932.events_*`
where
-- define static and/or dynamic start and end date
_table_suffix between '20211222'
and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
Berechnete Nutzer-Dimensionen
- user type
- count of sessions
Berechnete Nutzer-Metriken
- users
- new users
- % new sessions
- number of sessions per user
- event count per user (see separate query below)
-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
'20211222' as start_date,
format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),
-- subquery to prepare and calculate user data
user as (
select
user_pseudo_id,
case
when (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') = 1 then 'new visitor'
when (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') > 1 then 'returning visitor'
else null end as user_type,
(select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') as count_of_sessions,
count(distinct user_pseudo_id) as users,
count(distinct case when (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') = 1 then user_pseudo_id else null end) as new_users,
count(distinct case when event_name = 'session_start' and (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') = 1 then concat(user_pseudo_id, cast(event_timestamp as string)) end) / count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) as percentage_new_sessions,
count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) / count(distinct user_pseudo_id) as number_of_sessions_per_user
from
-- change this to your google analytics 4 export location in bigquery
`privat-327611.analytics_266663932.events_*`,
date_range
where
_table_suffix between date_range.start_date and date_range.end_date
group by
user_pseudo_id,
user_type,
count_of_sessions)
-- main query
select
-- user type (dimension | a boolean, either new visitor or returning visitor, indicating if the users are new or returning)
user_type,
-- count of sessions (dimension | the session index for a user, each session from a unique user will get its own incremental index starting from 1 for the first session)
count_of_sessions,
-- users (metric | the total number of active users)
sum(users) as users,
-- new users (metric | the number of users who interacted with your site or launched your app for the first time)
sum(new_users) as new_users,
-- % new sessions (metric | the percentage of sessions by users who had never visited before)
avg(percentage_new_sessions) as percentage_new_sessions,
-- number of sessions per user (metric | the total number of sessions divided by the total number of users)
avg(number_of_sessions_per_user) as number_of_sessions_per_user
from
user
where
user_type is not null
group by
user_type,
count_of_sessions
order by
users desc
Anzahl der Ereignisse pro Benutzer
Diese benutzerbasierte Metrik ist in einer separaten Abfrage einfacher zu ermitteln, da wir alle Ereignisse mit einem bestimmten event_name zählen müssen:
-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
'20211222' as start_date,
format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date)
select
-- event count per user (metric | the number of times an individual event (change event_name 'page_view' to event that needs to be counted) was triggered divided by amount of users)
count(distinct case when event_name = 'page_view' then concat(user_pseudo_id, cast(event_timestamp as string)) end) / count(distinct user_pseudo_id) as event_count_per_user
from
-- change this to your google analytics 4 export location in bigquery
`privat-327611.analytics_266663932.events_*`,
date_range
where
_table_suffix between date_range.start_date and date_range.end_date