What 6,000 Pharmacy Transactions Taught Me About Discounts

A 25% discount cut this pharmacy's margin from 31% to 8% and did not sell a single extra unit per order. That was the clearest finding from a project I built on 6,000 transactions from a multi-branch Kenyan retail pharmacy, running from a raw CSV through PostgreSQL to a Power BI dashboard.

The finding is useful. The way I got to it is the part I want to write about. Before any chart reached the dashboard, the SQL behind it had to reconcile to the raw data to the last shilling. That habit shaped every decision in the project, and it is the main thing I'd want another analyst to take from this article.

The project

The data covers nine months of sales, January to September 2026, from a pharmacy chain selling in-store, online and through a mobile app.

Item	Detail
Volume	6,000 order lines, 1,724 customers, 189 products
Footprint	11 branches in Nairobi, Mombasa, Nakuru, Kisumu and Eldoret
Categories	9, from Cold & Flu to Dermatological Skincare
Database	PostgreSQL 16, hosted on Aiven
Reporting	Power BI Desktop, three report pages

The pipeline has three layers. Raw data lands in a staging schema exactly as it arrives. A cleaning step produces one typed, validated fact table. Nine SQL views sit on top of that table, each pre-aggregated to the grain one part of the dashboard needs, and Power BI reads only those.

I trained in clinical medicine before moving into data, so a pharmacy's sales ledger was familiar ground. Knowing what Postinor, Femiplan or a compression stocking is helps when a product list needs a sanity check.

Land it raw, then look before you clean

The staging table stores every column as text. That sounds lazy, but it is deliberate: a bad date or a stray character can't stop the load or get silently turned into something else. The CSV goes in with \copy, which also works against a managed cloud database where server-side file access is blocked.

Next came profiling, before a single cleaning rule was written. I checked duplicates, missing values, whether every value could be cast to its proper type, whether categories were spelled consistently, and whether the money columns added up. Revenue should equal price × quantity × (1 − discount), and profit should equal revenue minus cost × quantity. Both held on all 6,000 rows.

Three results shaped what came next:

• A quarter of ratings were missing (1,546 rows, 25.8%). I left them empty instead of filling in a value. Filling them in would have quietly shifted every average-rating figure in the report.
• 203 sales lost money. These looked like errors at first, but they are real transactions, and they turned out to be the most interesting part of the data.
• Customer details weren't stable. 1,530 customer IDs had a different gender, age or city on different orders, which means demographics were recorded at the till on each sale, not stored once per person. The customer view takes each person's most recent details, and I treat customer-level demographics as indicative only.

One clean table, one definition per number, and tests

All the cleaning happens in a single script. It trims text, casts every column to its real type, removes duplicate orders, rejects rows that break business rules, and adds seven derived columns such as month, weekday, hour and line margin. A primary key and check constraints then lock those rules in, so a future reload can't sneak bad rows past them.

The nine views share one set of definitions. Revenue excludes delivery fees, which are a pass-through charge (KES 461,850 over the period). Margin is total profit divided by total revenue. Average order value is total revenue divided by the number of distinct orders. Writing each formula once in SQL means the dashboard, an ad-hoc query and any tool added later all report the same figure.

Then the tests. Each view has to add back up to the fact table's revenue, profit and units, and each has to return exactly one row per key: one per day, per branch, per product. A duplicated row in a view doesn't throw an error in Power BI. It just doubles a number on a chart and nobody notices. All 15 tests pass, and the branch table in the dashboard matches SQL to the cent: Eldoret Rupa shows KES 1,186,695.23 in both.

The dashboard

Power BI connects straight to the cloud database and reads the nine views. Because the business logic already lives in SQL, the report mostly handles layout, with one DAX measure for the hourly order count.

The report has three pages:

1. Executive Overview: six KPI cards, the monthly revenue trend, revenue against profit, branch and channel splits, and order patterns by weekday and hour.
2. Sales & Revenue Analysis: daily and monthly revenue, revenue by city, and a branch scorecard with revenue, orders, order value, profit and margin side by side.
3. Product Performance: top 10 products by revenue and by units, category profit and revenue, and a bubble chart of revenue against profit for all 189 products.

What the data said

The business made KES 14.30 million in revenue and KES 3.50 million in gross profit over nine months, a 24.5% margin on 6,000 orders. Monthly revenue held between KES 1.38 and 1.76 million, so this is a steady business rather than a growing one.

Discounts cost margin and bought nothing

Every step up in discount took margin down with it, while units per line hardly changed. A third of all revenue (KES 5.08 million) was sold at 15% off or more, at a combined margin of about 15%. All 203 loss-making sales sat in that band, 127 of them at 25%.

Other findings

• Volume and value are different lists. Paracetamol, Femiplan and sanitary pads top the units chart. Premium sun care and serums top revenue. Beauty & Skin Care and Dermatological Skincare together bring in 34.5% of revenue.
• No 80/20 here. It takes 119 of the 189 products (63%) to reach 80% of revenue, so there's no small set of hero products to lean on.
• Nairobi carries the business with 54.9% of revenue, but branches are evenly matched: the top one has 10.2% of revenue and the bottom one 7.7%.
• Revenue rank and margin rank disagree. Online (26.2%) and Westlands (25.9%) earn the best margins. Kilimani is fourth on revenue and last on margin.
• In-store brings half the revenue, Online 36% and the App 14%. M-Pesa pays for 61% of transactions in every channel.
• Customers come back. 89% bought more than once. The top quarter of customers bring in 44% of revenue.
• Prescription items are a small slice: 6.3% of revenue, at a lower margin than over-the-counter products. Commercially, this is a front-of-shop business.

What I told the pharmacy

1. Cap routine discounts at 10%. Keep deeper cuts for clearance or near-expiry stock, with a manager's sign-off. This is the biggest lever in the data.
2. Re-price the high-revenue, thin-margin lines. Products such as La Roche-Posay Anthelios Oil Control (10.2% margin) and Betadine Gargle (11.3%) sell well and earn little.
3. Protect the skincare range. Keep sun care, serums and dermatology brands in stock everywhere, and lead App and Online promotions with them.
4. Grow the App. It carries the joint-highest order value but only 13.6% of revenue, and nearly nine in ten customers already buy more than once, so reorder reminders should land well.
5. Win back loyal customers before they drift. 247 frequent, high-spending customers who haven't bought recently account for 25% of revenue.
6. Look closely at Kilimani. It is fourth on revenue but last on margin (23.0%), which points at discount use or product mix.

What I'd do differently

The tie-out caught something in my own dashboard. The Average Order Value card shows KES 2.39K. The true figure is KES 2,383.90, which rounds to 2.38K. The card was averaging 273 daily order values instead of dividing total revenue by total orders. The gap is small here, but an average of averages can mislead badly when the groups differ in size, and it is easy to miss because the number looks plausible. The fix is an explicit DAX measure that divides totals, and it is first on my list.

Also on the list:

• A proper date table in Power BI, so one slicer filters every visual on a page.
• Slicers for date, city, channel and category, and a drill-through from branch to product.
• Day names instead of 0–6 on the weekday chart.
• A fourth page on discounts and customer segments, since that is where the strongest findings are.
• Moving the SQL into dbt for lineage, documentation and automated tests.

Closing

The dashboard is the part people see, but most of the work was in the layers under it: profiling before cleaning, defining each number once, and testing that every view adds back up to the source. That work is why I can stand behind a finding like the discount one.

The full project is on GitHub, including the SQL scripts, data dictionary, test results and the Power BI file: pharmacy-sales-analytics.

If you work in retail or pharmacy analytics, I'd like to hear how you handle discount approval. And if you spot something I've missed in the analysis, tell me.