Google-Analytics-Export-to-CSV is a free (open source) command-line tool that makes it easy to pull large volumes of data out of Google Analytics and process it in your favourite analytics tool including Tableau, R or (even) Excel. For an introduction see here, to download it click here.

Extracting data from Google Analytics is a simple, 3 step process:

1. Develop your query
2. Run the query
3. Import the results into an analytics tool

The three steps are described in more detail below.

1. Develop your query

Google offers analysts the opportunity to look at a whole variety of different metrics (including visitors, visits, page views, goals, transactions etc.) and slice them by a whole range of dimensions (including the source of the traffic, the date and time of the session, the page visited etc.) Unfortunately, Google does not make it possible to slice every metric against every dimension. Further, the online documentation is not brilliant at explaining what cuts of data analysts are and are not able to download.

Fortunately Google does offer an excellent Data Feed Query Explorer. Using this it is easy for analysts to experiment with different combinations of dimensions, metrics, segments and filters and see whether they work together.

To use it, open up the Data Feed Query Explorer in your browser: http://code.google.com/apis/analytics/docs/gdata/gdataExplorer.html

If you are not already logged in, click the button “Authenticate with Google Analytics” and enter your login details. If prompted, select “Grant Access”.

Once you have been logged in, you can “click to list your accounts”. Select the account you’d like to query: Google will helpfully automatically add the “tableID” to the “ids” field, as in the example below.

Now experiment! We suggest adding first the metrics that you are interested in, one-at-a-time, and then adding the dimensions, one at a time. The user interface makes adding metrics and dimensions easy: you can select them from the dropdowns as below:

After adding each metric, we recommend clicking the “Get Data” button. You can then inspect the results and see if data is successfully being pulled from the API. If you find no data returned, then you know you are trying to pull a combination of dimensions / metrics that the API does not support, so you can remove the last addition and keep building your query.

Once you have finished putting together your query, it is time to run it in the in the Google-Analytics-Export-to-CSV tool. To do so, you need to save the query to an XML file.

Copy the file recipes/query-template.xml to a new file e.g. recipes/new-query.xml. Open the file in your favourite text editor:

Now copy all the values from the query explorer to the XML file, as below. Where there are fields you have left blank, leave them blank in the XML as well.

Don’t forget to add your username and password. You should be left with something like below:

Save the file. Now you are ready to run the query and extract all the relevant data to a CSV.

2. Run the query

Google-Analytics-to-CSV runs from the command line.

If you are on a PC:

1. Open the Windows Powershell, by clicking on the start menu, selecting “All programs”, clicking on the “Accessories” folder, clicking on the “Windows PowerShell” folder in that and then clicking on “Windows PowerShell”
2. Navigate to the directory you downloaded Google-Analytics-to-CSV to. When you type lsin that directory, you should see the files your downloaded including the recipes folder and the ga2csv-0.1.jar file:

   

3. Run the following command: java –jar recipes/new-query.xml output/new-query-output.csv

   

4. Wait a few seconds. When the programme finishes, use the Windows Explorer to view the output folder. You should find a new file in there called “new-query-output.csv”

If you are on a Mac:

1. Open the Terminal by going to Applications, opening the Utilities Folder and selecting Terminal
2. Navigate to the folder you downloaded Google-Analytics-to-CSV to. If you type ls, in the folder, you should see the files below including the recipes folder and the ga2csv-0.1.jar:

   

3. Run the following command: java –jar recipes/new-query.xml output/new-query-output.csv

   

4. Wait a few seconds. When the programme finishes, use the Windows Explorer to view the output folder. You should find a new file in there called “new-query-output.csv”