Sample Data Audit: An Online Gift Wholesaler
Prepared by Monroe Analytics | Sample report built from a public dataset, not a client engagement
About this sample. This is what a Data Audit and Quick Wins report looks like. It uses a public dataset of real transactions from a UK online retailer that sells giftware, mostly to wholesale buyers. The business is not a client of mine and I have no inside knowledge of it. Everything below comes from the data alone, which is why I mark what the data proves and what it only suggests.
The short version
- April 2011 looked like a 25% sales drop. The data points to the calendar, not demand. About 86% of the drop is the calendar: the business recorded no sales on four April days, including Easter, so it had 21 trading days instead of 27. Sales on a normal trading day fell only about 4%.
- December 2011 looked like a collapse. It wasn't. The data stops on 9 December. The first eight days of December ran at the same daily pace as November, the best month of the year.
- Two enormous orders were entered and reversed within minutes. They add £246k to raw sales if nobody catches them.
- Sales roughly double from summer to November. The climb starts in September, so planning for it needs to start in the summer.
- One in seven pounds of sales can't be tied to a customer, because the customer ID is missing. That limits how much more the data can tell you.
The question
"Our sales dropped in April and we don't know why. Was something wrong?"
This is the kind of question I hear from owners. The data holds the answer, but the raw monthly total points in the wrong direction.
What I looked at
| Records | 541,909 order lines |
| Period | 1 December 2010 to 9 December 2011 (the last month is partial) |
| Customers | 4,372 identified customers |
| Products | 4,070 product codes |
| Markets | 91% United Kingdom, the rest mostly Europe |
| Sales analysed | £10.0M over 13 months, after cleaning (see Method) |
Data map. Every row is one product line on one invoice, with an invoice number, product code and description, quantity, unit price, timestamp, customer ID and country. There is one table and no separate customer or product tables. Returns and cancellations are stored as invoices whose number starts with "C".
Data quality scorecard
| Area | Status | What I found | What it means |
|---|---|---|---|
| Customer ID | ✗ Fix | Missing on 24.9% of rows, which is 15.1% of sales value | About £1 in £7 can't be tied to a customer, so customer-level analysis is incomplete |
| Cancellations and returns | ! Watch | 9,288 lines stored as negative "C" invoices with no link to the original sale | Returns are 4.8% of sales value, but you can't easily see which sale each one reverses |
| Reversed bulk orders | ! Watch | Two orders (£168k on 9 Dec, £77k on 18 Jan) were cancelled 12 and 16 minutes after entry | Raw January sales are overstated by about 13% |
| Non-product lines | ! Watch | 2,772 rows for postage, fees, manual adjustments and similar | These mix with product sales unless filtered out |
| Zero or negative prices | ! Watch | 2,517 rows, 1,454 of them with no description | Possibly write-offs, samples or corrections. Worth confirming with the business |
| Possible duplicates | ! Watch | 5,268 rows (1%) are exact copies of another row | Could be a double-scan or a legitimate repeat. Needs an owner's answer |
| Date coverage | ! Watch | The last month has only 8 trading days | Any monthly total for December 2011 will mislead |
| Dates, quantities, prices | ✓ Good | Complete, no missing values | Safe to use as they are |
Quick wins
1. Measure sales per trading day, not per month
Monthly totals punish short months. Sales equal the number of trading days times sales per day, so I split the change into those two parts.
April's total looks weak, but its daily figure sits right in line with the months around it. December 2011's total looks like a collapse, but its daily figure matches November's. Reporting the daily figure, and flagging partial months, stops false alarms like both of these.
2. Clean the revenue number before anyone uses it
Two single-line orders (74,215 ceramic storage jars on 18 January, 80,995 paper craft items on 9 December) were entered and reversed within 16 minutes. Together they'd add about £245,650 to raw sales. With them removed, January's sales are £595k, not £672k.
A simple rule catches this class of error automatically: flag any order many times larger than usual, or any order cancelled within the hour. Postage and fee lines should also be reported separately from product sales.
3. Plan the year around the September to November climb
Sales per trading day in November were about 2.0 times the May to August average. October was 1.5 times and September 1.4 times. The ramp starts in September, so stock buying, staffing and cash flow all need to move earlier than the peak.
Caution: this is one year of data. I can't separate a true seasonal pattern from general growth. A second year would settle it.
The "why" memo: what happened in April?
Established by the data - April's sales were 25.3% below March. - April had 21 trading days against 27 in March, a fall of 22.2%. Sales per trading day fell 4.0%. Those two effects multiply to the full drop, and the trading-day part accounts for about 86% of it. - The four April days with no sales were Friday 22, Sunday 24, Monday 25 and Friday 29. The business never records sales on Saturdays.
A reasonable hypothesis, not proven - Those four days line up with Good Friday, Easter Sunday, Easter Monday and the 2011 royal wedding bank holiday. The likeliest explanation is that the business was closed or not processing orders. The data can't confirm this. The owner could, in one sentence.
Not established - Whether the 4% dip in daily sales is real. It's smaller than ordinary month-to-month swings, for instance February to March moved by about 21%, so I would not act on it.
The answer to the owner's question: The data shows no sign of a demand problem in April. The drop was mostly a short trading month.
Roadmap
| Priority | Action | Why | Effort |
|---|---|---|---|
| 1 | Report sales per trading day and mark partial months on every dashboard | Ends the false alarms in this report | Low |
| 2 | Add an automatic check for very large or quickly reversed orders | Keeps bad orders out of the numbers | Low |
| 3 | Record a customer ID on every order | Fixes the biggest gap: 15% of sales value can't be attributed | Medium |
| 4 | Link each return to the sale it reverses | Lets you see return rates by product and customer | Medium |
| 5 | Keep a simple calendar of closures, promotions and stock-outs | Lets you explain dips in minutes instead of days | Low |
| 6 | Look into what drives the September to November ramp: more customers, bigger orders, or both? | Tells you where to invest before the next peak | Follow-on project |
Method and caveats
- Sales means product lines with a positive quantity and price, excluding cancellation invoices, non-product codes (postage, manual adjustments, fees) and the two reversed bulk orders described above.
- Trading days are days with at least one sale.
- Share of April's drop. Sales change equals the trading-day change times the per-day change. I split the drop using logarithms so the two parts add up cleanly.
- What this can't tell you. The data contains no cost, margin, marketing or stock information, so it can't say whether sales were profitable or why customers ordered. One year of data limits any seasonal claim.
- Source. Chen, D. (2015). Online Retail [Dataset]. UCI Machine Learning Repository. https://doi.org/10.24432/C5BW33. Licensed under CC BY 4.0; no changes to the source data, and all analysis is my own.
- Reproducible. The scripts that produced every figure are in the
analysis/folder.
Interested in something like this for your business? Book a free 30-minute call: https://calendly.com/titus-christofferson-monroe-analytics/30min