FOR YOUR ANALYST

The formulas

Review coverage by revenue

-- revenue in the last 90 days by product, with published review count
-- reviews: review_id, product_id, order_id, rating, created_at, status
SELECT p.product_id, p.title,
       SUM(ol.price * ol.quantity)                     AS revenue_90d,
       COALESCE(r.reviews, 0)                          AS reviews,
       CASE WHEN COALESCE(r.reviews, 0) < 5 THEN 1 ELSE 0 END AS under_five
FROM order_lines ol
JOIN orders o   ON o.order_id = ol.order_id
JOIN products p ON p.product_id = ol.product_id
LEFT JOIN (SELECT product_id, COUNT(*) AS reviews
           FROM reviews WHERE status = 'published'
           GROUP BY product_id) r ON r.product_id = p.product_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.product_id, p.title, r.reviews
ORDER BY revenue_90d DESC;

Sum revenue where under_five = 1 and divide by the total for the scorecard’s “revenue under five reviews.”

Review rate per delivered order

-- reviews per 100 delivered orders, by delivery month, split by topic
-- reviews.about_delivery: true when the review only discusses shipping or packaging
SELECT DATE_TRUNC('month', f.delivered_at)                          AS month,
       COUNT(DISTINCT f.order_id)                                   AS delivered,
       100.0 * COUNT(r.review_id) FILTER (WHERE NOT r.about_delivery)
             / COUNT(DISTINCT f.order_id)                           AS product_reviews_per_100,
       100.0 * COUNT(r.review_id) FILTER (WHERE r.about_delivery)
             / COUNT(DISTINCT f.order_id)                           AS delivery_reviews_per_100
FROM fulfillments f
LEFT JOIN reviews r ON r.order_id = f.order_id
WHERE f.delivered_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1 ORDER BY 1;

Count reviews against the month the order was delivered, not the month the review arrived, or a change in ask timing will look like a change in response. Leave the latest two months out of any comparison until their reviews have had time to come in.

Drift: new-review average by month and by sequence

-- monthly average of new reviews, and average by position in the sequence
WITH seq AS (
  SELECT r.*, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at) AS n
  FROM reviews r WHERE status = 'published'
)
SELECT product_id,
       DATE_TRUNC('month', created_at) AS month,
       COUNT(*)                        AS new_reviews,
       ROUND(AVG(rating), 2)           AS avg_new,
       ROUND(AVG(rating) FILTER (WHERE n <= 10), 2)          AS avg_first_10,
       ROUND(AVG(rating) FILTER (WHERE n BETWEEN 11 AND 100), 2) AS avg_11_to_100
FROM seq
GROUP BY 1, 2 ORDER BY 1, 2;

Chart avg_new by month with supplier, formula and packaging changes marked. A step of 0.3 stars or more at a change date is a product question; a slow slide is drift.

Days to the fifth review

-- for products published in the last 6 months
WITH ranked AS (
  SELECT product_id, created_at,
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at) AS n
  FROM reviews WHERE status = 'published'
)
SELECT p.product_id, p.title, p.published_at::date AS launched,
       (r.created_at::date - p.published_at::date)  AS days_to_fifth
FROM products p
LEFT JOIN ranked r ON r.product_id = p.product_id AND r.n = 5
WHERE p.published_at >= CURRENT_DATE - INTERVAL '6 months'
ORDER BY days_to_fifth NULLS FIRST;

The syntax is Postgres. In BigQuery, replace FILTER (WHERE ...) with COUNTIF or AVG(IF(..., rating, NULL)), and date subtraction with DATE_DIFF. Column names follow a generic Shopify-style export; rename to match yours.