DEV Community: Esther Karanja

Understanding Business Metrics for data analysis.

Esther Karanja — Thu, 01 Oct 2026 10:43:20 +0000

Introduction

Breaking into data analytics isn’t just about running SQL queries or building dashboards. To stand out, you also need to understand the language of business.
Every organization from retail,healthcare to tech and beyond runs on metrics. They shape strategy, guide decisions and define success.
When you can speak to those metrics with confidence, you instantly become more credible as an analyst.

Metrics are basically anything you can track as long as it is in quantitative value then its a metric.

Key Perfomance Indicators(KPIs) are the metrics that actually matter.
How do i differentiate?ask yourself would management really care about this metric?if yes then its a KPI.Should have around 5-10 KPIs.

Objective Key Reports(OKRs)-this is basically setting the direction and concrete projections of the company or business eg.we want to hit 100M profit by December,reduce expenditure etc.
At a company there may be 3-5 main OKRs that get circulated across all departments because they are top-line priorities that leadership wants everyone to align themselves on.

Conclusion

Which metrics to choose?ask yourself:

Which sector are we in?Finance,IT,Health etc since they may have different KPIs.
Can we influence this metric?
Does this metric geniunely affect perfomance

A Comprehensive Data Analysis For J Cars Logistics.

Esther Karanja — Wed, 30 Sep 2026 11:18:44 +0000

Introduction

JCARS Logistics imports,sells and delivers vehicles to customers across different regions in Kenya.
To help management track business growth and perfomance,i built a power bi analytics solutions.
This article breaks down how raw data was transformed into revenue,profits and insights using data modelling and DAX.

Objective

My objective is to develop a solution that enables J cars logistics management to understand the performance of the business and investigate factors contributing to the performance.
The data that was collected is below.

I started by cleaning the dataset inside the power query

Cleaned dateset inside powerquery

Referenced the flat table inorder to get dimension tables and a fact table
I connected the fact table to the dimension table by generating a primary key for each dimension table and merging it to the fact table(foreign key).

Star schema

Data Analysis Expressions (DAX)

In J Cars and Logistics data,after cleaning,in the power query i used DAX to transform raw transactional records into financial KPIs and breakdowns. By establishing measures across my star schema connecting sales facts with customer, regional and sales representative dimension tables.
Some of the dax functions i used are:
Total Units Sold: Sums all transactions of quantities across regions.

Total Units Sold = SUM(Fact[Unit Count])

Total Revenue: Aggregates gross sales value across all vehicle transactions to yield KSh. 1.35 Billion.

Total Revenue = SUM(Fact[Revenue])

Gross Profit: Evaluates actual earnings by subtracting unit cost from total revenue, resulting in KSh. 515.45 Million

Gross Profit = [Total Revenue] - SUM(Fact[Unit Cost])

Gross Margin Percentage: Utilizes the error-safe DIVIDE function to determine profitability margin without risking divide-by-zero errors when handling zero-revenue filters.

Gross Margin % = DIVIDE([Gross Profit], [Total Revenue], 0)
Resulting in a consistent business margin of 30% or 0.30 across current sales operations

Regional Revenue Analysis: Evaluates specific regional contributions, such as isolating the Eastern region's KSh. 151.36M revenue share within trend lines.

Eastern Revenue = CALCULATE([Total Revenue], Region[Region Name] = "Eastern")

My dashboard

Business Insights

Total Revenue stands at 1.35 Billion from 466 units sold.The gross profit is at 515.45 Million delivering a gross margin % of 30%.
The Rift valley generates the highest volume of units sold,followed n=by Western,cental and Nyanza Regions
Lower Volume markets include Nairobi,Coast and Eastern Regions by showing lower unit sales relative to the top perfoming regions.The Unknown region category also recorded minor traces of sales.
Higher perfoming sales representativees include Aisha Mohammed (sold 6 units of Axio),Grace Njeri selling 4 units of 320i and Brian Otieno selling 2 units of 320i.
Faith Achieng,Kevin Mwangi,Macy Atieno and Samuel Mutua each registered individual sales for models such as 320i.
The total sum pf units sold was 466 units across all models,regions and sales representatives.

Challenges Faced

Regional Performance Disparity- Strong concentration of sales in Rift Valley and Western highlights an underperformance or untapped potential in key major urban hubs like Nairobi. - Data Quality & Tracking Issues: The presence of an "Unknown" region entry suggests data entry gaps or unassigned sales locations in the source data.
Revenue heavily dependant on a single dominant vehicle eg. Toyota drive a significant portion of representative sales, while other brands along the bottom chart eg Mazda, Mercedes-Benz, Mitsubishi,Honda,Subaru, show flat or very low individual counts in comparison. - Dependence on Specific Sales Representatives: Volume is heavily dependent on a few key sales representatives (e.g., Aisha Mohamed and Grace Njeri)therefore posing potential operational risks if staff turnover occurs.

Assumptions

- Data Completeness: The dataset represents the entire operational transaction history within the defined reporting period (no unrecorded offline transactions).

- Region Categorization: Sales are assigned to regions based on customer location or vehicle delivery points rather than dealership headquarters.

Product Classification: Each unit sold corresponds to a single vehicle unit across the listed makes and models.

- Uniform Market Availability: Vehicle inventory and model availability availability were evenly distributed across regions during the reporting period.

Conclusion

J Cars and Logistics demonstrates strong performance in key regions like Rift Valley and Western, backed by consistent contributions from top sales reps. However,to drive sustained growth, the business needs to address regional disparities—particularly by boosting sales in major regions like Nairobi and clean up reporting anomalies like the "Unknown" region. Standardizing sales strategies across all representatives and balancing model availability across all brands will help convert overall unit volume into long term market leadership and profits.
Click here to access the full power bi report file on my github repository

Data Modelling,Relationships and Joins.

Esther Karanja — Sun, 20 Sep 2026 08:13:03 +0000

Introduction

Data modelling is the process of defining tables and establishing their relationships.The benefits of data modelling is that it simplifies data analysis expressions,speeds up query perfomance and reduces complexicity.
Flat table is a single table containing all dimensions and facts. Combines all of your data into one place containing all attributes

It is suitable for a small,simple dataset where data has few rows.
Advantages of a flat table
It is easy to understand
Appropriate for small databases
Easy to import and analyze.
Requires few or no relationships
Disadvantages include
Creates data redudancy-where the same customer,product or category information may be repeated many times.
The table may become very wide and difficult to maintain as the dataset grows.

Star schema - centralized fact table surrounded by single dimension tables.
It organizes data into two distinct table types to form a star like structure.

An example of a star schema above,we can see the fact table being sales table and the rest being dimensional tables.
Advantages
Fast performance
Simple DAX formulas and clear pathways.
_Disadvantages
_
Redudancy within dimensions since dimension tables are denormalized
Requires light upfront preparation since raw data does not come arranged as a star schema.
Intergrating data measured at different detail into a star schema requires creating separate fact tables or careful model design.

A star schema is suitable for systems with large volumes of transactional data where performance and file size matter.
I recommend a star schema with one to many cardinalities and single direction filtering.

Snowflake schema-normalized extension of a star schema where dimension tables is normalized and connects to other dimension tables.
Advantages :
Easier to update attributes in one place.
Saves storage in relational database
Reduces duplicates dimensional values.
Disadvantages : increase DAX complexicity as it requires multiple joins steps.
Degrades power bi performance due to chain filtering and relationships.

The difference between star and snowflake schema is that Snowflake schema data is split into sub-dimensional tables to eliminate duplicate data therefore expanding hierarchical branches while the star schema each dimension is a single,flat table connected directly to the fact table.

Fact tables and dimension tables
Fact tables -contains mostly keys,dates and numerical metrics eg patients_id,doctors_id,procedure_id

Dimension tables- contains descriptive attributes used to slice,dice,filter and group facts.It mostly describes and gives more of the context around the facts

Granularity-The exact level of detail representation by a singe row in afact table eg individual item line vs daily total transactional summary.Data Granuality is the level of detail shown in my report/how far i can aggregate my data.

Key entities:
Facts:fact sales,fact orders,fact inventory
Dimensions:dimcustomer,dimproduct,dim location

Cardinalities in data modelling/creating relationships-this refers to the raw data in one table in relation to data in another table based on numerical count of matching rows.

Many to one(:1) this relationship occurs mostly when connecting from the fact table to the dimension table. The foreign key in the fact table connecting with the primary key in the dimension table.
One to many(1:)Primary key in the dimension table connects to foreign key in fact table.
One to one(1 : 1)connected using one primary key in the fact table to another primary key in the dimensional table or vice versa.
Many to many(* : *)used when bridge tables or direct relationships handle non unique keys on both sides.eg a foreign key and another foreign key.

Primary Key-is the main unique column identifier for each row in a table,it is exactly one per table eg customer id in dimcustomerexample C100)
Unique keys-ensures values in a secondary column are unique across all rows eg emailaddress,dimcustomers.
Foreign key non unique identifier column in a fact table linking back to a dimension.
Referential integrity-ensuring foreign key values in the fact table exist in the referenced dimension table.
Active vs inactive-only active filter path can exist between two tables at a time.

We activate relationships on the home tab>manage relationships>status>active

Inactive paths are activated using DAX functions like USERLATIONSHIP()and are dotted lines.

Filter direction
Single direction-filter flows strictly from the one side dimensionn to the many side(fact).This is the default.

Bi-directional(Both)-filters flow in both directions across tables.

Some disadvantages of bi-directional filters are that they may cause perfomance drops,ambigous filtering paths and unexpected visual results.

Joins in power query

select the join kind and click ok

Left outer-Keeps all rows from left table or table 1 and matching rows from table two.
Right Outer -keeps all rows from the right table or table two and the matching rows from table one.
Full outer-keeps all rows from both tables,combining matches and leaving nulls for non matches.
Inner-keeps only rows where the join keys match in both tables
Left anti-Keeps rows from table 1 that have no match in table two.Great to use for finding missing records.
Right anti-Keeps rows from table two and those that have no match in table one.

MERGE AND APPEND IN POWER QUERY.
Merge-this is basically physically combining two physical tables into a single wide table.

When to merge?we merge when combining staging lookup tables before loading eg merging product subcategory into product category

Append-We do this by stacking rows of tables on top of each other.This makes the table longer.The condition is that the table must have same exact number of columns and be the same type of columns.

append command below merge queries is used to stack or combine rows together,rows with exact number columns.

Conclusion

Understanding data modelling,relationships and joins is the foundation of an effective data analysis.
Understanding the flat tables and relationships simplifies my DAX functions and power query which speeds up the query perfomance. Understanding the right join type to use and establishing a clear cardinality makes it easier to model my data.

Jumia Product Performance and Analysis.

Esther Karanja — Sun, 06 Sep 2026 00:14:28 +0000

Introduction

Jumia is one of Africa's leading e-commerce platform that manages millions of transcations with a diverse products from electronics,beauty products and many more categories.Therefore,tracking key perfomance indicators is essential for supply chain operations and profit optimization.

Objective

My project aim is to build an interactive excel dashboard using Jumia transactional data.I aim to convert disorganized data into an interface that can help in decison making,identify trends and monitor products.

Dataset description

Data Cleaning and preparation process

Raw data mostly contains inconsistency and errors that may occur that may interfere or give the wrong output.

An example of a raw dataset

In the example above we can see inconsistent and missing data that we need clean in order to have an effective output.
First step is to format the prices from text to currency format and replace the before since excel will in order to calculate the discount eg below

The image above is the discount price which was obtained by finding the difference between the old price and the new price.

The image below is an example of the formula to categorize the prices whether high,low or medium.I used the IF,AND functions.
The difference when using the IF(AND function is that all the conditions must be met while in the IF(OR,only one condition has to be met.

In the image below i used IF and ANDfor the discount category.

In the image below i also used the IF AND functions in the ratings category.

After removing duplicates,removing inconsistent data eg texts in numbers columns.Below is an image of the cleaned version of the Jumia dataset.

An example of a clean dataset

Descriptive Analysis

To calculate the average current price of products i used the average formula and highlighted the cells eg =AVERAGE(B2:B113).The average old price of products was obtained by the same formula but now on the old price column =AVERAGE(D2:D113)

To calculate the number of product since they are in text format we will use =COUNTA(A2:A113)

To calculate the average product ratings we will use =AVERAGE(

We use the count function to calculate the total number of reviews.

We will use the MIN() and MAX()Formulas to calculate the least expensive product and most expensive product respectively.

Some of my pivot tables and charts include:

Slicers are interactive buttons to filter data used and to display the filters being applied.

Examples of slicers i will use are above.

Dashboard Analysis
My dashboard that had the following KPIs(KPIs translates and evaluates data for better understanding)

Key Insights
High discounts are not translating into higher customers satisfaction.

Poor rating dominates the donut chart at 60% while average accounts for 28% and excellent represents just 12%.eventhough the average rating is at 3.89 the volume of negative reviews show a potential of customers not being satisfied with the products.

The discount strategy seems to be leverage used to drive products interest to the customers.eg Over half of the catalogue falls under high discount while 32 products sits in medium discount range and only 19 products have low discounts.

My dashboard

Connecting slicers to multiple tables,charts and pivot tables

Conclusion

This case study has explored how ratings,reviews,prices and large discounts do not guarantee for major sales instead,for a platform like Jumia, consistent product quality and customers values are more important.
This is the link to this project on github(https://github.com/muthoni-ess/Jumia-Excel-Dashboard.git)

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

Esther Karanja — Sat, 29 Aug 2026 11:41:27 +0000

Introduction

Excel is used in our day to day lives.
Excel is a microsoft spreadsheet used to store,organize and analyze data.
A row is horizontal that is from left to right while a column is vertical that is top to bottom.
A cell is an intersection of a row and a column.
Rows, columns and cells are contained in a worksheet.
One or more worksheets form a workbook

excel ribbon main components

Tabs-this are the headers on top ie File,Insert,Page layout,Formulas,Data,Review and view In each tab there are subsections,dropdowns theres also a dialougue launcher which is a small arrow at the bottom right corner that opens advanced settings panels.

A sample excel worksheet interface.

Common terms we may hear a lot:

Formatting this is just changing the visual appearance of data in a spreadsheet to make it easier to manipulate data and understand.
Sorting data this is basically arranging the text entries from A to Z or numerical values eg sales, arranging them form largest to smallest
Formulas all formulas start with the = sign to tell excel that the following is a calculation not a text.
Functions this are operations prebuilt in excel thet make calculations easy eg SUM,AVERAGE,VLOOKUP
Cell Referencing this shows the cells highligted or containing data eg A1,B2:B20)

What is Data Cleaning?

This is the process of removing inacurate,duplicate informations,blanks and any errors that may appear inorder for data analysis and reporting to be clear,accurate,seamless and easy'
Why is cleaning data important because we may experience some of the common issues like:

Duplicates-we basically get rid of duplicates by selecting all,clicking on the ribbon named data and removing duplicates or one could click on the column one wants to remove duplicates,under home ribbon,conditional formatting select remove duplicates or give conditions
Spelling errors-Select all,under review ribbon,spellings
Inconsistent spacing-use the TRIM command =TRIM()
Wrong formatting eg numbers formatted as text-select the column,under the home ribbon,select number format as required eg if its date,use the short date if its salary use accounting format,numbers under general etc or different date format,under the data ribbon,select text to columns and fill the date format you want to use eg DMY,YMD etc
Messy borders and blanks-to get rid of the blanks,ctrl G(go to shortcut) and click on special,type the replacement value,press ctrl enter it will replace blanks with the values i types eg 0,N/Aor Missing.
Text not standardized eg under name columns instead of Esther Karanja displays eStheR KaRanja.We use the formula =UPPER()will return ESTHER KARANJA uppercase.
We can use =LOWER()for all names to be in small case and =PROPER()for all the names in the normal case

After cleaning the data properly one can create a table ctrl T or under insert ribbon create table.

Conclusion

This week i have realized that cleaning data is a very important in the data analysis process and i should be keen and ensure everything is in the right format,no blanks,no redudant data,no spelling errors,spacing errors and many more errors that may be overlooked.

Understanding the Git Workflow:Working directory,staging ,commit and push.

Esther Karanja — Fri, 21 Aug 2026 22:22:18 +0000

Introduction

Understanding git has become easier for me when broken down into this four stages.
The working directory is where we write code,amend and delete files,here changes are made but can not be tracked unless they are instructed to commit.
The staging phase is an area where files are modified.
The commit phase is where git takes everything from the staging area and sends it to our local repository,
The push phase is where the saved commits are sent to a remote repository like github.

What is Git?

Git is a version control system or tool used to track changes by developers.
When one installs git it comes with an inbuilt terminal called gitbash

Understanding how Git works.

We start by installing git on my Pc, after installation is done one can check if git is installed by opening a terminal eg powershell on windows and run git --version

How to create folders and files on git bash

First identify where we want the folder to be located
lsis used to list
mkdir "name of the folder" (means make directory)cd " name of the folder"(change directory)

Readme texts README.mdend with .md since they are written using markdown language.Can use echo,touch or nano commands to write a readme file.
If i want to know the contents of my readme file we use:cat README.md

git config-this is basically telling it my identity
git config --user.name"Esther Karanja"
git config --user.email "my email"
git init-this command is used to create or initialize a repository in main/master.
git init main
git status-shows the repository status. This command shows changes and what is happening in git
git status
git add-stages changes made
git add .this means stage all or one can specify what to be added e.g i want to add only a javascript folder git add script.js
git commit-commits records that have been staged in the local git repository.Its like getting a snapshot or memory of the file.
git commit -m "write a commit message"
git log-shows a history of all the commits that were made.
git log

git branch should return main

To upload my git folder on github:
Open the github account,create a new repository,click on ssh and copy the link
git remote add origin "paste the ssh key link"

git remote -v

git push-this command to send commits to the remote repository git push -u origin main Git will send the files to my github repository,i can refresh my github and see them

What is github?

Github is a cloud based platform for storing git repositories online.
Just sign up for free,verify via email and your account is created.
git and github are connected using a SSH KEY.

What is a SSH KEY?

SSH stands for secure shell which is a protocol that allows a computer to communicate with another computer over a network.

So how do i generate an SSH key?
ssh-keygen -t ed25519 -C "email signed on github.com"and press enter, the passphase(password authentication)is optional.
This command will generate two keys:example
ed25519
ed25519.pub
The one with .pub is the public key which is the one that we share with github while the other one is a private key which should not be shared.

How to add the SSH Key to Github-open my github account,on settings click on SSH and GPG keys, create a new key and copy the public key that we generated.
How to test the SSH connection
Test before pushing a project using ssh -T git@github.com

In conclusion,i can say that using git and github is easier than i thought,the commands are self explanatory.Basically,i just create my project locally,initialize git,connect my github account and git together using the SSH keys, create a repository,commit and push them to my github account

DEV Community: Esther Karanja

Understanding Business Metrics for data analysis.

Introduction

Conclusion

A Comprehensive Data Analysis For J Cars Logistics.

Introduction

Objective

Data Analysis Expressions (DAX)

Business Insights

Challenges Faced

​Assumptions

Conclusion

Data Modelling,Relationships and Joins.

Introduction

Conclusion

Jumia Product Performance and Analysis.

Introduction

Objective

Dataset description

Data Cleaning and preparation process

Descriptive Analysis

Conclusion

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

Introduction

Common terms we may hear a lot:

What is Data Cleaning?

Conclusion

Understanding the Git Workflow:Working directory,staging ,commit and push.

Introduction

What is Git?

Understanding how Git works.

How to create folders and files on git bash

What is github?

What is a SSH KEY?

Assumptions