Przykładowe zapytania na tej stronie mają zastosowanie do danych eksportu zdarzeń BigQuery dotyczących: Google Analytics
Tworzenie zapytania do zbioru danych zamiast przykładowego zbioru danych
O ile nie zaznaczono inaczej, wszystkie wymienione tutaj zapytania korzystają z przykładowych zbiorów danych i powinny
dawać prawidłowe wyniki. Używanie BigQuery z własnej usługi w Google Analytics
danych eksportu zdarzeń, poszukaj komentarza -- Replace table
przy każdym zapytaniu
i zastąpić przykładową tabelę. Aby skopiować nazwę tabeli ze zbioru danych:
- Otwórz interfejs BigQuery i wybierz projektu, który zawiera zbiór danych.
- Znajdź tabelę w Eksploratorze.
- Kliknij trzy ułożone pionowo kropki po prawej stronie tabeli, a następnie Kopiuj identyfikator.
- Wklej nazwę tabeli w zapytaniu w miejsce przykładowej tabeli.
- Zastąp fragment tabeli zawierającym datę
*
.
Jeśli na przykład funkcja Kopiuj identyfikator skopiowała nazwę tabeli BigQuery
my-first-gcp-project:analytics_28239234.events_20240718
, a potem zastąp:
-- Replace table
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
z:
-- Replace table
`my-first-gcp-project.analytics_28239234.events_*`
Zapytanie dotyczące konkretnego zakresu dat
Aby wysłać zapytanie dotyczące konkretnego zakresu dat ze zbioru danych eksportu zdarzeń BigQuery, użyj funkcji _TABLE_SUFFIX pseudokolumny w klauzuli WHERE zapytania. Aby dowiedzieć się więcej, Wyświetl artykuł Filtrowanie wybranych tabel za pomocą funkcji _TABLE_SUFFIX.
Na przykład poniższe zapytanie zlicza unikalne zdarzenia według datę i nazwę zdarzenia dla określonego okresu (w dniach) i według wybranych zdarzeń:
-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).
SELECT
event_date,
event_name,
COUNT(*) AS event_count
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name IN ('page_view', 'session_start', 'purchase')
-- Replace date range.
AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;
Liczba użytkowników i liczba nowych użytkowników
- Aby uzyskać łączną liczbę użytkowników, policz liczbę unikalnych użytkowników
user_id
. Jeśli jednak klient Google Analytics nie odsyłauser_id
z każde działanie, a jeśli nie masz pewności, policz liczbę unikalnychuser_pseudo_id
. - W przypadku nowych użytkowników możesz zastosować taką samą metodę liczenia jak powyżej, ale w przypadku
te wartości zmiennej
event_name
:
-- Example: Get 'Total User' count and 'New User' count.
WITH
UserInfo AS (
SELECT
user_pseudo_id,
MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
-- Replace table name.
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
-- Replace date range.
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
GROUP BY 1
)
SELECT
COUNT(*) AS user_count,
SUM(is_new_user) AS new_user_count
FROM UserInfo;
Średnia liczba transakcji na kupującego
Podane niżej zapytanie pokazuje średnią liczbę transakcji na kupującego.
-- Example: Average number of transactions per purchaser.
SELECT
COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name IN ('in_app_purchase', 'purchase')
-- Replace date range.
AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';
Wartości konkretnej nazwy zdarzenia
To zapytanie wyświetla event_timestamp
dla wszystkich
Zdarzenia purchase
i powiązany parametr zdarzenia
wartości:
-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.
SELECT
event_timestamp,
(
SELECT COALESCE(value.int_value, value.float_value, value.double_value)
FROM UNNEST(event_params)
WHERE key = 'value'
) AS event_value
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name = 'purchase'
-- Replace date range.
AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';
Poprzednie zapytanie można zmodyfikować, aby wyświetlić łączną wartość parametru zdarzenia wartości zamiast listy:
-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.
SELECT
SUM(
(
SELECT COALESCE(value.int_value, value.float_value, value.double_value)
FROM UNNEST(event_params)
WHERE key = 'value'
))
AS event_value
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name = 'purchase'
-- Replace date range.
AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';
10 produktów dodanych do koszyka
To zapytanie pokazuje 10 produktów dodanych do koszyka przez największą liczbę użytkowników.
-- Example: Top 10 items added to cart by most users.
SELECT
item_id,
item_name,
COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_web_ecommerce.events_*`, UNNEST(items)
WHERE
-- Replace date range.
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name IN ('add_to_cart')
GROUP BY
1, 2
ORDER BY
user_count DESC
LIMIT 10;
Średnia liczba odsłon strony według typu kupującego (kupujący a użytkownicy, którzy niczego nie kupili)
Następujące zapytanie pokazuje średnią liczbę odsłon typu kupującego (kupujący a użytkownicy, którzy niczego nie kupili):
-- Example: Average number of pageviews by purchaser type.
WITH
UserInfo AS (
SELECT
user_pseudo_id,
COUNTIF(event_name = 'page_view') AS page_view_count,
COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
-- Replace table name.
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
-- Replace date range.
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1
)
SELECT
(purchase_event_count > 0) AS purchaser,
COUNT(*) AS user_count,
SUM(page_view_count) AS total_page_views,
SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;
Kolejność odsłon
To zapytanie pokazuje sekwencję odsłon wywołanych przez każdego użytkownika. Kolejność zapytania użyj następujących pól, aby zdarzenia były wymienione w odpowiedniej kolejności. które wystąpiły w przypadku użytkownika, nawet jeśli zdarzenia zostały przesłane w tej samej grupie:
user_pseudo_id
user_id
batch_page_id
batch_ordering_id
batch_event_index
Chociaż próbka ogranicza wyniki do tylko zdarzeń page_view
, możesz stosować
tę samą klauzulę ORDER BY
, aby poprawnie uporządkować wszystkie zdarzenia poprzez usunięcie
Warunek klauzuli WHERE
dla event_name
.
Zapytanie pokazuje również, jak używać tagu zdefiniowanego przez użytkownika
funkcje
GetParamString
i GetParamInt
, aby ograniczyć duplikowanie i tworzyć zapytania
ich zrozumienie i utrzymanie.
-- Example: Sequence of pageviews.
/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));
/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));
SELECT
user_pseudo_id,
user_id,
batch_page_id,
batch_ordering_id,
batch_event_index,
event_name,
GetParamInt(event_params, 'ga_session_id') as ga_session_id,
GetParamString(event_params, 'page_location') as page_location,
GetParamString(event_params, 'page_title') as page_title,
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name = 'page_view'
-- Replace date range.
AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
ORDER BY
user_pseudo_id,
user_id,
batch_page_id,
batch_ordering_id,
batch_event_index;
Lista parametrów zdarzeń
To zapytanie zawiera listę wszystkich parametrów zdarzeń występujących w zbiorze danych:
-- Example: List all available event parameters and count their occurrences.
SELECT
EP.key AS event_param_key,
COUNT(*) AS occurrences
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
-- Replace date range.
_TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
event_param_key
ORDER BY
event_param_key ASC;
Dołączanie za pomocą Google Ads
Aby pobierać dodatkowe dane Google Ads dotyczące zdarzeń Google Analytics, skonfiguruj
BigQuery Data Transfer Service dla Google
Reklamy, a potem
połącz collected_traffic_source.gclid
z danych zdarzeń Google Analytics z tabelą gclid
ads_ClickStats_
customer_id z przeniesienia Google Ads.
Pamiętaj, że eksport danych zdarzeń Google Analytics tworzy tabelę dla każdego dnia, a
przeniesienie Google Ads generuje jeden
ads_ClickStats_
customer_id tabela na klienta.