Business Analysis · E-Commerce · Customer Analytics
E-Commerce Sales, Cancellations & Customer Value Decision System
An end-to-end Business Analyst case study transforming more than one million transaction lines into auditable KPIs, SQL models, dashboards and management-ready decision support.
Independent end-to-end case study · Public data · Documented stakeholder scenarios
60–90 second overview
The case at a glance.
- Business question
- Where should an e-commerce leadership team focus first to protect sales value, reduce cancellation exposure and retain valuable customers?
- Scope and role
- I documented business requirements and KPI rules, built the SQL model and dashboard, and linked requirements to UAT checks and output evidence.
- Intended users
- Executive sponsors, commercial leads, operations teams, CRM leads and data owners represented through documented user stories.
- Key result
- A reconciled decision system across 1,067,371 transaction lines, with £20.97M gross sales and £1.53M cancellation amount in the historical dataset.
- Guardrail
- The source has no cost or cancellation-reason fields; the project does not claim profitability, root cause or realised commercial impact.
An analytical decision
Keep sales separate from profit
The source contains transactions but no cost or cancellation-reason fields. I report sales and cancellations separately without presenting net sales as profit. Assessing profitability or explaining why orders were cancelled would require additional data.
Follow a requirement through the KPI definition, SQL output and UAT check.Analytical chain
Project evidence
Artifacts that make the work reviewable.
The dashboard is a real repository output. The traceability and UAT previews reproduce verified rows from the project documentation.
Executive dashboard
Sales, cancellations and customer value
Verified dashboard output generated from UCI Online Retail II.
Why it mattersIt brings governed sales and cancellation measures into one decision view while explicitly separating net sales from profit.Traceability matrix excerpt
Business requirements connected to evidence
Verified requirement rows from the completed analytical build.
Why it mattersEach requirement points to implementation, output evidence and a test rather than ending as an isolated document.UAT evidence excerpt
Reconciled measures and controlled change
Representative passed checks from the repository’s UAT plan.
Why it mattersThe tests verify KPI arithmetic, lineage, database integrity and consistency across dashboard and Office outputs.Problem & scope
Where should an e-commerce leadership team focus first to protect sales value, reduce cancellation exposure and retain valuable customers? Both sheets of the historical UCI Online Retail II workbook, covering 2009-12-01 to 2011-12-09, analysed at transaction-line grain. The historical UCI Online Retail II dataset. All figures describe analytical findings in that dataset, not revenue or profit generated by Onur.
My contribution & intended users
I documented business requirements and KPI rules, built the SQL model and dashboard, and linked requirements to UAT checks and output evidence. Executive sponsors, commercial leads, operations teams, CRM leads and data owners represented through documented user stories.
Requirements & calculation rules
The BRD, user stories and acceptance rules specify what each audience must be able to understand and verify in the finished reporting pack. Gross sales, cancellation amount, completed orders and repeat-customer rate are documented with calculation logic, grain, exclusions and data-quality expectations.
Analysis & outputs
A reproducible Python pipeline standardises transactions, identifies cancellations and prepares governed inputs for SQL analysis and management reporting. A SQLite star schema separates transaction facts from customer, product and time dimensions, creating consistent routes to order and customer-level measures. The historical data contains 1,067,371 transaction lines, £20.97M gross sales, £1.53M cancellation amount and 41,938 completed orders. The repeat rate is 72.4% among identified active customers.
Validation & traceability
Tests connect business requirements to pipeline rules, SQL outputs, dashboard elements and the Excel management pack. Cancellation identification, customer availability, invalid quantities and price handling are explicit. Net sales are never presented as profit, and historical figures are not claimed as commercial impact.
Recommendation & limits
The system creates a shared basis for deciding which cancellation patterns, customer groups and sales-value exposures deserve investigation first. Customer-level measures depend on identified customer records, and the dataset represents a historical retail context. Recommendations require validation against current commercial conditions. The source has no cost or cancellation-reason fields; the project does not claim profitability, root cause or realised commercial impact.