Forem: Kaira Kelvin.

Putting it All together: Dashboard Design Tips

Kaira Kelvin. — Thu, 18 Dec 2025 07:11:53 +0000

A complete guide that will help BI developers create Powerful dashboards.
At the top, we can see KPI cards, pie chart and slicers in tile form are scattered around. It seems that elements with different purposes are visually together on the same level and we perceive them together ,which can be confusing.
The table seems to be placed there at the last moment, roughly compressing the column chart whose labels become rotated and dispro-portionately compressing the line chart showing sales dynamics.
The visuals have different proportions,backgrounds and borders, with uneven spacing between them thus creating a sense of chaos.
The Visuals also shows lack of organization, a failure to separate elements into logical blocks.
To Fix this we will get a version where the same visualization are arranged in a logical structure from general to specific look at the image below. It is quite sufficient for corporate reporting.

Making the same dashboard more attractive.

Change the standard cards to new ones with category labels at the top and align all elements to the left edge.
Add the planned value to the first card.
Apply conditional formatting to the deviation indicator in the card.
Apply the same font style to all the charts and the table.
While working with the table: the header in the previous version(the table header is not the same as the title).
Applied the conditional formatting to the cell background for the average price indicator to highlight the highest prices; formatting minimum values with a different background color.
Set the background of the main part or set it to the background color of the block.
Also add a separator line for the header row and totals.
Align all the totals in all the columns exactly below the values (performed in the specific column setting for each indicator separately)
Finally the set the background for the page.
Place a block shape below the header row to highlight it against the background.
After Finalizing on those we get this dashboard

The dashboard is divided into several zones, each designated for specific purposes and charts.

a. Header(level zero)

This includes the title, navigation buttons and individual slicers( or a button to invoke the slicer panel)
The header can also contain the company logo, report generation date, and other common elements related to the entire dashboard.

b. The first level -KPIS

Here the cards are placed. The rule is that we always start the dashboard with card at the top and only cards.
Remember that the uses will need to pay attention to the most important information(main KPIs) first and add details to the KPIs

c. The Second level- Visulaized subtotals,trends

Below the cards on the upper half of the working area.The high level visualizations, categorized by items such as regions and timelines.These visuals also serve as filters and clicking on a column reveals details about it on the next level.

d. The Third level - Details

In this section we present more granular data usually in tables with conditional formatting.

Below is a dashboard information level scheme that guides on how to create a good dashboard.

Steps-by-step Guide for a dashboard.

Step 1: Place KPI cards on Top

It may seem obvious but not everyone adheres to it. I recommend placing no more than six cards at the top as they are key indicators. Do not overload the section , like the image below shows.

Instead have the KPIs follow the rule below ...

Step Two Define Dashboard Grid.

The workspace refers to the page area below the cards, where the tables and charts do sit.The grid is the division of the workspace into blocks(modules) where we place visual elements.
A symmetric grid should be achieved. The Image below

The most second popular grid for a dashboard consists of six modules which we call the 3X2 meaning three columns plus two rows.The image below shows the grid layouts

The image below shows an example of a 3X2 grid.The visual shows three visual elements in its workspace but the chart on the left the sales plan execution chart. The chart is well stretched vertically and occupies two modules while the sales dynamic chart and the table on the right are stretched horizontally and each occupy two modules.
The KPIs cards are also well postioned and grouped into three blocks, they are positioned according to the modular grid and aligned relative to the charts below them.

Step three Adjust the Page Background.

Power BI is not a tool for advanced UI design it is for data and visualization.

Customize the color Theme.

Under the View tab you will find the various premade color theme options. When you click one of them, all the visuals elements on all the report pages will automaticlly adjust to match the selected theme.
Remember the company/corporate colors(brand book) are often used as the working palette.

I conclude the dashboard assembly.

Analyzing Machine downtime

Kaira Kelvin. — Thu, 30 Oct 2025 15:23:52 +0000

Introduction

In Manufacturing,keeping machines running efficiently is essential for meeting production targets and maintaining day to day operations. By analyzing operational data, manufactures can identify patterns that lead to downtime and take proactive steps to prevent it.
I focused on exploring and understanding machine performance data to uncover insights about what factors contribute to downtime.

overview.

The company operates three different machines on its shop floor that produce different sized components, so minimizing the downtime of these machines is vital for meeting production deadlines.

💾 The data

The company has stored the machine operating data in a single table, av Each row in the table represents the operational data for a single machine on a given day:
"Date" - the date the reading was taken on.
"Machine_ID" - the unique identifier of the machine being read.
"Assembly_Line_No" - the unique identifier of the assembly line the machine is located on.
"Hydraulic_Pressure(bar)", "Coolant_Pressure(bar)", and "Air_System_Pressure(bar)" - pressure measurements at different points in the machine.
"Coolant_Temperature", "Hydraulic_Oil_Temperature", and "Spindle_Bearing_Temperature" - temperature measurements (in Celsius) at different points in the machine.
Spindle_Vibration, "Tool_Vibration", and "Spindle_Speed(RPM)" - vibration (measured in micrometers) and rotational speed measurements for the spindle and tool.
Voltage(volts) - the voltage supplied to the machine.
Torque(Nm) - the torque being generated by the machine.
Cutting(KN) - the cutting force of the tool.
Downtime - an indicator of whether the machine was down or not on the given day.

Primary Objectives.

What is the first and last date readings were taken on?
What is the average Torque?
Which assembly line has the highest readings of machine downtime?
Explore correlations between the various operational data in the dataset.
Do you see a pattern in machine downtime over time?
Which factors (visually) seem to be connected to machine downtime?

Findings and insights

✅ Average spindle speed across all machines

select avg(spindle_speed_rpm)::numeric(10,2) as avg_spindle_speed
from machine_downtime;

✅ total machines in the industry

select count(distinct machine_id) from machine_downtime;

✅What is the first and last date readings were taken on?

select min(recorded_date) as first_date, max(recorded_date) as last_date
from machine_downtime;

✅ What is the average Torque?

select (avg(torque_nm))::numeric(10,2) as average_torque
from machine_downtime;

✅ Which assembly line has the highest readings of machine downtime?

select assembly_line_no,count(downtime) as total_downtime 
from machine_downtime
where downtime= 'Machine_Failure'
group by assembly_line_no
order by total_downtime desc;

select assembly_line_no,machine_id,count(downtime) as total_downtime 
from machine_downtime
where downtime= 'Machine_Failure'
group by assembly_line_no, machine_id 
order by total_downtime desc;

💡 Understanding Customer Churn Through SQL: A Data-Driven Exploration

Kaira Kelvin. — Thu, 30 Oct 2025 10:23:41 +0000

Introduction.

In today's competitive telecom market,customer retention is everything. Churn the rate at which customers stop using a service can make or break profitability.
I will utilize SQL to explore a dataset of telecom customers,analyze churn patterns and uncover key insights that can help businesses take action.

Overview.

The dataset is from a telecom company databel and i used it to investigate customer churn.

💾 The data
The data consists of four parts:

Customer status: the status and reason why customers churned
Demographics: demographic data on each customer
Contract information: information on the type of contract
Subscription types & charges: numerical data on how the customer uses his subscription

Project Overview.

The project was done entirely using PostgreSQL,focusing on data cleaning, transformation, and exploratory queries to answer specific business questions, including:

1️⃣ What is the average churn rate?

2️⃣ What are the top reasons for churn?

3️⃣ Do data usage and unlimited plans affect churn?

4️⃣ Are there demographic differences in churn (gender, age)?

5️⃣ How do contract types and payment methods influence customer retention?

🧹 Step 1: Data Cleaning and Preparation.

The first step was to prepare the raw dataset. Many column names were inconsistent they contained spaces,uppercase letters) I renamed them using snake_case for easier querying.

ALTER TABLE databel RENAME COLUMN "Customer ID" TO customer_id;
ALTER TABLE databel RENAME COLUMN "Churn Label" TO churn_label;
ALTER TABLE databel RENAME COLUMN "Account Length (in months)" TO account_length_months;

I repeated this for all the columns, ensuring the dataset was clean and consistent.
I handled missing values in churn_reason by replacing blanks with 'not defined'

UPDATE databel
SET churn_reason ='not defined'
WHERE churn_reason = '';

📊 Step 2: Calculating the Average Churn Rate

To understand the performance , I computed the average churn rate

SELECT 
    AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS avg_churn
FROM databel;

it shows the percentage of customers who left the company - a key health indicator for any telecom.

🔍 Step 3: Identifying Key Churn Reasons

To reveal the top churn drivers such as poor customer service,high data charges and lack of network reliability. I used this formula

SELECT churn_reason, COUNT(churn_reason) AS reasons_for_count
FROM databel
GROUP BY churn_reason
ORDER BY reasons_for_count DESC
LIMIT 5;

📶 Step 4: Churn by Data Usage.

Finding: Customers with low data usage (≤ 3 GB) showed a higher churn rate — possibly due to underutilization or perceived lack of value.

SELECT usage_category,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS churned_proportion
FROM (
    SELECT *,
        CASE 
            WHEN avg_montly_gb_download <= 3 THEN 'low usage (<=3)'
            ELSE 'high usage (>3)'
        END AS usage_category
    FROM databel
) sub
GROUP BY usage_category;

🌐 Step 5: The Impact of Unlimited Data Plans.

SELECT usage_category,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS churned_proportion
FROM (
    SELECT *,
        CASE 
            WHEN avg_montly_gb_download <= 3 THEN 'low usage (<=3)'
            ELSE 'high usage (>3)'
        END AS usage_category
    FROM databel
    WHERE unlimited_data_plan = 'Yes'
) sub
GROUP BY usage_category;

Customers on unlimited data plans had significantly lower churn ,showing the retention power of flexible data offerings.

👥 Step 6: Demographic Analysis

By Gender.

SELECT gender,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS churned_proportion_per_gender
FROM databel
GROUP BY gender;

By Age Category.

SELECT age_category,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS avg_churn
FROM (
    SELECT *,
           CASE 
                WHEN age <= 30 THEN 'Youths'
                WHEN age BETWEEN 31 AND 50 THEN 'Adults'
                ELSE 'Seniors'
           END AS age_category
    FROM databel
) sub
GROUP BY age_category;

Younger customers (under 30) tended to churn more frequently.

💳 Step 7: Contract and Payment Insights.

By Contract Type

SELECT contract_type,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS churned_proportion
FROM databel
GROUP BY contract_type
ORDER BY churned_proportion DESC;

By Payment Method

SELECT payment_method,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS avg_churn
FROM databel
GROUP BY payment_method
ORDER BY avg_churn DESC;

Contracts with month to month plans and manual payment methods showed higher churn,indicating that automation and long -term contracts help retention.

🗺️ Step 8: Geographic Insights

SELECT state,
       AVG(CASE WHEN churn_label='Yes' THEN 1 ELSE 0 END)::NUMERIC(10,2) AS avg_churn_location
FROM databel
GROUP BY state
ORDER BY avg_churn_location DESC
LIMIT 2;

Key Takeaways

Low data users churn more -Possibly due to underutilization.
Unlimited Plans reduce churn - highlighting their customer loyalty effect.
Young Customers are more likely to leave - engagement programs could help
Contract type and payment methos are strong churn predictors
Location -based strategies could address regional retention challenges.

Recommendations to Reduce Churn:

Competitive Analysis: Investigate competitor offers and devices to develop more attractive packages.
Customer Support Training: Enhance support staff training to improve customer interactions.

Breaking Barriers: Understanding and Improving HIV Prevention Pill Use in Kenya.

Kaira Kelvin. — Wed, 29 Oct 2025 14:52:48 +0000

Introduction.

Kenya has made tremendous strides in making HIV preventive pills (often referred to as DP pills or PrEP) freely available across public and private clinics. Despite this accessibility, uptake and long-term adherence remain a pressing concern. While many individuals take the first dose, a significant number discontinue soon after the initial month.
This report explores behavioral patterns behind these trends and suggests targeted interventions to boost continuation rates and overall effectiveness of the HIV prevention program.

Who Is Using HIV Prevention Pills?

The majority of those who received the pills were young women, particularly in their teens to early thirties. This age group represents Kenya’s most sexually active demographic and therefore plays a critical role in HIV prevention strategies.
Counties such as Nairobi and Kisii recorded the highest distribution numbers, while male participation remained notably lower.

-Interestingly, the type of health facility—whether public, private, or community-based—did not significantly affect uptake rates. This suggests that accessibility is not the main barrier to initial adoption.

What Drives People to Start—and Stop—Taking the Pills?

Referrals Matter

Peer referrals and Voluntary Counseling and Testing (VCT) sites were strong drivers of initial interest. Many first-time users were encouraged by friends or transferred from VCT centers. However, despite high initial uptake from these sources, continuation rates dropped sharply after the first refill.

Early Drop-Off Rates

Every patient began with a 30-pill starter pack. The steepest decline occurred immediately after the first month—only a fraction returned for subsequent refills. By the sixth month, continuation was extremely low.

Health Factors: BMI, Blood Pressure, and STIs

BMI (Body Mass Index) showed little impact on adherence. Normal-weight individuals had the highest initial refills, but the trend evened out across all BMI groups over time.
Blood Pressure (BP) appeared inconsistent as a predictor—low BP correlated with early refills, while medium-to-high BP patients showed better long-term adherence.
STIs (Sexually Transmitted Infections) temporarily boosted adherence. Patients recently diagnosed with STIs demonstrated high initial motivation, which declined as symptoms improved.

The Power—and Limits—of Counseling

Counseling had one of the strongest early effects on adherence. Patients who received counseling before or during their first month were far more likely to return for refills. Unfortunately, without continued follow-up, motivation waned after the initial phase.

Key Insights

The data clearly show that behavioral factors, not clinical ones, play the biggest role in continuation rates. While health metrics such as BMI or BP had minimal long-term influence, motivation, support, and social referral networks had the greatest impact.
Low adherence can thus be viewed not as a medical issue, but as a behavioral challenge—one that requires ongoing engagement, not just a one-time intervention.

Recommended Interventions

Based on the analysis, the most effective strategy is targeted, ongoing counseling and follow-up support, particularly during the critical first month.
Introduce peer mentorship programs to sustain motivation beyond initial referrals.
Implement structured follow-ups via SMS, phone calls, or clinic visits.
Track patient feedback to better understand reasons for discontinuation.
If applied systematically, these interventions could increase initial uptake by 25% and continuation rates by up to 30%—a meaningful improvement in Kenya’s fight against HIV.

Budget Analysis Report — Wote County Watchdog FY 2019

Kaira Kelvin. — Wed, 29 Oct 2025 14:03:56 +0000

Overview

The wote County watchdog FY 2019 Budget Dashboard provides a detailed financial snapshot of the county's budget allocation,actual expenditures and variance (over or under budget ) across multiple departments and funds.

Purpose of the Dashboard

Which departments stayed within budget
Which ones overspent
How funds were distributed across different county projects

Findings and Insights

Amount Over Budget: ($325.12 million)
Number of Expenditures: 469,000
Largest Fund: General Fund (~$4 billion)
Department with Overspending: Wake County Public School System

Department Analysis

Each department is listed with a small symbol showing its budget status:

✅ Green = within budget
🚫 Red = over budget

The dashboard tells us that:
Wote County manages its big funds quite well.
Smaller or specialized departments might not be tracking budgets as closely.
Education spending needs better planning and review.
With over 469,000 expenditure entries, even small overruns can add up fast.

Recommendations

Audit over-budget departments - finding out why spending exceeded plans.
Link budgets to results - expecially for education and public services
Simplify expense tracking - make it easier for departments to update records.

Conclusion:

The Wote County FY 2019 Budget Dashboard shows both strength and challenge. While most departments stayed disciplined, the total overspending of $325 million reveals room for tighter control.

🧠 Customer Churn Analysis with Power BI — Understanding Why Customers Leave

Kaira Kelvin. — Wed, 29 Oct 2025 12:42:58 +0000

Introduction.

In today's competitive business world, customer retention is the new growth strategy. It's far cheaper to keep an existing customer than to acquire a new one -yet many companies struggle to understand why customers leave.
The project Customer Churn Analysis dives deep into real-world customer data using Power BI to uncover patterns,behaviors and reasons behind churn.

Project Purpose

The purpose of this project is to analyze customer churn patterns — understanding what drives customers to discontinue a service and how those insights can improve retention.

Primary Objectives.

Measure churn rate across various customer segments.
Identify major churn reasons (competitor influence, pricing, dissatisfaction, etc.).
Visualize churn by demographics — age, seniority, and usage habits.
Understand the impact of contracts and data plans on churn.

📊 Key Findings and Visual Insights

1️⃣ Overall Churn Overview

Total Customers: 6,687
Churned Customers: 1,796
Overall Churn Rate: 26.86%

2️⃣ Payment Methods and Churn Behavior

Customers using digital payments (Direct Debit, Credit Card) show better retention than those paying via paper checks — possibly due to convenience and automation.

3️⃣ Churn by Demographics.

Seniors are most likely to churn, potentially due to lower tech adoption, cost sensitivity, or customer support difficulties. Younger users (under 30) show more brand loyalty but may still switch for better deal.

4️⃣ Contract Type vs. Churn

Month-to-month customers make up more than half of all accounts and have the highest churn rate — suggesting that longer-term contracts can improve retention.

🔍 Insights Summary

From the dashboard insights, a few strong patterns emerge:

Competitor churn dominates — the company must match or beat offers to retain customers.
Senior customers are most at risk — they need dedicated support and simplified communication.
Month-to-month contracts are convenient but risky — loyalty incentives are essential.
Unlimited data doesn’t ensure satisfaction — perceived value and service quality matter more.
The first 12 months are crucial — early experience determines long-term commitment.
Customer service performance directly influences retention

Recommendations.

1️⃣ Build Loyalty Programs: Offer discounts or perks for customers renewing after one year.

2️⃣ Target Competitor Leavers: Analyze competitor offerings and develop retention-specific counterplans.

3️⃣ Monitor Early Churn Signals: Use predictive models to flag customers with short tenure or frequent service calls.

4️⃣ Improve Customer Support: Reduce response time, improve agent training, and use follow-up surveys.

5️⃣ Segment by Demographics: Tailor communication and offers to different age groups, especially seniors.

6️⃣ Encourage Long-Term Contracts: Provide bundles, free add-ons, or premium upgrades for multi-year signups.

Conclusion.

Every bar,pie and map in this dashboard tells a story - of loyalty, competition and opportunity. Customer churn is not just a number - it is a reflection of trust lost.

Analyzing Crime against women in India.

Kaira Kelvin. — Wed, 29 Oct 2025 11:35:15 +0000

About this project

Purpose:

The aim of this project is to analyze crime against women in India during the year 2022. By transforming raw crime statistics into visual insights through Power BI.

Objectives.

To identify the most common crime types affecting women.
To analyze the states with the highest and lowest number of reported cases.
To highlight correlations and patterns in gender-based violence.
To provide insights that can inform prevention and policy decisions.

Tools and Technologies used.

Power BI Desktop : for data cleaning, modelling and visualization.
Dataset : National Crime Records Bureau (NCRB) 2022 - Crimes Against Women.
Data Model : State-wise and crime-type-wise aggregation with calculated metrics such as crime rate per 100k population and percentage distribution.

Key Insights.

a.Overview.

Total crime cases Reported 445,000
States/UTs Covered: 35
Total Crime types: 20
Women Population: 686M
Overall Death Rate: 1.5%

b.Frequent Crimes Against Women

Reflecting a troubling global pattern, cruelty by husband/relatives stands out to be most persistent and underreported crimes against women, cutting across culture,economy and geography.

c.Insights Summary.
🔴 Uttar Pradesh consistently ranks highest in total reported cases, demanding urgent intervention.
❗ Domestic abuse is systemic: Found across every Indian state with alarming frequency.

Lakshwadeep city is the safe city in India that had no incidence of crime committed against women.
The Safe regions show that crime reduction is possible through social cohesion, education, and proactive governance.

Conclusion.

The Crime Analysis in India 2022 project paints a sobering but necessary picture of women’s safety in India. While progress has been made in reporting and awareness, the persistence of domestic abuse, dowry deaths, and crimes against young girls shows that gender-based violence is both a social and cultural challenge. Through data-driven storytelling and visual analytics, this project serves as both a mirror and a call to action — urging policymakers, citizens, and advocacy groups to turn insights into impact.

Fashion Analysis: Understanding Customer Churn in Fashion Industry

Kaira Kelvin. — Tue, 28 Oct 2025 15:17:26 +0000

Introduction

The fashion industry is growing daily and business owners need to understand customer behaviour to make profits and drive operations.The article delves into the fashion industry to understand customer churn.

🎯 Project Objective.

The primary objective of this analysis is to replace gut-based decisions with research-backed strategies to improve stock management and customer retention.The analysis aims to:

Understand consumer behavior - This includes customer preferences,purchase frequency and understanding customer demographics.
Analyzing inventory performance - identifying fast and slow moving items to optimize stock levels.
Analyzing sales trends over time - Determining peak sales days, product categories and seasonal variations.
Time -based analysis - examining how timing affects consumer engagements and purchases using the rolling 3-Month Sum.

Dataset Overview.

The dataset used was inserted using SQL using the function;

create table fashion (
    clothing_type VARCHAR(8),
    category VARCHAR(11),
    price DECIMAL(5,2),
    discount DECIMAL(4,2),
    shop_outlet VARCHAR(50),
    delivery_date DATE,
    order_date DATE,
    revenue DECIMAL(7,2),
    customer_name VARCHAR(50),
    customer_email VARCHAR(50)

Key Fields in the dataset:

Clothing_type--Brand of the cloth
Category- category according to men,women or child price
Discount - Discount offered on clothes
shop outlet- Various shops that customers bought their clothes.
delivery date- Date when the bought clothes reached clients
order date - Dates when ordered were made
revenue - Amount generated from the clothes sold
customer name - Customers first and last name customer email - Customers email address

Analysis and Findings:

🔵 To understand the consumers demographics we analyzed the top 10 customers by revenue and found that women and children clothes are the leading cloths type purchased a lot and leads in revenue generation.

Recommendation:

🟢 Shop oulets should consider discounting men's items to drive up sales , since there discount is directly related to sales.

Findings.

🔵 Analyzing shop outlets that had highest customer visits and made a purchase.

Brightbean recorded the highest number of visits among all shop outlets.

Brightbean also offered the highest discounts, which likely contributed to its higher foot traffic and engagement.

Interpretation.

There’s a positive correlation between the level of discounts offered and the number of visits.
Outlets providing more promotions and discounts attract significantly more customers.

Recommendations:

🟢 Replicate Brightbean’s Strategy: - Other outlets should adopt similar discount or promotional approaches (e.g., limited-time offers, loyalty discounts).

Use data-driven promotion scheduling to align offers with customer visit patterns.

🟢 Improve Outlet Visibility: - Increase online presence and share shop updates on Instagram, WhatsApp, and TikTok to reach a broader audience.

Sales Trends by Day:

🔵 Friday recorded the highest sales, making it the peak shopping day.
🔵 Wednesday had the lowest sales, showing reduced customer engagement mid-week.

Interpretation.

The spike in Friday sales reflects strong end-of-week consumer activity, possibly driven by payday spending or weekend preparation.

Recommendation.

🟢 Enhance Friday Promotions: - Launch “Friday Frenzy” or “Fresh Friday” sales to maintain and grow Friday momentum.
🟢 Boosting sales - use mid-week social media campaigns and personalized offers to attract attention.such as "Ladies Wednesday".
🟢 Marketing Implications: - Build pre-Friday hype through digital channels.

Time Based Analysis.

Findings.

🔵 Sales were highest on Fridays and Saturdays, showing that consumers prefer shopping toward the weekend.

Interpretation:

The end-of-week sales peak aligns with consumer behavior trends, as most shoppers have more free time or disposable income closer to weekends.

Recommendations:

🟢 Enhance Weekend Engagement:

Launch "Styling Saturdays" promotions to maximize weekend traffic.
Offer free outfit styling services or photo-tag discounts (e.g., “Take a pic in-store, tag us, and get 10% off your next visit”).

🚀 Call to Action.

AS I have analyzed and explored the financial performance of this project, its crystal clear that data driven decision- making is crucial for optimizing sales, driving the growth of the business and retaining happy customers.

If you found this analysis insightful, consider sharing it with business stakeholders and your network . Also you can reach out or leave a comment if you want to apply the same techniques on your data.

Electrifing insights with BI.

Kaira Kelvin. — Mon, 29 Sep 2025 09:40:31 +0000

Introduction.

Equipments such as computers and smartphones, are widely available today, and the global consumer electronics market generated an impressive $950 billion in revenue in 2024.
The dataset is from GlobalElec, a leading global electronics retailer.

⭐ Project overview.

The goal of this analysis was to uncover electronic sales patterns of across three continents.
I analyzed and visualize the company’s sales data, covering customers, products, sales, stores, and international exchange rates.

Dataset.

💾 The data

Group	Column name	Dataset	Definition
Customers	CustomerKey	Customers.csv	Primary key to identify customers
Customers	Gender	Customers.csv	Customer gender
Customers	Name	Customers.csv	Customer full name
Customers	City	Customers.csv	Customer city
Customers	State Code	Customers.csv	Customer state (abbreviated)
Customers	State	Customers.csv	Customer state (full)
Customers	Zip Code	Customers.csv	Customer zip code
Customers	Country	Customers.csv	Customer country
Customers	Continent	Customers.csv	Customer continent
Customers	Birthday	Customers.csv	Customer date of birth
Products	ProductKey	Products.csv	Primary key to identify products
Products	Product Name	Products.csv	Product name
Products	Brand	Products.csv	Product brand
Products	Color	Products.csv	Product color
Products	Unit Cost USD	Products.csv	Cost to produce the product in USD
Products	Unit Price USD	Products.csv	Product list price in USD
Products	SubcategoryKey	Products.csv	Key to identify product subcategories
Products	Subcategory	Products.csv	Product subcategory name
Products	CategoryKey	Products.csv	Key to identify product categories
Products	Category	Products.csv	Product category name
Sales	Order Number	Sales.csv	Unique ID for each order
Sales	Line Item	Sales.csv	Identifies individual products purchased
Sales	Order Date	Sales.csv	Date the order was placed
Sales	Delivery Date	Sales.csv	Date the order was delivered
Sales	CustomerKey	Sales.csv	Unique key identifying which customer ordered
Sales	StoreKey	Sales.csv	Unique key identifying which store processed
Sales	ProductKey	Sales.csv	Unique key identifying which product purchased
Sales	Quantity	Sales.csv	Number of items purchased
Sales	Currency Code	Sales.csv	Currency used to process the order
Stores	StoreKey	Stores.csv	Primary key to identify stores
Stores	Country	Stores.csv	Store country
Stores	State	Stores.csv	Store state
Stores	Square Meters	Stores.csv	Store footprint in square meters
Stores	Open Date	Stores.csv	Store open date
Exchange Rates	Date	Exchange_Rates.csv	Date
Exchange Rates	Currency	Exchange_Rates.csv	Currency code
Exchange Rates	Exchange	Exchange_Rates.csv	Exchange rate compared to USD

Data Model.

Questions to answer ?

1️⃣:Which countries or regions contribute the most to overall revenue?
✅ Toronto.
🌎 North America
2️⃣:Which product categories and regions yield the highest profit margins?
💻 Computer
3️⃣: Are there patterns or changes in delivery times across different regions or product categories?

Dashboards.

The product dashboard

📌 After data cleaning, the dataset comprised 11 product brands,16 products varieties, 8 categories of products, and 32 sub categories.

Insights

🔹 White products were the most sold.

🔹 Computers were the most profitable products.

sales dashboard

📌 Total revenue was 55.76M, total profit 33.66M, 26K orders made and the sales were done in 5 currencies.

Insights.

🔹 North America had the highest sales, February recorded the highest profit .
🔹 Female purchased more products than there counterparts male.
🔹 Clients transacted more frequently using USD.

Customer dashboard.

📌 After data cleaning and creating KPIs: the total number of customers who bought products at Globalelec were 15.3k and the came from 8 counties.

Insights.

🔹 Canada in North America had the highest number of customers.
🔹 Most customers were above 50 years old.

Tools Used

📈 Microsoft excel - for data cleaning
📊 Power Bi - for data visualization, dashboards

📝 Mastering Date/Time Extraction in Pandas: From Year to Weekend Check.

Kaira Kelvin. — Sun, 11 May 2025 04:10:39 +0000

Time series data is everywhere in stock prices, weather data,server logs, sales records ,website traffic per hour,machine logs per second. Pandas has tools for parsing,indexing,resampling and analyzing datasets.

Note - The .dt accessor works for period and timedelta dtypes.

1.Converting to Datetime.

The first process convert the date column to date time before doing the time series analysis.

df['date'] = pd.to_datetime(df['date'])

2.Extracting day from a column date

a.Having the output as 0,1,2,3...

df['day_of_week_num'] = df['date'].dt.weekday

df['day_of_week_num']: It extracts the numeric day of the week, where Monday is 0 and Sunday is 6.

b. Having the output as name Monday,tuesday,Wednesday,thursday

df['day_of_week_name'] = df['date'].dt.day_name()

It returns the name of the day for each date in the Series or DataFrame, such as "Monday", "Tuesday"

c. Abbreviated day name (e.g., "Mon", "Tue", "Wed")

df['day_of_week_abbr'] = df['date'].dt.strftime('%a')

This gives you a short form for the day name, which is often used when you want to save space or display compact labels.

3. Check for Weekend.

df['is_weekend'] = df['date'].dt.weekday >= 5

This create a new column in your DataFrame that flags whether a date falls on a weekend (Saturday or Sunday).

4. Extracting time attributes .

Here we will extract Hours,Minutes and seconds.

.dt.hour: - This Extracts the hour from the datetime (0–23).
.dt.minute: - It Extracts the minute from the datetime (0–59)
.dt.second: It Extracts the second from the datetime (0–59).

5. Extract Month (Number and Name).

df['month'] = df['date'].dt.month

Extracts the numeric month (1–12) from the date column.

df['month_name'] = df['date'].dt.month_name()

Extracts the full month name (e.g., "January", "February").

C. Three-letter abbreviation

df['month_abbr'] = df['month_name'].str[:3]

6.To Extract the quarter of the year from a datetime column use .dt.quarter

 df['quarter'] = df['date'].dt.quarter - 1

7.Half of the Year (0 = Jan–Jun, 1 = Jul–Dec)

df['half'] = np.where(df['month'] < 6, 0, 1)

It assumes your month column is zero-based (i.e., 0 = January, 11 = December).

Months 0–5 → First Half (0)
Months 6–11 → Second Half (1)

8 . Week of the Year (.dt.isocalendar().week)

df['week_of_year'] = df['date'].dt.isocalendar().week

Note: As of Pandas 1.1.0, .dt.week is deprecated and replaced by .dt.isocalendar().week.

Note.What is ISO Week?

ISO weeks follow the ISO 8601 standard:
Weeks start on Monday
Week 1 is the week that contains the first Thursday of the year
Can have 52 or 53 weeks.

9. Year.

df['year']=df['date'].dt.year

Extracts the year as an interger.
It's useful for grouping,filtering or feature engineering.
Splits time series data by year.
Groups data for annual trends.
Creates Pivot tables or plots year-wise

date	year
2022-04-05	2022
2023-11-12	2023
2024-01-01	2024

10. 📅 Day of the month.

This extracts the day of the month (1-31)

df['day_of_month'=df['date'].dt.day

date	day
2024-01-05	5
2024-01-15	15
2024-01-31	31

11. How to handle holidays in pandas

You have to manually define your own list of public holidays and pass them to customBusinessDay.

from pandas.tseries.offsets import CustomBusinessDay
 Kenya_holidays=pd.to_datetime([
   '2025-01-01',  # New Year's Day
   '2025-03-29',  # Good Friday
    '2025-05-01',  # Labour Day
    '2025-06-01',  # Madaraka Day
    '2025-10-20',  # Mashujaa Day
    '2025-12-12',  # Jamhuri Day
    '2025-12-25',  # Christmas
    '2025-12-26',  # Boxing Day
])

Create a custom businessDay (Holidays=kenya_holidays)

kenya_bday=CustomBusinessDay(holidays=kenya_holidays)

used to generate business days

dates=pd.date_range('2025-12-20','2025-12-31' freq=kenya_bday)

12. dt.period is an accessor in pandas used with datetime-like columns to convert them into period objects.

a.Calendar Frequencies.

This is an example of calendar frequency. df['period'] = df['date'].dt.to_period('M')

Frequency Code	Description	Example Output
`'D'`	Daily	`2023-05-11`
`'W'`	Weekly	`2023-05-08/2023-05-14`
`'M'`	Month End	`2023-05`
`'MS'`	Month Start	`2023-05`
`'Q'`	Quarter End	`2023Q2`
`'QS'`	Quarter Start	`2023Q2`
`'A'` or `'Y'`	Year End	`2023`
`'AS'` or `'YS'`	Year Start	`2023`

b.Business Frequencies.

They are special time intervals that consider only working days(Monday to Friday) and can exclude holidays if well defined.
Finance - No trading on weekends or holidays.
Payroll/HR - Payments and attendance only on working days.
Operations - Delivery and logistics exclude weekends. The key difference from calendar frequencies include.

Business Day Frequencies

Business Month Frequencies.

Get last business day of each month in 2024

bm_end = pd.date_range(start='2024-01-01', end='2024-12-31', freq='BM')
print(bm_end)

Output >>> DatetimeIndex(['2024-01-31', '2024-02-29', '2024-03-29', '2024-04-30', ...], dtype='datetime64[ns]', freq='BM')

-Business Quarter Frequencies.

Business Year Frequencies.

Cleaning data in PostgreSQL.

Kaira Kelvin. — Tue, 29 Apr 2025 09:30:28 +0000

Cleaning string data.

1a. Capitalization of strings eg people's names, cities, countries.

INITCAP(input_string) - fixing capitalization. To update the names of the whole column permanently use UPDATE eg

UPDATE customers
SET customer_name = INITCAP(customer_name);

1b. Renaming columns

When renaming columns:

Use the snake rule i.e machine_id;
Use lower case machine_id rather than MACHINE_ID
Do not include parenthesis() in naming columns for instance Spindle_Speed(RPM) it should be spindle_speed_rpm.

Benefits of renaming columns.

Easier automation and tooling : Many tools (like ORMs, data pipelines, or SQL formatters) expect lowercase, unquoted identifiers.

The two ways to rename columns include;

1️⃣ Rename Columns in a Query Result (use AS).

If you just want different column headings in the output, use an alias:

SELECT 
    first_name AS "First Name",
    last_name  AS "Last Name",
    salary * 12 AS "Annual Salary"
FROM employees;

This does not change the table structure—only the names in the result set.

2️⃣ Permanently Rename Columns in a Table.

If you need to actually change the column name in the database schema, use ALTER TABLE … RENAME COLUMN.

alter table databel
rename column"Avg Monthly GB Download" to avg_montly_gb_download;

1c. Stripping.

For instance removing $ from a column sales['Revenue'] = sales['Revenue'].str.strip('$')

2 a.Removing spaces in strings.

REPLACE(input_string, to_replace, replacement)

2b. Standardizing data using the .loc function
Finds all rows where tire_sizes is greater than 27 and sets those values to 27.

 ride_sharing.loc[ride_sharing['tire_sizes'] > 27, 'tire_sizes'] = 27

Set all in the future to today's date

ride_sharing.loc[ride_sharing['ride_dt'] > today, 'ride_dt'] = today

3.Makes strings have uniform length. LPAD(input_string,length[,fill_value]

SELECT 
    LPAD(event_id, 10, '0') as event_id, 
    -- Replace consecutive spaces with a single space
    REGEXP_REPLACE(INITCAP(parking_held), ' +', ' ','g')  as 
    parking_held
    FROM 
    film_permit;

4.Pattern matching using the regular expressions (REs)
- Basic of REs:

~ : Matches regex pattern (case-sensitive)
~* : Matches regex pattern (case-insensitive)
!~ : Does not match regex pattern (case-sensitive)
!~* : Does not match regex pattern (case-insensitive)
Regex Metacharacters

Metacharacter	Meaning	Example Query	Returns
`\d`	Digit (0–9)	`SELECT '123' ~ '\\d\\d\\d';`	`true`
`\w`	Word character (alphanumeric or underscore)	`SELECT 'abc_123' ~ '\\w+';`	`true`
`\s`	Whitespace character	`SELECT ' ' ~ '\\s';`	`true`
`.`	Any character except newline	`SELECT 'a9c' ~ 'a.c';`	`true`
`^`	Anchors to the beginning of a string	`SELECT 'Hello' ~ '^H';`	`true`
`$`	Anchors to the end of a string	`SELECT 'Hello' ~ 'o$';`	`true`
`*`	Matches 0 or more of the preceding character	`SELECT 'hoop' ~ 'ho*p';`	`true`
`+`	Matches 1 or more of the preceding character	`SELECT 'hoop' ~ 'ho+p';`	`true`
`?`	Matches 0 or 1 of the preceding character	`SELECT 'color' ~ 'colou?r';`	`true`
`[]`	Character class: match one of the characters	`SELECT 'a' ~ '[abc]';`	`true`
`[^]`	Negated character class: not in the set	`SELECT 'x' ~ '[^0-9]';`	`true`
`	`	Alternation (OR)	`SELECT 'dog' ~ 'cat
{% raw %}`()`	Grouping for subpatterns	`SELECT 'abab' ~ '(ab)+';`	`true`

Note: In SQL strings, backslashes (\) must be escaped, so you’ll often use double backslashes (\\d, \\s, etc.).

Filtering Rows with Regex

SELECT * FROM users
WHERE email ~* '^[a-z0-9._%+-]+@[a-z0-9.-]+\\.[a-z]{2,}$';

5.Using REGEXP_REPLACE() used to replace patterns in strings. - - Its similar to REPLACE() function.

Its general syntax is

REGEXP_REPLACE(source,pattern,replace,flags).

pattern- string pattern to match in source string.
replace- replacement string to use in place of the pattern.
flags- an optional string used to control matching. for instance

REGEXP_REPLACE(cars,'\d','_','g')

6.Matching similar strings
postgreSQl does not have function soundex first
u run create extension.
CREATE EXTENSION Fuzzystrmatch

     SOUNDEX(input_string)  -  4 character code

SELECT summons_number, vehicle_color
FROM parking_violation
WHERE
  -- Match SOUNDEX codes of vehicle_color and 'GRAY'
DIFFERENCE(vehicle_color, 'GRAY') = 4;

SELECT summons_number,vehicle_color,
    -- Include the DIFFERENCE() value for each color
    DIFFERENCE(vehicle_color, 'RED') AS "red",
    DIFFERENCE(vehicle_color, 'BLUE') AS "blue",
    DIFFERENCE(vehicle_color, 'YELLOW') AS "yellow"
FROM
    parking_violation
WHERE 
    (
        -- Condition records on DIFFERENCE() value of 4
        DIFFERENCE(vehicle_color, 'RED') = 4 OR
        DIFFERENCE(vehicle_color, 'BLUE') = 4 OR
        DIFFERENCE(vehicle_color, 'YELLOW') = 4
    )

SELECT 
    summons_number,
    vehicle_color,
    DIFFERENCE(vehicle_color, 'RED') AS "red",
    DIFFERENCE(vehicle_color, 'BLUE') AS "blue",
    DIFFERENCE(vehicle_color, 'YELLOW') AS "yellow"
FROM
    parking_violation
WHERE
    (
        DIFFERENCE(vehicle_color, 'RED') = 4 OR
        DIFFERENCE(vehicle_color, 'BLUE') = 4 OR
        DIFFERENCE(vehicle_color, 'YELLOW') = 4
    -- Exclude records with 'BL' and 'BLA' vehicle colors
    ) AND vehicle_color NOT SIMILAR TO 'BLA?'

7.Handling missing data.

Missing data can be caused by human error,systematic issues such as a failed replication process. Types of missing data. -Missing completely at random (MCAR) -Missing at Random (MAR) -Missing not at random(MNAR)

Representations for missing values :

Null (general)
'' - empty string (used for string columns)

Types of missing data

1️⃣ Missing Completely at Random (MCAR)

The data is missing for no reason at all.
No systematic relationship between missing data and other values.
Best kind of missing data - safe to remove or ignore.

2️⃣ Missing at Random (MAR)

For example - Older patient skipping recording their weight.
Systematic relationship between missing data and other observed values.

3️⃣ Missing Not at Random (MNAR).

The data is missing because of the missing value itself.
for example; People with very high incomes don't want to share their income.

Identifying missing data.

Is Null is used to check whether a value is NULL. Null in databases indicates that data is either missing or not applicable which is different from an empty string or zero. example

  select title from film 
  where release_year is null;

select inspection_type,count(*) as count
from restaurant_ispection
where score is null
group by inspection_type
order by count desc;

Rectifying missing data.

Best option: locate and add missing values , may not be feasible, may not be worthwhile.
Provide a value.
Exclude records.

The coalesce() function is designed to evaluate a list of arguments and return the first non-null argument it encounters.

It enhances the readability and robustness of SQL queries.

Features

It has unlimited arguments.
Returns first non null value evaluates each argument in the order they are provided and returns the first one that is not null.
Returns Null if all are null
It stops evaluating if it finds a non null value.
Coalesce can be employed in JOIN conditions to handle nullable columns ensuring that null values do not disrupt the join logic.

replacing null values with unknown

UPDATE countries
SET
--replace null countries values with 'Unknown'
country=Coalesce(national_park,'Unknown');
Select Count(*) From countries

Handling duplicated data.

Duplicates wastes storage resources.
Duplicates distorts analysis.

NOTE
Duplicated values should be addressed with intimate domain knowledge.

The Row_number() function determines where groups of columns values are duplicated in a dataset.An OVER clause must follow the ROW_NUMBER()function call.The OVER clause defines the window(or set of results over which the function will be applied.

ROW_NUMBER() OVER( PARTITION BY 
    plate_id, issue_date,violation_time,house_number,street_name
      ) - 1 AS duplicate

7a Visualizing missing values. we use msno.matrix(airquality)

import missingno as msno
import matplotlib.pyplot as plt
msno.matrix(airquality)
plt.show()

7b. Isolate missing and complete values aside.

missing = airquality[airquality['C02'].isna()]
complete= airquality[~airquality['C02'].isna()]

~airquality['co2'].isna()
The ~ operator inverts the Boolean values, so True becomes False and vice versa. This selects rows where 'co2' is NOT missing.

8.Detecting invalid values.

Data may need cleaning when table columns contains values that are not valid for the attribute that the column represents. ### Handling invalid data with pattern matching Using

where score NOT SIMILAR TO '\d+';

No restriction on length of value
Query only restricts non-digits character

Using not similar to get the registration_state not in UPPER CASE and not in 2 characters

SELECT Summons_number,plate_id,registration_state
FROM parking_violation
WHERE registration_state NOT SIMILAR TO '[A-Z{2}']

9.Determining column types.

Select column_name, data_type
from information_schema.columns
Where
table_name = 'machine_downtime' AND 
column_name = 'recorded_date';

BIGINT is a data type used to store very large whole numbers safely, much larger than the regular INT type.

INT can handle about ±2 billion (±2^31)

BIGINT can handle about ±9 quintillion (±2^63)

SELECT MAX(summons_number) - MIN(summons_number) AS range_size
FROM parking_violation;

10.Parsing timestamps with TO_TIMESTAMP()
The timestamp syntax is

TO_TIMESTAMP(ts_string,format_string) > TIMESTAMP

TO_TIMESTAMP(inspection_datetime,'YYYY-MM-DD HH24:MI'),

T0_TIMESTAMP(ts_string,format)-
TO_CHAR(ts_value,format) - timestamp - to string

11.Combining columns.
-Concatenation involves joining individual values end-to-end to create a single combined value.

CONCAT(string[,string2,string3,....])

Select CONCAT ( name,E'\n',
building, ' ', street, E'\n',
boro, ', NY', Zip_code ) AS
mailing_address
FROM
restaurant_inspection;

|| double pipe operator, like CONCAT() joins the values that surround the double pipe.
string1 || string2 [ || string3 || ...]

select 'data' || ' ' || 'cleaning' || ' ' || 'is' || ' '|| 'fun';
11.Splitting columns.
-STRPOS(source_string, search_string).

It returns the integer representing the first position containing search string in source string.
For instance to find the position of first '-' we use

  SELECT 
     STRPOS(house_number,'-') AS dash_position
  FROM
     parking_violation;

-SUBSTRING(source_string FROM start_pos For num_chars)

SELECT
  house_number,
  SUBSTRING(
    house_number
    FROM STRPOS(house_number, '-') + 1
    FOR LENGTH(house_number) - STRPOS(house_number, '-')
  ) AS new_house_number
FROM
parking_violation;

12.Splitting strings using SPLIT_PART()

SPLIT_PART(source_string,delimiter_string,part_number) - The SPLIT_PART() function requires 3 arguments: a source string, the delimiter on which to split and the index of the component to return from the split components.

 SELECT 
      SPLIT_PART ('Cycle Inspection / Re-inspection', ' / ', 2 );

REGEXP_SPLIT_TO_TABLE(source,pattern) - the REGEXP_SPLIT_TO_TABLE() function splits string values in a source column using a regular expression pattern. For instance

SELECT REGEXP_SPLIT_TO_TABLE('Pizza/Italian', '/' );

Duplicate values
These are columns that have the same values. we use the .duplicated () to get duplicates across all columns, it returns a series of boolean values that are True for duplicate values and false for non-duplicated values.

   duplicates= ride_sharing.duplicated(subset = 'ride_id', keep =       False)

To drop complete duplicates from ride_sharing.

ride_dup = ride_sharing.drop_duplicates()

Uniformity treating date data. It converts date to datetime- but won't work! since the date is not in the same format.

birthdays['Birthday']=pd.to_datetime(birthdays['Birthday'],infer_datetime_format=True,errors='coerce')

infer_datetime_format=True tells Python to try to figure out the format of the date automatically,instead of the coder 'me' having to specify it. It makes the conversion easier and faster escpecially if the dates are written in different styles such as 2015-05-31, 31/05/2025 or May 31,2025.