Calculating Your Google Voice Minutes Usage with Ease

In this tutorial I will present you with my script for calculating your Google Voice minutes usage as well as generating a detailed call history.

Introduction

Many people in the U.S. and around the world use Google Voice's free calling feature. Basically, Google Voice lets you make free calls to any phone in the U.S. and Canada. To apply, you need to have a U.S. phone number (for verification purposes) and also register for a free Google account. There are plenty of tutorials on the web.

To make Google Voice easier to use, several third-party applications integrate with it through the XMPP protocol. These include the OBi100, Groove IP, Talkatone, etc. These applications make it possible to make GV calls via WiFi or 3G, send or receive SMS via WiFi, and in the case of the OBi, can even replace one's home phone.

Unfortunately, Google had announced that all third-party app developers "must stop making unauthorized use of Google Voice to run their services and transition users by May 15, 2014" (source). As a result, providers of the above mentioned apps OBi, Groove IP and Talkatone all stated that their products will stop working with GV on that date.

Problem: For existing users who want to switch from Google Voice to another low-cost VoIP provider (such as Localphone), how to get a copy of your detailed call history with minutes usage?

Solution: Since Google Voice does not provide any call report, I wrote a bash script for exactly this task. Read below for usage instructions.

As a side note, check out my tutorial on setting up your personal VoIP server, and learn how to secure your server with IPTables.

Getting Your Google Takeout Archive

Before using my script, you need to generate and download a Google Takeout archive of your Google Voice history:

1. Go to Google Takeout: https://takeout.google.com/settings/takeout
   Click on "Select None" and then select only "Voice". Proceed to archive creation and when finished, download it.
2. Inspect the downloaded file in an archive manager (e.g. WinRAR). Go inside folders until you see Calls. This folder contains your Google Voice call history, each phone call corresponds to an HTML file.

Next Steps for Windows Users

Update 1: As of March 4, 2015 my script has been updated to also process Missed Calls (if present in your GV Inbox) and Google Hangouts calls. However, text messages and voicemails are NOT processed.

Update 2: Thanks to Rob Thompson for creating a Windows version of my script. (Disclaimer: I have not tested his script, use at your own risk.)

To use my script in Windows, you need to install Cygwin.

1. Download Cygwin from here:
   32-bit: http://cygwin.com/setup-x86.exe
   64-bit: http://cygwin.com/setup-x86_64.exe
2. Run Cygwin setup. Accept the defaults and choose a mirror close to you. At the package selection screen, search for package nano. Expand Editors below and then click on Skip to mark the package for install. It should change to a version number. See screenshot.
3. Proceed to install with the default settings. When finished, select the option to create Start Menu shortcuts. Now click on Start Menu -> Cygwin, run Cygwin Terminal.
4. Download your Google Takeout archive as outlined above, and open it in an archive manager (e.g. WinRAR). Next, open Windows Explorer, browse to C:\cygwin(64)\home\. Switch window back to WinRAR, go inside folders until you see Calls. Drag & drop that Calls folder into Windows Explorer.
5. Now, go back to Cygwin Terminal, and run command:
   cd /home/Calls; nano -w call_stats.sh
   The nano editor opens.
6. Use your web browser to view my script at:
   https://gist.github.com/hwdsl2/8393906
   Scroll down to call_stats.sh. Click on the "Raw" button at the top-right corner. Then press Ctrl-A to select all, Ctrl-C to "Copy".
7. Switch window back to Cygwin Terminal. Right-click and select "Paste". Then press Ctrl-O and Enter to save the file, Ctrl-X to exit nano.
8. Run the script using the command below. It may take a while.
   chmod +x call_stats.sh; ./call_stats.sh
9. When the script finishes, close Cygwin Terminal. In Windows Explorer, browse to the new folder Calls\GV_CSV_Files. You will see the generated call history files with txt extension.
10. Inspect these results in WordPad (Right-click -> Open with -> WordPad). If they look good, you can then import them into MS Excel (see below).

Next Steps for Linux Users

This bash script can be run natively on Linux. Follow these steps:

1. Download and extract your Google Takeout archive.
2. Use your web browser to view my script at:
   https://gist.github.com/hwdsl2/8393906
   Scroll down to call_stats.sh. Click on the "Raw" button at the top-right corner. Then press Ctrl-A to select all, Ctrl-C to "Copy".
3. Open a new terminal, cd into the Calls folder in the extracted files. From there, run nano -w call_stats.sh and then paste the script into nano. Press Ctrl-O and Enter to save the file, Ctrl-X to exit nano.
4. Make the script executable and run it:
   chmod +x call_stats.sh; ./call_stats.sh
5. When finished, open your file manager and browse to the new folder Calls/GV_CSV_Files. You will see the generated call history files with txt extension.
6. Now you can copy these results out, e.g. sending yourself an email with those attached, and then import them into MS Excel!

Importing Results into MS Excel

Here's how to import the results into MS Excel. I use Office 2013:

1. File -> Open -> Select your results file. Text Import Wizard appears.
2. Choose "Fixed width", select "My data has headers", click Next.
3. Adjust column break positions or accept the defaults, click Next.
4. For first and second columns, select "Text" above; Leave the other columns as "General". Click Finish.
5. After importing, adjust widths of the columns so all texts are shown. Alternatively, double-click on the column separators to auto-adjust.
6. Now you can save it as a normal Excel workbook and run analysis.

Note: If your contact names contain non-ASCII characters, they will be shown as HTML entity codes like this one: 的. How to fix:

1. Go to this online converter:
   http://online-toolz.com/tools/unicode-html-entities-convertor.php
   (Note: The above site does not use HTTPS and data transfer is not encrypted.)
2. Scroll down to "Unicode Entities to Text Convertor". In your Excel workbook, select the entire first column (Contact_Name), press Ctrl-C to copy. Return to the web page and paste the contents into "HTML Entities". Click "Convert".
3. Your contact names should now show normally in the "Unicode Text" box. Ctrl-A to select all, then Ctrl-C to copy to clipboard.
4. Return to your Excel workbook. Place cursor at Row 1, Column A, Ctrl-V to paste back.

Rounding up calls to the next minute

The call durations in the result files are in the format hh:mm:ss. If you want to round them up to the next minute:

1. In your Excel workbook, pick a new column to the right, select that entire column and choose "Format Cells...", select Time and then the one corresponding to hh:mm:ss. Click OK.
2. Select the second cell in that column, put this equation =ceiling(F2,time(,1,)), press Enter. The call durations should now be rounded up in the new column.
3. Apply to other cells by dragging down on the cell's bottom-right corner. Put a header on first row if you like.

Converting date and time to your timezone

The call start date and time in the generated results should be in your local timezone. However, in case you need to convert them from UTC, here's how:

1. In your Excel workbook, pick a new column to the right, select that entire column and choose "Format Cells...", select Custom and in the "Type" textbox, put m/d/yyyy h:mm:ss. Click OK.
2. Select the second cell in that column, put this equation =C2+D2-TIMEVALUE("6:00:00"), change "6:00:00" to the number of hours your timezone is behind UTC (e.g. Central U.S. Time without Daylight Saving UTC-6:00). If ahead of UTC, then use +TIMEVALUE. Press Enter.
3. Apply to other cells by dragging down on the cell's bottom-right corner. Put a header on first row if you like.

Calculating monthly usage and other metrics

To calculate total call duration for a given month, select values in the call duration column only for that month, and look for "SUM:" in the status bar.

To break down outgoing calls by area code, try sorting in Excel based on the "Phone_Number" column and then use the method above.

Please share this post if you like it, and do not hesitate to write your comments or questions in the Disqus form below.

Next article: Ghost Blog Auto Setup with Nginx and Naxsi
Previous article: Install PBX in a Flash (PIAF) on a DigitalOcean Droplet

Return to Lin's Tech Blog Homepage