Lammer Law
New Member
- Joined
- Mar 23, 2018
- Messages
- 2
I have been a user of this board for many years. Until now I have always found the answer I am looking for without ever having to join. I knew the day would eventually come when I couldn't find what I was looking and I'd have to join. And here I am.
I am attempting to do some analysis on call centre data. We've identified that we have a high repeat call rate but we've also identified that it is not across the whole operation. As the service we provide is confidential we do not record the details of individual callers, therefore the only way we can track repeats is by looking at the telephone number of those who call us. By extracting the the Calling Line Identity (or ANI as it is known in some markets) from our MI database I can see instances or where we have received 2 or more calls from the same telephone line.
The screenshot below shows what I can extract (it is completely anatomised in the screenshot). In this example I can see that ajones has had 3 occasions where they have spoken to someone who has then called back later. In the actual data the real person has significantly more than that!
I've then used VLOOKUP to count the number of times that ajones has appeared in the data and then calculated the percentage against their total volume. Again the data in the screenshot is only to show what I am doing and the actual numbers are way higher.
However, what it does not tell me is the split between when they were the first person to speak to the customer or the second, third, fourth, etc. In the example above you can see that in all 3 calls ajones has been the first person they spoke to. While we have customers who have called us multiple times in a few days I am not particularly interested in breaking that down as our main focus is trying to deal with the customer at the first point of contact.
In the full dataset ajones was involved in more than 100 calls where the same person has called more than once. Without going through it line by line I can't find a way of saying "of the 113 calls that ajones handled where the customer had called 2 or more times, on XX occasions ajones was the first person they spoke to.
I've tried filtering but cannot get that to work and would simply like to have a number alongside the total percentage.
Any suggestions would be more than welcome.
I am attempting to do some analysis on call centre data. We've identified that we have a high repeat call rate but we've also identified that it is not across the whole operation. As the service we provide is confidential we do not record the details of individual callers, therefore the only way we can track repeats is by looking at the telephone number of those who call us. By extracting the the Calling Line Identity (or ANI as it is known in some markets) from our MI database I can see instances or where we have received 2 or more calls from the same telephone line.
The screenshot below shows what I can extract (it is completely anatomised in the screenshot). In this example I can see that ajones has had 3 occasions where they have spoken to someone who has then called back later. In the actual data the real person has significantly more than that!
I've then used VLOOKUP to count the number of times that ajones has appeared in the data and then calculated the percentage against their total volume. Again the data in the screenshot is only to show what I am doing and the actual numbers are way higher.
However, what it does not tell me is the split between when they were the first person to speak to the customer or the second, third, fourth, etc. In the example above you can see that in all 3 calls ajones has been the first person they spoke to. While we have customers who have called us multiple times in a few days I am not particularly interested in breaking that down as our main focus is trying to deal with the customer at the first point of contact.
In the full dataset ajones was involved in more than 100 calls where the same person has called more than once. Without going through it line by line I can't find a way of saying "of the 113 calls that ajones handled where the customer had called 2 or more times, on XX occasions ajones was the first person they spoke to.
I've tried filtering but cannot get that to work and would simply like to have a number alongside the total percentage.
Any suggestions would be more than welcome.