Verizon recently added a “Friends & Family” thing to their mobile plans where you can pick 10 numbers that won’t count against your minutes. The website provides details about your calls, but they don’t provide a good tool to find the 10 people you call the most. Hmm, what to do?
OpenOffice to the rescue! Since Verizon makes call details available as a CSV download, they can be easily sucked into a spreadsheet where you can do all sorts of unholy data manipulations.
Here’s the, somewhat ungraceful, procedure I worked out to find out who we call the most. I’m sure things could be automated more, but I’m also pretty sure it would take me a lot longer to do that than this took. All this stuff should work in Excel too, but no promises.
- Login to your Verizon account, Go to My Bill then Bill Details.
- Select the date you want and the number you want.
- Hit the Download to Spreadsheet link to download your call data into OpenOffice, you’ll need to repeat this for each number on your account separately. The default import options should work fine.
- Get all the data pasted into 1 spreadsheet page. I did a page per month, but you could mash it all together too.
- Turn on Data->Filter->Autofilter.
- Filter out the calls to other Verizon numbers that don’t count towards minutes.
- Click the pulldown that appeared at the top of the Usage Type column.
- Select each type of in network call. The are listed as M2MAllow, M2M Allow,CallWait, IN Allow, or IN Allow,CallWait. Depending on the date of your statement. You may also want to filter out nights and weekends (N&W), but I left that in since I really just wanted to know who we call the most regardless of time.
- Select all the filtered rows and delete them, repeat this for each type. Don’t delete the header.
- Select All in the filter to get back to the remaining data.
- Now, here’s the good stuff, select the entire sheet and go to Data->Subtotals.
- Select Number for group by, Minutes to calculate subtotals for, and Sum as the operation. Click Okee Dokee.
- Violin! The little elves will group all the unique numbers and place a subtotal of the minutes under each group. If you click the little 2 on the far upper right, you can collapse it so you only see the subtotal lines.
Just go and pick out your top 10 from there, easy peasy, well, easier peasier.
