JCARS LOGISTICS DATA ANALYSIS: FROM RAW DATA TO INTERACTIVE POWERBI BUSINESS INSIGHTS.

INTRODUCTION

JCars Logistics imports, sells, and delivers vehicles to customers across different regions in Kenya. The company collects information about its sales transactions, vehicles, customers,
branches, sales representatives, payments, deliveries, logistics costs, returns, cancellations, and customer experiences. Management would like to use this information to better understand how the business is performing and identify areas that require attention.

TOOLS USED FOR THIS DATASET

• PowerQuery for cleaning the dataset.
• PowerBI for Modelling, Relationships, Visualization, and Dashboard.

DATA OVERVIEW

Step one: Understand the data before you build

Grain of the data what each row and _column _represents.
I then sorted the columns into groups: transaction, customer, geography, people and channel, vehicle, money, status and experience.
The dataset had 277 rows and 32 columns.
Columns were named column 1 to column 32 each representing different data as shown below:
OrderID, Order date, Delivery date, customer name, age, type, Region, county, city, branch, sales rep, lead, car details, units sold, selling price, Discount, revenue recorded, delivery fee, logistic fee, customer rating, review count and returned
The loaded data shown below

DATA QUALITY

The most important step in data analysis.
This helps in discovering data quality issues.
After loading the data in powerBI here are some of the issues found:

• The Order ID was not an ID. There were five prefix styles (LC1000, LCL-1001, ORD1004, ord1020, CAR1086), 19 blanks or N/A, and three numbers used twice.
• Dates were in six formats. Some were numbers (46066), some 26-Mar-25, some Aug 29, 2025, and four were 2026-13-04, which is year-day-month. Worst were the 47 slash dates like 03/04/2026, which are valid as both 3 April and 4 March.
• Categories exploded. Toyota appeared as Toyota, TOYOTA, Toyta, Totoya, Toyota Kenya. In total, Car Make had 62 spellings for 10 real car makes. Sales reps had 80 "names" for 10 people, including Dan1el Kimani, where a digit 1 had replaced the letter i.
• Discounts came in over 50 different spellings. 7%, 0.07, 7, 7 percent, ten percent, No Discount, #ERROR.
• Numbers were formatted as text as shown below.
• Five columns held money: price, unit cost, delivery fee, logistics cost, and recorded revenue. They mixed KSh, KES, plain numbers, $, USD, EUR, ZAR, R, a ?, and an M suffix (9.14M).
• Customer names, Region, Branch, City were not in proper case and inconsistent spellings like COUNTY GOVERMENT, Msa, Nkr, Faith ACHIENG.
• Numerical values that held monetary values had negative values like -22,738,440 and customer rating of -1.
• Customer Age as text.

DATA CLEANING

After identifying the data quality issues, I hit transform data.
First step I categorized according to:

• Customer details
• Location details
• Currency details
• Payment details
• Customer rating

First and foremost I made first rows as headers (promoted headers) as shown below

Customer, sales rep details cleaning

Here I cleaned the customer name, customer type and age
Customer names were in improper case as shown below
Select the customer name column -> Rightclick-> Transform-> Capitalize each word ->OK
The Age I changed from text format to wholenumber type by
Selecting customer age -> Rightclick -> Change type -> Wholenumber -> OK
Sales rep had Improper case too, I transformed to proper case and used Replace values with the inconsistent words like Dan1el to Daniel

Location data quality issues

I cleaned includes city names like Nkr changed to Nakuru, Msa to Mombasa, Eldo to Eldoret by replacing values Selecting column -> Right click -> Replace values -> Write the value to be replaced and value to replace with (Msa -> Mombasa )->OK
Then changed to proper case by Transform -> Capitalize each word ->OK

Currency

Currency was to be standardized to KES or Ksh.
Currency was in USD or $, EURO, ? assumed as EURO and ZAR/R.
I used 1USD = 129.35, 1EURO = 147.15 and 1ZAR = 7.8.
I changed the values to fixed decimal numbers in the rows affected holding monetary values like Revenue, Delivery fee, Logistics fee, Unit selling price and others.
The negative values I assumed they were whole and changed them to a positive number.

Date column

This contains delivery date and order date.
Date was in serial numbers like 4648, mixed date formats as text.
I changed the format to date format and Removed errors.

Payment methods and Customer rating

The issues with payment methods was improper case, names were mispelled.
I used Replace values with correct spelled words.
Customer rating was in words , I used Replace values with the correct values like 4.5 out of 5 with 4.5.

DATA MODELLING

After cleaning I had one Flat table.
To start modelling process, I had to categorize the Flat table into separate Dimension table and one Fact table.
I categorized according to each components of the table namely:
I used duplication of the flat table into 8 tables and removed columns one by one until I got the required details for each.

Dim_customer

This will hold all customer details like:

• Customer name
• Customer age
• Customer ID

Sales representative

This will hold the sales rep details:

• Sales rep name
• Lead source
• Sales_rep ID

Order status

This represents order and delivery status:

• Delivery status
• Order status
• Status ID

Dim_location

Contains the location details like:

• Branch
• Region
• County
• City
• Location ID

Payment method

Represents payment methods used:

• Payment method
• Payment ID

Dim_date

Represents the dates for time series analysis.

• Delivery date
• Order date

Dim_car

Represents the car details:

• Car make
• Vehicle
• Fuel type
• Transmission
• Color
• Car model
• Vehicle year
• Car ID

Dim_Fact_table

This contains all numeric values and all foreign keys.

• Customer rating
• Revenue recorded
• Delivery fee
• Logistic fee
• Units sold
• Unit selling price
• Primary key (OrderID)

After categorizing the tables into dimtables and one fact table, I checked for duplication by Select the column -> Remove duplicates -> OK
Then I added an index column by Selecting the table -> add index column -> Choose from 1 -> OK and rename accodring to the table created.
I closed and applied for loading in PowerBI for creating relationships.

RELATIONSHIPS

Data is loaded to PowerBI.
To establish relationships, I used the Primary keys in the Dimension tables to Foreign keys in the jcars_fact_table.
The cardinality used is one to many because is the required standard in star schema approach, with a Filter direction of single , and made the relationship active.
The relationships was represented by a star schema, with one fact table at the centre with an active relation with all the dimension tables as shown below.

DAX MEASURES

I created some important DAX measures to represent the Business performance.
Select column jcars_fact_table -> Create a new measure -> Write the formule example Total revenue =SUM(Revenue recorded) -> Enter
To display the measure go to Report view -> Select a card visual -> Look for the created measure in the jcars_fact_table -> click on it -> The measure will be displayed on the card

DAX MEASURES CREATED

These are some of the DAX measures I created.

Revenue = sum(jcars_fact_table[Revenue Recorded])
Total cost of units sold = SUMX(jcars_fact_table,jcars_fact_table[Unit Cost] * jcars_fact_table[Units Sold])
Total cars sold = sum(jcars_fact_table[Units Sold])
Gross Margin % = DIVIDE([Gross Profit],[Revenue], 0)
Gross Profit = [Revenue] - [Total cost of units sold]
Average Customer rating = AVERAGE(jcars_fact_table[Customer Rating])
Total Transcations = COUNTA(jcars_fact_table[Order ID])
Unpaid Revenue = CALCULATE([Revenue],dim_order_status[Payment Status] IN {"Pending", "Partially Paid", "Unpaid"})
Return Rate % = DIVIDE(CALCULATE([All Orders], dim_order_status[Returned] = "Y"), CALCULATE([All Orders], dim_order_status[Returned] = "Unknown"))

DASHBOARD

I created my dashboards with DAX measures as my Key Performance Indicators namely:

• Revenue
• Gross Profit
• Gross margin
• Return Rate
• Unpaid Rate
• Units sold
• Total transcations made I also included Slicers for Customer type and Regions for Interactivity, with Visuals as shown below.

BUSINESS INSIGHTS

What the data said

Headline: KSh 408.6M revenue, 128 vehicles, KSh 7.8M gross profit, 0.02 gross margin.
More Findings:

1. More Discounts does not mean more units sold.

Discount    Units sold  Gross margin
0–3%          1            16.0 % 
over 3-5%   5            49.0 % 
over 5-7%   7            12.0%  
over 7-10%      1                14.0%
over 10-15% 1            2.0%   

1. Revenue is up margin is down
   Quarterly gross margin slid from 15.0% (Q1 2025) to -3.0% (Q2 2026) while Q4 2026 revenue hit a period high of KSh 49.2M, with a gross margin of 53.0%.

2. Athi River Branch had the highest Revenue of 53.9M and a gross margin of 49.0%, with least is Kakamega Branch with a Revenue of 1.9M and a gross margin of -1.0%.

3. Volume is not value in lead sources. Whatsapp is the biggest revenue source (KSh 65.8M) but earns 36.0% margin, with 9 units sold and Walk-in had 16.2M revenue with a negative margin of -17%, selling 11 units.

4. 24% of active revenue (≈ KSh 96.33M) is Pending , Partially Paid or Unpaid. The exposure is spread across every customer type (NGO and Corporate each ≈ KSh 64.81M), so it is a process issue, not one bad customer.

5. Toyota and SUVs dominate, and customer types differ sharply in margin. Toyota is 8% of revenue and SUVs 15%%, which is strong but a concentration risk. Dealers earn the best margin (14.0%) while NGOs, at 16% of revenue. Cash and Wire deals earn 30.0% against 24% for RTGS and Cash.

6. Customer ratings are not linked to revenue. Correlation of rating with order revenue is −0.08, and the average rating is 3.6 out of 5 for every customer type (3.56–3.62). customer satisfaction is not a function of deal size or customer type.

CHALLENGES AND LESSONS LEARNED

• PowerQuery issues with performance and ran slower.
• Discovering that the delivery fee sits inside recorded revenue changed the profit definition. Testing a formula against a recorded column paid off more than any single cleaning step.
• Recorded values are not automatically right. The recorded revenue column was less reliable than a recalculation from its own components.
• Lesson: understand the business meaning of each field before modelling. Most of the value came from questions asked before touching a visual.

ASSUMPTIONS

The currency column the figures which were in EURO and ? were all marked as EURO.
Recorded Revunue has Delivery calculated in it.

CONCLUSION

Recommendations

1. Review Thika's pricing and discounting before scaling it.
2. Shift lead-generation effort toward Instagram and Referral, and review how Phone Call leads are handled.
3. Set collection targets and ageing reports, and start capturing payment dates.
4. Define "Returned" and record return date, reason and refund value.
5. Fix data entry at the source with dropdowns, currency and date validation, and real Order IDs. Cleaning once does not stop the mess coming back.

What I learned

• Test formulas against recorded values. Finding that the delivery fee sits inside revenue came from a validation check, not from a cleaning step, and it changed the whole profit definition.
• Certainty has limits. Ambiguous dates cannot be resolved perfectly. A documented rule plus a flag beats a silent guess.
• Excluding data has a cost. Showing the 68% coverage next to the KPIs is more honest than a clean-looking number.
• Questions come before visuals. Most of the value came before I dragged a single chart onto the canvas.

Code, data, model and the full data-quality log are in the:Github