Spreadsheet to Insight: A Repeatable Analysis Workflow

Most business analysis starts with a spreadsheet someone emailed and ends with a slide. In between, work is often done by hand: filter here, delete a few rows there, paste into a new tab. It produces an answer once. It cannot be checked, and next quarter it must be rebuilt from memory. The cost of that became famous in 2013, when a critique of an influential economics paper found coding errors and selectively excluded data in its working spreadsheet; corrected, one headline result changed from negative growth to 2.2% (Wikipedia: Growth in a Time of Debt).

The workflow below is one way to avoid that. It is followed here on a real 541,909-row sales workbook from a UK online gift retailer (UCI: Online Retail), using Python, and the same steps work in Excel with Power Query.

Step 1: Write down the question and the definitions

Before opening the file, write one sentence: "Is business outside the UK keeping pace with UK growth in 2011?" Then define the terms. Revenue means quantity times unit price on product lines, excluding cancellations, postage, fees and vouchers. The period is the first three quarters of 2011, because the data ends partway through December and a partial quarter would distort the comparison. Written definitions are what let someone else check, or repeat, the work.

Step 2: Keep the raw file untouched, and fingerprint it

Never edit the file you received. Save it as delivered, read-only if possible, and work from copies, as recommended in "Good enough practices in scientific computing" (Wilson et al., PLOS Computational Biology). Record where it came from and a fingerprint, so you can prove later which version you used:

import hashlib
import io
import urllib.request
import zipfile
import pandas as pd

SOURCE = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
START, END = "2011-01-01", "2011-10-01"          # three full quarters; change these to rerun

data = urllib.request.urlopen(SOURCE).read()
print("source:", SOURCE)
print("sha256:", hashlib.sha256(data).hexdigest()[:16], "| bytes:", len(data))
with zipfile.ZipFile(io.BytesIO(data)) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})

source: https://archive.ics.uci.edu/static/public/352/online+retail.zip
sha256: f5385cbb54bbebf7 | bytes: 23715478

If a colleague sends a "corrected" file next week, a different fingerprint tells you immediately that the numbers may change.

Step 3: Clean with rules, and log what each one removes

Every cleaning decision should be a rule, applied in code or recorded steps, with a count of what it removed:

log = []

def keep(df, mask, rule):
    log.append((rule, int((~mask).sum()), int(mask.sum())))
    return df[mask]

services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
sales = keep(raw, ~raw["InvoiceNo"].str.startswith("C"), "cancellation invoices")
sales = keep(sales, ~sales["StockCode"].isin(services) & ~sales["StockCode"].str.startswith("gift_"), "postage, fees and vouchers")
sales = keep(sales, (sales["Quantity"] > 0) & (sales["UnitPrice"] > 0), "zero or negative quantity or price")
sales = keep(sales, (sales["InvoiceDate"] >= START) & (sales["InvoiceDate"] < END), "outside the analysis period")
print(pd.DataFrame(log, columns=["rule", "rows removed", "rows left"]).to_string(index=False))

                              rule  rows removed  rows left
             cancellation invoices          9288     532621
        postage, fees and vouchers          2362     530259
zero or negative quantity or price          2498     527761
       outside the analysis period        208506     319255

This log belongs in the report's appendix. It answers the first question any careful reader asks, "what did you leave out?", and it makes a surprising change visible: if next quarter's run removes ten times as many cancellations, something happened. Data cleaning, step by step explains how these particular rules were chosen.

Step 4: Analyse to answer the question, not to explore everything

sales = sales.assign(revenue=sales["Quantity"] * sales["UnitPrice"],
                     quarter=sales["InvoiceDate"].dt.to_period("Q"),
                     market=sales["Country"].where(sales["Country"] == "United Kingdom", "Outside UK"))
by_quarter = sales.pivot_table(index="quarter", columns="market", values="revenue", aggfunc="sum") / 1000
by_quarter["outside UK share %"] = 100 * by_quarter["Outside UK"] / by_quarter.sum(axis=1)
print(by_quarter.round(1).to_string())

market   Outside UK  United Kingdom  outside UK share %
quarter                                                
2011Q1        333.1          1539.0                17.8
2011Q2        304.4          1691.7                15.3
2011Q3        409.1          2036.2                16.7

Revenue is in thousands. Between the first and third quarters, UK revenue rose by about 32% and revenue from outside the UK by about 23%, so the share from outside the UK slipped from 17.8% to 16.7%. The obvious follow-up is where the growth outside the UK came from:

q1, q3 = pd.Period("2011Q1"), pd.Period("2011Q3")
countries = (sales[sales["market"] == "Outside UK"]
             .pivot_table(index="Country", columns="quarter", values="revenue", aggfunc="sum").fillna(0))
countries["change"] = countries[q3] - countries[q1]
print((countries[[q1, q3, "change"]].sort_values("change", ascending=False) / 1000).round(1).head(4).to_string())

quarter      2011Q1  2011Q3  change
Country                            
EIRE           52.1   102.8    50.7
Germany        37.0    48.9    11.9
Norway          0.9    10.7     9.7
Switzerland     8.0    15.9     7.9

Ireland, labelled EIRE in the data, accounts for 50.7 thousand of the 76.0 thousand increase outside the UK. Growth abroad is not broad-based; it depends heavily on one market, which may itself depend on a handful of customers.

Step 5: Check before you share

print("rows reconcile:", len(raw) - sum(removed for _, removed, _ in log) == len(sales))
print("country changes sum to market change:",
      round(countries["change"].sum() / 1000, 1), "=", round((by_quarter.loc[q3, "Outside UK"] - by_quarter.loc[q1, "Outside UK"]), 1))

rows reconcile: True
country changes sum to market change: 76.0 = 76.0

Rows kept plus rows removed equal the raw file, and the country contributions add up to the market total. Checks like these take a minute and catch the formula dragged one row too far, the filter left on, or the double-counted join.

Step 6: Write the insight, not the output

The deliverable is a sentence a manager can act on: "Outside the UK, revenue grew 23% from Q1 to Q3 2011, slower than the UK's 32%, and two-thirds of that growth came from Ireland. We should check how many Irish customers drive it before planning around it." How to write a data analysis report covers structuring the rest.

Step 7: Make it rerunnable

Because the period is two parameters at the top, and every step is recorded, next quarter's analysis is a matter of changing the dates and running it again, then comparing the new cleaning log with the old one. In Excel, Power Query gives the same property: each cleaning step is recorded and replayed on refresh; Excel Power Query shows how. Keep the raw file, the steps and a short notes page together in one folder.

A folder layout that keeps it together

• raw: the files exactly as received, never edited, with the date received in the file name.
• steps: the script, or the workbook with its Power Query steps, that turns raw data into results.
• output: tables and charts produced by the steps, which can be deleted and regenerated at any time.
• notes: the question, definitions, cleaning log, checks and conclusions, dated.

Use dates in the form 2011-09-30 in file names so they sort correctly, and never name anything "final".

Warning signs that an analysis is not repeatable

• You cannot say which version of the source file the numbers came from.
• Values were copied and pasted between sheets, so a change upstream never reaches the result.
• Formulas contain typed-in numbers, such as a total or an exchange rate, instead of references.
• Rows were deleted by hand to "fix" something, and nobody recorded which.
• Running the same analysis twice gives different answers.

Any one of these means the result cannot be checked, however careful the analyst was. Fixing them usually takes less time than the next manual rebuild would.

Data cleaning, Excel and Python for analysis are part of the Data Analytics course.

Sources

• Good enough practices in scientific computing (PLOS Computational Biology, 2017) — Wilson, Bryan, Cranston, Kitzes, Nederbragt and Teal
• Growth in a Time of Debt (Wikipedia) — Wikipedia
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository