DEV Community: Gabriel Wainaina

Building J Cars Logistics Dashboard: From Raw Data to Power BI Insights.

Gabriel Wainaina — Wed, 30 Sep 2026 16:47:44 +0000

INTRODUCTION

I used to think of data cleaning as the "boring part" before the real work of building visuals began. Working on the JCars Logistics dashboard changed that. This article is a walkthrough of the inconsistent columns I found, the Power Query techniques I used to fix them, and the mistakes I made along the way right up through turning that cleaned data into an interactive executive dashboard.

The Setup
The raw dataset (Jcars_data) had 32 columns covering customer info, sales details, delivery logistics, and pricing sourced from what looked like several different people entering data over time, with zero validation rules in place which is a Classic real world data scenario.
Below is a preview of how the data looks like.

Cleaning the messy data.
First, I noticed the columns had no heading and the heading were being used as the first row therefore that was the starting point point using the first row as header.
Columns that didnt have alot of errors in the words I used the find and replace function replacing the blanks, N/A and unknown with null and also replacing places that required blanks with blank.

Challenge encountered was cleaning the columns that had alot of mispelling or wrong formatting. I couldn't use the find and replace option because it would have been time consuming therefore i had to be smart and work around that. I incoporated the use of codes that would help in cleaning. I added a corresponding custom column from where the customr name formula tab i inserted tabs to clean the respective columns.

Case Sensitivity: My First Real Roadblock

Take the Customer Type column. It should have had around 7–8 categories: Individual, Corporate, Government, NGO, Car Dealer, Company, Person, Retail. Instead it had things like; individual, INDIVIDUAL, corp, CORP, CORPORATE, govt, GOVT, n.g.o, N.G.O, Ngo, ngo, DEALER, dealer
I used the following code to be able to clean it

let
    Source = Csv.Document(File.Contents("C:\Users\User\OneDrive\Desktop\JCars_Logistics_Dashboard\Jcars_data.csv"),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Customer Typer_Clean", each let
        NullLike = {"N/A", "NA", "NULL", "Unknown", "UNKNOWN", "-", "", " "},
        Cleaned = Text.Clean(Text.From([Customer Type])),
        NoExtraSpaces = Text.Combine(List.Select(Text.SplitAny(Cleaned, " "), each _ <> ""), " "),
        Trimmed = Text.Trim(NoExtraSpaces),
        LowerTrimmed = Text.Lower(Trimmed),
        Mapping = [
            #"car dealer" = "Car Dealer",
            #"dealer" = "Car Dealer",
            #"company" = "Company",
            #"corp" = "Corporate",
            #"corporate" = "Corporate",
            #"county govt" = "Government",
            #"government" = "Government",
            #"govt" = "Government",
            #"individual" = "Individual",
            #"n.g.o" = "NGO",
            #"ngo" = "NGO",
            #"non profit" = "NGO",
            #"person" = "Person",
            #"retail" = "Retail"
        ],
        Result = if List.Contains(NullLike, Trimmed, Comparer.OrdinalIgnoreCase) then null
                 else try Record.Field(Mapping, LowerTrimmed) otherwise Text.Proper(Trimmed)
    in
        Result, type text)
in
    #"Added Custom"

What the code does

The code loads the raw CSV, promotes the first row to headers, and then adds a new column, Customer Type_Clean, that standardizes every customer type entry into one consistent label. The original column is left untouched, so the raw data is always there to compare against.

For each row, the code works in four stages:

Tidy the text. It strips hidden or non-printable characters, collapses repeated spaces into one, trims the edges, and converts everything to lowercase. This means GOVT, Govt and govt are all treated as the same thing.
Catch blanks. If the value is a placeholder like N/A, NULL, Unknown, or just empty, it becomes a true null instead of showing up as its own category in the dashboard.
Map variants to one label. The cleaned text is looked up in a mapping list. For example, corp and corporate both become Corporate, govt, county govt and government become Government, n.g.o, ngo and non profit become NGO, and dealer becomes Car Dealer.
Fall back gracefully. If a value isn't in the mapping, the code doesn't fail or discard it. It simply returns the text in Proper Case, so unexpected entries are still visible and can be reviewed later.

This would have been helpful since replacing one value with a correct value one at a time would have been time consuming.

Columns like Customer type that needed such i simply just used a code template that would have involved replacing the values.

// ===== FUNCTION: fnCleanText =====
(input as any, mapping as record) as nullable text =>
let
    NullLike     = {"n/a", "na", "null", "unknown", "-", "", "none"},
    AsText       = if input = null then null else Text.From(input),
    NoNBSP       = if AsText = null then null else Text.Replace(AsText, "#(00A0)", " "),
    Cleaned      = if NoNBSP = null then null else Text.Clean(NoNBSP),
    Trimmed      = if Cleaned = null then null
                   else Text.Combine(List.Select(Text.SplitAny(Cleaned, " "), each _ <> ""), " "),
    LowerTrimmed = if Trimmed = null then null else Text.Lower(Trimmed),
    Result =
        if LowerTrimmed = null or List.Contains(NullLike, LowerTrimmed) then null
        else if Record.HasFields(mapping, LowerTrimmed) then Record.Field(mapping, LowerTrimmed)
        else Text.Proper(Trimmed)
in
    Result


// ===== MAIN QUERY =====
let
    Source = Csv.Document(File.Contents("FILE_PATH"), [Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    Map_Column1 = [
        #"variant1" = "Label1",
        #"variant2" = "Label1",
        #"variant3" = "Label2"
    ],

    #"Added Column1_Clean" = Table.AddColumn(
        #"Promoted Headers", "Column1_Clean",
        each fnCleanText([Column1], Map_Column1), type text),

    #"Added Column2_Clean" = Table.AddColumn(
        #"Added Column1_Clean", "Column2_Clean",
        each fnCleanText([Column2], []), type text),

    #"Added Column3_Clean" = Table.AddColumn(
        #"Added Column2_Clean", "Column3_Clean",
        each fnCleanText([Column3], []), type text)
in
    #"Added Column3_Clean"

Currency Standardization Challenge

The Problem

JCars operates mainly in Kenya, but its monetary columns did not use a single currency or format. Values appeared as plain numbers, with symbols or codes ($, USD, EUR, ZAR, R 250,000, KES), with stray characters such as "?", and in shorthand such as 2.5M for millions. Some cells held placeholders like "missing", "not available", "error", "#VALUE!" or "TBD". Adding these together as they were would have produced meaningless totals.

Columns Affected

Five monetary columns needed standardizing: Unit Selling Price, Unit Cost, Delivery Fee, Logistics Cost and Revenue Recorded. Discount is a percentage, so it was cleaned separately and not converted.

Currencies Identified

Kenya Shillings (KES), US Dollars (USD, $), Euros (EUR) and South African Rand (ZAR, also written with an R prefix).

Approach

For each monetary column, I added a _Clean column and kept the original for auditing. Each value went through these steps:

Remove hidden characters, extra spaces and "?" symbols.
Convert placeholder text (missing, n/a, error, TBD, etc.) to null.
Detect the currency from the text (EUR, ZAR or an R prefix, USD or $, KES).
Apply the brief's rule: if no currency is indicated, assume the value is in KES.
Extract the numeric part and expand shorthand (M means × 1,000,000).
Multiply by a fixed exchange rate and store the result as a currency type.
Exchange Rates

One fixed set of rates was used across the whole project so that every figure is comparable.

Currency with corresponding Rate to KES
KES=1.00
USD=129.20
EUR=147.67
ZAR=7.84

Assumptions
Values with no currency indicator are in KES.
One fixed rate per currency is applied to every record, regardless of transaction date.
Values that could not be read as a number were set to null rather than guessed.
"M" after a number means millions.
Result

After cleaning, the dashboard uses the converted columns, renamed back to the original names, so all financial analysis is on a common KES basis.
Code used;

(input as any) as nullable number =>
let
    // Fixed exchange rates to KES
    RateKES = 1,
    RateUSD = 129.20,
    RateEUR = 147.67,
    RateZAR = 7.84,

    // Step 1: remove hidden characters, extra spaces and ? symbols
    Raw     = if input = null then "" else Text.From(input),
    Cleaned = Text.Trim(Text.Clean(Raw)),
    NoQ     = Text.Trim(Text.Replace(Cleaned, "?", "")),
    Upper   = Text.Upper(NoQ),

    // Step 2: placeholders become null
    NullLike = {"", "MISSING", "NOT AVAILABLE", "NULL", "ERROR", "-", "N/A", "NA", "#VALUE!", "TBD"},
    IsNull   = List.Contains(NullLike, Upper),

    // Steps 3 and 4: detect currency; no indicator means KES
    Rate =
        if Text.Contains(Upper, "EUR") or Text.Contains(Upper, "€") then RateEUR
        else if Text.Contains(Upper, "ZAR") or Text.StartsWith(Upper, "R") then RateZAR
        else if Text.Contains(Upper, "USD") or Text.Contains(Upper, "$") then RateUSD
        else if Text.Contains(Upper, "KES") then RateKES
        else RateKES,

    // Step 5: extract the number and expand M (millions)
    NumberOnly = Text.Select(Upper, {"0".."9", ".", "M"}),
    HasMillion = Text.Contains(NumberOnly, "M"),
    NumStr     = Text.Remove(NumberOnly, {"M"}),
    Num        = try Number.FromText(NumStr) otherwise null,

    // Keep negative signs (e.g. refunds)
    IsNegative = Text.Contains(Upper, "-"),
    Signed     = if Num = null then null else if IsNegative then -Num else Num,

    // Step 6: convert to KES
    Base   = if Signed = null then null else if HasMillion then Signed * 1000000 else Signed,
    Result = if IsNull or Base = null then null else Base * Rate
in
    Result

Date Standardization Challenge

The Problem
The JCars dataset had two date columns, Order Date and Delivery Date, and they did not share one format. The same field contained Excel serial numbers (such as 45367), slash dates (15/03/2024), dash dates with month names (15-Mar-24), ISO-style dates (2024-03-15) and written-out dates (March 15, 2024). Others held placeholders such as "n/a", "TBD", "not sure", "unknown" and "#DATE!". Sorting, filtering by month or calculating delivery times on this data would have given wrong or missing results.

Columns Affected

Two date columns: Order Date and Delivery Date.

Approach

For each column, I added a Clean column and tested every value against each known format in a fixed order. The first format that produced a valid date was used.

Trim and normalize. Remove extra spaces and convert the text to lowercase so comparisons are consistent.
Catch placeholders. Values such as "", "n/a", "na", "null", "#date!", "not sure", "unknown", "tbd", "none" and "-" become null.
Excel serial numbers. If the value is only digits, it is treated as an Excel serial and added to the base date of 30 December 1899.
Slash dates. Values in the form a/b/yyyy are split into three parts. If the first part is greater than 12, it must be the day. If the second part is greater than 12, the first must be the month. If both are 12 or below, the value is assumed to be day/month/year.
Dash dates with month names. For values like 15-Mar-24, the month name is matched on its first three letters, and two-digit years are treated as 20xx.
ISO-style dash dates. Numeric values separated by dashes, such as 2024-03-15, are converted directly.
Written dates. Values like March 15, 2024 are split into month name, day and year.
Combine results. The formats are chained so the first successful parse wins, and anything that matches none of them becomes null.
Set the type. The result is stored as a Date type.
Assumptions
Ambiguous slash dates, where both parts are 12 or below, are day/month/year, which is the common convention in Kenya.
All-digit values are Excel serial numbers.
Two-digit years belong to the 2000s.
Values that could not be parsed by any rule were set to null rather than guessed.
Result

After cleaning, the original date columns were removed and the Clean columns were renamed back to Order Date and Delivery Date. Both columns are now true date fields, so the dashboard can group by month and year, filter by period and calculate delivery times reliably.

Code Used;

(input as any) as nullable date =>
let
    raw = try Text.Trim(Text.Clean(Text.From(input) ?? "")) otherwise "",
    low = Text.Lower(raw),

    // Step 2: placeholders become null
    Garbage = {"", "n/a", "na", "null", "#date!", "not sure", "unknown", "tbd", "none", "-"},

    MonthMap = {
        {"jan",1},{"feb",2},{"mar",3},{"apr",4},{"may",5},{"jun",6},
        {"jul",7},{"aug",8},{"sep",9},{"oct",10},{"nov",11},{"dec",12}
    },
    MonthNum = (name as text) as nullable number =>
        let hit = List.First(List.Select(MonthMap, each _{0} = Text.Lower(Text.Start(name, 3))), null)
        in if hit <> null then hit{1} else null,

    ToNum = (t as text) as nullable number => try Number.From(t) otherwise null,
    FixYear = (y as nullable number) as nullable number =>
        if y <> null and y < 100 then 2000 + y else y,

    // Step 3: Excel serial numbers
    IsAllDigits = raw <> "" and Text.Length(Text.Select(raw, {"0".."9"})) = Text.Length(raw),
    SerialResult =
        if IsAllDigits
        then try Date.From(#date(1899, 12, 30) + #duration(Number.From(raw), 0, 0, 0)) otherwise null
        else null,

    // Step 4: slash dates (a>12 is the day, b>12 is the month, otherwise day/month/year)
    SlashParts = Text.Split(raw, "/"),
    SlashResult =
        if SerialResult = null and List.Count(SlashParts) = 3 then
            let
                p0 = ToNum(SlashParts{0}),
                p1 = ToNum(SlashParts{1}),
                p2 = FixYear(ToNum(SlashParts{2})),
                Day   = if p1 <> null and p1 > 12 then p1 else p0,
                Month = if p1 <> null and p1 > 12 then p0 else p1
            in try #date(p2, Month, Day) otherwise null
        else null,

    // Step 5: dash dates with a month name (15-Mar-24)
    DashParts = Text.Split(raw, "-"),
    DashHasLetters = List.Count(DashParts) = 3 and ToNum(DashParts{1}) = null,
    DashMonthResult =
        if SerialResult = null and SlashResult = null and DashHasLetters then
            let
                m = MonthNum(DashParts{1}),
                y = FixYear(ToNum(DashParts{2})),
                d = ToNum(DashParts{0})
            in if m <> null then try #date(y, m, d) otherwise null else null
        else null,

    // Step 6: numeric dash dates (yyyy-mm-dd, or dd-mm-yyyy)
    DashIsoResult =
        if SerialResult = null and SlashResult = null and DashMonthResult = null
           and List.Count(DashParts) = 3 and not DashHasLetters then
            let
                IsYearFirst = Text.Length(DashParts{0}) = 4,
                y = if IsYearFirst then ToNum(DashParts{0}) else FixYear(ToNum(DashParts{2})),
                m = ToNum(DashParts{1}),
                d = if IsYearFirst then ToNum(DashParts{2}) else ToNum(DashParts{0})
            in try #date(y, m, d) otherwise null
        else null,

    // Step 7: written dates (March 15, 2024 or 15 March 2024)
    WordParts = Text.Split(Text.Replace(raw, ",", ""), " "),
    WordMonthResult =
        if SerialResult = null and SlashResult = null and DashMonthResult = null and DashIsoResult = null
           and List.Count(WordParts) = 3 then
            let
                DayFirst = ToNum(WordParts{0}) <> null,
                m = if DayFirst then MonthNum(WordParts{1}) else MonthNum(WordParts{0}),
                d = if DayFirst then ToNum(WordParts{0}) else ToNum(WordParts{1}),
                y = ToNum(WordParts{2})
            in if m <> null then try #date(y, m, d) otherwise null else null
        else null,

    // Step 8: the first successful parse wins; anything else is null
    Result =
        if List.Contains(Garbage, low) then null
        else SerialResult ?? SlashResult ?? DashMonthResult ?? DashIsoResult ?? WordMonthResult
in
    Result

The Last step was going through the cleaned data and making sure they are in the correct data type respectively.
Now the cleaned data set looked like this;

I then created dimensions and facts table that would assist me in creating a relationship in the model view.

DASHBOARD CREATION

First I had to create DAX functions that would aid in dashboard creation.

My dashboard had a total of 6 pages with each page showing different visualization that would be used in interpretation of the overall JCars Data set,

What the Dashboard Showed

Headline results

The Executive Dashboard summarizes the business in four numbers:

Units sold: 458
Total revenue: Ksh 1.46bn
Gross profit: Ksh 26.43M
Gross profit margin: 1.80%

Revenue is large, but very little of it becomes profit. Everything below explains where the profit comes from and where it leaks.

Page by page findings

Executive Dashboard. Thika is the highest-revenue branch, followed by Kakamega and Nakuru. Nairobi is the lowest. Toyota leads revenue by make, and the Harrier leads units sold among models.
Products. Gross profit margin varies sharply by make. Volkswagen and Subaru are strongly positive, while BMW and Isuzu are deeply negative. The returns table shows 149 returned units and 37 cancelled transactions.
Geography and Sales Team. Rift Valley is the largest region at Ksh 377.47M. Central, Nyanza and Nairobi all have negative margins. Faith Achieng leads the sales team by revenue, ahead of Mary Wanjiku and Brian Otieno. Instagram and Facebook are the top lead sources by revenue.
Customers and Payments. Car Dealers, Government, NGOs and Corporates make up most revenue. M-Pesa, cash and bank transfer are the largest payment methods. Most revenue sits in the Paid status, followed by Pending and Partial Paid.
Logistics and Delivery. Total cost is Ksh 1.44bn. Logistics cost is Ksh 27.78M, while delivery fees collected are Ksh 24.91M.

Interpretation: Key Insights

SUVs carry the business. SUVs bring in Ksh 844.86M, or 57.7% of revenue, from 38% of units (175 of 458), at a 9.14% margin. The company-wide margin is only 1.80%, so SUV profit is being offset by losses elsewhere. The business's profit depends heavily on one vehicle type.
Four vehicle types sell at a loss. Sedans lose 16.24% on Ksh 189.68M, Trucks 65.57% on Ksh 58.74M, Vans 27.67% on Ksh 27.48M, and Crossovers 9.84% on Ksh 70.19M. Together they make up 23.6% of revenue and about 34% of units. The dashboard shows where the losses are but not why. Purchase cost, discounting and delivery cost are possible causes that have not yet been tested. Margins also differ sharply by make, but small volumes can exaggerate those swings, so check units per make first.
Three regions with negative margins account for about 35% of revenue. Central (-4.13%), Nyanza (-8.90%) and Nairobi (-16.77%) together bring in Ksh 512.32M. Rift Valley is the largest region, at 25.8% of revenue with a positive 4.58% margin. Nairobi has the lowest branch revenue and the weakest margin, which is unexpected for the capital. Whether the cause is vehicle mix, discounting or cost is not established.
Social media leads are the most profitable; referrals and corporate tenders lose money. Instagram (17.20%), WhatsApp (12.51%) and Facebook (10.09%) have positive margins and the highest revenue per customer, about Ksh 6.1M to 7.0M. Referral (-22.21%) and Corporate Tender (-18.02%) sell at a loss, and Website (-2.95%) and Phone Call (-2.49%) are slightly negative. This does not prove the channel drives margin, since each channel may sell a different vehicle mix.
Revenue depends on a few buyer groups. Car Dealers (23.4%), Government (22.7%), NGOs (20.5%) and Corporates (16.8%) make up about 83% of revenue. Individuals account for only 11.7%. Government and NGO buyers alone are 43.2%, so losing one of these groups would have a large effect.
Returns, cancellations and delivery may weaken the headline figures. The 149 returned units equal roughly one in three of the 458 units sold. One cancelled order still carries Ksh 4.38M of revenue. If returned or cancelled sales are counted in the Ksh 1.46bn, revenue is overstated. Separately, logistics cost exceeds delivery fees collected by about Ksh 2.87M.

Recommendations

Investigate pricing and cost on Sedans, Trucks, Vans and Crossovers. Pull the individual negative-margin sales in these types and compare purchase cost, selling price, discount and delivery cost against profitable SUV sales. Then decide whether to reprice, limit discounting or reduce stock of the worst performers. Trucks come first, given their -65.57% margin.
Review margin performance in Nairobi, Nyanza and Central before changing branch strategy. Compare their vehicle mix and discount levels with Rift Valley and Coast to see what differs. Track monthly margin by region so any fix can be measured.
Review pricing approval for Referral and Corporate Tender sales, and keep investing in Instagram, WhatsApp and Facebook. First test whether the gap comes from the channel or from the vehicles sold through it. If it is the channel, tighten discount approval for tenders and referrals, and monitor margin by lead source each month.
Fix how returns, cancellations and delivery are measured and priced. Reconcile revenue so cancelled and refunded orders are excluded, and confirm what the 149 returned units represent. Review delivery fees against logistics cost, since delivery currently costs more than it recovers.
Protect and diversify the buyer base. Assign clear account ownership for the dealer, government, NGO and corporate accounts that generate about 83% of revenue, and set targets to grow the individual and retail segments.

Conclusion

J Cars Logistics turns over Ksh 1.46bn but keeps only 1.80% as gross profit. Nearly all of that profit comes from SUVs, while Sedans, Trucks, Vans and Crossovers, three weak regions and two lead sources pull it back down. Revenue also depends on a small group of dealer, government, NGO and corporate buyers, and returns, cancellations and delivery costs may be reducing the real figures further.

None of this would have been visible without cleaning the data first. Standardizing every monetary value to KES, converting dates to true date fields and correcting inconsistent categories allowed revenue, cost and margin to be compared on one basis. The next step is to test the causes behind the losses, starting with vehicle level pricing and cost, so management can act on evidence rather than assumption.

Github link:(https://github.com/wainainagabriel63-dev/JCARS-POWERBI-DASHBOARD)

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

Gabriel Wainaina — Tue, 08 Sep 2026 15:42:06 +0000

Introduction

In this project, I set out to analyze a dataset of products listed on Jumia, one of Africa's leading e-commerce platforms, with the goal of understanding how pricing strategies, discounts, and customer reviews influence overall product performance. Using Microsoft Excel, I cleaned and prepared the raw dataset, engineered new features to categorize products, ran correlation and trend analyses to uncover relationships in the data, and ultimately built an interactive dashboard to present the findings in a clear, business friendly format.

The objective was not just to explore the numbers, but to answer practical business questions: Do bigger discounts actually drive more customer engagement? Does price influence how a product is rated? Which products are genuinely performing well, and which ones might need a different pricing or marketing approach?

This article breaks down the full process behind how I turned raw data into a finished report from initial data cleaning to the creation of an interactive dashboard.

The following is the uncleaned data(original dataset)

Data Set Overview.

The dataset contains information about products listed on Jumia with the following columns:
•Product: Name of the product.
•Current Price: The current selling price of the product (in KSh).
•Old Price: The original price before discount (in KSh).
•Discount: The percentage discount offered on the product.
•Review: The number of customer reviews received by the product.
•Rating:The average customer rating of the product (out of 5).

Data Cleaning and Preparation

On a separate sheet after copy pasting the original data set I had to ensure there was no any present duplicate in the dataset.

Now that I had removed any duplicates that was present, next process was changing the cell format for each column from General to the appropriate data type i.e. Text for product names, Currency for prices, Percentage for Discount and Number for ratings and reviews.

Next, I used Find and Replace on;

Both the Current Price and Old Price, replacing "KSh" and replacing it with blank.
On rating category to correct it from Ratingd to Rating.
Replacing the out of 5with blank in the review category.
Replacing blanks with unknown

On both the Current Price and Old Price columns, there were highlighted cells (as seen in row 40 shown in the image below) containing a price range instead of a single value. To correct this, I used the =AVERAGE() formula to calculate the average amount on each cell.

The Review numbers were in negative values and reviews can't be negative hence so I used the formula =ABS() to remove the negatives.
I also used the =TRIM() and =CLEAN() functions on the Product column to remove extra spaces and non-printable characters that could interfere with sorting, filtering, or matching later in the analysis.

Below is the final Cleaned Dataset.

Data Enrichment.

I Created additional columns which would improve my analysis.

Discount Amount.
Formula used-
=(Old Price − Current Price).
Rating Category.
Formula used- =IF(OR(F2="Unknown",G2="Unknown"),"Unknown",IF(G2<3,"Poor",IF(G2<=4,"Average","Excellent")))
Discount Category.
Formula used-
=IF(E2<20%,"Low Discount",IF(E2<=40%,"Medium Discount", "High Discount"))
Price Category.
I used QUARTILE.INC() to create named breakpoints that split my Current Price data based on its actual distribution, rather than relying on arbitrary cutoffs.

I created two named ranges:

Price_Q1 → =QUARTILE.INC(Cleaned!$B:$B,1) which is the 25th percentile, meaning 25% of products are priced at or below this value.
Price_Q3 → =QUARTILE.INC(Cleaned!$B:$B,3) which isthe 75th percentile, meaning 75% of products are priced at or below this value.

Using these two breakpoints, I then classified products into Low Price (bottom 25%), Medium Price (the middle 50%, between Q1 and Q3), and High Price (top 25%) using the formula:
=IF(B2<=Price_Q1,"Low Price",IF(B2<=Price_Q3,"Medium Price","High Price"))

The final Data set

Data Analysis

I used the formula =AVERAGE() to find;

Average current price of products= KES1,187.
Average old price of products= KES1,811.
Average discount percentage= 37%
Average product rating= 3.9

I used the formula =COUNTA() to find the total number of products which is 112.
To find the sum of total number of reviews I used the function =SUM()which is 723.
To find the most and least expensive products I used =MAX() and =MIN() which are KES 3,750 and KES 38 respectively.

Trend Analysis

I wanted to find out whether;

Higher discount percentage result in more customer reviews.
Using=CORREL(F2:F113,H2:H113) the correlation was r =-0.1368.
This means that there is a weak negative relationship between discount percentage and number of reviews. Hence as discount increases, reviews very slightly tend to decrease.
Highly rated products receive more customer reviews.
Using =CORREL(I2:I113,H2:H113) the correlation between Rating and Review count was found to be 0.0572, indicating virtually no relationship. This suggests that review volume is not driven by product quality, but likely by other factors.
Expensive products rated higher than cheaper product.
Using =CORREL(B2:B113,I2:I113) the correlation was 0.1100 indicating a very weak positive relationship. This indicates that more expensive products tend to be rated very slightly higher, but the effect is negligible. Price does not appear to be a reliable predictor of product rating.

while also;
• Identifying the top 5 products with the highest ratings.
Using the filter option on rating, I was able to filter the rating column to see the top 5 products as shown below;

• Identifying the top 5 products with the lowest ratings.
Using the filter option, I was able to find the top 5 products with Lowest Ratings.

Product Performance Analysis.

I was able to analyze the following using the filter option in the respective categories,

- Top 10 products with the highest discounts.

- Top 10 products with the highest number of reviews.

- Top 10 highest-rated products.

- Products with high discounts but low ratings.

- Products with strong customer engagement.

To identify products with strong customer engagement, I created an Engagement Category column that classifies each product into one of three tiers i.e. Weak, Average, or Strong which is based on a combination of review count and rating.
Rather than picking arbitrary thresholds, I used quartiles function
QUARTILE.INC()to set data driven cutoffs based on the actual distribution of review counts.
I created two ranges:
Review_Q1 — the 25th percentile of review counts
Review_Q3 — the 75th percentile of review counts
Then I used the formula: =IF(OR(H2="",H2="Unknown",I2="",I2="Unknown"),"", IF(AND(H2>=Review_Q3,I2>=4),"Strong Engagement", IF(H2<=Review_Q1,"Weak Engagement","Average Engagement")))

Then I filtered the Engagement column to retrieve Strong engagments as shown below;

Seller Performance Analysis.

Using the data i was able to answer the following by simply using filter.

- Which products appear to have strong customer demand?

- Which products may require better pricing or marketing strategies?

-Are there products receiving many reviews but having average ratings?

Using the Quartile Formula i was able to comeup with a review category and I used the formula =IF(OR(H2="",H2="Unknown"),"", IF(H2<=Review_Q1,"Low Reviews", IF(H2<=Review_Q3,"Medium Reviews","High Reviews"))) to highlight the three tiers respectively hence allowing me to see the products being asked.

Hence deducing yes.

-Are there products with high discounts but low customer engagement?
Yes.

Dashboard Design.

Business Insights and Recommendations

At the end of this project, the following insights were drawn from the analysis:

1. Are higher discounts leading to higher customer engagement?
Not significantly. The correlation between Discount and Review count was -0.1368, indicating a weak negative relationship.This suggests that discounting alone is not an effective driver of customer engagement on this platform.

2. Do highly rated products have higher or lower prices?
There is only a very weak positive relationship, with a correlation of 0.1100 between Price and Rating. Ratings appear to be driven more by product quality or customer experience than by price point alone.

3. Which products are performing best based on reviews and ratings?

4. Which products may need improved pricing strategies?
Products which have weak engagements.

5. What recommendations would you give to Jumia sellers?

Don't rely on discounting alone to drive engagement. Since discount percentage and review count showed almost no meaningful relationship, sellers should invest in other areas rather than assuming bigger discounts will attract more attention.
Price is not a strong signal of quality perception. Moderately priced products can achieve ratings just as high as expensive ones, so sellers should not assume that premium pricing alone builds customer trust.
Focus on products with Weak Engagement. Sellers should investigate why these products are not attracting reviews or interest as this represents the biggest opportunity for improvement.
Encourage more customer reviews overall. Since review counts were generally low across most products, sellers could benefit from actively prompting satisfied customers to leave feedback, as review volume does not appear to be strongly tied to any single factor in this dataset.

GitHub repository link: https://github.com/wainainagabriel63-dev/Jumia-Product-Performance-Dashboard

My First GitHub Project: From a Local Folder to GitHub Using Git and SSH.

Gabriel Wainaina — Sun, 23 Aug 2026 06:37:37 +0000

Introduction

The following is a step-by-step process of how i was able create my first GitHub project from a local folder and upload it to Github using Git and SSH.

Creating a local folder using git.

Using Gitbash, I wanted to see the list of files and directories on my pc. I used the command function ls which enabled me to see them.
Since I wanted to create a folder in Desktop under OneDrive, I used firstly cd OneDrive which allowed me to access OneDrive then used cd Desktop allowing me to access Desktop. Basically the command function cd (change of directory) allows one to access a directory that is required while Using cd .. allows one to go to the previous directory.
Using mkdir Kenya-Hospital-Health-Records-Project I was able to create a folder under the name Kenya-Hospital-Health-Records-Project. We can use hyphens, underscores and quotes in names that contain more than one word because inclusion of spaces will create separate folders instead of the required one folder.
Using cd Kenya-Hospital-Health-Records-Project to access our folder, I created another folder inside it called data using mkdir data.

Creating a README file

I created Readme file using touch README.md where .md denotes mean Markdown. A Readme file would be able to explain the context of our project on Github and to do this we use the command function echo to print text.
I used echo "# KENYA HEALTH RECORDS ANALYSIS" > README.md to indicate title of the README file and using # to indicate it is as the main title.
Using echo "## Project Overview" >> README.md I was able to mark it as a sub heading. using > instead of >> would have simply replaced our Tittle an used the subtitle as the main tittle hence I used >> to simply add and not replace.
To see the contents we have written so far we use cat README.md.
I also incorporated nano README.md to edit some parts where i saw fit to edit.

Initializing git

Then I used git init which is used to treat the folder as a git repository creating a hidden folder. To see the hidden files the command function ls -la is used.
I used the function git status to see the untracked files (these are files which exist but git hasn't been instructed to add them).

Committing and pushing

I then prepared changes for the next commit by using git add . followed by git status to confirm it is ready for commitment.
git commit -m "add Kenya Hospital Analysis Project" to save changes to the commit. The message in quotes indicates what changes I made.
Then I used git log --oneline to see the commit history.
A side note was the command function git branch -M main which would have been used if the output did not have main to it.
I further ran git remote add origin SSH where the SSH is on Github and its content is copy pasted directly after the origin.
Then used the command git remote -v to confirm the remote URL was added correctly and lastly git push -u origin main to upload it on Github.