Product Analytics and Recommendation Signals

Product analytics answers: which ec_products are growing, which are declining, and which ec_customers should you show which ec_products? These questions drive everything from homepage merchandising to email recommendations to inventory planning.

SQL generates the signals that feed recommendation engines and product managers' dashboards. You don't need machine learning to produce useful recommendations — market basket analysis and affinity scores are pure SQL.

What "Product Analytics" Covers

In an e-commerce context, product analytics typically includes:

• Performance metrics: Which ec_products sell most, generate most revenue, have best margins?
• Trend analysis: Is this product growing or declining month over month?
• Customer overlap: Which ec_products are bought by the same ec_customers? (the basis of "you might also like...")
• Affinity/lift: If someone bought Product A, how much more likely are they to buy Product B vs. the average customer?

Product Performance Dashboard

Before doing any advanced analysis, establish the baseline metrics for every product. This query gives you a full performance summary: sales volume, revenue, unique buyers, margin, and ranking.

The RANK() OVER (ORDER BY revenue DESC) provides an overall revenue ranking. The RANK() OVER (PARTITION BY category ...) provides a within-category ranking — so you can compare ec_products to their category peers, not just the overall catalog.

WITH product_stats AS (
    SELECT
        p.product_id AS product_id,
        p.name AS name,
        p.category,
        COUNT(DISTINCT oi.order_id) AS ec_orders,
        COUNT(DISTINCT o.customer_id) AS unique_buyers,
        SUM(oi.quantity) AS total_units_sold,
        ROUND(SUM((oi.quantity * oi.unit_price)), 2) AS revenue,
        ROUND(AVG(oi.unit_price), 2) AS avg_selling_price,
        -- Margin: profit as % of revenue
        ROUND((SUM((oi.quantity * oi.unit_price)) - SUM(oi.quantity * p.cost))
            / NULLIF(SUM((oi.quantity * oi.unit_price)), 0) * 100, 1) AS margin_pct
    FROM ec_order_items oi
    JOIN ec_orders o ON o.order_id = oi.order_id
    JOIN ec_products p ON p.product_id = oi.product_id
    WHERE o.status NOT IN ('cancelled', 'refunded')
    GROUP BY p.product_id, p.name, p.category
)
SELECT *,
    RANK() OVER (ORDER BY revenue DESC) AS overall_revenue_rank,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_revenue_rank,
    ROUND(revenue / SUM(revenue) OVER () * 100, 2) AS pct_of_total_revenue
FROM product_stats
ORDER BY overall_revenue_rank;

What This Returns

Each row is one product's full performance summary. Key metrics:

Products with low ec_orders but high revenue are high-ticket items bought occasionally. Products with high ec_orders but low revenue are impulse purchases. These need different merchandising strategies.