DEV Community: Expert Writer

JCars Logistics Power BI Business Intelligence Assessment

Expert Writer — Wed, 30 Sep 2026 13:26:24 +0000

Introduction

A vehicle sales report can look convincing and still mislead. A revenue total is only useful when its currency is clear, the underlying price fields agree, returns are handled consistently, and the report grain is understood. For JCars Logistics, those questions matter because a single flat file brings together vehicle sales, customers, branches, representatives, payments, delivery activity, costs, and customer feedback.
The goal of this article is to explain the analytical decisions behind that structure, on how to think about data quality, how to model the subject, how to define reusable calculations, and how to make findings responsibly. The dataset is small enough to inspect row by row, but its mixed formats and conflicting values make it a useful example of why preparation and validation are part of analysis, not housekeeping.
This project turns that operational dataset into a Power BI analysis intended to help management explore sales, profitability, operational performance, and areas needing investigation. The supplied report contains dedicated pages for calculations, the model, branches, sales representatives, payments, customers, and revenue. Its measure set includes total transactions, sales revenue, cost, gross profit, gross profit margin, revenue per car, units sold, discounted units, logistics cost ratio, logistics cost per car, and orders requiring investigation.

Data Cleaning

I was working with a deliberately dirty flat file of 276 vehicle sales records from a Kenyan importer, JCars Logistics, and asked to turn it into a Power BI solution that helps management make decisions. This article walks through how I did it: understanding the grain, auditing data quality, cleaning with Power Query, modelling, writing DAX, building the report, and pulling out insights.

The most important thing I learned is that the headline numbers lied. As recorded, the business shows a 20.8% gross margin. Once I stopped trusting two suspicious transactions, the margin fell to 8.0%. I also found bugs in my own model while reconciling my numbers, and I document them here because they are the most useful part of the analysis.

The Business Problem

JCars Logistics imports, sells, and delivers vehicles across Kenya. Management needs to understand not only how much business was recorded, but also where value is generated and what operational conditions may be associated with it. Questions include:

How many transactions and vehicles are represented, and what revenue and gross profit do they produce?
Which makes, models, branches, regions, and sales representatives contribute to performance?
How do payment method and payment status relate to recorded sales?
What does delivery status indicate about fulfilment, and where do logistics costs deserve attention?
How do customer type, returns, and customer ratings add context to the sales picture?
Which unusual records should be checked before a manager acts on a result?

Understanding the Dataset

The data set had 276 rows and 32 columns in a single flat CSV.

Grain: One row is one sales order line, meaning one customer buying a specific vehicle (make, model, year, colour, transmission, fuel) in some quantity, at one branch, handled by one sales rep, with its own payment, delivery and rating information.

Column groups:

Group Columns
Transaction Order ID, Order Date, Delivery Date
Customer Customer Name, Customer Type, Customer Age
Geography Region, County, City, Branch
People / channel Sales Rep, Lead Source
Vehicle Car Make, Car Model, Vehicle Type, Vehicle Year, Fuel Type, Transmission, Color
Measures Units Sold, Unit Selling Price, Unit Cost, Discount, Delivery Fee, Logistics Cost, Revenue Recorded
Status Payment Method, Payment Status, Delivery Status, Returned
Experience Customer Rating, Review Count

Data Quality Assessment

In the first few rows alone, dates use multiple representations, a delivery date appears as an Excel serial number; amounts include currency symbols, text suffixes, and plain numbers; categories vary in casing; and vehicle and employee names have spelling variants. The file also contains missing values in key fields, including Order ID, dates, customer details, unit cost, units sold, discount, logistics cost, customer rating, and return status.
These are the challenges that I faced while was working on the data:
Missing transaction identifiers. Missing or repeated Order IDs weaken distinct-order counts and make reconciliation difficult. Preserve the record for analysis where possible, but do not silently treat it as a confidently identified order.
Mixed date formats. Text dates in different patterns and spreadsheet serial values can parse differently depending on locale. Convert using explicit rules, then compare order and delivery chronology and flag unresolved values.
Inconsistent monetary formats. Values such as KSh 8,136,000, KES 56,000, plain numbers, and abbreviated text such as 9.14M required parsing into numeric amounts and currency codes before calculations.
Currency ambiguity. Kenya Shillings are the required reporting currency. I treated the values without any currency as KES.
Conflicting revenue fields. Unit selling price, units sold, discount, and Revenue Recorded permit an independent revenue check.
Negative or implausible monetary values. A negative recorded revenue may represent a return, a correction, or a mistake.
Category spelling and casing. Examples in the source include Toyta versus Toyota, petrol versus Petrol, and multiple spellings/casings for branches and locations.
Nonstandard categorical values. Payment statuses include variants such as Paid and complete; delivery statuses include AT YARD, held, and Delayed.
Inconsistent numeric types. Unit costs, prices, discounts, and ratings appear as text in some rows. Parse percentages and numeric amounts separately, and capture parse failures instead of converting them to zero.
Missing unit cost and units sold. These fields underpin gross profit and unit metrics. A missing cost should not silently become zero, because that would overstate profit; a missing quantity should not be assumed to equal one without a documented rule.
Unverified duplicate orders. Exact duplicate rows and repeated identifiers can inflate revenue and volume. I investigated using transaction keys and row attributes.

Business Definitoions Calculations

Total Sales Revenue = SUMX('Jcars_Fact Table', 'Jcars_Fact Table'[Units Sold] * 'Jcars_Fact Table'[Unit Selling Price] * (1-COALESCE('Jcars_Fact Table'[Discount], 0)) + SUM('Jcars_Fact Table'[Delivery Fee]))

Total Cost = SUMX('Jcars_Fact Table', 'Jcars_Fact Table'[Units Sold]* 'Jcars_Fact Table'[Unit Cost])

Total Logistics = SUM('Jcars_Fact Table'[Logistics Cost])

Total Transactions = DISTINCTCOUNT('Jcars_Fact Table'[Order ID])

Total Units Sold = SUM('Jcars_Fact Table'[Units Sold])

Rated Transactions =
CALCULATE( DISTINCTCOUNT('Jcars_Fact Table'[Order ID]), NOT ISBLANK('Jcars_Fact Table'[Customer Rating]))

Discounted Revenue = CALCULATE([Total Sales Revenue], 'Jcars_Fact Table'[Discount]>0)

Data Modelling

The flat table repeats every descriptive attribute on every row, mixes measures with attributes, and cannot support reliable time intelligence. A star schema gives one narrow fact table and small, clean dimensions, so filters flow predictably.

I modelled my data into the following dimension tables:
Dim_Customer One customer profile
Dim_Location One branch (region, county, city, branch)
Dim_SalesRep One salesrep
Dim_Payment One method and status combination
Dim_Vehicle One make, model, type, year, fuel, transmission, colour combination

Fact_Tableholds the surrogate key, the dimension keys, the measures (units, KES price, KES cost, discount, KES delivery fee, KES logistics cost), delivery status, returned flag, rating, review count, order and delivery dates, and the data quality flag. It is deliberately free of descriptive text.

Analysis and Answers

Which parts of the business have high logistics costs relative to value? Overall logistics is 1.6% of revenue. The pressure lies in cheaper vehicles and specific places:

Models with at least 8 orders: Corolla 4.5% of revenue (KES 83,600 per car), Demio 4.5%, Fielder 3.5%, Fit 3.0%, Outlander 3.0%, Canter 2.8%.
Vehicle types: hatchbacks and wagons 3.1%, crossovers 2.7%. Per car, trucks (KES 81,600), wagons (KES 73,600) and pickups (KES 70,400) cost the most to move.
Locations per car: Nairobi HQ KES 78,300, Mombasa KES 74,900, Athi River KES 72,300, against Nakuru KES 46,900 and Rift Valley KES 51,800.
36 individual orders spend more than 5% of their value on logistics. They are 4.6% of revenue but 21% of logistics cost. The worst are single low-value cars such as a Fit (14.6%), a Demio (12.3%) and an Axela (12.0%).

Which customer types contribute most?
Dealers are the most valuable segment: largest, second-best margin, cheapest to serve. NGOs generate the most orders (58) but earn the lowest margin. Individuals cost the most to deliver per car

*What is the relationship between discounts and performance?
*
The correlation between discount and margin is -0.85.
Discounts show almost no link to volume (-0.11 with cars per order, -0.12 with revenue).
Since the average price-to-cost ratio is 1.20, the break-even discount is about 16%. 21 orders sell below cost, together losing KES 8.9M, and all carry a discount of at least 5% (average 21%).
Orders discounted more than 10% are 23% of all orders and KES 297M of revenue, but earn only KES 2.0M of profit: a margin of 0.7%, against 14.0% for the rest.

Which orders or customers should management investigate?
(blank ID) Harrier Thika (10.7M) Delivery cancelled but partly paid, marked returned, 68-day gap, 15% discount
LCL1095 Impreza Nairobi HQ (1.6M) Partly paid, delivery cancelled, marked returned, 15% discount
LCL1195 CX-5 Kakamega (24.8M) 20% discount sold below cost, partly paid, delivery cancelled
LCL1201 Land Cruiser Thika (12.9M) Paid, but delivery cancelled, date missing
LCL1137 Hilux Nakuru (2.5M) 50% discount, returned
LCL1028 Impreza Kisumu (1.0M) 50% discount, returned

Findings and recommendations: what the data can support

The report design makes it possible to investigate where performance varies, but a defensible published article should only state results after checking the current model output under the intended filters. The files inspected for this write-up establish the source structure, report pages, measure names, and visible data-quality risks. They do not establish a validated headline revenue, profit, top branch, best-selling make, or delivery rate. Those figures should be read from the refreshed Power BI model after currency and revenue reconciliation, not inferred from a few sample records.
That distinction is important. A high-revenue branch may also have higher costs or a larger transaction base, a high logistics-cost ratio may be driven by vehicle mix or geography, and a low rating may have very few reviews. The report should show denominators and context, and the article should state when a pattern is descriptive rather than causal.

Based on the issues surfaced, management and the analyst should prioritize these evidence-led next steps:

Reconcile recorded revenue before using it for targets or incentives. Compare recorded revenue with quantity, unit selling price, discount, delivery fees, and returns. Investigate material mismatches and keep an exception list until the accounting rule is confirmed.
Treat delivery and logistics differences as investigation signals. Compare logistics cost per unit and delivery status by branch, region, and vehicle type. Review the contributing orders before changing routes, delivery commitments, or branch processes.
Improve transaction capture for decision-critical fields. Require valid order IDs, dates, quantities, currency, unit cost, and return/payment statuses at entry. These fields directly affect transaction counts, margins, time analysis, and exception monitoring.
Interpret customer ratings with sample size. Compare ratings alongside review counts and customer/vehicle context before proposing service interventions.
Keep an auditable exception workflow. Surface missing costs, negative revenue, unresolved currencies, date conflicts, and unusual amounts in a dedicated investigation view, then record their resolution rather than silently dropping them.

These are recommendations for analysis and control improvements, not causal conclusions about why a particular branch or employee performs as it does.

Conclusion

The most valuable step in this project was to distrust the first number. The same 276 rows told three different profit stories depending on two decisions, whether foreign currencies were converted, and whether two impossible prices were believed. Only after resolving both, I could answer the real business questions, discounting drives profit, half of revenue is unpaid, and fulfilment problems sit at Nairobi HQ and Kisumu.

GitHub Link

[(https://github.com/Expertwriter006/From-Raw-Data-to-Business-Decisions-A-Complete-Power-BI-Solution-for-JCars-Logistics)]

Data Modelling, Relationships and Joints in Power BI

Expert Writer — Thu, 24 Sep 2026 21:07:08 +0000

Introduction

A Power BI report is only as reliable as the model beneath it. Data modelling is the process of organising tables, keys, relationships, and calculations so that business questions can be answered accurately and efficiently. A good model reduces ambiguity, makes DAX easier to write, improves query performance, and gives report users predictable filtering behaviour.
The attached Kenya Crops Data PBIX provides a useful starting point for this discussion. Its report definition shows a single table named Kenya_Crops_Dataset 5, with fields including County, Yield (Kg), Revenue (KES) and Profit (KES), plus measures named Total Revenue and Total Profits. In modelling terms, this is essentially a flat-table design: it can produce useful visuals quickly, but it also provides an excellent example of why a growing analytical solution may eventually benefit from separating facts and dimensions.

1. Data Modelling in Power BI

Data modelling is the process of deciding how the tables loaded into Power BI relate to one another, which tables exist, what each one contains, which columns act as keys, and how relationships connect them so that a filter selected in one visual correctly reaches every other value it should affect. It happens after data is loaded and cleaned in Power Query, and before any report visuals or DAX measures are built. It is the structural layer that everything else sits on.
A properly designed model supports reporting, analytics, DAX, performance, scalability and maintainability because each table has a clear purpose and each relationship reflects a real business rule.

Flat Table

A flat table stores every attribute and every measurable fact in one wide table, with one row per transaction or observation. The Kenya Crops Data model is an example of this approach: County, Yield, Revenue and Profit-related fields are available in the same dataset used by the report.

Advantages

Simple to build and understand since there are no relationships to configure, so no risk of an incorrect join.
Fast for very small datasets or fast analysis
Every value is visible in one place, which is convenient for data exploration just as in excel.

Disadvantages

Repeated descriptive values increase redundancy
Changes to descriptive attributes become harder to manage
A single wide table can make the semantic model less reusable
No natural place for hierarchy or for attributes that don't change per transaction, such as a farmer's registration details

When it is appropriate

Flat tables are appropriate for small, static datasets, quick prototypes, or reports with a short shelf life where the overhead of proper modelling isn't needed. It becomes a liability once the dataset grows, needs multiple related fact sources.

Star Schema

A star schema places one central fact table, holding the measurable, numeric events (yield, revenue, profit), surrounded by multiple dimension tables, each holding descriptive attributes for one subject area (county, crop, date, farmer). Every dimension connects directly to the fact table, and the dimensions do not connect to each other. Viewed in Power BI's Model view, the fact table sits in the middle with lines radiating outward to each dimension, resembling a star.

The star schema is the default preferred approach for the overwhelming majority of Power BI reporting and analytics solutions, from small departmental dashboards to enterprise semantic models because it balances simplicity, performance, and maintainability better than either alternative.
It provides a simple filter path, clear table responsibilities, efficient columnar storage and straightforward DAX. Its main disadvantage is that it requires more modelling work than a flat table and may require surrogate keys or dimension-management processes.

Snowflake Schema

A snowflake schema normalises one or more dimensions into additional related tables. For example, Product could connect to Category, while Location could be separated into County, Region and Country tables.

Snowflaking is useful where dimensions are genuinely shared, large, hierarchical or governed centrally, but unnecessary normalisation is usually best avoided.

Advantages

Removes duplicate attribute values
Can better reflect a genuine multi-level business hierarchy as separate, independently maintainable tables.
Slightly smaller storage footprint for very large, highly repetitive dimension attributes

Disadvantages

More tables to manage, name, and document
Extra hops mean filters must pass through an additional relationship to reach the fact table, which can slow queries and complicate DAX
The storage savings are usually negligible in Power BI

Fact Tables and Dimensional Tables

The star and snowflake schemas both rest on a single distinction, separating what happened (facts) from who, what, when, and where it happened to (dimensions).

Fact Tables

A fact table stores the measurable, numeric business events such as sum, average, or count.
Fact tables are typically long with many rows and narrow, having few columns that are mostly foreign keys plus numeric measures.
The grain of a fact table is the precise definition of what a single row represents, the level of detail at which facts are recorded. For FactCropProduction, the grain might be "one row per farmer, per crop, per season, per county." Getting the grain right is one of the most important modelling decisions.
Every measure and every dimension key in the fact table must be true at that declared grain.

Dimension Tables

A dimension table stores descriptive, mostly textual attributes that give context to the facts, the who, what, when, and where. Dimension tables are typically short (relatively few distinct rows) and wide (many descriptive columns), and they change far less often than fact rows accumulate.

Worked Example: A Star Schema for Crop Production

Bringing this together, a central FactCropProduction table connects to four dimensions, each answering a different question about the same production event:
DimDateanswers "when?" — supporting month, season and year-over-year analysis.
DimCounty answers "where?" — supporting county- and region-level comparison.
DimCrop answers "what?" — supporting crop and crop-category breakdowns.
DimFarmer answers "who?" — supporting analysis by farm size or individual farmer.

Relationships in Power BI

A relationship is a logical link between two tables, defined by matching a column in one table to a column in another. It does not copy or merge any data, it simply tells Power BI's engine how two tables are connected through columns, so that a filter applied to one table can propagate to the other. Relationships are what make it possible to split data across multiple tables.

One-to-Many (1:*)

This is usually the standard dimensional relationship, where one row in DimProduct can correspond to many rows in FactSales. ProductID is unique on the dimension side but may repeat on the fact side. This should normally be the default pattern for Power BI models.

One-to-One (1:1)

Each row in one table relates to exactly one row in the other, and vice versa, both key columns contain entirely unique values.

Many-to-Many (:)

Both sides of the relationship can contain repeated (non-unique) values, where many rows in Table A can match many rows in Table B.

Keys, Uniqueness, Cardinality and Referential Integrity

Primary key: This the column (or columns) in a dimension table that uniquely identifies each row
Foreign key: The corresponding column in the fact table that references a dimension's primary key
Cardinality: The mathematical nature of the relationship (1:1, 1:, *:1, or *:), which Power BI detects automatically when a relationship is created but which should always be reviewed and confirmed by the modeller
Referential integrity: the assurance that every foreign key value in the fact table actually has a matching primary key value in the dimension
Active and inactive relationships: Power BI allows only one active relationship between two given tables at a time

Filter Direction

Once a relationship exists, Power BI needs to know which direction a filter is allowed to travel across it. This is the relationship's cross-filter direction, and it determines how selecting a value in one visual affects the values shown in another.

Single-Direction Filtering

In a single-direction relationship, filters flow only from the "one" side to the "many" side, from a dimension into the fact table. This is the default and recommended setting for the great majority of relationships in a star schema.

Bidirectional (Both) Filtering

A bidirectional relationship allows filters to flow both ways, a selection in the fact table can also filter the connected dimension table, and vice versa.

Why Bidirectional Filtering Needs Care

Bidirectional filtering is powerful but risky, and should be switched on deliberately, not by default:

Ambiguous filter paths: if multiple bidirectional relationships create more than one route by which a filter could reach a table, Power BI may refuse to create the relationship, or worse, resolve the ambiguity in a way the model author didn't intend.
Unnecessary model complexity: every bidirectional relationship is another path a report author (or a future you) has to mentally trace when a number looks wrong.
Performance cost: bidirectional relationships require the engine to evaluate filters in both directions, which is more expensive than a single-direction filter, especially on large fact tables.
Circular relationships: combining bidirectional filtering with certain model shapes e.g., a many-to-many bridge plus a bidirectional dimension can create circular filter logic that Power BI will block outright

Joins in Power Query

A join in Power Query is performed through Merge Queries. Unlike a model relationship, a merge combines columns from one query with matching rows from another query during data preparation. The result becomes part of the query output loaded into the model.

Left Outer Join

Keeps every row from the left (first) table, and adds matching columns from the right table where a match exists.
This is the default and most commonly used join in Power Query since it is appropriate whenever you want to enrich a complete list.

Right Outer Join

This join keeps every row from the right table, adding matching columns from the left table where available, and nulls where not.

Full Outer Join

Keeps every row from both tables, matching where possible and filling with nulls on whichever side has no match.

Inner Join

Keeps only rows where a match exists in both tables, and anything unmatched on either side is dropped entirely.
Inner joins are appropriate when incomplete or unmatched data is not useful for the analysis.

Left Anti Join

Keeps only rows from the left table that have no match in the right table, and the matching rows are excluded

Right Anti Join

The mirror of a left anti join, keeping only rows from the right table that have no match in the left table.

Power Query Joins vs Power BI relationships

Merging in Power Query and relating tables in the data model look similar since both connect two tables on a common key, but they operate at different stages of the Power BI workflow and have very different effects on the model.
A Power Query merge happens during data transformation before the final model is used for reporting by physically adding columns/rows to the query result.
A Power BI relationship is a semantic connection between already separate model tables, it does not copy the columns of one table into another.

Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema rather than a flat table or a heavily snowflaked model. The key reason is balance, as it provides enough separation to reduce redundancy and support scalability without introducing the relationship complexity of excessive normalisation.
A star schema normally gives better report readability, simpler DAX, predictable filter propagation and a strong foundation for adding new facts and dimensions. Power BI's columnar storage engine also benefits from well-structured tables with appropriate data types and low cardinality dimension attributes. The model remains easier to test because each relationship has a clear business meaning.
My default relationship design would be one-to-many from dimensions to facts, active relationships for the main analytical path, and single-direction filtering. Bidirectional relationships would be introduced only when there is a demonstrated business requirement and the resulting filter paths are unambiguous.
The final objective is not simply to minimise the number of tables. It is to create a model in which each table has one clear responsibility, each relationship represents a valid business rule, and users can build reports without needing to understand the physical complexity of the source system.

Conclusion

Data modelling decisions in Power BI are rarely about right or wrong in the abstract, they are about matching the shape of the model to the shape of the analysis it needs to support. A flat table is honest and simple but scales poorly. A star schema strikes the best balance for almost every practical reporting scenario, a snowflake schema earns its extra complexity only in specific, large-scale situations. Relationships, cardinality, filter direction, and Power Query joins are the mechanics that make any of these structures actually work, understanding when to use a one-to-many (1:*) relationship versus a merge, or when to allow bidirectional filtering versus keeping it single-direction, is what turns a technically correct model into a genuinely usable one. Relationships connect semantic tables without physically merging them, while Power Query joins reshape data before it reaches the model. Understanding this distinction allows a data scientist to build solutions that are accurate, performant, scalable and maintainable.

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Expert Writer — Tue, 08 Sep 2026 15:04:20 +0000

Introduction

Data analytics play a vital role in decision-making processes for e-commerce. Online market platforms generate massive amounts of data and information about products, their prices, discounts, customer reviews, offers available and, ratings. When properly analysed, this information can be of great use to the sellers, and institutions in understanding customer behaviour, identify products that are performing highly, improve pricing strategies, and come up with better promotional decisions.
In this particular project, I will focus on the analysis of Jumia product dataset using Microsoft Excel, which is an important tool that can be used for data analysis, to get some essential indications. The major objective of the analysis is to transform raw data into cleaned, structured, analysed, and interactive dashboard that provides meaningful business insights.

Project Objective

The primary objective of this project was to create an interactive Excel dashboard for analysing the performance of products listed on Jumia.
The final dashboard provides an overview of product performance using key performance indicators (KPIs), charts, product rankings, and category breakdowns.
The dashboard is intended to help Jumia sellers and decision-makers understand how pricing, discounts, ratings and customer engagement interact.

The Dataset

The dataset contains information and data about Jumia products.
The original dataset was made of 115 product records and 6 main columns

Product : Name of the product
Current Price : Current selling price (In Ksh)
Old Price : Original price before discount
Discount : Percentage discount offered
Review : Number of customer reviews
Rating : Average customer rating out of 5

Data Cleaning

The original dataset had several quality issues that I had to address before analysis such as:

Duplicate records.
Prices were stored as text containing the KSh currency symbol
Ratings were stored in text such as 4.5 out of 5
Review values appeared as negative numbers.
Missing values in the Review and Rating fields

Action Taken

I started by checking for duplicates in the Original dataset and deleted them.
I did this by selecting the whole data set, and under the Data tab in Excel, I clicked on Remove Duplicates.

This step reduced the number of products to 111

Secondly, I changed the values in Original Price field,which were in Text form, into Numbers.
Similarly, the rating field contained values such as 4.5 out of 5 which is in Text format, and need to be in Numerical values

Checking for Missing Values

Missing values can interfere with calculations and visualizations. The original dataset had missing values in the Review and Rating Columns.

Cleaning the Review Column

The Review column had its values written as negative such as -2, -4, -14, -7.
Logically, the number of customer reviews cannot be negative. I treated the negative signs as erroneous and removed the negatives.

Creating the Discount Amount Column

One of the calculated fields required by the project was the absolute discount amount.
The formula is:
=Old Price - Current Price

Creating the Rating Category

I created the Rating Category to group the products according to their customer ratings as follows:

Poor: Rating below 3
Average: Rating between 3 and 4
Excellent: Rating above 4.5 The Excel formula is: =IF(F2<3,"Poor",IF(F2<=4.4,"Average","Excellent"))

Creating the Discount Category

I created the discount category in three groups:

Low Discount: Below 20%
Medium Discount: 20%–40%
High Discount: Above 40% The Excel formula is: =IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))

Descriptive Statistics

After the data cleaning and transformation, I calculated the descriptive statistics as follows, with the excel functions:

Total Products (=COUNTA(A2:A113)):111
Avg Current Price (=AVERAGE(B2:B113)): 1181.37
Avg Old Price(=AVERAGE(C2:C113)):1803.10
Avg Discount(=AVERAGE(D2:D113)): 37%
Avg Rating (=AVERAGE(F2:F113)): 3.88
Total Reviews(=SUM(E2:E113)): 721

Correlation Analysis

I investigated three major relationships:

Discount and reviews
Rating and reviews
Price and rating

1. Discount Vs Customer Reviews

The first relationship examined was whether products with higher discounts receive more customer reviews.
The correlation between discount percentage and number of reviews, of the final 111 products was -0.139.
This indicates a very weak negative relationship in the dataset.
The results, therefore, suggests that higher discounts are not associated with substantially higher customer engagement in this dataset.
Consequently, it is important, for the sellers not to assume that increasing the discount will automatically generate more customer reviews.

Rating Vs Customer Reviews

The correlation for this relationship was 0.066.
This indicates a weak positive relationship.
Therfore, highly rated products do not necessarily receive significantly more reviews.
It is, therefore, important to note that Customer satisfaction and customer engagement are different dimensions of product performance.

Price Vs Rating

The correlation between price and rating is 0.104.
This represents a very weak positive relationship.
Therefore, more expensive products are not necessarily rated substantially higher than cheaper products.
The result suggests that price alone does not appear to determine customer satisfaction in this dataset.

Identifying Top 10 Products by Customer Reviews

The strongest observation is the first product, 120W Cordless Vacuum Cleaner. It has the highest number of reviews but only a 2.8/5 rating.
This translates into high customer engagement but low customer satisfaction.

Identifying Top 10 Products by Rating

The products showed to genarally have relatively small review counts.
This is why a high rating should not automatically be interpreted as strong market demand.

Identifying Top 10 Products by Discount

Identifying High Discounts with Low Ratings

One of the most useful analyses was to identify products that combine:
High discount + Low rating
These products are a bit troublesome since they may be heavily promoted but still receive poor customer feedback.
One clear example is the 5-PCS Stainless Steel Cooking Pot Set with:

Discount: 55%
Rating: 2.1/5
Reviews: 13
This data suggests that the problem is not in the price. Reducing the price further, may increase sales temporarily, but may not solve the root cause of underlying customer dissatisfaction.
Potential causes may include:
Product quality
Product description
Product expectations
Packaging
Delivery experience
Product durability

Creating KPI cards for the Dashboard

The Key Performance Indicators for my dashboard included:

`- TOTAL PRODUCTS

AVERAGE PRICE
AVERAGE DISCOUNT
AVERAGE RATING
TOTAL REVIEWS`

Dashboard Layout

I organised the dashboard into several sections, keeping the most important information visible, while also providing detailed physical views.

Pivot Tables

Rating Category

This pivot table was to provide a breakdown of products into:

Poor
Average
Excellent

Discount Category

Key Business Findings

The analysis produced several important findings as follows:

1.High Discounts Do Not Guarantee High Engagement

The correlation between discount percentage and reviews was approximately -0.139, indicating a weak negative relationship.
Therefore, increasing discounts does not automatically result in more customer reviews.

2.High Ratings Do Not Guarantee High Demand

The relationship between ratings and review gave a correlation of 0.066, indicating almost no linear relationship. Some highly rated products have very few reviews.
Sellers should, therefore, consider both Rating and Rating Volume to evaluate performance.

3.Price Does Not Strongly Determine Rating

The correlation between price and rating of 0.104 indicates a very weak positive relationship.
Therefore, premium pricing does not automatically result in higher customer ratings.

4.Some Products Have Strong Engagement but Poor Satisfaction

A clear example in this category is the 120W Cordless Vacuum Cleaner. with
29 Reviews 2.8 Rating
This product attracts considerable customer engagement but has poor satisfaction. It needs to be analysed what could be the cause for this trend.

Some Products Are Heavily Discounted Despite Poor Ratings

A good example in this category is the 5-PCS Stainless Steel Cooking Pot Set that has:
55% Discount, 2.1 Rating, 13 Reviews
This indicates that high discounting does not necessarily solve customer satisfaction problems.

Recommendations

1. Avoid excessive reliance on discounts

The sellers should not assume that increasing discounts will automatically increase customer engagement.
Promotional startegies should involve improvements of product quality and better customer experienvces

2. Investigate products with high reviews, and low ratings

Products with large numbers of reviews but low ratings should receive urgent attention. A clear example is the 120W Cordless Vacuum Cleaner

3. Analyse customer feedback

The sellers should examine the content of negative reviews to identify recurring problems that may include:

Quality
Durability
Size
Functionality

4. Use ratings and the number of reviews together

A 5.0 rating based on one review should not be treated in the same way as a 4.7 rating based on dozens of reviews.
A better performance framework should consider:

Customer Rating + Review Volume + Discount + Price

5. Reconsider high discounts on poorly rated products

When a product has high discount and poor rating, the selllers need to examine the underlying problem before enhancing the discount.

Limitations

Missing data in ratings and review counts of some products
The database does not contain a dedicated product-category field. Adding categories would make it possible to compare performance across product groups
The sales data contains reviews but does not have actual sales quantities
The revenue and profit-margin data are not available

Conclusion

This project demonstrated how Microsoft Excel can be used to transform raw e-commerce data into a practical business intelligence dashboard.
The final cleaned dataset contains 111 products after removing three duplicate records and the problematic sofa-cover record.
The analysis demonstrates the importance of cleaning and validating data before creating a dashboard.
The final findings show that:

Higher discounts do not necessarily generate higher customer engagement.
Highly rated products do not necessarily have more reviews.
Expensive products are not necessarily rated higher.
Some products have high customer engagement but poor ratings.
Some products have substantial discounts but poor customer satisfaction.

The most important lesson from this project is that a dashboard is only as reliable as the data behind it. Careful data cleaning, appropriate Excel formulas, meaningful visualizations and thoughtful interpretation are all necessary to turn raw e-commerce data into actionable business intelligence.

Github Link

Below is the link for Github Repository.
https://github.com/Expertwriter006/Jumia-Product-Performance-Dashboard

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Expert Writer — Tue, 01 Sep 2026 07:45:13 +0000

Introduction

Microsoft Excel remains one of the most widely used tools for data analytics, not because it is the most powerful platform available, but because it combines accessibility with genuine analytical depth. It has data entry, data inspection, cleaning, calculation, filtering, visualisation, and reporting in one familiar environment. Through the Excel, we always get to have our first real encounter with dataset for analysis. This is exactly where my Week 1 learning began: understanding the building blocks of a spreadsheet, then immediately putting those building blocks to work on a dataset that badly needed attention.

This article explains my practical exercise, where I used the HR_Dataset_Dirty.xlsx dataset. The dataset contains 876 employee records and 21 variables, including Employee ID, department, salary, hire date, age, gender, performance score, employment status, bonus, education level, work experience, office location, project count, remote-work status, training hours and manager feedback score. The purpose of the exercise was not simply to calculate statistics, but to demonstrate an important principle of data analytics that reliable analysis begins with reliable data.

1. Understanding the Excel Environment

Before touching the dataset, it is worth revisiting the fundamental concepts that make everything else in Excel possible. These are not advanced features, they are the vocabulary of the spreadsheet, and every cleaning technique used later in this article is really just a combination of these basics.

Workbooks, Worksheets, and Cells

An Excel workbook is a file that can contain multiple worksheets (tabs), each organised as a grid of rows and columns. Columns are normally used for variables or fields, while rows represent individual observations. In the HR dataset, for example, Department is a variable and each row represents an employee record. The intersection of a row and a column is a cell, identified by a unique address such as A1 or D9. Every worksheet in the workbook built for this project - from the raw data to the final cleaned table, is simply a grid of cells connected to one another through references and formulas.

The image above illustrates a worksheet, it having both Original Dirty Hr Dataset, and Cleaned Dataset.

The above image demonstrates an Active Cell, bordered in green colour, and positioned in D5, with D being the column, and 5 being the row. The active cell therefore, is at the intersection of D column, and row 5.

Formulas and Functions

A formula is any expression that begins with an equals sign and calculates a result, a function is a predefined formula such as TRIM, PROPER, or IF. In this, I learnt text functions (TRIM, PROPER, SUBSTITUTE, UPPER), logical functions (IF), and counting functions (COUNTIF, COUNTBLANK). As shown throughout this article, these five or six functions are, in practice, almost the entire toolkit needed to take a messy dataset and make it ready for analysis.

2. Inspecting the HR Dataset

The first step in a data-cleaning workflow is data profiling. I checked the number of rows and columns, missing values, duplicate records, data types and inconsistent categories.

The original HR dataset contained 876 rows and 21 columns. It also contained 277 blank cells and seven exact duplicate rows. More importantly, several fields contained inconsistent representations of the same concept.
For example, the Department field contained values such as HR, H.R, Hr, Human Resource, Human Resources, Humman Res. and Humna Resources. These values may refer to the same department, but Excel would treat them as different categories when counting. See in the picture below.

Similar problems occurred in other fields. Gender included Male, MALE, male, M, Female, female, F and Femle. Employee Type included Permanent, Perm, permanent, Contract, Contrct and contractor. Office Location contained variations such as London and Londn, Nairobi and Nairob, and Tokyo and Tokio.

The dataset also contained invalid or suspicious numeric values. Examples included a performance score of 11 even though the intended scale is 1–10, an age recorded as thirty, and a project count recorded as ten. There were also values such as -1 in work experience and 1900 or 2030 in Last Promotion Year that require validation rather than blind acceptance.

Additionally, it had:
Inconsistent Employee IDs - some stored as plain numbers (10764), others prefixed with text (EMP-10540), and several IDs duplicated across two different employees.
Invalid or inconsistent dates - hire dates such as "2019-02-30" (30 February does not exist), "2020/13/05" (month 13 does not exist), "31/04/2021" (April has 30 days), and plain text such as "not available".
Missing values - blank first names, blank office locations, and blank salaries scattered throughout the sheet.

Data Cleaning

Rather than cleaning the data by hand, I built the entire workflow using formulas, so that every 'cleaned' value is calculated automatically and stays connected to its raw source. If the raw data changes, the cleaned output updates with it - this is the real advantage of doing data cleaning in Excel using formulas rather than typing corrections over the original values.

Step 1: Standardising Text - Names, Departments, Locations, and Status

The first pass targets text inconsistency. Full names are rebuilt from First Name and Last Name using TRIM (to remove stray spaces), SUBSTITUTE (to collapse accidental double spaces), and PROPER (to apply consistent capitalisation). Where either name was blank, an IF/OR check flags the record as "MISSING - REVIEW" instead of silently producing an incomplete name.
The result was a worksheet where every text field - name, department, office location, and employment status - now uses one consistent spelling throughout, which is essential before any analysis of the data started.

Step 2: Detecting Duplicate and Missing Records

With text standardised, my next step was finding rows that should not exist (duplicates) and rows that are missing critical information. I first stripped Employee ID of its "EMP-" prefix and converted to a genuine number with SUBSTITUTE and VALUE, so that "EMP-10540" and "10540" are recognised as the same identifier. Then I used COUNTIF against that cleaned ID column to flag any employee ID that occurs more than once, and COUNTBLANK to check each entire row for missing fields.

Step 3: Correcting Numeric Fields - Salary, Age, and Work Experience

Several numeric columns were stored as text, which silently breaks any SUM, AVERAGE, and other calculations on them. Salary values such as "$65109" were converted to true numbers, and the currency used as KES(Kenyan Shillings).

Step 4: Validating and Standardising Hire Dates

Dates were the least consistent field in the dataset, mixing genuine date values with plain text ("not available"), and several strings that look like dates but describe days that do not exist on any calendar.

Step 5: Consolidating into an Analysis-Ready Table

The final worksheet pulls the cleaned output of every previous step into a single, consolidated table, with one row per employee, with standardised names, departments, locations, employment status, salary, age, work experience, and hire date. A final Record Status column combines every flag raised earlier (duplicate ID, incomplete record, invalid date, or missing name/salary/age) into one clear verdict: "Ready for analysis". I added an AutoFilter so that a colleague could instantly isolate the records that still need attention before running any further analysis.

Step 6: Validating the Cleaned Dataset

After cleaning, I performed another inspection rather than assuming the data was correct.
The cleaned working dataset contained 869 rows after removal of the seven exact duplicate rows. Standardised categories were used for fields such as Department, Gender, Education Level, Employee Type, Office Location and Remote Work Status.
Numeric fields were converted to usable numeric values where possible, and invalid values were flagged or converted to blanks when they fell outside defined logical ranges.

Step 7: Basic Excel Analytics After Cleaning

Once the data was clean enough to analyse, I used Excel formulas to provide immediate descriptive statistics.
For example, I used AVERAGE to calculate mean salary, age or performance score. COUNTIF to count employees belonging to a particular department, and SUMIF to total salaries or bonuses for a selected category.
Using the cleaned working data, the average salary was approximately $74,114.16, the average age was approximately 41.98 years, and the average performance score was approximately 4.98.
There were 155 Finance employees and 263 employees classified as Fully Remote in the cleaned working dataset.

Conclusion

Excel is a powerful tool, and the skills provide the foundation for the wider data-analytics workflow. Understanding workbooks, worksheets, cells, ranges, formulas, data types, tables, sorting and filtering may appear basic, but these skills directly support professional data preparation and analysis.
The HR dataset demonstrates that real-world data is rarely perfectly structured. The original file contained 876 records, 21 variables, 277 missing cells, seven exact duplicate rows, inconsistent categorical labels, mixed data types, invalid dates and suspicious numeric values.
Through inspection, standardisation, type conversion, validation and duplicate handling, the working dataset was reduced to 869 records and made substantially more analysis-ready.
The most important lesson is that data cleaning is not merely about making a spreadsheet look neat. It is about making the meaning of the data consistent, transparent, and defensible.

Understanding Git Workflow from Working Directory, Staging, Commit, and Push.

Expert Writer — Sun, 23 Aug 2026 20:34:12 +0000

Git

Git is used to track changes in files and allow for managing ythe developmnet of projects, especially those that entail data. I used git to enable me record the different versions of my data from the original raw data, compare changes, without interfering with the initial data.

Git Workflow

Git has a series of commands that are important for committing changes and tracking the data. The commands are described as a Git Workflow as it involves checking the status of the current project, making changes to the projects by adding files, and committing the changes. The Git Workflow is quite vital as it allows for a systematic and organised way of managing changes, keeping a record of every progress, and making it easier to identify the areas that changes were made.

My project was Kenya Health Records Analysis and it contained and excel data, thus was in this structure:

`Kenya Health Records Analysis/ 
- Kenya Health Records Analysis.xlsx 
- README.md`

Checking the Project Directory

I used cd "Kenya Health Records Analysis" to access the project directory then ran the command ls -la to list the contents that were in the directory

Checking README File

cat README.md
This displayed the contents of the file such as the Title, tools used, and the challenges faced.

Initialize Git

git init
I ran this command to instruct Git that I needed the project to become a git repository.
To confirm whether it was successful, I ran the command ls -laand git status

Changing a file

I was able to edit and modify my project in this stage by opening the README.md file and adding more information in the Challenges Faced section.
I then ran git status but got this error:

`Changes not staged for commit: 
      modified: README.md

I noted that changing a file does not directly create a commit. I therefore, ran git add README.md which communicates to git to adjust the changes.

Commit

git commit -m
This was for creating a checkpoint in the project analysis by recording the changes I have done in the project. It also allowed for me to continue working without having to push the changes in Github immediately.

git commit -m "Kenya Health Records Analysis"

Checking Commit History

git log --oneline
This command allowed me to inspect every of my commit history

This is followed by git status to understand the current state of the project

Push

git push
After making the challenges locally, I ran git push origin main to send them to the remote repository.

Functions of Git

Track changes
Create commits
Mmaintain project history
Manage different versions of my project locally

Conclusion

During this project, I learnt that Git workflow is a sequence and not just a list of random commands. It starts from inspecting the project files with ls -la and then reading the documentation through running cat README.md. I can then run git status to understand the latest status of the repository. After this, I commit using git commit -m, and can review the history using git log --oneline.To finally share the local commits I made, I ran the command git push origin main.
Understanding this workflow gave me essential foundation for working with data analytics project as the one in Kenya Health Records Analysis in terms of documenting my work, and building a portfolio as a Data Scientist.