Hello brilliant people...
I work in customer support and I have two sets of data...
- Duration of time spent at each stage
- Duration of time spent at each status
Stages mostly progress in a straight line (Initial Investigation --> Collaboration --> Engineering Escalation --> Bug Fixing --> Solution Provided) for example.
Statuses jump back and forth (New, Customer Pending, Customer Updated, Vendor Pending, Customer Pending, Customer Updated, Vendor Pending, Closed) for example.
The total duration of stage and the total duration of status add up to the same value for a support case.
Thinking about our support processes from beginning to end, plotting these two sets of data would help us understand where we need to focus on our next support process improvement initiative.
What I thought might help visualize the data is either...
- Two stacked horizontal bar charts on the same graph. One for stage and one for status.
- A single horizontal stacked bar chart (for stage) with scatter plots with error bars or skinning vertical bars that would appear like tick marks on a timeline. Or vice versa (status is stacked while stage is scatter/bar).
I've tried the first option, but I cannot get the second dataset to plot it's own horizontal stacked bar. It's always plots it on the first one. I've tried the second option, but I'm lost and think the first option chart would look nicer.
Data basically looks like this...
Dataset 1
Stage / Duration
Investigation / 30
Collaboration / 20
Eng. Escalation / 200
Solution Provided / 100
Dataset 2
Status / Duration
New / 5
Vendor Pending /10
Customer Pending / 20
Customer Updated / 10
Vendor Pending / 155
Customer Pending /10
Vendor Pending / 45
Customer Pending / 45
Because Status values can repeat, and stage values once in a while, each value should be the same color. That is, if the status was Customer Pending 6 times, it should be represented by a single color, not 6 colors.
It would be great if the chart can come from a pivot table so it can update based on various slicer settings. Then we could see average of all cases, average per customer, average per process, average per year, etc.
This would help answer questions like, why does the Investigation stage take x time? Ah, because we are waiting on customer of 80% of that time. Let's come up with a better process to get customer response.
Thanks in advance!
Bookmarks