پرس و جوهای پیشرفته

پرس‌وجوهای پیشرفته در این صفحه برای داده‌های خروجی رویداد BigQuery برای Google Analytics اعمال می‌شوند. برای مثال‌های ساده‌تر، به صفحه پرس‌وجوهای پایه مراجعه کنید.

محصولات خریداری شده توسط مشتریانی که محصول خاصی را خریداری کرده‌اند

پرس‌وجوی زیر نشان می‌دهد که مشتریانی که یک محصول خاص را خریداری کرده‌اند، چه محصولات دیگری نیز خریداری کرده‌اند. این مثال فرض نمی‌کند که محصولات به همان ترتیب خریداری شده‌اند.

مثال بهینه‌شده برای تعریف متغیری که مشخص می‌کند کدام آیتم‌ها باید فیلتر شوند، به ویژگی‌های اسکریپت‌نویسی 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;

میانگین هزینه در هر جلسه خرید

پرس‌وجوهای زیر میانگین مبلغ خرج‌شده در هر جلسه را محاسبه می‌کنند، و فقط جلساتی را در نظر می‌گیرند که در آن‌ها کاربر خریدی انجام داده است. هر دو پرس‌وجو از یک عبارت جدولی مشترک (CTE) برای محاسبه کل هزینه برای هر جلسه خرید منحصر به فرد استفاده می‌کنند.

۱. میانگین مبلغ خرج شده در هر جلسه خرید توسط کاربر:

این کوئری میانگین هزینه صرف شده برای هر کاربر در هر سشن را نشان می‌دهد:

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

۲. میانگین مبلغ خرج شده در کل جلسات خرید:

این کوئری میانگین کلی هزینه صرف شده در هر جلسه خرید منحصر به فرد از همه کاربران را محاسبه می‌کند:

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

آخرین شناسه جلسه و شماره جلسه برای کاربران

کوئری زیر لیستی از آخرین ga_session_id و ga_session_number از ۴ روز گذشته را برای لیستی از کاربران ارائه می‌دهد. می‌توانید یا لیست user_pseudo_id یا لیست 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);