Yuanyuan Zhang
← All projects

01 — Retail analytics / Data warehouse

NordHome Retail Analytics

A retail analytics project combining PostgreSQL, Python, dimensional modeling and Power BI to investigate revenue, customer behaviour, product performance and marketing effectiveness. NordHome is a fictional pan-European online retailer (home décor, kitchen, beauty, lifestyle and gift sets, ten markets). The data covers January 2021 to June 2024, read as H1 2024 against H1 2023. 166K+ raw rows in 7 source tables.

  • PostgreSQL
  • SQL
  • Python / Pandas
  • Jupyter
  • Power BI
  • DAX
  • Power Query
  • Git / GitHub

View the repository on GitHub →

The data journey

  1. 01Raw dataSeven CSV files with planted errors: duplicates, orphaned keys, impossible values.
  2. 02ETLSQL in PostgreSQL: detect, count and judge each issue, then clean, flag or map to unknown.
  3. 03ModelA Kimball star schema with the grain of every table defined first.
  4. 04AnalysisPython EDA: one business question per chart, with limits stated.
  5. 05BIA four-page Power BI report that turns the model into recommendations.

Business question

What is happening across revenue, customers, products and marketing?

Approach

Data cleaning → ETL → dimensional modeling → EDA → BI

Output

A reusable analytical model and a four-page Power BI report.

Key findings

What the analysis revealed

01

Revenue declined despite a relatively stable gross margin.

The waterfall points to falling order value, not fewer or less frequent customers, as the driver. H2 initiatives should target basket size rather than acquisition.

02

Order cancellations and returns create a meaningful operational revenue impact.

In the report, cancellations (€115.6K) are the biggest leak, ahead of returns (€90.0K) and refunds (€85.3K). Cancellations are where an audit pays off first.

03

A small high-value customer segment is hidden by average customer metrics.

In the EDA, 151 of 7,969 customers (1.9%) spend above €6,790. The report also shows churn outpacing new customers in every month and market (Nordics: 802 retained vs. 1,674 churned).

04

Marketing performance varies substantially across channels and campaigns.

Influencer has the highest cost per acquisition (€28.21) and the lowest ROI (184.9%); Display and Pinterest return 6–8× more. Moving spend there is the most direct lever.

Power BI report

Building the dashboard

The Power BI report translates the analytical work into business-facing views. Each page starts with a business question and follows the data to understand what is happening, why it is happening, and where to look next.

The report lives in a private Power BI workspace, so these are screenshots. Click a view to open it full size.

Under the hood

How I built it

The data foundation, the quality work and the exploratory analysis behind the dashboards.

Data + modeling

Data quality

The difficult part wasn’t simply loading the data. I first had to determine which records could be trusted, which needed correction and which needed to be flagged.

496orders that point to customers who don’t existraw_orders
1,835returns for products missing from the catalogue (30.1%)raw_returns
471duplicate payment rows, plus 1,930 with no methodraw_payments
379negative quantities on order lines, plus 241 zero pricesraw_order_items

Ghost records are kept, not deleted: an order whose customer can’t be found is still a real sale, so it is mapped to an “unknown” key (−1). Totals stay correct, and analyses that need a real customer or product can exclude it. Also found: every order_id twice, ~18 spelling variants across 7 payment methods, 105 returns dated before the order, 22 negative refunds.

Found later, by questioning a number that looked wrong

228 corrupted quantities

A suspiciously high average order value led me to quantities of up to 1,996. Back-solving from the line total recovered the true quantity for 216 rows.

A date table that quietly stretched

A downstream defect pushed the date range to Dec 2024 and silently broke every year-over-year measure. I fixed it to the real Jan 2021 – Jun 2024 window.

148 people registered twice

91% had real orders under both IDs. Deleting would lose history, merging would break keys — so I kept both and added a canonical customer key.

Data + modeling

Data foundation

Pipeline

  • 01PostgreSQL — raw, staging and mart layers
  • 02Staging / transformation — cleaning and standardising seven source tables
  • 03Fact and dimension tables — 4 facts and 6 dimensions, with customer and date shared
  • 04Validation — post-cleaning checks, with later findings documented

Modeling decisions

The grain is the most important modeling decision. Get it wrong and joins fan out, measures double-count and totals stop being trustworthy.

  • Grain decisions — “one row = what?” answered before each build
  • Primary keys — one unique key per row in every fact and dimension table, e.g. customer_key or fact_order_item_key
  • Foreign keys — facts point to their dimensions through keys such as customer_key, date_key and product_key
  • Surrogate keys — an unknown member (−1) instead of deleted records
Power BI model view: four fact tables (marketing touchpoints, payments, order items, returns) connected to six dimension tables (date, customer, product, payment, return reason, marketing campaign)
Star schema in the Power BI model view: four fact tables around six dimensions. Unmatched (“ghost”) references point to an unknown member instead of being deleted.

Data + modeling

A problem I had to solve

Problem

On my first version of the revenue-leakage chart, refunds looked like the biggest source of lost revenue: €142K.

Investigation

Three loss-rate measures had been written at different times against different bases — one divided by Net Revenue, another by Gross Sales Revenue. And the refund figure came from the returns table, which also holds refunds booked against cancelled and returned orders. The same money sat in more than one bucket.

Solution

I rebuilt all three on one base — Gross Order Value — with mutually exclusive numerators, all taken from order status. Order value now splits cleanly into retained, cancelled, returned and refunded.

Lesson

A reliable dashboard starts with a reliable data model. A wrong denominator isn’t cosmetic — it can change the decision an executive makes.

Exploratory analysis

Asking questions before building the dashboard

Before the dashboard, I explored the data in Python: 25 charts across six business areas. Three examples:

The question

How much order value do cancellations, returns and refunds take away?

Bar chart of order value lost to cancellations, returns and refunds per year

What I found

About 17% of potential order value every year (16.5–17.4%) — a stable band with no improving trend. Cancellations are consistently the largest part.

Limitation Operational impact, not cash loss: actual cash refunds are a much smaller 2.6%.

The question

Does the “average customer” describe the customer base?

Histogram of revenue per customer with the top 1.9% highlighted above 6,790 euros

What I found

Not quite. 151 of 7,969 customers (1.9%) spend above €6,790, up to €11,428 — a small high-value group that the average hides.

Limitation Unknown customers and returned, refunded or cancelled value are excluded.

The question

Do return rates depend on the sales channel or on the market?

Heatmap of return rates by sales channel and country

What I found

Neither alone. Across 40 channel × country combinations, rates range from 16.0% to 22.8% (average 19.0%), highest for Marketplace in the Netherlands.

Limitation A modest signal: each cell is a small sample (~769 orders), so extreme cells may be noise.

The Python analysis and the Power BI report were built on different states of the data (some dashboard fields were regenerated later), so their numbers aren’t meant to be compared line by line.

Reflection

What I learned

Thinking comes before the dashboard

This project changed how I think about analytics. The dashboard is the visible part of the work, but most of the real thinking happens before it: understanding the business, defining the grain, checking relationships, writing down assumptions and deciding how imperfect data should be handled.

Grain and context decide trust

Grain and data quality decide whether a dashboard can be trusted. A refund figure that ranked first rested on a different base than its neighbours, and a date table that quietly stretched to December 2024 broke every year-over-year measure. A KPI without its context — its base, its period, its definition — can point a decision in the wrong direction.

Questions drive the design

It also taught me to let business questions drive the design, and that not everything I can build should be shipped: I built concentration measures, checked them, and dropped them when the data showed no real pattern. Connecting the technical work with the business question is the part I enjoy most — and next I want to take the customer question further with a segmentation (RFM) project.