A Case for JCars Logistics Business

This story outlines the process undertaken to develop a comprehensive Business Intelligence (BI) solution for JCars Logistics, a vehicle sales and logistics enterprise operating in Kenya. The primary goal was to convert a messy, unstructured dataset into an interactive, business-ready Power BI platform capable of providing crucial insights into sales performance, profitability, operational efficiency, and delivery logistics.

The dataset was a complex mix of information on vehicle sales, customers, sales representatives, branches, payments, deliveries, logistics costs, returns, ratings, and revenue. To prepare the data for analysis, the following cleaning steps were implemented:

1. Text columns were systematically cleaned, including trimming extra whitespace, capitalizing the first letter of each word, and converting placeholder strings to standardized null values.

2. Typographical errors and abbreviation inconsistencies in branch names, sales representatives, lead sources, and vehicle attributes were corrected through targeted find-and-replace rules.

3. Categorical features were standardized for uniform grouping, such as normalizing "person", "retail", and "individual" to "Individual".

4. Missing location data was imputed using existing domain knowledge, including populating missing counties based on branch locations and inferring missing regions directly from county names.

5. Numbers were removed from individual names (e.g., A1sha -> Aisha).

The underlying data was then modeled as a Star Schema, anchored by a Fact_Sales table linked to various dimensions through integer surrogate keys. This structure facilitated time-series reporting across significant milestones like order and delivery dates. Key tables included fact_sales, dim_location, dim_sales, and dim_customer.

To visualize the insights generated, a dashboard was created with key performance indicators (KPIs) such as Gross Profit, Gross Profit Margin, Total Revenue, and Total Units Sold. These metrics were built using DAX functions and made interactive through slicers based on region and car models.

The overall project demonstrated an end-to-end Power BI workflow, from data profiling to ETL processes, dimensional modeling, DAX measure creation, UI design, testing, and stakeholder communication. It also established a clear data lineage by linking all metrics to documented transformation rules and validation checks. Ultimately, the BI solution delivered actionable insights to JCars, empowering the company to optimize inventory planning, enhance branch performance, and maximize profitability.