Skip to content


Case Study: Cell Phone Bill Analysis

Have you ever looked at the long list of calls on your phone bill, and wished there were a better way to see who all you’ve spoken with and other related information? Like some of you, I’m a data junkie, and I sure did. For now, I’m an AT&T customer. Like most modern phone companies, AT&T makes your call history available for download from their website in CSV format. Using a simple script, we can transform this long ugly list of numbers into something more meaningful.

Pie Charts showing To Whom I Speak as Percent of Total Calls and Total MinutesGraphs such as these can tell us a few interesting things, other than the fact that PrettyNerd and I talk frequently and that I’m a momma’s boy. It shows that, save for these two people, I don’t spend a lot of time on the phone. However, the time I do spend on the phone actually increases with these two people. The remainder tend to be short, to-the-point conversations with friends, family, and colleagues. The data itself also shows that I make a lot of calls to service providers (e.g., I made 8 calls to Comcast in this period as I repeatedly tried to cancel my service), and that I spend far too long on hold for my tastes.

If you’re an AT&T customer, you can use this script for yourself. Download it from my GitHub.

Example usage:

$ ./att_phone_stats 061411-071311.csv > 061411-071311.out.csv

This command will parse the call history (presumably for June 14, 2011 to July 13, 2011) and write out a new CSV that is more useful for data analysis and statistics. We can then open this file in Excel or other spreadsheet software and create simple graphs to visually depict our behaviors regarding cell phone usage.

The expected columns on the input CSV: Item, Day, Date, Time, Number Called, Call To, Min, Rate Code, Rate Pd, Feature, Airtime Charge, LD/Add’l Charge, and Total Charge. The output CSV contains the following columns in order: Number Called, Total Number Calls, Total Mins, Number Incoming Calls, Number Outgoing Calls, Incoming Mins, and Outgoing Mins. Note that most of the input columns are unused, and there is a lot of data munging that we could still do.

Does anyone else have other ideas for how we might look at and interpret this data? Please share them in the comments. :)

Posted in Case Studies.


2 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. mark e says

    I really like this! Was doing something similar, manually, for several years. Will your script work with any other cell providers? I’m with T-Mobile, for instance…

    • codyaray says

      Thanks, Mark! The theory should work with other providers, as long as they have an option to download your call data as a CSV. The script will most likely need to be adapted to their CSV format, but it should be pretty trivial to do if they provide the data you’re looking for.



Some HTML is OK

or, reply to this post via trackback.

 



Log in here!