W3SKILLSET

VAPM Agent Data Power BI Report

By /

Requirement Document for VAPM Agent Data Power BI Report

1. Introduction

This document outlines the requirements for the VAPM Agent Data Power BI report. The report is designed to track and analyze agent performance based on incoming and outgoing call trends over different time periods.

2. Objectives

The primary objectives of this Power BI report are:

  • To monitor agent performance trends on a daily, weekly, and monthly basis.
  • To visualize key metrics such as answered calls, missed calls, and outgoing connected calls.
  • To provide insights into call handling efficiency through percentage calculations.
  • To track billed hours and total working hours.

3. Scope

The report focuses on the following key areas:

  1. Daily Call Trends
    • Number of Incoming Answered Calls.
    • Number of Incoming Missed Calls.
    • Number of Outgoing Connected Calls.
    • % Answered Calls.
  2. Weekly Call Trends
    • Breakdown of calls received, answered, missed, and outgoing connections by week.
    • Performance comparison across different weeks.
    • Weekly billed hours and total hours worked.
  3. Monthly and 4-Week Trends
    • Overview of agent activity over the last 4 weeks.
    • Trend analysis of answered and missed calls.
    • Outgoing call connections over time.
  4. Agent-Specific Performance
    • Individual agent statistics.
    • Daily and weekly performance breakdown.
    • Calls handled and billed hours per agent.

4. Key Metrics

The Power BI report includes the following key metrics:

  • Incoming Answered Calls: Total calls answered by the agent.
  • Incoming Missed Calls: Calls that were not answered.
  • Outgoing Connected Calls: Outgoing calls successfully connected.
  • % Answered Calls: The percentage of incoming calls that were answered.
  • Hours Worked: Total hours logged by the agent.
  • Billed Hours: Total hours billed.

5. Data Sources

The report is based on data extracted from the call center system, containing:

  • Call logs categorized by date and agent.
  • Work hours and billed hours records.
  • Agent performance statistics.

6. Report Visuals

  • Line Charts: To display daily and weekly trends of incoming, outgoing, and missed calls.
  • Bar Charts: To compare performance metrics across weeks and agents.
  • Tables: To show detailed numerical breakdowns of calls and working hours.

7. Filters & User Interactions

  • Date filters for selecting a specific day, week, or month.
  • Agent filters to view performance for individual agents.
  • Comparison filters to analyze trends across different timeframes.

8. Expected Benefits

  • Improved visibility into agent performance.
  • Better decision-making based on historical trends.
  • Enhanced call center efficiency by reducing missed calls.
VAPM_Agent_DataDownload

1. Data Extraction Using SQL Queries

The first step is extracting raw data from the database using SQL queries. The key tables include:

  • Call Logs Table (call_logs)
  • Agent Details Table (agents)
  • Work Hours Table (work_hours)

1.1 SQL Query to Extract Call Logs

sqlCopyEditSELECT 
    agent_id,
    call_date,
    COUNT(CASE WHEN call_type = 'Incoming' AND status = 'Answered' THEN 1 END) AS incoming_answered,
    COUNT(CASE WHEN call_type = 'Incoming' AND status = 'Missed' THEN 1 END) AS incoming_missed,
    COUNT(CASE WHEN call_type = 'Outgoing' AND status = 'Connected' THEN 1 END) AS outgoing_connected
FROM call_logs
WHERE call_date BETWEEN '2024-07-01' AND '2024-09-01'
GROUP BY agent_id, call_date;

1.2 SQL Query to Extract Work Hours

sqlCopyEditSELECT 
    agent_id,
    work_date,
    SUM(hours_worked) AS total_hours,
    SUM(billed_hours) AS billed_hours
FROM work_hours
WHERE work_date BETWEEN '2024-07-01' AND '2024-09-01'
GROUP BY agent_id, work_date;

2. Data Transformation Using Power Query

Once the data is imported into Power BI, we use Power Query to clean and shape the data.

2.1 Merging Tables in Power Query

  • Merge the call_logs table with agents to get agent names.
  • Merge the work_hours table with call_logs based on agent_id and call_date.

2.2 Data Cleaning Steps

  • Convert call_date and work_date columns to Date Type.
  • Remove duplicates and null values.
  • Ensure consistency in column names (e.g., Incoming Answered Calls instead of incoming_answered).

2.3 Creating a Date Table in Power Query

mCopyEditlet
    StartDate = #date(2024, 7, 1),
    EndDate = #date(2024, 9, 1),
    DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    ChangedType = Table.TransformColumnTypes(TableFromList, {{"Date", type date}})
in
    ChangedType
  • This Date Table helps in time-based analysis and filtering.

3. Data Modeling in Power BI

  • Relationships Setup:
    • call_logs[agent_id] → agents[agent_id]
    • call_logs[call_date] → Date[Date]
    • work_hours[work_date] → Date[Date]
  • Data Types:
    • Convert % Answered to Decimal Percentage Format.

4. DAX Measures for KPI Calculations

DAX is used to calculate key performance metrics.

4.1 Total Calls Answered

DAXCopyEditTotalCallsAnswered = SUM(call_logs[incoming_answered])

4.2 Total Calls Missed

DAXCopyEditTotalCallsMissed = SUM(call_logs[incoming_missed])

4.3 Total Outgoing Calls Connected

DAXCopyEditTotalOutgoingConnected = SUM(call_logs[outgoing_connected])

4.4 Percentage of Calls Answered

DAXCopyEditPercentAnswered = 
DIVIDE([TotalCallsAnswered], [TotalCallsAnswered] + [TotalCallsMissed], 0)

4.5 Total Hours Worked

DAXCopyEditTotalHoursWorked = SUM(work_hours[total_hours])

4.6 Total Billed Hours

DAXCopyEditTotalBilledHours = SUM(work_hours[billed_hours])

4.7 Average Answer Rate by Week

DAXCopyEditAvgAnswerRateByWeek = 
CALCULATE([PercentAnswered], DATESINPERIOD(Date[Date], MAX(Date[Date]), -7, DAY))

5. Report Visualization in Power BI

The final step is creating interactive visuals in Power BI.

5.1 Report Pages

  1. Daily Call Trends (Line Chart, Table)
  2. Weekly Call Trends (Bar Chart, KPI Cards)
  3. Monthly Performance (Trend Lines, KPI Summary)
  4. Agent-Specific Performance (Table, Filter by Agent)

5.2 Visual Elements

  • Line Chart: Trend of answered and missed calls over time.
  • Bar Chart: Weekly call volumes.
  • Table: Detailed breakdown of agent performance.
  • KPI Cards: Display key performance metrics.

6. Completion & Deployment

  • Validate data accuracy by comparing SQL results with Power BI visuals.
  • Publish the report to Power BI Service.
  • Set up Refresh Schedule (daily or weekly).
  • Enable User Filters to interact with the data.

7. Summary

The project completion method includes:

SQL Queries for data extraction.
Power Query for data cleaning and merging.
DAX Measures for advanced calculations.
Interactive Visuals for better insights.
Deployment to Power BI Service.

Sample data files for VAPM Agent Data Power BI Report based on the following key tables:

  • Call Logs (call_logs.csv) – Contains records of incoming and outgoing calls.
  • Agent Details (agents.csv) – Contains agent names and IDs.
  • Work Hours (work_hours.csv) – Contains agents’ total working and billed hours.
call_logsDownload
agentsDownload
work_hoursDownload

Scroll to Top