My Data Analysis
Platt.Tiara.Excel Analyst Reportiing Proficiency Test.xlsxAnalysis: What do you think members of management may find valuable based on the information presented?
Agent Efficiency: The average time agents spend in acw can be a measure of how fast they handle post-call tasks. Agents who consistently have high acw times might need more training.
Agent Efficiency: The average time agents spend in acw can be a measure of how fast they handle post-call tasks. Agents who consistently have high acw times might need more training.
Peak Times: By examining when agents are most active in certain statuses, like acd call, management can identify peak call times and adjust staffing levels based on those times.
Top Performers: Management can identify top-performing agents like those who handle calls well or have minimal acw and consider them for rewards or recognition.
Extended Inactivity: If any agent has extended periods of inactivity, like not being in acd call status for long durations during their shifts, it might be a red flag that needs to be addressed.
Create a list from the 'Data' tab that has all unique agent names (UserID or UserName) and unique status codes (StatusKey).
I have replicated the columns from the original dataset to ensure data integrity. Maintaining backup copies of the original data is crucial to prevent any potential damage. Additionally, I have eliminated duplicates from both columns to establish a distinct list.
Create a table that combines the two lists above and provides a total time (StateDuration) each agent spent in each status code. (NOTE: StateDuration is provided in seconds)
I required a data range encompassing the columns: UserName, StatusKey, and StateDuration to facilitate the construction of a pivot table.
For ease of use, I consolidated the data from these columns side-by-side. Within the pivot table: The UserName was allocated to the rows section. StatusKey was designated for the columns section. StateDuration was positioned in the values section, where Excel automatically aggregates the data.
It's noteworthy that Excel stores date and time values as numerical data. To enhance clarity, I adjusted the time formatting in the pivot table by selecting the 'Number Format', opting for 'Number', and setting the decimal place to zero.
I required a data range encompassing the columns: UserName, StatusKey, and StateDuration to facilitate the construction of a pivot table.
For ease of use, I consolidated the data from these columns side-by-side. Within the pivot table: The UserName was allocated to the rows section. StatusKey was designated for the columns section. StateDuration was positioned in the values section, where Excel automatically aggregates the data.
It's noteworthy that Excel stores date and time values as numerical data. To enhance clarity, I adjusted the time formatting in the pivot table by selecting the 'Number Format', opting for 'Number', and setting the decimal place to zero.
Create a list that provides the average 'acw' status code for each agent.
I refined the dataset by filtering based on 'UserNames' and 'StateDuration'. This facilitated the creation of the PivotTable range. I positioned 'UserNames' in the rows section and 'StateDuration' in the values section.
Within the value field settings, I transitioned from sum to average. To illustrate, the table indicates that Acie Moore has an average call work (ACW) time of 62.52 seconds.
I refined the dataset by filtering based on 'UserNames' and 'StateDuration'. This facilitated the creation of the PivotTable range. I positioned 'UserNames' in the rows section and 'StateDuration' in the values section.
Within the value field settings, I transitioned from sum to average. To illustrate, the table indicates that Acie Moore has an average call work (ACW) time of 62.52 seconds.
Create a chart that compares the amount of time in the 'acd call' status code each agent used.
Create a chart that provides the number of times each agent enters the 'acd call' status code.
Create a pivot chart that shows the agent average 'acw' and can be filtered by Team.
Market Analysis Case Study by Tiara Platt .pptx