Products - SourceMedium

Use the same query standards from the SQL Query Library overview: is_order_sm_valid = TRUE for order analyses, sm_store_id scoping for multi-store setups, and your_project placeholders.

Top 10 products by net revenue (last 30 days)

What you’ll learn: Your highest revenue-generating products with units sold and order counts. Use this to identify your cash cows and prioritize inventory, marketing, and merchandising decisions.

-- Assumptions: timeframe=last_30_days | metric=net_revenue=SUM(order_line_net_revenue) | grain=sku | scope=valid_orders_only
SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_net_revenue) AS order_line_net_revenue,
  SUM(order_line_quantity) AS units_sold,
  COUNT(DISTINCT sm_order_key) AS orders
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY order_line_net_revenue DESC
LIMIT 10;

Top products by units sold (last 30 days)

What you’ll learn: Your most popular products by volume, which may differ from your top revenue generators. Useful for forecasting demand, managing inventory levels, and identifying viral or gateway products.

-- Assumptions: timeframe=last_30_days | metric=units_sold=SUM(order_line_quantity) | grain=sku | scope=valid_orders_only
SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_quantity) AS units_sold,
  SUM(order_line_net_revenue) AS order_line_net_revenue,
  COUNT(DISTINCT sm_order_key) AS orders
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY units_sold DESC
LIMIT 20;

Products most common with new customers (first valid orders, last 90 days)

What you’ll learn: Which products are most often the entry point for new customers. These “gateway products” are key to acquisition strategy—consider featuring them in ads, bundles, or welcome offers.

-- Assumptions: timeframe=first_valid_orders_last_90_days | metric=units_sold=SUM(order_line_quantity) | grain=product_title | scope=new_customers_valid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_order_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  ol.product_title,
  SUM(ol.order_line_quantity) AS units_sold,
  COUNT(DISTINCT ol.sm_order_key) AS orders,
  COUNT(DISTINCT ol.sku) AS skus
FROM `your_project.sm_transformed_v2.obt_order_lines` ol
INNER JOIN first_valid_orders fvo
  ON ol.sm_order_key = fvo.sm_order_key
WHERE ol.is_order_sm_valid = TRUE
  AND ol.sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY units_sold DESC
LIMIT 25;

Most commonly ordered product combinations

What you’ll learn: Which products are frequently purchased together in the same order. Use this for bundle recommendations, cross-sell strategies, and merchandising decisions.

-- Assumptions: timeframe=all_time | metric=order_frequency | grain=product_combination | scope=valid_orders_only
WITH RECURSIVE product_combos AS (
    -- Anchor: Start with individual products per order
    SELECT
        ol.sm_store_id,
        ol.sm_order_key,
        1 AS combo_length,
        CONCAT(ol.product_title, ' - ', ol.product_variant_title) AS combo,
        CONCAT(ol.product_title, ' - ', ol.product_variant_title) AS last_item
    FROM `your_project.sm_transformed_v2.obt_order_lines` AS ol
    WHERE ol.sm_store_id = 'your-sm_store_id'
      AND ol.is_order_sm_valid = TRUE
      AND ol.sku IS NOT NULL
      AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee|order specific)')

    UNION ALL

    -- Recursive: Build combinations up to 5 products
    SELECT
        ol.sm_store_id,
        ol.sm_order_key,
        pc.combo_length + 1,
        CONCAT(pc.combo, ', ', CONCAT(ol.product_title, ' - ', ol.product_variant_title)),
        CONCAT(ol.product_title, ' - ', ol.product_variant_title)
    FROM product_combos AS pc
    INNER JOIN `your_project.sm_transformed_v2.obt_order_lines` AS ol
        ON ol.sm_order_key = pc.sm_order_key
        AND CONCAT(ol.product_title, ' - ', ol.product_variant_title) > pc.last_item
    WHERE pc.combo_length < 5
      AND ol.is_order_sm_valid = TRUE
      AND ol.sku IS NOT NULL
      AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee|order specific)')
)

SELECT
    combo AS product_combinations,
    COUNT(DISTINCT sm_order_key) AS order_frequency,
    combo_length AS num_products
FROM product_combos
WHERE combo_length >= 2
GROUP BY combo, combo_length
HAVING order_frequency >= 100
ORDER BY order_frequency DESC, combo ASC
LIMIT 100;

Current inventory by product (Shopify)

What you’ll learn: Current available units per product variant across all physical, trusted Shopify locations. Use is_inventory_default_total_eligible = TRUE to get a conservative, safe total — this excludes unmatched, untrusted, and non-physical rows.

-- Assumptions: source=Shopify | filter=default_total_eligible | grain=product_variant | metric=inventory_quantity_available
SELECT
  product_title,
  product_variant_title,
  sku,
  SUM(inventory_quantity_available) AS units_available,
  COUNT(DISTINCT inventory_location_id) AS location_count
FROM `your_project.sm_transformed_v2.obt_inventory_positions`
WHERE is_inventory_default_total_eligible = TRUE
GROUP BY 1, 2, 3
ORDER BY units_available DESC;

Out-of-stock and oversold products (Shopify)

What you’ll learn: Products with zero or negative available inventory across trusted Shopify locations. Negative values are oversold/backordered positions — they are preserved as-is from the source.

-- Assumptions: source=Shopify | filter=default_total_eligible | grain=product_variant | metric=units_available<=0
SELECT
  product_title,
  product_variant_title,
  sku,
  SUM(inventory_quantity_available) AS units_available
FROM `your_project.sm_transformed_v2.obt_inventory_positions`
WHERE is_inventory_default_total_eligible = TRUE
GROUP BY 1, 2, 3
HAVING units_available <= 0
ORDER BY units_available ASC;

Inventory by location type (Shopify)

What you’ll learn: How your available inventory is distributed across warehouses, fulfillment services, and other location types. Useful for spotting over-concentration in a single node.

-- Assumptions: source=Shopify | filter=default_total_eligible | grain=location_type | metric=units_available
SELECT
  inventory_location_type,
  inventory_fulfillment_method,
  COUNT(DISTINCT inventory_location_id) AS locations,
  COUNT(DISTINCT sku) AS skus,
  SUM(inventory_quantity_available) AS units_available
FROM `your_project.sm_transformed_v2.obt_inventory_positions`
WHERE is_inventory_default_total_eligible = TRUE
GROUP BY 1, 2
ORDER BY units_available DESC;

Inventory coverage vs trailing 30-day sales (Shopify)

What you’ll learn: Which products have low available inventory relative to recent sales velocity. This uses trusted current inventory and valid order lines from the last 30 days to estimate days of supply.

-- Assumptions: source=Shopify | filter=default_total_eligible+valid_orders | grain=product_variant | metric=days_of_supply
WITH inventory AS (
  SELECT
    sm_store_id,
    sm_product_variant_key,
    product_title,
    product_variant_title,
    sku,
    SUM(inventory_quantity_available) AS units_available
  FROM `your_project.sm_transformed_v2.obt_inventory_positions`
  WHERE is_inventory_default_total_eligible = TRUE
  GROUP BY 1, 2, 3, 4, 5
),
trailing_sales AS (
  SELECT
    sm_store_id,
    sm_product_variant_key,
    SUM(order_line_net_quantity) AS units_sold_30d
  FROM `your_project.sm_transformed_v2.obt_order_lines`
  WHERE is_order_sm_valid = TRUE
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND sm_product_variant_key IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  i.product_title,
  i.product_variant_title,
  i.sku,
  i.units_available,
  COALESCE(s.units_sold_30d, 0) AS units_sold_30d,
  SAFE_DIVIDE(COALESCE(s.units_sold_30d, 0), 30) AS avg_daily_units_sold,
  SAFE_DIVIDE(i.units_available, SAFE_DIVIDE(COALESCE(s.units_sold_30d, 0), 30)) AS estimated_days_of_supply
FROM inventory AS i
LEFT JOIN trailing_sales AS s
  ON i.sm_product_variant_key = s.sm_product_variant_key
  AND i.sm_store_id = s.sm_store_id
WHERE COALESCE(s.units_sold_30d, 0) > 0
ORDER BY estimated_days_of_supply ASC, units_sold_30d DESC;

Back to the SQL Query Library.

Orders & Revenue

Tie product-level insights to topline revenue mix.

Customers & Retention

Understand product performance by customer lifecycle stage.

LTV & Retention

Connect gateway products to long-term cohort value.

​Related Categories

Orders & Revenue

Customers & Retention

LTV & Retention

Related Categories