Wypróbuj serwer MCP w Google Analytics. Zainstaluj go z GitHub. Więcej informacji znajdziesz w ogłoszeniu.

Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

Zapytania zaawansowane

Zaawansowane zapytania na tej stronie dotyczą danych z eksportu zdarzeń do BigQuery w Google Analytics. Prostsze przykłady znajdziesz na stronie Podstawowe zapytania.

Produkty kupione przez klientów, którzy nabyli określony produkt

Poniższe zapytanie pokazuje, jakie inne produkty kupili klienci, którzy kupili określony produkt. W tym przykładzie nie zakładamy, że produkty zostały kupione w ramach tego samego zamówienia.

W przykładzie zoptymalizowanym wykorzystano funkcje skryptowe BigQuery do zdefiniowania zmiennej, która określa, które elementy mają być filtrowane. Nie poprawia to skuteczności, ale jest bardziej czytelne niż tworzenie tabeli z jedną wartością za pomocą klauzuli WITH. Uproszczone zapytanie korzysta z tego drugiego podejścia, używając klauzuli WITH.

Uproszczone zapytanie tworzy osobną listę „Kupujący produkt A” i łączy ją z tymi danymi. Zoptymalizowane zapytanie tworzy listę wszystkich produktów, które użytkownik kupił w ramach zamówień, za pomocą funkcji ARRAY_AGG. Następnie za pomocą klauzuli zewnętrznej WHERE zapytanie filtruje listy zakupów wszystkich użytkowników pod kątem target_item i wyświetla 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

Podane niżej zapytania obliczają średnią kwotę wydaną podczas sesji, biorąc pod uwagę tylko sesje, w których użytkownik dokonał zakupu. Oba zapytania używają zapytania CTE (Common Table Expression), aby najpierw obliczyć łączne wydatki w każdej unikalnej sesji zakupu.

1. Średnia kwota wydana podczas sesji zakupu PRZEZ UŻYTKOWNIKA:

To zapytanie pokazuje średnie wydatki na sesję każdego użytkownika:

-- Calculates the average session spend per user.
WITH
  session_spend AS (
    SELECT
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
      SUM(
        COALESCE(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.float_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
          0.0)
      ) AS total_session_spend
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'purchase'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND EXISTS(SELECT 1 FROM UNNEST(event_params) WHERE key = 'ga_session_id' AND value.int_value IS NOT NULL)
    GROUP BY
      user_pseudo_id, session_id
  )
SELECT
  user_pseudo_id,
  COUNT(session_id) AS number_of_purchase_sessions,
  AVG(total_session_spend) AS avg_spend_per_session_by_user
FROM
  session_spend
GROUP BY
  user_pseudo_id
ORDER BY
  avg_spend_per_session_by_user DESC;

2. Średnia kwota wydana WE WSZYSTKICH sesjach zakupu:

To zapytanie oblicza ogólną średnią wydatków w przypadku każdej unikalnej sesji zakupu wszystkich użytkowników:

-- Calculates the overall average session spend across all users and sessions.
WITH
  session_spend AS (
    SELECT
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
      SUM(
        COALESCE(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.float_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
          0.0)
      ) AS total_session_spend
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'purchase'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND EXISTS(SELECT 1 FROM UNNEST(event_params) WHERE key = 'ga_session_id' AND value.int_value IS NOT NULL)
    GROUP BY
      user_pseudo_id, session_id
  )
SELECT
  COUNT(session_id) AS total_purchase_sessions,
  AVG(total_session_spend) AS overall_avg_spend_per_session
FROM
  session_spend;

Identyfikator ostatniej sesji i numer sesji użytkowników

To zapytanie zwraca listę najnowszych identyfikatorów ga_session_id i numerów ga_session_number z ostatnich 4 dni dla listy użytkowników. Możesz podać user_pseudo_idlistę lub user_idlistę.

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);

Wstecz

Podstawowe zapytania dotyczące zdarzeń

Dalej

Zapytania o dane użytkownika

Zapytania zaawansowane Zadbaj o dobrą organizację dzięki kolekcji Zapisuj i kategoryzuj treści zgodnie ze swoimi preferencjami.