Should FruitCo expand local sales, export sales, or specific fruit types?
A five-year profitability analysis of a Kenyan orchard business.
At a glance
- Years of records
- 2021–2025
- Harvest events
- 414
- Fruit types
- 5
- Connected tables
- 6
The verdict
Not a binary export-versus-local decision. Expand export investment in Mango and Avocado, hold Orange's export effort at its current level, and maintain Pawpaw and Banana without prioritising further capital toward them.
Stage 1
Business context
Executive summary
FruitCo Limited is a fictional orchard business based in Ukambani, Kenya, built as a case study to demonstrate end-to-end SQL analytics on a realistic agrifood business problem. FruitCo grows five fruit types and sells through two channels: local markets, priced by weight, and international export, priced by grade. Management faces a real strategic question with no obvious answer on the surface: should the business expand its local sales, push harder into export, or concentrate effort on specific fruit types, and can that decision be made on evidence rather than instinct.
Company overview
FruitCo operates a multi-fruit orchard across Ukambani's semi-arid growing regions, cultivating Mango, Avocado, Orange, Pawpaw, and Banana. The business employs a farm workforce split across harvesting, grading and packing, and supervisory roles, and has operated continuously since before 2021.
Industry context
Kenya's horticultural export sector is a significant foreign exchange earner, with mango and avocado in particular commanding real international demand. At the same time, local fresh produce markets remain the backbone of most smallholder and mid-size orchard operations, offering lower prices but faster, lower-risk cash flow. Businesses like FruitCo sit at the intersection of these two markets, and the decision of how much to invest in each is a live operational question across the sector, not unique to this business.
Business model
Revenue comes from two distinct channels. Local sales move fruit by weight to markets and traders, priced per kilogram, with minimal quality sorting required. Export sales require fruit to be graded (A, B, or C) before sale, a process that adds labor cost but unlocks meaningfully higher prices per kilogram. Not every fruit type is viable for export; Pawpaw and Banana are sold exclusively through local channels due to their fragility and lower per-unit value internationally.
Stakeholders
Farm management, responsible for harvest allocation and expansion decisions; farm workers, split between harvesting and grading/packing roles, paid fixed monthly salaries; local buyers and traders; international export buyers and distribution partners.
The problem
FruitCo has five years of operational records (2021–2025) but has never formally analyzed them to determine whether its current channel mix, and its allocation of labor toward export grading specifically, is optimal. Management does not know, in hard numbers, whether directing more harvest and labor toward export grading is actually more profitable once labor cost is accounted for, or whether some fruit types are better left local. Revenue figures alone have driven past intuition, but revenue and profitability are not the same thing.
Without this analysis, FruitCo risks either over-investing labor into an export channel that isn't paying back the added grading cost, or under-investing in fruit types where export is genuinely worth the added effort. Both are avoidable if the decision is grounded in per-unit profitability rather than total revenue.
Assumptions
Labor is costed by converting each worker's fixed monthly salary into an hourly rate, assuming 26 working days per month at 8 hours per day (208 hours/month). This is a labeled assumption, not a fact present in the raw data; it applies uniformly across all workers and fruit types, so relative comparisons between fruits are reliable even though the absolute KES figures depend on this conversion. Harvest, sales, and labor data are assumed complete and accurately recorded for the full 2021–2025 period.
Expected business value
A clear, evidence-based answer to which fruit types and which channels deserve more harvest and labor investment, and which don't, directly informing FruitCo's resource allocation for the coming seasons.
Stage 2
Data understanding
Data overview
The dataset spans five years (2021–2025) and is structured across six connected tables, reflecting how a real orchard business would maintain records: fruit reference data, workforce records, harvest events, local sales transactions, export sales transactions, and labor time allocation. Records are maintained internally by FruitCo across harvest logging, sales transactions, and payroll/labor tracking.
Collection and pipeline
Harvests are logged weekly during each fruit's active growing season rather than year-round, reflecting Ukambani's rain-fed seasonal growing pattern. Sales are recorded transactionally as they occur, with a single harvest batch often generating multiple local and export sale records. Labor hours are logged per worker, per harvest batch, per activity type. Raw records are exported into six CSV files, then loaded into a SQL environment (DataCamp DataLab) for analysis.
Data dictionary
fruits
- fruit_id
- fruit_name
- variety
- is_export_eligible
workers
- worker_id
- name
- role
- monthly_salary
- hire_date
harvests
- harvest_id
- fruit_id
- harvest_date
- quantity_kg
- season
local_sales
- sale_id
- harvest_id
- weight_kg
- price_per_kg
- sale_date
export_sales
- sale_id
- harvest_id
- grade
- weight_kg
- price_per_kg
- sale_date
- destination_country
labor_allocation
- allocation_id
- worker_id
- harvest_id
- activity_type
- hours_worked
- allocation_date
Data quality
Validation confirmed harvest dates span the full five-year window for every fruit with no gaps, export sales appear only for the three export-eligible fruits (Mango, Avocado, Orange) as expected, and export grade pricing follows a consistent A > B > C hierarchy. No labor was logged against workers prior to their recorded hire date. No missing values were found in required fields. A row-duplication risk was identified and resolved during query construction rather than existing in the raw data itself.
No raw-data cleaning was required. The primary data-integrity risk was structural: naively joining the one-to-many child tables (local_sales, export_sales, labor_allocation) directly to harvests without aggregating first would silently inflate revenue and cost figures.
Limitations
Labor cost is derived from an assumed hours-per-month conversion, not a directly recorded hourly wage. Unsold/waste volume is inferred as the gap between harvested and sold quantity, not tracked with its own cause codes (spoilage, rejection, etc.), which limits deeper waste-specific analysis. Once aggregated correctly at the harvest level, the dataset supports revenue, cost, and profitability analysis by fruit, by channel, and by year.
Stage 3
Objectives & KPIs
Business goal
Determine where FruitCo should direct harvest and labor investment: toward local sales, export sales, or specific fruit types, based on profitability rather than assumption.
Analytical questions
- 1Which fruit types generate the most revenue, and through which channel?
- 2Which fruit types are most profitable once labor is subtracted?
- 3Does export profitability hold up per kilogram, or only in totals?
- 4How does profitability trend year over year?
- 5How much harvested volume goes unsold per fruit?
KPI definitions
Total Revenue
Local + export revenue per fruit; shows earning scale, calculated directly from sales tables.
Profit and Margin %
Revenue minus labor cost (harvesting + grading), and profit as a percentage of revenue; shows what's actually retained after labor, not just what's earned.
Net Export Price per KG
Export revenue minus grading cost, divided by export weight sold; isolates whether the export premium survives the labor cost specific to accessing it.
Year-over-Year Margin Volatility
Spread of margin % across 2021–2025; indicates risk and predictability, not just average return.
Unsold %
Harvested volume minus total sold, as a percentage of harvested volume; flags whether strong profitability is being achieved efficiently or is masking waste.
Expected outcomes
A ranked, evidence-based view of which fruits and channels deserve expanded investment, which should be held steady, and which carry more risk than their average numbers suggest.
Stage 4
Analysis journey
Workflow
The analysis proceeded in five phases: orienting in the raw data table-by-table, translating the business question into five queryable sub-questions, building and validating a harvest-level summary view, answering each sub-question in turn, and cross-checking the five findings against each other before drawing conclusions.
Initial review confirmed 414 harvest events across five fruit types with dates spanning the full five-year period, harvest frequency matching each fruit's designed seasonal pattern (Pawpaw, harvesting across two windows a year, showed the most events at 132; single-season Mango and Avocado each showed around 65), export sales present only for the three export-eligible fruits, and a clean A > B > C export grade price hierarchy.
A deliberate mistake, caught and fixed
The first version of the harvest-level join connected harvests directly to local_sales, export_sales, and labor_allocation in a single query without aggregating any of them first. Because each of these tables is one-to-many against harvests, this join silently multiplied revenue and labor cost figures — an error that produced plausible-looking but incorrect numbers, with no query error to flag it.
-- THE WRONG WAY — demonstrated deliberately, not used for analysis
-- Joining one-to-many child tables directly multiplies rows before aggregation,
-- inflating both revenue and labor hours.
SELECT
h.harvest_id,
h.quantity_kg,
SUM(l.weight_kg * l.price_per_kg) AS inflated_local_revenue,
SUM(la.hours_worked) AS inflated_labor_hours
FROM harvests h
JOIN local_sales l ON h.harvest_id = l.harvest_id
JOIN labor_allocation la ON h.harvest_id = la.harvest_id
WHERE h.harvest_id = 1
GROUP BY h.harvest_id, h.quantity_kg;The fix was to aggregate each child table down to exactly one row per harvest_id, summing revenue and labor cost separately in subqueries, before joining any of them back to harvests. This became the base view every sub-question query runs against.
-- THE CORRECT PATTERN — aggregate each child table before joining
CREATE VIEW harvest_summary AS
SELECT
h.harvest_id,
h.fruit_id,
f.fruit_name,
f.is_export_eligible,
h.harvest_date,
strftime('%Y', h.harvest_date) AS harvest_year,
h.quantity_kg,
COALESCE(l.local_weight, 0) AS local_weight_sold,
COALESCE(l.local_revenue, 0) AS local_revenue,
COALESCE(e.export_weight, 0) AS export_weight_sold,
COALESCE(e.export_revenue, 0) AS export_revenue,
COALESCE(lab.harvesting_cost, 0) AS harvesting_cost,
COALESCE(lab.grading_cost, 0) AS grading_cost
FROM harvests h
JOIN fruits f ON h.fruit_id = f.fruit_id
LEFT JOIN (
SELECT harvest_id, SUM(weight_kg) AS local_weight, SUM(weight_kg * price_per_kg) AS local_revenue
FROM local_sales GROUP BY harvest_id
) l ON h.harvest_id = l.harvest_id
LEFT JOIN (
SELECT harvest_id, SUM(weight_kg) AS export_weight, SUM(weight_kg * price_per_kg) AS export_revenue
FROM export_sales GROUP BY harvest_id
) e ON h.harvest_id = e.harvest_id
LEFT JOIN (
SELECT la.harvest_id,
SUM(CASE WHEN la.activity_type = 'Harvesting' THEN la.hours_worked * w.monthly_salary / (26*8) ELSE 0 END) AS harvesting_cost,
SUM(CASE WHEN la.activity_type = 'Grading' THEN la.hours_worked * w.monthly_salary / (26*8) ELSE 0 END) AS grading_cost
FROM labor_allocation la
JOIN workers w ON la.worker_id = w.worker_id
GROUP BY la.harvest_id
) lab ON h.harvest_id = lab.harvest_id;This corrected view was then manually verified: raw rows for several individual harvest_ids were summed by hand and checked against the view's output. A final structural check confirmed the view returned exactly one row per harvest_id with zero exceptions.
SELECT harvest_id, COUNT(*) AS row_count
FROM harvest_summary
GROUP BY harvest_id
HAVING COUNT(*) > 1;
-- Returned zero rowsKey findings
Which fruit types generate the most revenue, and through which channel?
SELECT
fruit_name,
ROUND(SUM(local_revenue), 2) AS total_local_revenue,
ROUND(SUM(export_revenue), 2) AS total_export_revenue,
ROUND(SUM(local_revenue + export_revenue), 2) AS total_revenue
FROM harvest_summary
GROUP BY fruit_name
ORDER BY total_revenue DESC;Mango led with ~KES 3.31M total revenue, the only fruit earning more through export (KES 1.83M) than local (KES 1.48M). Avocado followed at ~KES 1.93M, split nearly evenly. Orange, despite export eligibility, earned far more locally (KES 1.13M) than through export (KES 328K).
Which fruit types are most profitable once labor is subtracted?
SELECT
fruit_name,
ROUND(SUM(local_revenue + export_revenue), 2) AS total_revenue,
ROUND(SUM(harvesting_cost + grading_cost), 2) AS total_labor_cost,
ROUND(SUM(local_revenue + export_revenue) - SUM(harvesting_cost + grading_cost), 2) AS profit,
ROUND(100.0 * (SUM(local_revenue + export_revenue) - SUM(harvesting_cost + grading_cost))
/ SUM(local_revenue + export_revenue), 1) AS margin_pct
FROM harvest_summary
GROUP BY fruit_name
ORDER BY profit DESC;Mango remained the profit leader in absolute terms (~KES 2.56M), ahead of Avocado (KES 1.47M) and Orange (KES 1.15M). But on margin percentage, Pawpaw (83.9%) and Banana (83.8%) outranked every export-eligible fruit, revealing that revenue leadership and margin leadership are not the same thing.
Does export profitability hold up per kilogram, or only in totals?
SELECT
fruit_name,
ROUND(SUM(local_revenue) / NULLIF(SUM(local_weight_sold), 0), 2) AS local_price_per_kg,
ROUND(SUM(export_revenue) / NULLIF(SUM(export_weight_sold), 0), 2) AS export_price_per_kg,
ROUND(SUM(grading_cost) / NULLIF(SUM(export_weight_sold), 0), 2) AS grading_cost_per_kg,
ROUND((SUM(export_revenue) - SUM(grading_cost)) / NULLIF(SUM(export_weight_sold), 0), 2) AS export_net_price_per_kg
FROM harvest_summary
WHERE is_export_eligible = 1
GROUP BY fruit_name
ORDER BY export_net_price_per_kg DESC;Isolating grading cost specifically, Mango's net export return (KES 70.03/kg) nearly doubled its local return (KES 36.93/kg); Avocado showed the same pattern (KES 60.24 vs KES 35.02). Orange's net export return (KES 31.78/kg) barely cleared its local price (KES 27.37/kg), a gap of just KES 4.4/kg after grading cost.
How does profitability trend year over year?
SELECT
fruit_name,
harvest_year,
SUM(local_revenue + export_revenue) AS total_revenue,
SUM(harvesting_cost + grading_cost) AS total_cost,
SUM(local_revenue + export_revenue) - SUM(harvesting_cost + grading_cost) AS profit,
ROUND(100.0 * (SUM(local_revenue + export_revenue) - SUM(harvesting_cost + grading_cost))
/ SUM(local_revenue + export_revenue), 1) AS margin_pct
FROM harvest_summary
GROUP BY fruit_name, harvest_year
ORDER BY fruit_name, harvest_year;No fruit showed a clear multi-year decline or improvement. Pawpaw (82.6–84.5%) and Banana (82.7–85.2%) were the most stable performers. Mango and Orange showed real volatility — Mango dipping from 78.6% (2023) to 74.3% (2024) before recovering; Orange falling from 83.0% (2021) to 75.3% (2023) before partially recovering.
How much harvested volume goes unsold per fruit?
SELECT
fruit_name,
SUM(quantity_kg) AS total_harvested,
SUM(local_weight_sold + export_weight_sold) AS total_sold,
SUM(quantity_kg) - SUM(local_weight_sold + export_weight_sold) AS total_unsold,
ROUND(100.0 * (SUM(quantity_kg) - SUM(local_weight_sold + export_weight_sold))
/ SUM(quantity_kg), 1) AS unsold_pct
FROM harvest_summary
GROUP BY fruit_name
ORDER BY unsold_pct DESC;Unsold rate sat in a tight band across all fruits, 9.6% to 10.1%, with no fruit-specific outlier. This confirmed Pawpaw and Banana's strong margins were not being propped up by hidden waste, and that Mango's larger absolute unsold quantity was simply proportional to its larger harvest size.
Cross-checks and challenges
Cross-checking the five findings against each other surfaced the analysis's most important nuance: Pawpaw and Banana's margin-percentage lead is mechanically explained by having zero grading cost, not by superior earning power.
-- Cross-check: revenue per kg harvested, and cost structure per kg, by fruit
SELECT
fruit_name,
ROUND(SUM(harvesting_cost) / NULLIF(SUM(quantity_kg), 0), 3) AS harvest_cost_per_kg,
ROUND(SUM(grading_cost) / NULLIF(SUM(quantity_kg), 0), 3) AS grading_cost_per_kg,
ROUND(SUM(local_revenue + export_revenue) / NULLIF(SUM(quantity_kg), 0), 2) AS revenue_per_kg_harvested
FROM harvest_summary
GROUP BY fruit_name
ORDER BY revenue_per_kg_harvested DESC;Comparing revenue generated per kilogram harvested instead, Mango earned KES 47.56/kg against Pawpaw's KES 17.37/kg, nearly triple. Margin percentage rewards a lean cost structure; revenue-per-kg rewards actual value generated per unit of farm capacity, and for an expansion decision, the latter is the more relevant metric.
Insight summary
Revenue, profit, per-kg economics, year-over-year stability, and waste were each examined independently, and where they agreed (Mango and Avocado's export strength held up on every metric) and where they diverged (Pawpaw and Banana's margin lead did not survive a revenue-per-kg lens) both mattered to the final recommendation.
Lessons learned
A single metric, whether revenue, profit, or margin percentage, can each independently mislead if trusted in isolation. The join-aggregation error was a reminder that a query returning plausible numbers is not the same as a query returning correct numbers; manual verification against raw records is not optional when the output will drive a real recommendation.
Results
KPI summary
| Fruit | Total Revenue | Profit After Labor | Margin % | Export Net KES/kg | Revenue per KG Harvested |
|---|---|---|---|---|---|
| Mango | ~3.31M | ~2.56M | 77.2% | 70.03 | 47.56 |
| Avocado | ~1.93M | ~1.47M | 75.9% | 60.24 | — |
| Orange | ~1.46M | ~1.15M | 78.8% | 31.78 | — |
| Pawpaw | ~1.06M | — | 83.9% | n/a | 17.37 |
| Banana | ~0.79M | — | 83.8% | n/a | 14.68 |
Figures in KES. Dashes mark metrics not applicable to that fruit's channel mix.
Stage 5
Business recommendations
Executive summary
FruitCo should not treat this as a binary "export versus local" decision. The evidence supports a fruit-specific strategy: expand export investment in Mango and Avocado, hold Orange's export effort at its current level, and maintain, without prioritizing further capital toward, Pawpaw and Banana.
Key insights
Mango and Avocado's export channel is profitable on every metric tested — revenue, absolute profit, and per-kilogram economics after grading cost — and neither result is an artifact of volume or hidden waste. Orange's export premium over local sale exists but is thin and has been the most volatile margin performer in the portfolio. Pawpaw and Banana's high margin percentage reflects low cost structure, not high earning power, a distinction that only became visible by cross-checking margin against revenue per kilogram harvested.
Expand
Mango & Avocado
Prioritise additional harvest and grading labor here. Export is profitable on every metric tested — revenue, absolute profit, and per-kilogram economics after grading cost.
Hold
Orange
Keep current export effort rather than expanding it; the added grading cost is not being rewarded with a meaningful premium, and margin has been the most volatile in the portfolio.
Maintain
Pawpaw & Banana
Stay at current local-only production levels. They are the most stable, lowest-risk holdings — but their high margin reflects a lean cost structure, not high earning power.
Expected business impact
Redirecting incremental harvest and grading labor toward Mango and Avocado, rather than spreading it evenly or continuing to invest in Orange's export grading, should improve overall profit per unit of labor deployed, since these two fruits demonstrated the strongest and most consistent per-kilogram returns after cost.
Risks
Mango carries more year-to-year margin volatility than Avocado, and any expansion plan should account for this rather than assume a flat annual return. Labor cost figures throughout this analysis depend on an assumed 208-hours-per-month conversion; if FruitCo's actual working-hour norms differ, absolute KES figures would shift, though relative rankings between fruits would remain valid.
Next steps
Validate the assumed labor-hours conversion against FruitCo's actual payroll practices. Track the root cause of the consistent ~10% unsold volume across all fruits, since even a modest reduction would improve returns across the board without requiring any channel shift.
Future analysis opportunities
Investigate why Orange's export channel showed the highest margin volatility of any fruit, whether tied to specific years, buyers, or grading batches. Extend the unsold-volume analysis with waste cause codes (spoilage vs. rejection vs. unharvested) to separate genuinely recoverable loss from unavoidable loss.
Have a dataset with a decision inside it?
This is the level of rigour every engagement gets — assumptions labelled, numbers verified, recommendation defensible.