Hi there.
This is a little out of my depth but I work in reporting and this kind of fell in my lap.
We have a SQL database where logs of all incoming phone calls are dumped. It's a fairly large call center that handles ~100k calls per year. Pretty basic tables such as date of call, DNIS, ANI, etc.
One of our partners had projected a much higher volume of calls and because we haven't seen much activity associated with their campaign they have begun to question whether it's possible that calls are being made that we aren't receiving. Our telcom team looked into that and determined we were only at or around 60% of our total capacity at which point or partner requested to see the physical phone bill and to have it matched up against our call records.
So this was kind of a mess and it took a bit of work in Excel to get the data cleaned up and comparable. Everything "seems" to match up just fine, except there are a couple of issues and I was hoping someone could give me some confirmation or information that would elucidate these abnormalities.
These issues are:
1. Changing ANI's.
Through an automated comparison the phone bill matches our logs 90.86% of the time, however our logs only match the phone bill 80% of the time. So I went through the remaining entries that don't match and noticed a fairly clear pattern. The phone bill might receive 3 calls in a row to the same DNIS and record an area code of 123, whereas our system records a different area code. Sometimes it's just the last 4 digits of the number that change.
2. Changing time zones.
It "seems" that both the phone bill and our logs are on central time. Usually calls are 10-30 seconds off, or as much as a minute or two, but sometimes I'm noticing that the calls are offset by a full hour, however these calls typically form the same pattern described above. We'll receive 3 calls at 12pm noon from the same area code, to the same DNIS, and on the other report they're at 1PM, same DNIS, but again a different ANI.
3. Dates not present.
I have had no communication with our provider, but I am under the impression that all records between a specific date range were requested and provided for all DNIS's associated. However, there are missing dates. Our records will show we received 10 calls to a DNIS, but on the phone bill there are only 8... and the 2 that are missing come after the last date provided for that DNIS. None of the missing calls appear within a series that is provided for, but always come before or after the first or last call on the phone bill provided for this campaign. I've been told that this is "normal" and that billing can be broken up in a variety of ways which prevent this 100% accuracy.
I'm pretty certain that I remember that ANI's can change for a variety of reasons (VOIP, etc.), but I haven't had any direct experience with telephony in about a decade.
This is a little out of my depth but I work in reporting and this kind of fell in my lap.
We have a SQL database where logs of all incoming phone calls are dumped. It's a fairly large call center that handles ~100k calls per year. Pretty basic tables such as date of call, DNIS, ANI, etc.
One of our partners had projected a much higher volume of calls and because we haven't seen much activity associated with their campaign they have begun to question whether it's possible that calls are being made that we aren't receiving. Our telcom team looked into that and determined we were only at or around 60% of our total capacity at which point or partner requested to see the physical phone bill and to have it matched up against our call records.
So this was kind of a mess and it took a bit of work in Excel to get the data cleaned up and comparable. Everything "seems" to match up just fine, except there are a couple of issues and I was hoping someone could give me some confirmation or information that would elucidate these abnormalities.
These issues are:
1. Changing ANI's.
Through an automated comparison the phone bill matches our logs 90.86% of the time, however our logs only match the phone bill 80% of the time. So I went through the remaining entries that don't match and noticed a fairly clear pattern. The phone bill might receive 3 calls in a row to the same DNIS and record an area code of 123, whereas our system records a different area code. Sometimes it's just the last 4 digits of the number that change.
2. Changing time zones.
It "seems" that both the phone bill and our logs are on central time. Usually calls are 10-30 seconds off, or as much as a minute or two, but sometimes I'm noticing that the calls are offset by a full hour, however these calls typically form the same pattern described above. We'll receive 3 calls at 12pm noon from the same area code, to the same DNIS, and on the other report they're at 1PM, same DNIS, but again a different ANI.
3. Dates not present.
I have had no communication with our provider, but I am under the impression that all records between a specific date range were requested and provided for all DNIS's associated. However, there are missing dates. Our records will show we received 10 calls to a DNIS, but on the phone bill there are only 8... and the 2 that are missing come after the last date provided for that DNIS. None of the missing calls appear within a series that is provided for, but always come before or after the first or last call on the phone bill provided for this campaign. I've been told that this is "normal" and that billing can be broken up in a variety of ways which prevent this 100% accuracy.
I'm pretty certain that I remember that ANI's can change for a variety of reasons (VOIP, etc.), but I haven't had any direct experience with telephony in about a decade.
