Explain what changed between two CSV exports
Somebody has asked why a number moved and a screenshot will not survive the follow-up questions. Give this page last period's export and this one, and it works out which parts of the business account for the movement, how much of it each is responsible for, and whether the file itself changed shape underneath you. Both exports stay in your browser and neither is uploaded.
Want the button-by-button reference instead? See the CSV compare documentation
The meeting decides the analysis
Variance analysis has a specific audience: the person who will push back. They will not dispute that the number moved. They will dispute your account of why, and they will do it with three questions that come up almost every time.
- Are you sure that is the biggest cause? Answered by a ranking built on how much each category moved the total, not on which category has the most impressive growth rate.
- Is that a real decline or did the data change? Answered by naming, up front, every column that was added or removed and every column that got emptier between the two files.
- What about the thing that is missing? Answered by pulling categories that vanished into their own list instead of leaving them in the ranking as a minus one hundred per cent row that reads like a collapse.
Everything below is organized around those three questions rather than around the controls. A good variance write-up is short, and it is short because the person writing it already knows which three numbers survive scrutiny.
Contribution beats delta, and it certainly beats growth rate
Suppose recurring revenue slipped from 52,100 to 51,000. Three ways of sorting the plans give three different headlines, and only one of them answers the question.
Sort by growth rate and the starter plan wins with plus twenty per cent. It moved the total by 1,400 on a fall of 1,100, so leading with it would be perverse. Sort by raw delta and you learn which plans moved most in absolute terms, which is closer, but you still have to do division in your head to know whether 3,000 is a lot relative to a fall of 1,100. Sort by contribution and the arithmetic is already done: each plan is expressed as its share of the net change, signed, so a plan pushing the other way reads as a negative share instead of quietly sorting to the bottom of a list of positives.
The signed part matters more than it looks. In an ordinary ranking, a category that grew during a decline is an inconvenience. In a contribution ranking it is a headline: it says the fall would have been worse, and by exactly how much. That is a sentence executives remember.
Read growth rate last, as color on the categories contribution has already selected. A plan that contributed a fifth of the fall and did it while shrinking three per cent is a slow leak. One that contributed a fifth while shrinking forty per cent is an emergency confined to a small base. Same contribution, different response, and you only see the difference by reading the two columns together.
A worked case: recurring revenue down 1,100
Here is q1-plans.csv:
plan,seats,mrr
Starter,140,7000
Pro,60,18000
Enterprise,8,24000
Trial,25,0
Legacy,22,3100And q2-plans.csv, the same shape one quarter later:
plan,seats,mrr
Starter,168,8400
Pro,52,15600
Enterprise,9,27000
Trial,31,0
Metric mrr, aggregate Sum, breakdown plan, labels Q1 and Q2. Total goes from 52,100 to 51,000, a fall of 1,100 or 2.1 per cent. The drivers table:
DRIVERS
-------
category baseline current change change % of net
Legacy 3,100 0 -3,100 -100.0% +281.8% GONE
Enterprise 24,000 27,000 +3,000 +12.5% -272.7%
Pro 18,000 15,600 -2,400 -13.3% +218.2%
Starter 7,000 8,400 +1,400 +20.0% -127.3%
Trial 0 0 +0 n/a +0.0%
DISAPPEARED CATEGORIES (1)
----------------------
Legacy 3,100 (was 1 rows)Verify the shares. Legacy fell 3,100 against a net fall of 1,100, so minus 3,100 over minus 1,100 is plus 2.818, printed as plus 281.8 per cent. Enterprise rose 3,000 against a fall, so plus 3,000 over minus 1,100 is minus 272.7 per cent. Pro gives plus 218.2, Starter minus 127.3, Trial nothing. Sum: 281.8 plus 218.2 is 500.0, minus 272.7 and minus 127.3 is 400.0 away, leaving exactly 100. Every category is accounted for and none is double counted.
Now notice what a delta ranking would have hidden. The headline fall is 1,100, a rounding error at this scale, and it is the product of a 3,100 disappearance, a 3,000 gain and a 2,400 loss. Three large things happened and they very nearly cancelled. Reporting that quarter as roughly flat would be technically true and completely misleading, and the shares over two hundred per cent are the signal that told you so.
Trial is the second lesson. It billed nothing in both quarters, so its share is zero and its percentage column is empty rather than showing a change from a base that does not exist. And the seats column is worth a second run: switch the metric to seats and the same two files say something else entirely. Seats went 255 to 260, up slightly, while revenue fell. More customers paying less each is a mix shift, and it is a different conversation from losing customers.
Percentages on a small base will embarrass you
The most common way a variance write-up falls apart is a percentage attached to a number too small to carry one. A region that billed 40 last month and 120 this month grew two hundred per cent. Put that on a slide and somebody will ask what it means, and the honest answer is that eighty units of revenue arrived. On a total in the tens of thousands, that is noise wearing a large number as a disguise.
The extreme case is a baseline of zero, and this is where free tools quietly lie. Divide by zero and you get infinity; most spreadsheets print an error, many dashboards print a hundred per cent, and both are inventions. This report refuses the question. When the baseline for a category is zero, the change percentage is left as n/a and nothing is substituted. When the headline baseline itself is zero, the report adds a short paragraph explaining that the absolute change is all there is. You cannot express arrival as a rate of growth, because there is no earlier quantity for anything to have grown from.
The practical habit that follows: never quote a percentage without the two figures it came from, and never rank on percentages at all. Say the Central region went from nothing to 700. Say the pro plan fell 2,400, which is thirteen per cent of where it started and roughly two fifths of the gap you are explaining. Both sentences are checkable, and a checkable sentence ends an argument that a percentage on its own would have started.
A category that vanished is a question about the pipeline
In the ranking, a category present in the baseline and absent from the current file reads as minus one hundred per cent. That number is arithmetically correct and analytically dangerous, because minus a hundred per cent looks like a collapse and absence is not a collapse. Before it is a business event it is one of four data events.
- It was renamed. Legacy became Legacy Plan, or the label picked up a suffix. The old name went to zero, the new one appeared out of nowhere, and both are in the report. Whenever something vanishes, look for something of similar size arriving.
- It was filtered out. Somebody added a clause to the query behind the export, or changed a date window, or excluded a status the previous export included.
- It was rolled up. Two categories were merged upstream, so one absorbed the other. The total is unchanged and the breakdown is not.
- It genuinely ended. The product was retired, the contract expired, the region closed. This is the only case that belongs in a business narrative, and it is the one you have to earn by ruling out the other three.
That is why disappeared categories get a section of their own, listing the baseline value and the number of rows they used to occupy. The row count is the tell. A category that used to fill 400 rows and now fills none is a pipeline event almost every time; one that filled a single row may simply have been a customer who left.
New categories get the same separate treatment for the same reason. Something appearing is a fact about the file, and it becomes a fact about the business only after you have checked that it was not always there under another name.
Mix shift, rate change, and telling them apart
Two very different things produce the same fall in a total. In a rate change, every part of the business does slightly worse: prices drop, conversion slips, discounts widen. In a mix shift, nothing gets worse at all, but the balance moves toward the cheaper parts, and the average drags the total down behind it. The remedies have nothing in common, so guessing wrong wastes a quarter.
Two runs on the same pair of files separate them. First run the comparison on the total with Sum, which is the movement you were asked about. Then run it again with Average on the same metric. If the sum fell and the average held steady, the volume moved and the individual values did not: mix shift. If the average fell in step with the sum, the values themselves moved: rate change. If the sum fell while the average rose, the business lost small customers and kept large ones, which is a fall worth celebrating quietly.
A third run adds the missing dimension. Set the metric to Count rows and you get the volume on its own, free of any value column. Sum, average and count taken together tell you whether you have fewer things, cheaper things, or a different blend of the same things. In the worked case above, the sum fell while seats rose, and the arithmetic points straight at the third answer.
The other dial worth turning is the breakdown column. The same movement attributed by region and by channel produces two accounts of the same quarter, and neither is more valid than the other. Run both. When the two agree you have a robust finding. When they disagree, the disagreement is the finding, and you have just learned that the cause cuts across one dimension and not the other.
Rule out the file before you blame the business
The fastest way to lose a room is to explain a decline that turns out to be an export change. Two sections of the report exist to close that door, and reading them first costs about ten seconds.
Schema drift lists every column that appears in one file and not the other, and flags the case where the columns match but their order changed. A column that arrived between the two periods often means the upstream query was edited, and a query that was edited may have been edited in ways that also affect the rows.
Completeness drift lists every shared column whose blank rate moved, in percentage points. This is where a broken join usually shows itself: a column that was two per cent empty and is now forty per cent empty has a problem upstream, and if the analysis depends on that column the analysis has the problem too. Crucially, each side is measured against its own row count. Five blanks out of twenty rows and five out of a hundred are wildly different failures, and a shared denominator would have described neither file honestly. The report states the two row counts underneath the table so nobody has to take the percentages on trust.
Bring both sections into the meeting even when they are empty. Being able to say that the two exports had identical columns and no column changed how complete it was is a sentence that ends a whole category of objection before it is raised.
Write the finding in three sentences
A variance analysis that runs to two pages will not be read. Three sentences will be, and they have a reliable structure: what moved, what moved it, and what you cannot yet explain. The third sentence is the one people skip and the one that buys you credibility.
Applied to the worked case above: recurring revenue fell 1,100 in the quarter, or 2.1 per cent. Retiring the legacy plan removed 3,100 and the pro plan lost 2,400, and enterprise growth of 3,000 covered most of both. Pro is the part I cannot yet account for, since seats there fell from 60 to 52 with no matching arrivals elsewhere in the file.
Attach evidence rather than pasting it into the message. The standalone HTML report opens in any browser with no network access and needs nothing installed, which makes it the right thing to send to somebody who will not open a spreadsheet. The Markdown version drops into a wiki page or a pull request. The driver CSV is for the colleague who wants to check your arithmetic, and letting them do so is how the number becomes agreed rather than merely stated. Save the setup with a name and next quarter the same analysis is four fields and a file drop, since the stored configuration holds only labels and column names and carries none of this quarter's figures.
Frequently Asked Questions
What is variance analysis, in plain terms?
Taking a number that moved and attributing the movement to the parts of the business that caused it. Revenue fell by 1,100 is a fact. The enterprise plan grew, the pro plan shrank, and a retired legacy plan accounted for more than the entire net fall is an explanation. Variance analysis is the step between the two, and it is the step people skip when they present a chart and hope nobody asks.
Why rank by contribution rather than by percentage growth?
Because percentage growth is blind to size. A tiny category that tripled looks spectacular and moved the total by almost nothing, while a large category that slipped four per cent may be the entire story. Contribution measures each category against the net change, so the ranking answers the question you were actually asked. Read the percentage second, as texture on the categories contribution has already put in front of you.
Why do the contribution figures add up to more than a hundred?
They add to exactly a hundred of the net change, but individual ones can be far larger than a hundred when categories moved in opposite directions and partly cancelled. If one category fell by 3,000 and another rose by 2,900, the net is 100 and those two contribute minus 3,000 and plus 2,900 per cent. That is not an error. It is a warning that the headline is small because two large opposing movements nearly hid each other, which is usually the most important thing on the page.
A category shows minus a hundred per cent. Did it really collapse?
It is absent from the current file, which is not the same claim. Perhaps it was retired, perhaps it was renamed, perhaps the export filter changed, perhaps the pipeline dropped it. Those are four different meetings. The tool marks such rows GONE and lists them separately precisely so that you check the pipeline before you write the word decline, because a missing category is a question about the data first and about the business second.
What if the baseline was zero?
Then there is no percentage and the report leaves the cell empty instead of filling it with infinity, or with a hundred per cent, or with a dash that a reader might mistake for a number. Growth from nothing is described by the absolute figure and by nothing else. Say the new plan billed 700 in its first month. Do not say it grew by any percentage at all, because there is no denominator to have grown from.
The total barely moved. Is there anything to say?
Often there is more to say than when it moves a lot. A flat total made of a large rise and a matching fall is a business in the middle of a change, and reporting it as steady is the mistake. Read the driver table even when the headline is dull. When the net change is exactly zero the share column is blank, because dividing by zero would be meaningless, and the individual movements are still listed in full.
How do I stop somebody blaming the numbers on the export?
Answer it before it is asked, using the last two sections of the report. Schema drift names any column added or removed between the two files. Completeness drift names any column whose blank rate moved, with each file measured against its own row count rather than a shared one. Bring both to the meeting and the question about whether the export changed is already settled.
Does anything leave my machine?
Nothing. Both files are read and aggregated by JavaScript in this tab, and there is no endpoint to send them to. That matters here because variance analysis usually runs on the numbers an organization guards most carefully. If you save a configuration for next month it is written to your own browser and holds only labels and column names.
Related
Turn the movement into an explanation
Free, no account, no upload. Two exports in, a ranked account of the change out.
Back to the variance report