If you want to download and analyze IVR Call Log data in Excel, this article shows you a few tips/tricks to analyze it.
NOTE: All the detailed audit trail data is placed in one column - Column E labelled "info"
STEP 1: Download data for a specific date range from your portal.
You can download all call logs that you select for a specific Time Period into a Comma Separated Variable (CSV) format file that can be opened and viewed or analyzed in Microsoft Excel.
STEP 2: Open the Downloaded file in MS Excel (or similar)
2-1 - "Save As" Excel workbook
2-2 Enable the filter in Excel
1- Click the top row
2- Click the Filter icon in the Excel Data menu.
2-3 Freeze the top row in Excel
1- Click View
2- Click Freeze Top Row
STEP 3: Analyze the Data using Excel Filters
NOTE: The different analysis types shown below may or many not work for your particular IVR, if you have questions, please reach out to your 1800 Notify Account Manager or Support Team.
Finding who didn't enter anything to authenticate (blank) for the 'info' for (blank) like this:
Use the pull down from the Filter you turned on in column E (info).
Uncheck the (Select All) at the top.
Scroll to the bottom and check the (blank).
Then "Apply Filter"
Now, to count how many did not enter anything, click any column with data all the way down, the "cdr_start" Col C is a good one and look at the Count at the bottom.
You can see that 22 callers entered nothing on the IVR, and just listened then hung up.
Now, let's see how many callers never entered an actual DOB (in this IVR the DOB - Date of Birth was the first prompt requested).
You can do this by a special filter where you look for the column E (info) where the data does not contain "DOB ("
Use the same trick to get the "count" at the bottom as you did above.
How to view the entire call log shown in column E (info) - turn on word wrap in Excel.
Click on the header to select the entire Row E
Right-click and Select Format Cells
Then click on Alignment and check the "Wrap Text" checkbox
Now, you can see the entire contents of the audit trail in Col E.
This can give you ideas on how you want to filter things looking at the contents and using "contains" and "does not contain"
Other tips and tricks:
How to see which callers paid something:
Filter Column H "balance" to remove the $0
Now you see only callers that completed a payment.
Comments