Case 042026 · SQL analytics

Where is money actually made once every cost is counted?

A full-cost-stack profitability investigation of a three-outlet Kenyan agrifood retailer.

Living document — in progressSQLMarket analytics

At a glance

Period covered: FY2025
Connected tables: 13
Orders: 47,693
Order line items: 82,759

The verdict so far

KES 75.3M of revenue against KES 73.6M of fully-loaded cost leaves a gross margin of KES 1,682,167 — about 2.2%. Nairobi looks dominant on revenue but sources only 25.1% of its own stock, and 38.6% of accepted inventory never reaches a customer.

Business context, data understanding, objectives and Day 1 of the analysis are final. Day 2's queries are built and shown below with findings pending. Days 3–4 and the recommendations are still to come.

Stage 1

Business context

Executive summary

Mkulima Direct FoodMart is a fictional but deliberately realistic agrifood retailer operating three outlets in Nairobi, Nakuru, and Meru. Nakuru and Meru sit in genuine agricultural production zones and procure produce, cereals, and value-added goods locally, retailing part of that stock and transferring the surplus north to Nairobi. Nairobi has no agricultural hinterland of its own — it functions primarily as an urban demand center, sourcing only meat, poultry, and fish directly from local distributors, while relying on inter-outlet transfers for the rest of its shelf stock. Management's core concern: understanding true profitability per outlet and category once procurement, transport, storage, and labor costs are all counted — not just gross sales against purchase price.

Company overview

Mkulima Direct runs three retail outlets on busy urban roads, all selling the same core catalog: cereals and grains, fresh produce, meat/poultry/fish, and value-added goods (honey, syrups, spices). Two of the three outlets (Nakuru, Meru) are also procurement points; Nairobi is a hybrid demand center with a narrow local procurement lane of its own.

Industry context

The business sits in Kenya's urban fresh-food retail space, competing against open-air markets, supermarket fresh sections, and informal vendors. Its differentiator is a hybrid model — physical outlets for trust and immediacy, combined with WhatsApp-first ordering for convenience — targeting customers who want reliability without supermarket markup or open-market unpredictability.

Business model

Revenue comes from retail markup across four product lines. Nakuru and Meru's local production advantage is deliberately not treated as automatically efficient: Meru itself is an agricultural zone, so shipping stock from it up to Nairobi is a real logistics cost, not a free win. That tension — is inter-outlet transfer actually paying for itself — is a central analytical question of this project, not an assumption baked into the story.

Stakeholders

The business owner/operator, outlet managers in each city, procurement staff at Nakuru and Meru, and the customer base — largely salaried urban professionals (teachers, healthcare workers, corporate professionals, civil servants) ordering primarily via WhatsApp, with e-commerce and walk-in filling the remainder.

The problem

The business has scaled to three cities without clear visibility into where money is actually made or lost. Sales figures look healthy at a surface level, but management doesn't know whether Nairobi's revenue dominance is genuinely profitable once its transfer-dependency costs are counted, which categories are quietly losing margin to waste, or whether the WhatsApp-heavy channel model helps or hurts the bottom line.

Without full cost-stack visibility, the business risks expanding on the assumption the current model scales cleanly, or cutting a genuinely profitable outlet/category because gross revenue looked weak while true margin was fine.

Assumptions

1Nakuru and Meru procure locally; Nairobi receives the majority of its stock via inter-outlet transfer, supplemented by its own direct procurement of meat/poultry/fish/eggs.
2WhatsApp and walk-in orders are logged manually into the same order records as e-commerce, tagged by channel.
3Storage cost covers both outlet-level holding and (for transferred stock) a second Nairobi leg.
4Indirect labor and shared transport cost are allocated to unit level using a volume-based driver — a modeling choice, not a measured fact, restated wherever it is used.
5Rent, utilities, fixed facility costs, marketing spend, and the rural-farm-to-outlet leg of the supply chain are out of scope.

Expected business value

A working profitability model by outlet, category, and channel — sharp enough to identify which transfer route (if any) is underperforming, which categories are structurally low-margin once fully costed, and whether WhatsApp's dominance is a net cost driver or a net positive once cancellations and negotiated pricing are weighed together.

Stage 2

Data understanding

Data overview

A synthetic relational dataset covering one full year (Jan 1 – Dec 31, 2025) across 13 tables: outlets, products, suppliers, customers, batches, transfers, transfer_batch_items, transfer_receipts, storage_costs, waste_log, labor_direct, labor_indirect, orders, and order_items.

Sources and collection

Batches represent individual procurement events, one row per purchase, carrying unit cost (with built-in seasonal variation tied to Kenya's long-rains and short-rains harvest cycles), quality grade, and intake rejection. Sales data spans three channels — WhatsApp (~60% of order volume at every outlet), e-commerce, and walk-in — with actual transaction price captured separately from list price to reflect WhatsApp's negotiated pricing behavior.

Data dictionary

Table	Purpose
outlets	3 outlets — Nairobi, Nakuru, Meru
products	24 SKUs across cereals, produce, meat_poultry_fish, value_added
suppliers	Farmer groups, cooperatives, distributors by sourcing outlet
customers	420 customers with segment and channel preference
batches	Procurement spine — every purchase event, cost, rejection, transfer/local split
transfers	Nakuru/Meru → Nairobi trips
transfer_batch_items	Junction: which batches fed which transfer
transfer_receipts	Arrival reconciliation, expected vs. received, shrinkage
storage_costs	Origin and destination storage legs, by batch
waste_log	Every loss point across 5 stages, tagged by cause
labor_direct	Event-tied labor (batch sorting, transfer packing)
labor_indirect	Daily outlet-level overhead labor
orders	47,693 orders across 3 channels
order_items	82,759 line items, batch-linked, actual transaction price

Data quality

Full validation (row counts, primary key integrity, foreign key referential integrity, date-boundary checks, duplicate key checks) passed cleanly across all 13 tables — detailed in the analysis journey, Day 1, Steps 1.1–1.3 below.

Known limitations

Indirect labor and shared transport cost are allocations, not direct measurements — see the assumptions log in Step 1.5 for the exact allocation logic used. value_added is a materially thinner category by volume than the other three; e-commerce is a materially thinner channel by order count than WhatsApp. Any finding drawn specifically from either should be read with that in mind.

Stage 3

Objectives & KPIs

Business goal

Determine true profitability by outlet, category, and channel once the full cost stack (procurement, transport, storage, labor) is applied — not gross margin against procurement price alone.

Stakeholder questions

1Is Nairobi's revenue dominance genuinely profitable given its transfer-dependency?
2Which waste stage is costing the most money, not just the most kilos?
3Is the WhatsApp-first channel model a net cost driver or a net positive?

KPI definitions

KPI	Why it matters	How calculated
Landed cost per unit	The true cost basis for any profitability comparison	unit_cost + allocated storage + allocated transport + allocated labor (varies by local vs. transferred path)
Waste rate (value-weighted)	Kilos lost overstate/understate the real damage depending on product value	quantity_wasted × landed cost at loss stage, summed and compared to accepted inventory
Outlet transfer-dependency	Determines how exposed an outlet is to transfer-route risk	transferred-in quantity ÷ total inbound quantity
Channel effective revenue	Captures the real cost of cancellations, not just fulfilled-order averages	total revenue ÷ total attempted orders (cancellations included at zero)
Margin by outlet × category	The core profitability decision-making unit for the business	(revenue − landed cost) ÷ revenue, at the outlet-category level

Analytical plan

A 4-day investigation: Day 1, data understanding and join architecture; Day 2, cost structure and waste attribution; Day 3, profitability synthesis across outlet/category/channel; Day 4, recommendations and the executive report.

Stage 4

Analysis journey

Day 1 — complete

Data understanding & join architecture

Leading question: can we reconstruct the full cost trail for any unit of product, from purchase to final fate, without the numbers breaking?

Step 1.1 — Table inventory and row-level sanity check

Confirmed row counts, checked for null primary keys, verified every foreign key resolves to a real parent row, confirmed all transactional dates sit inside 2025, and checked for duplicate primary keys across all 13 tables. All checks passed cleanly.

Step 1.1 — foreign key integrity

-- Foreign key integrity example: batches -> products
SELECT b.* FROM 'raw_data/batches.csv' b
LEFT JOIN 'raw_data/products.csv' p ON b.product_id = p.product_id
WHERE p.product_id IS NULL;
-- Returned empty on every relationship checked

Step 1.2 — Reconciliation of the batch lifecycle

Confirmed the core quantity identity: quantity_purchased = quantity_accepted + quantity_rejected_at_intake, and in aggregate, quantity_accepted ≈ quantity_sold + quantity_wasted (excluding sourcing_rejection).

Step 1.2 — quantity identity

WITH total_accepted AS (
    SELECT SUM(quantity_accepted) AS accepted_total FROM 'raw_data/batches.csv'
),
total_sold AS (
    SELECT SUM(quantity_sold) AS sold_total FROM 'raw_data/order_items.csv'
),
total_wasted AS (
    SELECT SUM(quantity_wasted) AS wasted_total FROM 'raw_data/waste_log.csv'
    WHERE waste_stage != 'sourcing_rejection'
)
SELECT accepted_total, sold_total, wasted_total,
       ROUND(100.0 * (accepted_total - (sold_total + wasted_total)) / accepted_total, 3) AS gap_pct
FROM total_accepted, total_sold, total_wasted;

Finding: initial aggregate gap of -0.379%. Broken down by product, every category sat under 1% except meat_poultry_fish, which showed a consistent ~-8% gap across all five products in the category — a clear, isolated anomaly rather than distributed rounding noise.

Bug found & fixed

Double-counted waste in Nairobi's meat/poultry/fish category

Root cause: the synthetic data generator applied an "origin storage spoilage" calculation uniformly to every batch, including Nairobi's own local procurement — which has no origin leg distinct from its point of sale, since Nairobi is both the source and the destination for that category. This caused 612 phantom waste entries (5,860.3 units) to be logged against Nairobi, double-counting stock that was actually still sellable.

The wrong way (data generation)

# Applied to ALL batches uniformly, with no check for whether the batch
# actually has a separate origin-to-destination leg:
batches["origin_storage_spoilage_qty"] = batches.apply(compute_origin_spoilage, axis=1)
# ...later, ALL origin_storage_spoilage_qty > 0 rows were logged to waste_log,
# including Nairobi's own local batches, which never travel anywhere:
for _, b in batches.iterrows():
    if b.origin_storage_spoilage_qty > 0:
        waste_rows.append({
            "waste_id": wid, "batch_id": int(b.batch_id), "transfer_id": None,
            "outlet_id": int(b.sourcing_outlet_id), "product_id": int(b.product_id),
            "waste_stage": "origin_storage_spoilage", ...
        })

The fix

waste = pd.read_csv('waste_log.csv')
before = len(waste)
fixed = waste[~((waste.waste_stage == 'origin_storage_spoilage') & (waste.outlet_id == 1))].copy()
after = len(fixed)
print(f'Removed {before-after} erroneous rows ({before} -> {after})')
fixed.to_csv('waste_log.csv', index=False)
# Removed 612 erroneous rows (13916 -> 13304)

Post-fix verification: re-ran the category-level reconciliation query. meat_poultry_fish moved from ~-8% to -0.004%, in line with every other category (all now under 0.4%). Full validation suite re-run and passed clean.

Step 1.3 — Join path verification

Confirmed no batch was split across more than one transfer, and directly demonstrated the aggregate-before-join rule using a real batch.

Step 1.3 — wrong vs. right join order

-- WRONG: joining before aggregating inflates a batch-level fact
SELECT b.batch_id, COUNT(*) AS order_item_lines,
       SUM(b.quantity_purchased) AS inflated_batch_quantity  -- repeats once per line item
FROM 'raw_data/batches.csv' b
JOIN 'raw_data/order_items.csv' oi ON b.batch_id = oi.batch_id
GROUP BY b.batch_id
ORDER BY order_item_lines DESC LIMIT 5;

-- RIGHT: aggregate first, then join the batch-level fact once
WITH order_agg AS (
    SELECT batch_id, COUNT(*) AS order_item_lines, SUM(quantity_sold) AS total_sold
    FROM 'raw_data/order_items.csv' GROUP BY batch_id
)
SELECT b.batch_id, b.quantity_purchased, oa.order_item_lines, oa.total_sold
FROM 'raw_data/batches.csv' b
JOIN order_agg oa ON b.batch_id = oa.batch_id
ORDER BY oa.order_item_lines DESC LIMIT 5;

Finding: order_items and storage_costs both carry a one-to-many relationship against batches and must be aggregated before joining; transfer_batch_items maps cleanly one batch to one transfer.

Step 1.4 — Descriptive shape of the business

Step 1.4 — Nairobi's inbound split

WITH local_inbound AS (
    SELECT SUM(quantity_accepted) AS qty FROM 'raw_data/batches.csv' WHERE sourcing_outlet_id = 1
),
transferred_inbound AS (
    SELECT SUM(tr.received_quantity) AS qty
    FROM 'raw_data/transfer_receipts.csv' tr
    JOIN 'raw_data/transfers.csv' t ON tr.transfer_id = t.transfer_id
    WHERE t.destination_outlet_id = 1
)
SELECT li.qty AS local_qty, ti.qty AS transferred_qty,
       ROUND(100.0 * li.qty / (li.qty + ti.qty), 1) AS local_pct,
       ROUND(100.0 * ti.qty / (li.qty + ti.qty), 1) AS transfer_pct
FROM local_inbound li, transferred_inbound ti;

Sourcing model holds exactly as designed: Nakuru and Meru procure at near-identical scale (within ~1% of each other); Nairobi sources only meat_poultry_fish.
Nairobi sources only 25.1% of its own stock — the remaining 74.9% arrives via transfer from Nakuru and Meru.
WhatsApp drives ~60% of order volume at every outlet. Nairobi alone generated KES 41.3M in revenue — more than Nakuru (KES 18.6M) and Meru (KES 15.3M) combined (KES 34.0M).
Nairobi's average order is smaller in volume (~9.7–9.9 units) but higher in value (~KES 2,059–2,160) than Nakuru/Meru (~13.5–14.2 units, ~KES 1,248–1,358) — driven by meat/poultry's higher unit price, not pricing power.
Cancellation rate: WhatsApp 4.96%, e-commerce 1.97%, walk-in 1.03% — the original “e-commerce ≈ walk-in” assumption was revised on the data's terms; e-commerce cancels at nearly double walk-in's rate.

Step 1.5 — Data quality flags and assumptions log

Indirect labor is allocated using a volume-based driver (outlet-day cost ÷ outlet-day volume handled) — an assumption, not a measurement. Transport cost on transfers is allocated by each batch's share of trip volume. value_added and e-commerce are flagged as thinner-sample categories/channels deserving extra caution in any specific finding. Rural-farm-to-outlet logistics, marketing spend, staff turnover, and fixed facility costs are explicitly out of scope.

Day 2 — queries built, findings pending

Cost structure & waste attribution

Leading question: where exactly is the full cost stack breaking margin down, and by how much?

2.1

Build landed cost per unit

Goal: Produce a defensible, batch-level landed cost figure, built differently for locally-sold vs. transferred units since each carries a different set of cost layers.

Step 2.1 — landed cost per unit (local path)

-- Locally-sold unit landed cost (Nakuru/Meru, sold at origin)
WITH origin_storage AS (
    SELECT batch_id, SUM(total_cost) AS origin_storage_cost
    FROM 'raw_data/storage_costs.csv' WHERE storage_stage = 'origin' GROUP BY batch_id
),
direct_labor AS (
    SELECT related_batch_id AS batch_id, SUM(labor_cost) AS batch_sorting_cost
    FROM 'raw_data/labor_direct.csv'
    WHERE labor_type = 'batch_sorting' AND related_batch_id IS NOT NULL
    GROUP BY related_batch_id
)
SELECT
    b.batch_id, b.unit_cost,
    b.unit_cost
        + (COALESCE(os.origin_storage_cost, 0) / NULLIF(b.local_available_qty, 0))
        + (COALESCE(dl.batch_sorting_cost, 0) / NULLIF(b.quantity_purchased, 0)) AS landed_cost_per_unit_local
FROM 'raw_data/batches.csv' b
LEFT JOIN origin_storage os ON b.batch_id = os.batch_id
LEFT JOIN direct_labor dl ON b.batch_id = dl.batch_id;

2.2

Actual revenue per unit

Goal: Measure what units actually sold for against list_price, by channel, and by outlet — testing for negotiated-pricing drift and a genuine Nairobi urban premium.

Step 2.2 — actual vs. list price

WITH actual_price AS (
    SELECT product_id, AVG(unit_price_actual) AS avg_actual_price
    FROM 'raw_data/order_items.csv' GROUP BY product_id
)
SELECT p.product_name, p.list_price, ROUND(ap.avg_actual_price, 2) AS avg_actual_price,
       ROUND(100.0 * (ap.avg_actual_price - p.list_price) / p.list_price, 2) AS price_gap_pct
FROM 'raw_data/products.csv' p
JOIN actual_price ap ON p.product_id = ap.product_id
ORDER BY price_gap_pct;

2.3

Cost-weighted waste analysis

Goal: Rank waste by KES value lost, not kilos — valuing each waste event at the landed cost it had accumulated by the stage it was lost at.

Step 2.3 — waste ranked by value

-- (built on the full waste_valued CTE chain assembled in this stage)
SELECT waste_stage, COUNT(*) AS event_count,
       ROUND(SUM(quantity_wasted), 1) AS total_qty_wasted,
       ROUND(SUM(quantity_wasted * cost_per_unit_at_loss), 2) AS total_waste_value_kes
FROM waste_valued
GROUP BY waste_stage
ORDER BY total_waste_value_kes DESC;

2.4

Nairobi's structural cost disadvantage test

Goal: Test whether Nairobi's transfer-in and double storage cost layers are visible in landed cost, and whether its higher price/volume actually closes that gap.

Step 2.4 — origin vs. Nairobi landed cost

SELECT p.product_name,
       ROUND(AVG(ol.origin_landed_cost_per_unit), 2) AS avg_origin_landed_cost,
       ROUND(AVG(nl.nairobi_landed_cost_per_unit), 2) AS avg_nairobi_landed_cost,
       ROUND(100.0 * (AVG(nl.nairobi_landed_cost_per_unit) - AVG(ol.origin_landed_cost_per_unit))
             / NULLIF(AVG(ol.origin_landed_cost_per_unit), 0), 1) AS cost_gap_pct
FROM nairobi_landed nl
JOIN origin_landed ol ON nl.batch_id = ol.batch_id
JOIN 'raw_data/products.csv' p ON ol.product_id = p.product_id
GROUP BY p.product_name
ORDER BY cost_gap_pct DESC;

2.5

Channel cost-behavior analysis

Goal: Determine whether WhatsApp's dominance is a net cost driver or a net positive, once cancellations and negotiated pricing are weighed together — including a direct test for any category sold below landed cost on WhatsApp specifically.

Step 2.5 — effective revenue per attempted order

SELECT order_channel, COUNT(*) AS attempted_orders,
       SUM(total_amount) AS total_revenue,
       ROUND(SUM(total_amount) / COUNT(*), 2) AS effective_revenue_per_attempted_order
FROM 'raw_data/orders.csv'
GROUP BY order_channel
ORDER BY effective_revenue_per_attempted_order DESC;

Day 2 findings will be appended here once the query outputs are finalized. The allocation drivers used above (indirect labor and shared transport, spread to unit level by volume) are assumptions, stated in Stage 1.

Day 3 · Planned

Profitability synthesis

Margin brought together across outlet, category, and channel — the layer that turns the cost stack into a decision.

Day 4 · Planned

Recommendations & executive report

The executive summary, dashboard walkthrough, recommendations with expected financial impact, risks, and next steps.

Results

KPI summary

As of Day 1 — confirmed figures only.

KPI	Value
Total fulfilled revenue (FY2025)	KES 75,306,131
Total procurement spend	KES 63,697,798
Total transport cost	KES 2,298,571
Total storage cost	KES 608,590
Total direct labor cost	KES 1,265,640
Total indirect labor cost	KES 5,753,365
Total cost (all layers combined)	KES 73,623,964
Gross margin (revenue − total cost)	KES 1,682,167 (≈2.2%)
Total quantity accepted into inventory	891,733.6 units
Total quantity sold	574,386.6 units (≈64.4% sell-through)
Total quantity wasted (all 5 stages)	344,283.2 units (≈38.6% waste rate)
Nairobi's local procurement share	25.1%
Nairobi's transfer-in share	74.9%
Nairobi revenue share	KES 41.3M (vs. KES 18.6M Nakuru, KES 15.3M Meru)
WhatsApp order share (all outlets)	≈60%
WhatsApp cancellation rate	4.96%
E-commerce cancellation rate	1.97%
Walk-in cancellation rate	1.03%
Total batches / transfers / orders	2,915 / 206 / 47,693

Stage 5

Business recommendations

Pending

This section is pending completion of Day 3 (profitability synthesis) and Day 4 (recommendations and the executive report). It will present the executive summary, KPI summary, dashboard walkthrough, key insights, business recommendations with expected financial impact, risks, and next steps — each traced directly back to a specific finding above.

Want this level of cost visibility in your own business?

Every assumption labelled, every number reconciled, every recommendation traceable to a query.

Book a call WhatsApp me

← All case files View the notebook