How to Measure the Impact of Sales Events on Margin — Before/After Analysis

You run a sale, traffic spikes, orders flood in — and then you look at your margin and something doesn't add up. Revenue is up, but profit isn't necessarily. How do you check whether the sale actually paid off and which product categories "ate" it alive?

Why Revenue Alone Isn't Enough

Most e-commerce businesses measure sales events through GMV (Gross Merchandise Value) or order count. That's a mistake. A 40% conversion uplift with a 30% discount can mean you sold more while earning less on every unit. Without a before/after margin analysis, you don't know whether the promotion was an investment or a cost.

Key questions you should be able to answer after every sale event:

• How did gross margin change at the category and SKU level?
• Did the volume increase compensate for the drop in unit margin?
• Which products were the "locomotives" of the sale, and which were just the "entry costs"?

The Data Structure You Need

Before writing your first SQL query, make sure you have three data sources joined in Keboola or another ETL tool:

1. Orders table — order_id, created_at, product_id, quantity, revenue, discount_amount
2. Products table with costs — product_id, cogs (cost of goods sold), category
3. Promotional periods table — promo_id, start_date, end_date, promo_name

Without a cogs column at the SKU level, margin analysis is impossible. If you don't have it, start by building that table in collaboration with your finance or operations team — it's the foundation of any credible e-commerce analysis.

SQL Query: Before/After Margin in Snowflake

Below is an example analysis for a 7-day sale, using 14 days "before" as the baseline.

WITH promo_period AS (
    SELECT
        DATE '2024-11-29' AS promo_start,
        DATE '2024-12-05' AS promo_end
),
orders_enriched AS (
    SELECT
        o.order_id,
        o.created_at::DATE AS order_date,
        o.product_id,
        p.category,
        o.quantity,
        o.revenue,
        o.discount_amount,
        p.cogs * o.quantity AS total_cogs,
        o.revenue - (p.cogs * o.quantity) AS gross_profit,
        CASE
            WHEN o.created_at::DATE BETWEEN
                DATEADD(day, -14, pp.promo_start) AND DATEADD(day, -1, pp.promo_start)
            THEN 'before'
            WHEN o.created_at::DATE BETWEEN pp.promo_start AND pp.promo_end
            THEN 'during'
        END AS period_label
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    CROSS JOIN promo_period pp
    WHERE o.created_at::DATE BETWEEN
        DATEADD(day, -14, pp.promo_start) AND pp.promo_end
)
SELECT
    category,
    period_label,
    COUNT(DISTINCT order_id)          AS orders,
    SUM(revenue)                      AS total_revenue,
    SUM(discount_amount)              AS total_discount,
    SUM(gross_profit)                 AS total_gross_profit,
    ROUND(SUM(gross_profit) / NULLIF(SUM(revenue), 0) * 100, 2) AS margin_pct,
    ROUND(SUM(gross_profit) / NULLIF(COUNT(DISTINCT order_id), 0), 2) AS profit_per_order
FROM orders_enriched
WHERE period_label IS NOT NULL
GROUP BY 1, 2
ORDER BY category, period_label;

This query gives you a per-category comparison of margin percentage and profit per order across both periods. If margin_pct drops from 38% to 22% while orders grow by 60%, you're finally seeing the real picture.

How to Interpret Results — Pitfalls of Before/After Analysis

Category cannibalization effect. During sale events, one category often "steals" traffic from others. A customer who bought electronics on promotion didn't buy home appliances at full price. Compare categories individually, but also look at mixed basket behavior.

Normalize by number of days. The "before" period has 14 days, the promotion has 7. Always convert results to daily average profit (total_gross_profit / period_days), otherwise you're comparing apples to oranges.

Account for returns. Sales events generate a higher return rate, sometimes with a 14–30 day delay. If you analyze margin right after the promotion, your numbers are still optimistic. Add a returns table to your model and subtract the returned gross_profit.

Acquisition cost vs. margin. If you increased your Google Ads or Meta budget for the sale, that cost must enter the model. Gross margin without deducting marketing costs gives a false picture of profitability.

How to Present Results in Tableau

For a before/after report in Tableau, I recommend two views:

1. Bullet chart — for each category, shows the "before" margin as the target and the "during" margin as actual. You immediately see where the promotion worked and where it burned margin.
2. Scatter plot: volume vs. margin change — X axis is the percentage change in order count, Y axis is the change in margin in percentage points. Each dot is a category or SKU. Products in the "high volume growth, small margin loss" quadrant are your sale stars. Products in the "low growth, large margin loss" quadrant are the ones you shouldn't discount next time.

Add a promo_name filter to the dashboard so analysts can compare different sale events over time — Black Friday vs. summer sale vs. store anniversary.

From Analysis to Decision

Data only makes sense when it leads to concrete actions. After every sale event, you should be able to answer:

• Which SKUs to exclude from future promotions — because their margin is already low enough that a discount generates losses
• Which categories to promote more aggressively — because they have a healthy margin and strong volume potential
• What the optimal discount level is — threshold analysis (what % discount still yields positive gross profit)

Building this analysis once as a SQL template plus a Tableau dashboard lets you evaluate every subsequent sale in an hour, not a week.

---

TL;DR

• Measure sales events through gross margin and profit per order — not just GMV and conversion rate
• Join order data with costs (COGS) at the SKU level — without this, the analysis is blind
• Normalize results to daily profit and account for returns after the fact
• Use a volume/margin scatter plot to quickly identify stars and margin killers
• A templated SQL query plus a permanent dashboard = evaluating every future promotion in an hour, not a week