weshy007/kenya-retail-sales

Management wants a reliable relational model and a dashboard that explain sales performance, customer behaviour, product demand, discounts, payments, and delivery operations. The source is nested JSON, so the first part of the project is to inspect and normalize it before doing any analysis. # Kenya Retail Sales — JSON, SQL and Power BI Project Brief **Dataset:** `kenya_retail_sales.json` · 80 orders · 199 order items · 12 customers · 10 products · 5 Jan 2026 → 28 Jul 2026 · Currency: KES You are a data analyst for a Kenyan omnichannel retailer. Management wants a reliable relational model and a dashboard that explain sales performance, customer behaviour, product demand, discounts, payments, and delivery operations. The source is nested JSON, so the first part of the project is to inspect and normalize it before doing any analysis. The data is synthetic and suitable for practice. Keep the source JSON unchanged and make your transformation reproducible. --- ## Project tools - Python 3 with `json` or pandas for inspecting and flattening the source - PostgreSQL and DBeaver for relational modelling and SQL analysis - Power BI Desktop for the final dashboard --- ## Source structure The root object contains dataset metadata and an `orders` array. Each order contains: - one nested `customer` object; - one nested `shipping` object; - one nested `payment` object; and - an `items` array containing one or more order-item objects. This means one JSON order must not be loaded directly into one flat table: repeating the order, customer, and payment fields for every item would introduce duplication and make updates unreliable. ### Getting started in Python ```python import json from pathlib import Path path = Path("../../datasets/json/kenya_retail_sales.json") with path.open(encoding="utf-8") as file: retail = json.load(file) orders = retail["orders"] print(len(orders)) print(orders[0].keys()) ``` If your notebook is stored somewhere else, adjust the relative path rather than moving or editing the source file. --- ## Recommended relational model Normalize the JSON into five tables. Use the IDs—not names—as join keys, and store all money fields as `NUMERIC(14,2)` so calculations do not depend on floating-point rounding. ### `customers` | Column | …