Case study 03 · Data analytics
A retailer's purchasing decisions are only as good as its view of which vendors actually make money. This pipeline (SQL for ETL, Python for analysis and hypothesis testing, Power BI for the dashboard) evaluated vendor efficiency, pricing effectiveness and inventory turnover, and ended up contradicting the intuition it started with.
Learning project. This was my first time using SQL and Python inside a real project rather than for problem solving, so I followed a guided tutorial to get going and then extended it with my own analysis, visualisations and optimisations to take it past where the tutorial stopped.
01 · How it works
Sales, vendor and inventory data arrive as three independent files with nothing in common to join on. Until they are reconciled, a buying team cannot answer basic questions: which brands to reprice, how exposed we are to a few suppliers, whether bulk ordering pays, and where cash is stuck on a shelf.
The expensive part was not the analysis. It was SQL joins through CTEs into a single vendor-level table with purchase, sales, freight, margin and inventory figures pre-aggregated. Every question after that is one query instead of a fresh reconciliation.
Rows sold at or below cost, undefined margins and extreme freight values are removed first, with the exclusion rules written down. Averaging over them would have made healthy vendors look poor.
Distributions, outliers and correlations were run before deciding what to test, so the questions came out of the data rather than out of assumption.
The margin finding contradicted intuition, so it went through a hypothesis test in SciPy. A counter-intuitive result should not change a negotiation strategy on the strength of a chart.
SQL for the ETL, Python for analysis, SciPy for the test, Power BI for the thing a buying team opens in a meeting.
02 · What it found
Efficient to manage, expensive to depend on. This turns "we should diversify" from an opinion into a risk with a size on it.
It is not a loss on the P&L, it is cash that cannot be spent. Calling it blocked capital rather than a turnover ratio is what makes it a decision.
Bulk buying pays, and pays a lot. Read next to the $2.71M it creates the real tension in the whole analysis.
A named list, not a category-level observation. These are the candidates for promotion or repricing, where a small volume gain lands on an already-healthy margin.
03 · Challenges
Sales, vendor and inventory extracts arrive separately with no shared grain, which is the polite way of saying there is no column they agree on. Nothing could be answered until they were reconciled. I could have joined them per question, but then every finding would rest on its own ad-hoc join and two findings could quietly disagree. Building one vendor-level summary table first was slower to start and made everything after it a single query.
Some rows sold at or below cost, one as low as −$52,002.78. Worse, sales at zero cost produce an undefined profit margin, so any average over that column is meaningless. These are real transactions, not corrupt data, which made deleting them a judgement call rather than a cleanup. I removed them and wrote the exclusion rules on the page, because a margin analysis where the reader cannot see what was dropped is not worth much.
The assumption going in was that large vendors negotiate harder and therefore earn better margins. The data said the opposite, by a wide gap. Reporting that off a bar chart would have been irresponsible, so I ran a hypothesis test and it came back significant. The two groups are running different strategies: scale vendors trade margin for volume, niche vendors do the reverse. That changes what "a good vendor" means in a negotiation.
04 · Limitations
The margin gap between vendor types is real and significant. Why it exists is a negotiation question, and the data cannot answer it.
Dropping non-positive margin rows is defensible and it is still a choice. A different rule would move the numbers, which is why the rule is stated rather than buried.
Turnover and demand move with the season. This is a single window, so a slow-moving brand here might simply be off-season.
The bulk-versus-capital tension really needs lead time and holding cost per vendor. Neither was in the extracts, so the recommendation stops at "order big only where turnover justifies it".
The 198 brands are candidates for repricing, not a proven uplift. Whether a price change works has to be measured after it ships.
The pipeline runs in notebooks. It is not scheduled, so the dashboard is current as of the last run rather than live.
05 · What I learned
The instinct is to answer question one, then question two. Doing the boring aggregation first meant five questions became five queries, and no two findings could disagree with each other.
Every cleaning decision changes the answer. Writing the exclusion rules on the page is the difference between an analysis someone can check and one they have to trust.
The margin result was the one that contradicted me, which made it the one that needed statistics. A result that confirms what you expected is the one you are least likely to check.
Bulk savings and blocked capital point in opposite directions. Presenting both and saying so is more useful than picking whichever supports a tidier recommendation.
The full write-up carries the dashboard, the complete EDA and every query behind the four findings.