1. Open up the file you downloaded in a spreadsheet, we use Microsoft Excel in this example.
2. Turn on the Filter for Row 1 (This will be useful to analyze the data)
3. Open up the Info column (should be column S) - as this column contains all the Call Detail Audit Trail.
Column P and Q show the Time and Date that the individual call started.
Column AD "duration" shows the duration of the call in seconds.
4. You'll want to get a total count of calls received for a specific period, so use the filer on the date (and time) columns. This will be your denominator for most of your calculations.
5. Then you'll want to look at filtering Col S ("info") to see details of each call.
|In the "Info" column of each call log entry you will see something like this:|
|Main Call Outcomes|
|(These are shown in the beginning of the 'Info' column on the User interface and in the downloads)|
|Person answered||A person called and followed the indicated audit trail but did not complete a payment|
|Person identified||A person stopped after being identified and hung up|
|Autopay success||The caller successfully made a payment|
|Autopay failure||The caller attempted but failed to make a payment|
|Transfer to office||The caller transferred to speak to a Financial Care Specialist at some point during the call|
|Call Audit Tracking Audit Trail Codes and Meaning|
|(These are shown in the 'Info' column on the User interface and in the downloads after the Main Call Outcome)|
|ID||Entered an identification (Guarantor or Account Number)|
|AuthSSN||Authorized using the last 4 of the SSN|
|AuthDOB||Authorized using the Date of Birth|
|AuthPh||Authorized using a Phone number|
|MMenu||Main Menu - this shows that the caller was successfully identified and authenticated
TIP: This is a good way to show % of people who were authenticated.
|Balance||The guarantor balance was said|
|RT0 - RT12||Calculated response types - see RT codes RT0 is not payment plan, RT1-12 are all payment plans|
|MMenu||Heard the Main Menu|
|HearBal||Selected to hear the balance repeated|
|HearPP||Selected to hear the payment plan details|
|MoreOpts||Selected to get to the More Options Menu|
|PrevPmts||Selected to hear Recent Payments|
|MyChart||Pressed the option to hear more about MyChart Link|
|DetBill||Selected option to request a Detailed Bill|
|AutoPay||Selected option to Make a Payment|
|PayFull||Selected option to make payment in full|
|PayLess||Selected option to pay less than the full amount|
|PayCC||Selected option to pay with a credit card|
|EndMOH||End of Music on Hold (while waiting to get the credit card or check book)|
|CC#||Entered a credit card number|
|CCExp||Enter the credit card expiration date|
|CCCharge||Attempt to charge the entered credit card with the payment processor|
|E-Routing||Entered an e-check ABA routing number|
|E-#||Entered a checking account number|
|E-Charge||Attempt to charge an e-Check|
|IM-Success||Successful payment returned by payment processor|
|IM-Failure||Payment failure notice returned by payment processor|
|EOTA-AuthCode||Selected option to hear the authorization code at the end of the call menu|
|EOTA-OfficeHrs||Selected option to hear the office hours and phone number for Financial Services at the end of the call menu|
|EOTA-StoreCOF||Store a payment method that was just used to make a payment on file|
|EOTA-Bye||Selected option to end the call at the end of call menu|