Zapytania zaawansowane na tej stronie dotyczą danych eksportowanych zdarzeń BigQuery w Google Analytics 4. Jeśli szukasz tego samego zasobu dotyczącego Universal Analytics, zapoznaj się z przewodnikiem BigQuery na temat Universal Analytics. Zanim skorzystasz z zapytań zaawansowanych, wypróbuj najpierw zapytania podstawowe.
Produkty kupione przez klientów, którzy kupili określony produkt
Podane niżej zapytanie wskazuje, jakie inne produkty kupili klienci, którzy kupili konkretny produkt. Ten przykład nie zakłada, że produkty zostały zakupione w tym samym zamówieniu.
Przykład zoptymalizowany korzysta z funkcji skryptów BigQuery do definiowania zmiennej deklarującej, które elementy mają być filtrowane. Nie poprawia to wydajności, ale jest to bardziej czytelne podejście do definiowania zmiennych niż tworzenie tabeli z jedną wartością za pomocą klauzuli WITH
. Uproszczone zapytanie korzysta z drugiej metody z zastosowaniem klauzuli WITH
.
Uproszczone zapytanie tworzy oddzielną listę „kupujących produktu A” i łączy dane z tymi danymi. Zoptymalizowane zapytanie tworzy natomiast listę wszystkich produktów kupionych przez użytkownika w zamówieniach przy użyciu funkcji ARRAY_AGG
. Następnie za pomocą klauzuli zewnętrznej WHERE
listy zakupów wszystkich użytkowników są filtrowane pod kątem elementu target_item
i wyświetlane są tylko odpowiednie produkty.
Uproszczony
-- Example: Products purchased by customers who purchased a specific product.
--
-- `Params` is used to hold the value of the selected product and is referenced
-- throughout the query.
WITH
Params AS (
-- Replace with selected item_name or item_id.
SELECT 'Google Navy Speckled Tee' AS selected_product
),
PurchaseEvents AS (
SELECT
user_pseudo_id,
items
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
-- Replace date range.
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name = 'purchase'
),
ProductABuyers AS (
SELECT DISTINCT
user_pseudo_id
FROM
Params,
PurchaseEvents,
UNNEST(items) AS items
WHERE
-- item.item_id can be used instead of items.item_name.
items.item_name = selected_product
)
SELECT
items.item_name AS item_name,
SUM(items.quantity) AS item_quantity
FROM
Params,
PurchaseEvents,
UNNEST(items) AS items
WHERE
user_pseudo_id IN (SELECT user_pseudo_id FROM ProductABuyers)
-- item.item_id can be used instead of items.item_name
AND items.item_name != selected_product
GROUP BY 1
ORDER BY item_quantity DESC;
Zoptymalizowany
-- Optimized Example: Products purchased by customers who purchased a specific product.
-- Replace item name
DECLARE target_item STRING DEFAULT 'Google Navy Speckled Tee';
SELECT
IL.item_name AS item_name,
SUM(IL.quantity) AS quantity
FROM
(
SELECT
user_pseudo_id,
ARRAY_AGG(STRUCT(item_name, quantity)) AS item_list
FROM
-- Replace table
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
WHERE
-- Replace date range
_TABLE_SUFFIX BETWEEN '20201201' AND '20201210'
AND event_name = 'purchase'
GROUP BY
1
),
UNNEST(item_list) AS IL
WHERE
target_item IN (SELECT item_name FROM UNNEST(item_list))
-- Remove the following line if you want the target_item to appear in the results
AND target_item != IL.item_name
GROUP BY
item_name
ORDER BY
quantity DESC;
Średnia kwota wydana podczas sesji zakupu przez użytkownika
Podane niżej zapytanie pokazuje średnią kwotę wydaną podczas sesji przez każdego użytkownika. Uwzględniane są tylko sesje, w których użytkownik dokonał zakupu.
-- Example: Average amount of money spent per purchase session by user.
WITH
events AS (
SELECT
session.value.int_value AS session_id,
COALESCE(spend.value.int_value, spend.value.float_value, spend.value.double_value, 0.0)
AS spend_value,
event.*
-- Replace table name
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS event
LEFT JOIN UNNEST(event.event_params) AS session
ON session.key = 'ga_session_id'
LEFT JOIN UNNEST(event.event_params) AS spend
ON spend.key = 'value'
-- Replace date range
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
)
SELECT
user_pseudo_id,
COUNT(DISTINCT session_id) AS session_count,
SUM(spend_value) / COUNT(DISTINCT session_id) AS avg_spend_per_session_by_user
FROM events
WHERE event_name = 'purchase' and session_id IS NOT NULL
GROUP BY user_pseudo_id
Identyfikator i numer ostatniej sesji użytkowników
To zapytanie zawiera listę najnowszych parametrów ga_session_id i ga_session_number z ostatnich 4 dni dotyczących listy użytkowników. Możesz podać listę user_pseudo_id
lub user_id
.
user_pseudo_id
-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.
-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';
-- Replace list of user_pseudo_id's with ones you want to query.
DECLARE USER_PSEUDO_ID_LIST ARRAY<STRING> DEFAULT
[
'1005355938.1632145814', '979622592.1632496588', '1101478530.1632831095'];
CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
(SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);
CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
(SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);
SELECT DISTINCT
user_pseudo_id,
FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
OVER (UserWindow) AS ga_session_id,
FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
OVER (UserWindow) AS ga_session_number
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
user_pseudo_id IN UNNEST(USER_PSEUDO_ID_LIST)
AND RIGHT(_TABLE_SUFFIX, 8)
BETWEEN GetDateSuffix(-3, REPORTING_TIMEZONE)
AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC);
user_id
-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.
-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';
-- Replace list of user_id's with ones you want to query.
DECLARE USER_ID_LIST ARRAY<STRING> DEFAULT ['<user_id_1>', '<user_id_2>', '<user_id_n>'];
CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
(SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);
CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
(SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);
SELECT DISTINCT
user_pseudo_id,
FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
OVER (UserWindow) AS ga_session_id,
FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
OVER (UserWindow) AS ga_session_number
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
user_id IN UNNEST(USER_ID_LIST)
AND RIGHT(_TABLE_SUFFIX, 8)
BETWEEN GetDateSuffix(-3, REPORTING_TIMEZONE)
AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC);