The advanced queries in this page apply to the BigQuery event export data for
Google Analytics. For simpler examples, see the
[Basic queries](https://developers.google.com/analytics/bigquery/basic-queries) page.

### Products purchased by customers who purchased a certain product

The following query shows what other products were purchased by customers who
purchased a specific product. This example does not assume that the products
were purchased in the same order.

The optimized example relies on BigQuery scripting features to define a variable
that declares which items to filter on. While this does not improve performance,
this is a more readable approach for defining variables compared to creating a
single value table using a `WITH` clause. The simplified query uses the latter
approach using the `WITH` clause.

The simplified query creates a separate list of "Product A buyers" and does a
join with that data. The optimized query, instead, creates a list of all items a
user has purchased across orders using the `ARRAY_AGG` function. Then using the
outer `WHERE` clause, the query filters the purchase lists across all users for
the `target_item`, and only relevant items are shown.

### Simplified

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

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

### Average spend per purchase session

The following queries calculate the average amount of money spent per session,
considering only sessions where a user made a purchase. Both queries use a
Common Table Expression (CTE) to first calculate total spend for each unique
purchase session.

**1. Average amount spent per purchase session BY USER:**

This query shows the average session spend for each individual user:

    -- 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. Average amount spent ACROSS ALL purchase sessions:**

This query calculates the overall average spend across every unique purchase
session from all users:

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

### Latest session ID and session number for users

The following query provides the list of the latest ga_session_id and
ga_session_number from last 4 days for a list of users. You can provide either a
`user_pseudo_id` list or a `user_id` list.

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