What this project is about
Retail reporting becomes unreliable when different dashboards disagree about what a sale represents or how returns affect revenue. Customer360 builds a small warehouse around questions about repeat customers, product performance and revenue over time. Its central contribution is an explicit data model and reporting contract that can be inspected alongside the code producing it.
The project uses Python and SQLite to make the warehouse reproducible without a managed database. Source CSVs, schema, load statements, analytical views, exports and Power BI measures are all visible in the repository. It is a demonstration of warehouse and business-intelligence modeling, not evidence of an enterprise customer platform or a deployed Power BI report used by a retail business.
How it works
- Retail source tables
- Star-schema load + integrity checks
- Reporting views + BI exports
Define the meaning of one fact row
The sales fact table stores one product line on an order. This grain—the event represented by one row—lets product quantities and returns aggregate consistently while distinct order identifiers count orders without counting every line twice. Separate customer, product and date dimensions provide descriptive attributes. Foreign keys connect facts to those dimensions, and constraints reject impossible quantities or returns larger than the purchased quantity.
Keep revenue calculations explainable
The load derives gross revenue from quantity and price, return value from returned quantity and price, and net revenue from the remaining quantity. Source staging tables stay available for inspection. Reporting views expose monthly performance, customer order history and product revenue and margin. Customer and product dimensions use generated surrogate keys while preserving source identifiers, creating a clear boundary between source systems and warehouse relationships.
Publish reusable analytical outputs
The Python builder creates the database, loads the sample sources and exports three reporting views as CSVs. SQL and DAX measures describe net revenue, distinct orders, active customers, return rate and returning-customer share. A dashboard wireframe proposes date, region and category filters with product and customer detail pages. Those design assets guide report construction; they should not be described as a completed interactive report deployment.
The important design choices
Model sales at order-line level
Store each product line separately and retain the order identifier for distinct-order measures.
The trade-offProduct-level revenue and returns remain traceable, but report authors must distinguish additive line measures from distinct counts. Summing a count of lines would not answer how many orders were placed.
Use current dimension attributes
Keep a simple current-state customer and product model rather than inventing historical attribute changes absent from the source.
The trade-offThe demonstration stays faithful to its data, but it cannot reconstruct a customer's former region or earlier product attributes. Historical reporting would require effective dates and explicit change rules.
An order containing a returned product
An illustrative order contains three units priced at £20 each, with one unit subsequently marked returned in the source row.
- The load resolves that row to customer, product and date dimension keys while preserving its order and line identifiers.
- The fact records £60 gross revenue, £20 return value and £40 net revenue from the supplied quantities.
- Product and monthly views include those amounts, while an order measure counts the distinct order identifier.
What this showsA reviewer can reconcile the product-level calculation to the monthly total and explain why revenue differs before and after returns.
Illustrative example, not a production incident.
How it was tested—and what it shows
The checks
Repository tests rebuild the sample warehouse in a temporary location, compare fact and source row counts, and check that gross revenue minus returns equals net revenue. Additional checks exercise the customer and product reporting views. The wireframe notes call for validating report totals against SQL before publishing. These checks establish consistency for the supplied fixtures, not comprehensive handling of arbitrary commercial data.
What to take from the results
The useful result is a compact analytical model with visible lineage and repeatable outputs. Its builder recreates the selected database and seeds a fixed date range, so it is not an incremental warehouse loader. The load also uses inner joins: unmatched dimension records can disappear unless reconciliation detects them. A production extension needs explicit rejected-row handling, batch provenance and safe incremental updates.
What to keep in mind
- A local reporting model is not evidence of a production warehouse deployment.
- Reporting quality still depends on the definitions and source data supplied.
Technical notes & source
Built with
Python · SQLite · SQL · Power BI modelling
Terms used in this project
- Star schema
- A reporting layout with transaction records linked to descriptive lookup tables.