Skip to main content

​
Overview

Use these queries as starting points for analysis in BigQuery. Replace placeholders like {{account_id}} and filter for your brand/store as needed. Notes:
  • Prefer SourceMedium v2 tables when available (sm_transformed_v2), and start with is_order_sm_valid = TRUE for order-based analyses.
  • If you’re not sure which table to use, start with: obt_orders and obt_order_lines.

​
Product Insights

WITH RECURSIVE `CTE` AS (
    -- Anchor Query
    SELECT
        p.sm_store_id,
        p.order_id,
        1 AS length,
        concat(p.product_title, ' - ', p.variant_title) AS combo,
        concat(p.product_title, ' - ', p.variant_title) AS lastitem
    FROM `sm-{{account_id}}.sm_transformed_v1.product_performance` AS p
    WHERE p.sm_store_id = 'your-sm_store_id'

    UNION ALL
    -- Recursive Part
    SELECT
        p.sm_store_id,
        p.order_id,
        r.length + 1 AS length,
        CONCAT(r.combo, ', ', concat(p.product_title, ' - ', p.variant_title)) AS combo,
        concat(p.product_title, ' - ', p.variant_title) AS lastitem
    FROM `CTE` AS r
    INNER JOIN `sm-{{account_id}}.sm_transformed_v1.product_performance` AS p
        ON
            p.order_id = r.order_id
            AND concat(p.product_title, ' - ', p.variant_title) > r.lastitem
    WHERE r.length < 5
)

-- Output query
SELECT
    combo as combinations,
    COUNT(DISTINCT order_id) AS frequency,
    (CHAR_LENGTH(combo) - CHAR_LENGTH(REPLACE(combo, ',', '')))+1 as number_of_products
FROM `CTE`
WHERE (CHAR_LENGTH(combo) - CHAR_LENGTH(REPLACE(combo, ',', ''))) >= 2
GROUP BY combo
HAVING frequency >= 1000
ORDER BY frequency DESC, combo ASC;

​
More recipes

More recipes are coming soon. If there’s a query you’d like added (subscription churn, cohort LTV curves, creative performance, etc.), reach out to your SourceMedium team and include the business question and the table(s) you’re using.