Building relational database and the ability to able to use MySQL for multiple queries to do more data analysis was a major goal for our team this week. The process of building MySQL database included:
- Extracting useful columns (ignoring mostly empty columns).
- Creating table, columns to match Refer-Net column data.
- Adding appropriate indices (category, actionservice, datetime fields, transitioned).
- Verification that data made sense.
- Creating data import tool to map Refer-Net exported data from XML to SQL- DB to push 2.5 years of data.
Our main goal is to identify problem areas, peaks and drops in call time. We are basing our assumption that by identifying these issues will contribute in re-constructing a better menu and thus decrease callers wait time; for more information on the 211 call center, check out last week’s blog post!
Using MySql database, we were able to perform more analysis; such an example is the analysis we have done on calculating the numbers of calls based on action-services Terms (Needs per call). In order to be more specific, we have completed some keyword clustering and were able to group them into smaller subsets, these subsets are shown in the pie chart below. The result is a little ambiguous because it shows that around 10% that is considered to be under the subset (Others). This needs more investigation and will require finding a good method to improve the result.
We also examined the abandoned calls data more carefully for specific months of the years 2014 & 2015. The following results are for February 2015.
Plotting the frequency of abandoned calls vs. time duration indicates the existence of outliers in our data which needs to be detected and removed.
The following plot shows the same results but with outliers removed from the dataset. Time is in seconds.
As shown above, most calls were abandoned during the first 10 seconds. From 10 sec. to 70 sec. calls were dropped almost at the same rate, for 10 seconds time intervals, but form 70 sec. and longer we see a decrease in the pattern (shown below).
we will continue this analysis with comparing the frequency of abandoned calls vs. different sections of the call menu to understand the reaction of the calls towards the menu.