이 페이지의 고급 쿼리는 Google 애널리틱스. 유니버설 애널리틱스용 동일한 리소스를 찾고 있다면 유니버설 애널리틱스용 BigQuery 설명서를 참고하세요. 고급 쿼리를 사용하기 전에 먼저 기본 쿼리를 사용해 보세요.
특정 제품을 구매한 고객이 구매한 제품
다음 쿼리는 특정 제품을 구매한 고객이 구매한 다른 제품을 보여줍니다. 이 예에서는 제품을 동일한 순서로 구매했다고 가정하지 않습니다.
최적화된 예는 BigQuery 스크립팅 기능을 사용하여 필터링할 상품을 선언하는 변수를 정의합니다. 이렇게 하면 성능이 향상되지는 않지만 WITH
절을 사용하여 단일 값 표를 만드는 데 비해 더 읽기 쉬운 방법으로 변수를 정의할 수 있습니다. 단순화된 쿼리는 WITH
절을 사용하는 후자의 접근 방식을 사용합니다.
단순화된 쿼리는 별도의 '제품 A 구매자' 목록을 만들어 이 데이터와 결합합니다. 대신 최적화된 쿼리는 ARRAY_AGG
함수를 사용하여 사용자가 여러 주문을 통해 구매한 모든 상품의 목록을 만듭니다. 그런 다음 외부 WHERE
절을 사용하여 모든 사용자의 구매 목록이 target_item
에 대해 필터링되고 관련 상품만 표시됩니다.
단순화된 쿼리
-- 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;
최적화됨
-- 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;
사용자별 구매 세션당 평균 지출 금액
다음 쿼리는 사용자별 세션당 평균 지출 금액을 보여줍니다. 여기에는 사용자가 상품을 구매한 세션만 반영됩니다.
-- 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
사용자의 최신 세션 ID 및 세션 번호
다음 쿼리는 사용자 목록에서 최근 4일 동안의 최신 ga_session_id 및 ga_session_number 목록을 제공합니다. user_pseudo_id
목록 또는 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);