Analyzing blocked telemarketer calls from asterisk with MySQL

Post date: Dec 29, 2013 3:15:44 PM

When one is subscribed to multiple PSTN carriers, one can gather a lot of data about who calls, when they call and if they are blocked or not. In this configuration, Asterisk is used passively to deny callers that are in added to asterisk's blacklist. I wrote a custom script to extract the inbound callers from each carrier and then insert the data into a MySQL database for further analysis. Asterisk is highly effective in this scenario as I use the blacklist across all PSTNs as nobody wants these calls. Note, in this scenario I have a total of 201 blocked calls in 2013. A blocked call represents a telemarketer calling a second time after asking to be removed from the list. In the examples below, I analyze the calls and graph them accordingly. Note the times are in EST.

The raw data, as stored in MySQL:

Analyzing the data for blocked calls by months of the year:

Analyzing the data for blocked calls by days of the month:

Analyzing the data for blocked calls by days of the week:

Analyzing the data for blocked calls by time of day:

Conclusion:

After analyzing the data, the following conclusions are reached:

1. The number of blocked calls per month of the year rises dramatically during the summer, perhaps due to summer break and summer jobs?

2. The number of blocked calls per day of the month rises the most at the start and in the middle of the month.

3. The number of blocked calls per day of the week start high and ramp up in the beginning of the week and taper off at the end.

4. The number of blocked calls per hour spike in the morning (9AM EST) and slowly ramp up over the course of the day, tapering off at night (9PM EST).