Average Product Rating

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 1 DAY);
DECLARE store_tz STRING DEFAULT 'America/New_York';
-- Set both to FALSE to report own-site organic ratings only
DECLARE include_syndicated BOOL DEFAULT TRUE;
DECLARE include_incentivized BOOL DEFAULT TRUE;

-- Approved reviews dated by approval date in store time zone, rating normalised to the 1-5 scale
WITH approved_reviews AS (
  SELECT
    review_id,
    item_group_id,
    rating * 5 / rating_scale_max AS rating_normalized
  FROM `your-project.reviews.product_reviews`
  WHERE moderation_status = 'approved'
    AND DATE(approved_at, store_tz) BETWEEN start_date AND end_date
    AND (include_syndicated OR NOT is_syndicated)
    AND (include_incentivized OR NOT is_incentivized)
),
-- Parent products (variants rolled up to item_group_id) with their category
product_groups AS (
  SELECT item_group_id, ANY_VALUE(item_category) AS item_category
  FROM `your-project.pim.products`
  GROUP BY item_group_id
)
-- Average per parent product and per category; each review weighs equally, no approved reviews returns NULL
SELECT
  p.item_category,
  p.item_group_id,
  SUM(r.rating_normalized) / NULLIF(COUNT(DISTINCT r.review_id), 0) AS average_product_rating
FROM product_groups AS p
LEFT JOIN approved_reviews AS r USING (item_group_id)
GROUP BY GROUPING SETS ((p.item_category, p.item_group_id), (p.item_category))
ORDER BY p.item_category, p.item_group_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Reporting window, store time zone and own-site organic switches (set both flags to FALSE for organic only)
WITH params AS (
  SELECT
    date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 90) AS start_date,
    date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 1) AS end_date,
    'America/New_York' AS store_tz,
    TRUE AS include_syndicated,
    TRUE AS include_incentivized
),
-- Approved reviews dated by approval date in store time zone, rating normalised to the 1-5 scale
approved_reviews AS (
  SELECT
    r.review_id,
    r.item_group_id,
    r.rating * 5 / r.rating_scale_max AS rating_normalized
  FROM aep_backend_dataset.product_reviews AS r
  CROSS JOIN params AS pr
  WHERE r.moderation_status = 'approved'
    AND to_date(from_utc_timestamp(r.approved_at, pr.store_tz)) BETWEEN pr.start_date AND pr.end_date
    AND (pr.include_syndicated OR NOT r.is_syndicated)
    AND (pr.include_incentivized OR NOT r.is_incentivized)
),
-- Parent products (variants rolled up to item_group_id) with their category
product_groups AS (
  SELECT item_group_id, first(item_category) AS item_category
  FROM aep_backend_dataset.products
  GROUP BY item_group_id
)
-- Average per parent product and per category; each review weighs equally, no approved reviews returns NULL
SELECT
  p.item_category,
  p.item_group_id,
  SUM(r.rating_normalized) / NULLIF(COUNT(DISTINCT r.review_id), 0) AS average_product_rating
FROM product_groups AS p
LEFT JOIN approved_reviews AS r ON r.item_group_id = p.item_group_id
GROUP BY GROUPING SETS ((p.item_category, p.item_group_id), (p.item_category))
ORDER BY p.item_category, p.item_group_id;