Introduction
JCars Logistics is a fictional business that imports, sells, and delivers vehicles to customers across different regions in Kenya. We have a dataset that contains information about its sales transactions, vehicles, customers, branches, sales representatives, payments, deliveries, logistics costs, returns, cancellations, and customer experiences.
It operates a diverse network with 276 orders across 10 sales representatives, 14 branches, and 272 car makes serving 276 unique customers.
Using this dataset, I have cleaned, analyzed, modeled and finally prepared an excecutive dashboard on the performance and status of the business in terms of different metrics, all of this in PowerBI.
Data Cleaning
While the business demonstrates operational scale, critical data quality issues and operational challenges require immediate attention to ensure sustainable growth and accurate performance measurement. The presented data was so messy with numerous inconsistencies and blanks through out most of the columns.
Inconsistent formatting
This format gave me the most headache, starting us strong is formatting, most columns had variables written in different formats. The order ID column having mixed numbers and varied alphabets eg LC1000, LCL1001, ORD1004. All of which have to be standardized in order to proceed with analysis. The different Date columns proved the most inconsistent with some dates being numerical figures for instance. The currency for revenue, unit cost and delivery fees were in different currencies and numeric formats.
other text columns contain numeric data such as 3 cars. Finally, different naming conventions. Example: "Toyota," "toyota," "TOYOTA," "Toyta" treated as different car makes
Example: "Rift Valley," "RIFT VALLEY," "Rift-Valley," "rift valley," "Riftvalley" counted as separate regions
Cleaning this data, involved manual formulas due to the many discrepancies, mostly including Replacing Values and Errors in Power Query.
Data Modelling
After cleaning my data, I created relationships on power query connecting my fact table to the ** 7 dimension tables** namely:
- Dim Customers
- Dim Sales Rep
- Dim Car Qualities
- Dim Location
- Dim Lead Source
- Dim Dates
- Dim Payments
On Power Query, I created the Dimension Tables by referencing my clean data instead of Duplicating. Thereafter I selected only the columns I needed in the particular tables and removed other columns. To only retain unique values in my dimension tables I removed duplicates and created new Indexes.
The new Indexes acted as my Primary Key In my created Dim Tables for all the tables I created since none was provided in the flat table that was provided.
In order, to create relationships between the Fact table and the Dimension tables. I merged the Fact table with every dimension table with the corresponding common Column, thereafter doing away with the other columns and retaining the foreign key in the fact table.
I maintained a Left Outer join since I used the Fact table as my left table and the dimension tables as the table on the right.
Six of the relationships created were many to one, with only one, Customers Table having One to One cardinality, since the number of customers matched the number of transactions. Mapping the specific columns from both tables together.
Luckily having created the relationships the right way after so many attempts, Power BI picked up on the relationship, so I did not have to do it manually.
Dashboard
Recommendations and Business Insights
- To uphold Data Governance measures, standardization of all data entry mechanisms is necessary by:
- Replace free-text fields with dropdown selections where possible
- Implement validation rules that reject non-standard entries and variables
- Audit historical data: Run de-duplication scripts on region, car make, and branch names Reconcile currency entries (establish single currency or documented conversion rates)
- Flag and correct negative values and #VALUE! errors
- System implementation:
- Deploy data quality rules in point-of-entry systems
- Establish monthly data audits
Key Findings
Phone-based leads outperform by generating higher ratings
Payment status showed that a whooping 55%of the cars being fully paid.
Prado TX had the most revenue recorded of 135B.
Toyota as a brand, dominates with 20% of all orders, as compared to other car brands.
As for the Sales Representatives, Faith Achieng records the most units sold as Grace Njeri records the most profit of 35M.
Diesel and Hybrid supremacy: Both fuel types generate 4.0+ average satisfaction, significantly exceeding petrol (3.1-3.85)
Petrol dominance but mediocrity: ~47% of order volume but lowest satisfaction metric commodity market with undifferentiated product
EV emerging market: Only 3 orders but environmental trends and government incentives will drive growth
Referral returns: High-expectation customers referred by existing buyers who learn vehicle has issues
Instagram returns: Young demographic with higher return rates, possibly due to online purchase impulse or photo-reality mismatch
Corporate Tender: Large-volume orders with complex negotiations may lead to specification mismatches
CONCLUSION
JCars operates a substantial business with 276 orders, 276 customers, and 10 sales representatives across 14 branches. However, the organization faces critical challenges across data quality, payment collection, operational delivery, and sales performance consistency.
The immediate priority is implementing standardized data governance without it, all performance metrics remain suspect. Simultaneously, urgent intervention is required for Nairobi HQ's 37.5% return rate, Mombasa Port Yard's operational failures, and the concerning 18.5% payment cancellation rate.
The organization has strong underlying assets (high-volume branch network, competent sales leaders like Grace Njeri, quality products like Nissan. Focused execution on these recommendations will lead to exponential growth.
