DEV Community: Akinyemi Ayodele

Call Center Data Analysis

Akinyemi Ayodele — Thu, 15 Dec 2022 11:10:29 +0000

Introduction
I have been provided with data set of a call center for the month of October, 2020. I am tasked with finding the call trend over the month and generating insights from the given data set. Some questions that will be answered from the data will include;

Total number of callers over the month.
Number of callers through various channels.
Customer sentiments.
Reasons for customer calls.
Total calls by response time.
Call trend over the 31 days of the month.
Average call duration.
Average satisfaction score.

Data Preparation
The data came in a .csv format and formatted as shown in the diagram below;

In order to make the data look more presentable, the column headers were made to standout. Also, to find the Call Trend for the month, I had to extract the the Days from the Call timestamp column as shown below;

The diagrams below show the .csv file in Power Query and the modified data set respectively;

Data Modelling
So as to find the trend of calls for each day of the month, a Date table was created using the CALENDARAUTO function as shown below;

A relationship was then created between the Date table and the Call center data.

ANALYSIS
I had to first, find the Key Performance Indicators - Total number of callers over the month, Average satisfaction score, Average call duration - using DAX functions. The diagrams below show how I was able to find these KPIs using DAX and the Card visual of Power BI;

-Total Calls: The COUNT function was used to count the total calls, as opposed DISTINCTCOUNT that would count the distinct number of callers (i.e ignore if a person had called more than once)

-Average Satisfaction Score: The AVERAGE function was used to find the average.

-Average Call Duration

-Total calls by Channels: this analysis shows the number of customers that contacted the Call center over the month through various communication channels.

-Total Calls by Sentiment: this shows the number of callers by what they think about the services that were being rendered to them.

-Percentage of Total Callers by Reasons: this analysis shows the percentage of the total callers that contacted the call center based on their reasons for calling.

-Total Calls by Response time:

-Total call trend by Day: this chart shows the number of calls received each day of the month, from 1st to 31st of October.

INSIGHT
At "10,639", Call-Center had the highest Total Calls and was 61.79% higher than Web, which had the lowest Total Calls at 6,576. Customers preferred to call the Call center, rather than using the Web or E-mail. This could be because they can get INSTANT response to their queries instead of the waiting time it would take to get a response via the E-mail. Call-Center accounted for 32.30% of Total Calls. The Response Time "Within SLA" had 20,625 Total Calls, "Above SLA" had 4,168, and "Below SLA" had 8,148. 71% of the Total Calls were from customers who called to make inquiries on "Billing". Over 17,000 callers were negative/very negative about the service they got over the month, possibly due to the fact that they were over-charged. It is highly recommended that the organization looks into the rate at which customers are being charged for services.
Three (3) filters were also added to filter out the number of callers from;

each call channels
each states
region

DASHBOARD

Thank you for your time. This report can be found here.
Images on the dashboard were gotten from Flaticon
Dashboard background was designed with Microsoft PowerPoint and be downloaded here
The Call Center dataset can be downloaded here

Financial Analysis using Power BI

Akinyemi Ayodele — Wed, 23 Nov 2022 09:21:28 +0000

I have been hired as a Freelance Data Analyst to help review a data and provide insights that would be valuable to the CFO of this organization. The business has been performing well and the management wants to get insights into their 2013 and 2014 performances.

The Business Questions

Through internal brainstorming, the CFO has confirmed that they needed answers to the following questions:

What is the company's performance in 2014 compared to 2013?
Which countries drove the orders and profit margins of the company in the two years period?
What was the trend in the company's sales over the two years period?
The profitability of each segments.
What were the order of countries in terms of the quantity of products sold?

To answer these questions, the company has provided us with their financial data for 2013 and 2014. I was required to design an interactive report with the insights I will discover from the analysis.

Tasks

I am going to model the data and perform analysis using DAX. This is the outline of the requirements:

Create a Date Table using the CALENDARAUTO function. This will support the time intelligence analysis.
Create a relationship between the financial table and the newly created Date Table using the Date columns in both Tables.
Using DAX, I am going to create measures to answer the business questions. Some of these measures include: Total orders, Total Sales Amount, Total Discount offered, Total Profit, Profit Margin etc.
Perform time intelligence analysis by creating measures that calculate the same measures in the requirement above for the previous year (2013).

ANALYSIS

A Date Table was created with new columns that include Month and Year as seen in the image below;
Both the business datasets and date table were modelled to create a relationship.
DAX functions were written to create measures for EACH of the analysis. An example is shown in the image below, to calculate the Total Orders made;
New measures were also created to find the SAME metrics for the previous year. Note that "LY" means Last Year. An example of the Total Revenue for LAST YEAR is shown in the image below;

INSIGHT
[Find insights on the dashboard].

DASHBOARD

Thanks for viewing... Feedbacks are highly appreciated (04yemi@gmail.com)

Data Analysis using Microsoft Excel: Sales Analytics.

Akinyemi Ayodele — Mon, 15 Aug 2022 23:39:52 +0000

Hello! You're welcome. This is my first analytics project. This analysis and visualization was done using Microsoft Excel.

Bekky's Place Online store is a supermarket that sells all varieties of items in different categories, from Fashion to Home and Office supplies.

They have got a dataset of their shopping over 5 years, from 2015 to 2020. The CEO, Miss Ahmed Oluwaseunfunmi Eberechukwu would like to see how the supermarket is fairing by certain Key Performance Indicators. The manager, Mr Idara Ogunyele Sabo has been assigned the task to identify the key areas within the business that need clarification and get them sorted out as soon as possible.

Mr. Idara decided to recruit Ayodele as a Data Analyst to analyze the sales dataset and come up with results and visualization.

Tasks

Total revenue by product category.
Reasons why customers return some of our items.
Revenue by YoY.
Customers' gender by percentage.
Which of our delivery channels have the biggest revenue by percentage?
Percentage of revenue by gender across age groups.
Top 5 product subcategories by revenue.
Product category by Total revenue, percentage of total revenue, total orders and percentage of total orders.

Data structure: The dataset was gotten from the Data Entry officer of the supermarket. The dataset contains records of sales with fields like Order date, Order ID, Delivery date, Customer age, Gender, Delivery type, Product category and etc.

Data preparation: In order to be able to find the Year on Year, I created a new column for the years. To also find the average delivery days, a new column was created.

Analysis & Insights: The analysis of the dataset was done using pivot table. The spreadsheet file can be found at the end of this article.

Charts
The Key Performance Indicators include: Total Revenue, Total Orders, Total Shipping Fee, Total Number of Customers and the Average Delivery Days of products.

° Total Revenue by Product Category

° Reasons why items are returned

° Year on Year revenue

° Percentage of Customers by gender

° Revenue by Delivery channels

° Percentage of revenue by gender across age groups

° Revenue of top 5 products subcategories

° Product category by total revenue, percentage of total revenue, total orders and percentage of total orders.

Dashboard

The project file can be found here

Thank you so much for reading. 🙏😊