Advanced queries

  • Advanced BigQuery queries are provided for Google Analytics event export data.

  • Queries include identifying other products purchased by customers who bought a specific item, calculating the average spend per purchase session by user, and finding the latest session information for a list of users.

  • The queries demonstrate both simplified and optimized approaches, utilizing features like WITH clauses and BigQuery scripting.

  • Users are guided to replace placeholder values in the queries for their specific data and requirements.

The advanced queries in this page apply to the BigQuery event export data for Google Analytics. For simpler examples, see the 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