Project Overview & Objectives
This capstone project was completed as the final assessment for the Data Analytics programme at New Horizons Learning Institute. The brief required students to select a real-world dataset from Kaggle and analyse it end-to-end using three industry-standard tools: Microsoft Excel, Power BI, and Tableau.
The dataset selected covers 800 phone sales transactions across four Nigerian states — Federal Capital Territory, Lagos, Oyo, and Rivers — capturing brand, customer demographics, pricing, cost, discounts, and ratings over a full calendar year.
The analysis was structured around seven business questions: sales performance trends, brand and product performance, customer behaviour, geographical distribution, profitability and cost dynamics, customer satisfaction, and monthly target achievement.
Data Cleaning & Preparation
The raw dataset contained 800 rows and 12 columns. Despite having no missing values, several data quality issues were found that would have compromised the integrity of any analysis left uncorrected.
Issues Found in the Raw Data
A close inspection of the brand column revealed 6 misspelled entries across 4 brands — "Opo" (2×), "Aple" (2×), "Tecn" (1×), "Samsng" (1×), "Gogle" (1×). These were inconsistencies that would cause any GROUP BY or pivot operation to produce incorrect brand totals.
The state column used abbreviations throughout ("OYO", "LAG", "FCT", "RIV") — valid for a raw data store but unhelpful for dashboard labels. All four were expanded to full state names.
Column headers used inconsistent casing and spacing (e.g. unit cost with a space, customer_rating with an underscore, orderdate as one word).
| Column / Issue | Type | Raw Value | Cleaned Value |
|---|---|---|---|
| brand — misspellings | FIXED | Opo, Aple, Tecn, Samsng, Gogle | Oppo, Apple, Tecno, Samsung, Google |
| state — abbreviations | FIXED | OYO, LAG, FCT, RIV | Oyo, Lagos, Federal Capital Territory, Rivers |
| unitprice → Unit Sales Price | RENAMED | unitprice | Unit Sales Price |
| unit cost → Unit Cost | RENAMED | unit cost | Unit Cost |
| customerage → Customer Age | RENAMED | customerage | Customer Age |
| customer_rating → Customer Rating | RENAMED | customer_rating | Customer Rating |
| Cost Price | NEW COLUMN | — | =Unit Cost × Units Sold |
| Sales Price / Revenue | NEW COLUMN | — | =(Unit Price × Units Sold) − Discount |
| Net Result | NEW COLUMN | — | =Revenue − Cost Price |
| Profit / Loss | NEW COLUMN | — | Positive / Negative label based on Net Result |
| Met Target? | NEW COLUMN | — | =IF(Net Result ≥ Target, "Yes", "No") |
| VLOOKUP population (Analysis sheet) | NEW | — | Used to pull values from raw into Analysis sheet by Order ID |
Key Excel Formulas Used
Cost Price — Total cost per order
Revenue — After discount applied
Net Result — Profit per order
Met Target? — Conditional flag
Monthly Target Gap — Difference formula
VLOOKUP — Populating the Analysis sheet
Power Query Transformations (Power BI)
Key Findings
Monthly Target Achievement — Zero months met
The most significant finding of this analysis is that no monthly target was met across the entire year. The closest month was January (−9.9% gap) and the furthest was December (−13.2% gap). The total annual shortfall was ₦291.4M against a target of ₦2.70B.
| Month | Target (₦M) | Actual (₦M) | Gap |
|---|---|---|---|
| May | 298.0 | 270.6 | −27.4M |
| Jun | 270.6 | 244.2 | −26.4M |
| Sep | 259.4 | 233.4 | −26.0M |
| Jul | 250.7 | 224.8 | −25.9M |
| Dec | 231.4 | 201.0 | −30.5M |
| Jan | 165.8 | 149.4 | −16.4M |
Tecno — Revenue Leader
Tecno led all brands in total revenue and units sold across all four states, consistently outperforming Samsung and Oppo which placed second and third.
Returning Customers Spend More
Returning customers ordered more units per transaction and generated higher average revenue compared to new customers — a clear case for loyalty investment.
Lagos & Oyo Lead Sales
Lagos and Oyo consistently generated the highest sales volumes. FCT followed closely behind. Rivers had the lowest net revenue but — surprisingly — the highest average customer rating.
Discounts Squeeze Margins
Higher discount levels directly correlated with reduced profitability. With discounts ranging from 0% to 30%, pricing discipline is a key lever for margin improvement.
Recommendations
- Bolster customer retention with a loyalty programme. Returning customers already outspend new ones — a structured loyalty reward system would amplify this effect and improve lifetime value.
- Expand market penetration in Rivers State. Rivers has the lowest revenue but the highest customer satisfaction scores. High satisfaction with low volume is an untapped growth opportunity.
- Implement controlled discounting policies. The current 0–30% discount range is too broad. Capping maximum discounts and linking them to inventory age or volume thresholds would protect margins.
- Prioritise Tecno and Samsung inventory. These two brands dominate sales volume across all states. Ensuring consistent stock availability — especially during peak months — would reduce lost sales.
- Investigate December performance specifically. December had the largest target gap (−₦30.5M) despite being a festive month. This warrants a targeted promotional strategy — increased discounts or bundle offers — in future cycles.
- Revise monthly targets using historical actuals. No month met its target across the full year, suggesting the targets may be miscalibrated. Targets built on rolling averages would be more actionable.
Conclusion
This project demonstrates a complete data analytics workflow from raw data to business intelligence. Beginning with messy Kaggle data — containing brand misspellings, abbreviated state codes, and unformatted columns — the dataset was cleaned and enriched in Excel with five new calculated columns before being modelled in Power BI and visualised in Tableau.
The core finding is that while the business generated ₦2.41B in net result over the year, it fell short of every single monthly target — a structural issue that goes beyond seasonal variation and points to a need for more grounded target-setting, tighter discount management, and focused regional strategy in underperforming states.
The three-tool pipeline — Excel for data preparation, Power BI for interactive business dashboards, and Tableau for visual storytelling — reflects industry-standard practice for a data analyst role.