CSV variance analysis: explaining a change, not just reporting it

"Revenue is down nine percent" is not analysis, it is arithmetic. The question that gets asked next, immediately and every time, is why. Variance analysis is the discipline of answering that in a way that reconciles.

It is also full of small traps that produce confident, wrong answers: percentages computed against a zero baseline, categories that appear or vanish between periods, and driver lists that do not add up to the total they claim to explain.

Step one: get both periods onto one grain

You need a baseline and a current period, aggregated to exactly the same grain, joined on the category key. That sounds obvious and it is where most of the errors enter.

If the two periods came from different extracts, check that the filters match. A baseline that quietly includes cancelled orders and a current period that excludes them will produce a driver ranking that is entirely about cancellations and says nothing about the business.

WITH base AS (
  SELECT category, SUM(revenue) AS revenue
  FROM baseline
  WHERE status <> 'cancelled'
  GROUP BY category
),
curr AS (
  SELECT category, SUM(revenue) AS revenue
  FROM current
  WHERE status <> 'cancelled'
  GROUP BY category
)
SELECT COALESCE(base.category, curr.category) AS category,
       COALESCE(base.revenue, 0)              AS baseline,
       COALESCE(curr.revenue, 0)              AS current
FROM base
FULL OUTER JOIN curr USING (category);

The FULL OUTER JOIN is the important part and it is the one people get wrong. An inner join silently drops every category that exists in only one period, which is precisely the set of categories most likely to explain the change. A left join drops the new ones. Only a full outer join keeps both sides.

Fix it: join two files on a key column, keeping unmatched rows from both →

Step two: rank by contribution, not by percentage

This is the single most common presentation error. A category that went from 4 to 8 is up 100 percent, and a category that went from 400,000 to 360,000 is down 10 percent. Sorted by percentage, the first one leads your report. It contributed 4 to a change of tens of thousands.

Rank by absolute change. Show the percentage as context, in a column, where it belongs.

SELECT category,
       baseline,
       current,
       current - baseline AS change,
       CASE WHEN baseline = 0 THEN NULL
            ELSE ROUND(100.0 * (current - baseline) / baseline, 1)
       END AS pct_change
FROM combined
ORDER BY ABS(current - baseline) DESC;

Two details. ORDER BY ABS(...) rather than by the signed change, because the biggest mover might be up or down and you want both at the top. And the CASE around the percentage, which is the subject of the next section.

Step three: handle the zero denominators honestly

A category with zero in the baseline has no percentage change. Not infinity, not 100 percent, not a large number. Undefined. Any tool that shows you a percentage there is lying, and the lie is usually a division that silently produced a very large float.

The right treatment is to split the categories into three groups and present them separately.

Labelling matters beyond arithmetic. A disappeared category is frequently not a business event at all: it is a spelling change, a re-categorization, or a filter that started excluding it. Every time I have seen a suspiciously large "disappeared" line, the cause was upstream renaming rather than lost revenue. Check the spelling before you write the narrative.

Fix it: list the distinct category values in both files and compare them →

Step four: make it reconcile

The test that separates a variance analysis from a list of numbers: the drivers you show, plus everything you did not show, must add up to the total change. Exactly.

SELECT SUM(current) - SUM(baseline) AS total_change,
       SUM(current - baseline)     AS sum_of_changes
FROM combined;
-- These two must be identical. If they are not, a category is
-- missing from one side of the join.

Then, in the presentation, show the top drivers and an explicit remainder line that closes the gap. A report reading "Enterprise down 41,000, EMEA down 18,000, all other categories net plus 6,000, total change minus 53,000" is complete. A report showing two drivers and a total that does not follow from them invites the only question you do not want: what else is in there?

Step five, when the headline is confusing: volume, rate and mix

Sometimes every category looks fine and the total still moved. That means the mix changed: the same customers bought a different blend of products, and the blend has different unit values.

Decompose the change into three parts. Volume, holding the price constant. Rate, holding the volume constant. And mix, the interaction between them.

SELECT category,
       (curr_units - base_units) * base_price          AS volume_effect,
       (curr_price - base_price) * base_units          AS rate_effect,
       (curr_units - base_units) * (curr_price - base_price) AS mix_effect
FROM combined;

The three effects sum to the total change for that category, which is why this decomposition is the standard one rather than a cleverer alternative. It also produces a sentence people can act on: "volumes held, average price fell four percent, and the mix shifted toward the entry tier" is a different problem from "we sold less".

Be careful with the mix term. It is a residual, and when both volume and price moved a lot it can be large enough to dominate, which usually means the categories are too coarse and the analysis should be done one level down.

Fix it: cross-tabulate category against period before decomposing →

Step six: present it as a sentence

The output of a variance analysis is not a table. It is a sentence, with a table underneath it for whoever wants to check.

Five rules for that sentence. Lead with the total change in absolute terms and then the percentage. Name at most three drivers. Say explicitly what did not change, because ruling things out is half the value. Flag any driver you suspect is a data issue rather than a business event. And state the period boundaries, because "last month" means different things to different readers.

A worked example of the shape:

That last sentence is the one that builds trust. Naming your uncertainty before somebody finds it costs you nothing and buys you the benefit of the doubt on everything else in the report.

Fix it: diff the two periods by key and see exactly which rows changed →