As consultas avançadas nesta página se aplicam aos dados de exportação de eventos do BigQuery para o Google Analytics 4. Consulte o manual do BigQuery para o Universal Analytics se estiver procurando o mesmo recurso para o Universal Analytics. Teste as consultas básicas primeiro antes de testar as avançadas.
Produtos adquiridos por clientes que compraram um determinado produto
A consulta a seguir mostra quais outros produtos foram adquiridos por clientes que compraram um item específico. Neste exemplo, não pressupomos que os produtos tenham sido comprados no mesmo pedido.
O exemplo otimizado depende de recursos de script do BigQuery para definir uma variável que declara quais itens serão filtrados. Embora isso não melhore o desempenho, é uma abordagem mais legível para definir variáveis em comparação com a criação de uma tabela de valor único usando uma cláusula WITH. A consulta simplificada usa a última abordagem com a cláusula WITH.
A consulta simplificada cria uma lista separada de "Compradores do Produto A" e faz a junção com esses dados. Por outro lado, a consulta otimizada cria uma lista de todos os itens que um usuário comprou em pedidos usando a função ARRAY_AGG. Em seguida, utilizando a cláusula WHERE externa, as listas de compras de todos os usuários são filtradas para target_item, e apenas os itens relevantes são mostrados.
-- 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;
Valor médio gasto por sessão de compra por usuário
A consulta a seguir mostra o valor médio que cada usuário gastou por sessão. Ela considera apenas as sessões em que o usuário fez uma compra.
-- 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 e número da sessão mais recentes dos usuários
A consulta a seguir fornece a lista dos ga_session_id e ga_session_number mais recentes dos últimos quatro dias para um grupo de usuários. Você pode fornecer uma lista de user_pseudo_id ou 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_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);
-- 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);