# Retail sales and customer analysis

**Business question:** Where are invoiced sales concentrated, and which customers should be considered for a retention experiment?

This independent analysis combines Python and SQL to turn a transaction-line dataset into sales metrics, purchase cohorts and customer segments. The results describe historical behavior; proposed actions have not been tested with the retailer.

## Results

| Measure | Result |
| --- | --- |
| Source invoice lines | 541,909 |
| Gross invoiced sales | £10,666,684.54 |
| Credits on positive-priced lines | £896,812.49 |
| Net invoiced value | £9,769,872.05 |
| Positive-sales invoices | 19,960 |
| Average order value | £534.40 |
| Identified purchasers | 4,338 |
| Gross sales attributable to identified purchasers | 83.5% |

The top 434 identified purchasers, approximately 10% of identified customers ranked by gross purchases, account for **61.4% of identified gross sales**. This concentration supports investigating service needs and repeat purchasing among larger buyers. It does not establish the effect of a retention campaign.

**65.6%** of identified purchasers placed more than one positive-sales invoice within the observation window. Different customer tenures affect that measure. It is not a fixed-horizon retention rate.

![Monthly gross sales](reports/figures/monthly_sales.png)

November 2011 is the largest complete sales month in the supplied period. December 2011 ends on 9 December and must not be interpreted as a full-month decline. United Kingdom customers account for 84.6% of gross invoiced sales.

## Customer view

| Segment | Customers | Gross invoiced sales | Share of identified sales |
| --- | --- | --- | --- |
| Inactive over 90 days | 1449 | £1,035,270 | 11.6% |
| One-time buyers | 646 | £259,173 | 2.9% |
| Other repeat buyers | 1456 | £2,266,948 | 25.4% |
| Recent frequent buyers | 787 | £5,350,017 | 60.0% |

These segments use observable purchase behavior and explicit rules. They are hypotheses for action, not a prediction of churn. Recent frequent buyers purchased within 30 days and placed at least five invoices; inactivity means more than 90 days since the last observed purchase. Inactive customers are assigned before the one-time-buyer category. All other repeat purchasers form the remaining group.

![Customer purchase cohorts](reports/figures/cohort_retention.png)

Each row tracks the customers first observed buying in that month. A percentage is the share buying again at a given month age, so it can increase after an inactive month. Grey cells are unobservable future periods, not zero retention. December 2010 includes pre-existing customers whose earlier history is unavailable; compare it cautiously with later cohorts.

## Analysis choices that matter

- **Unknown customers:** 135,080 rows (24.9%) lack CustomerID. Keep them in sales totals, exclude them from customer-level calculations, and expose the 83.5% sales coverage.
- **Credits:** Keep negative-quantity, positive-priced lines in a signed ledger. Gross sales minus credits reconciles to net invoiced value. There is no cost, margin or reliable invoice-to-return matching, so none of these metrics represents profit or a product return rate.
- **Repeated lines:** Retain 5,268 exact repeat rows because no unique line ID proves they are errors. Removing them once changes gross sales by £24,573.74 (0.23%).
- **Prices and charges:** Exclude 2,517 rows with nonpositive unit prices from priced sales and the signed ledger. Positive-priced charges remain included. Source precision is preserved until display.
- **Dates:** Use 2011-12-10 as the fixed segmentation date. Exclude the incomplete last calendar month from cohort analysis.

## Proposed business action

Review high-value repeat purchasers with a commercial owner to distinguish wholesale behavior from consumer buying. For inactive purchasers, test one service or reactivation intervention with randomized eligible customer groups and a predeclared observation period. Compare incremental repeat purchases and net contribution after discounts and fulfillment costs. The dataset cannot supply those costs or demonstrate campaign uplift.

## Reproduce and inspect

From the repository root:

```powershell
python scripts/download_data.py retail
python projects/01-retail-customer-analytics/src/analysis.py
python scripts/build_case_studies.py
```

The report builder requires outputs from both projects. Alternatively, open [analysis.ipynb](analysis.ipynb) in a notebook editor and run all cells with the project environment. The notebook exposes the preparation, cohorts, segmentation, SQL and reconciliation steps.

- [Analysis code](src/analysis.py) and [SQL queries](sql/01_monthly_sales.sql)
- [Metric definitions and data dictionary](DATA_DICTIONARY.md)
- [Computed metrics](outputs/metrics.json), [monthly sales](outputs/monthly.csv), [cohort percentages](outputs/cohorts.csv) and [cohort sizes](outputs/cohort_sizes.csv)
- [Validation checks](../../tests/test_analysis.py)

SQL independently reproduces monthly sales, invoice counts and customer segment summaries, checked against pandas. Original source files are untouched. The analysis writes a local SQLite database and customer-segment table; these are reproducible and excluded from Git.

## Explain it in an interview

Explain why counting invoice lines inflates order counts, why unidentified customers belong in sales totals, and why removing all negative quantities loses credits. Be able to distinguish repeat-buyer share from cohort retention and customer purchase value from lifetime value. Then propose the extra data and experiment needed before claiming commercial impact.

## Source

[UCI Online Retail](https://archive.ics.uci.edu/dataset/352/online+retail), Chen, D. (2015), [DOI: 10.24432/C5BW33](https://doi.org/10.24432/C5BW33), CC BY 4.0. UK online-retail transactions from 1 December 2010 through 9 December 2011. Data were cleaned and aggregated for this study. Download provenance and file hashes are in [source_manifest.json](data/source_manifest.json).