I want to export all of my Google Analytics data to Excel for a weekly report. I will need to re-export the data every week so the process needs to be maintainable.
What is the best way to just get a raw data dump of all analytics data into a CSV file (or something similar) for processing in Excel?
Excellent Analytics more or less lets you access GA directly through Excel and might be worth a look. There's an SEOMoz writeup if you want an overview, though note that's two years old; ignore the bit near top about only working with older versions. While not exactly "automated" you're going to end up in Excel anyway eventually, so it might cut out enough effort to work. There are a couple of limits on the amount of data returned that are fundamental to the Analytics API itself(see FAQ), but can be worked around by doing multiple queries.
Log in to Google Analytics and then, after taking note of the link targets associated with the data you want to download, download the data.
You can gather links from the reports you want and download the report after logging in to Google Analytics (take note of the dates 20110816-20110915 in the link - you'll want to change these or the reporting period you need).
Edit: Apparently Google will also allow automated requests using cURL, so you should be able to stuff all the CSV requests into a bash script to automate retrieval. (Must use Data Export API request format to interact with the service - requests for CSV's return 302 redirect + HTML document)
Edit #2: This seemed like something simple to automate so, for anyone who isn't using Excel and wants a cross-browser compatible way to download reports from Google Analytics, here's a really ugly JS/HTML file that should do the trick:
<html>
<head>
<title>Grab Google Analytics Reports</title>
<script type="text/javascript">
/**
* add/remove appended "rpt" values below
*/
var trackedRPTs = new Array(
'DirectSourcesReport',
'ReferringSourcesReport',
'SearchEnginesReport'
);
function grabReports(baseURL)
{
var gaIdExpression = new RegExp("\&id\=(.*)\&pdr");
if ( ! gaIdExpression.test(baseURL) )
{
alert("Unable to determine Google Analytics ID for request");
return;
} else {
var gaId = baseURL.match(gaIdExpression);
gaId = gaId[gaId.length-1];
}
var format = 5;
for ( i = 0; i < 6; i++ )
{
if ( document.getElementById('fmt-'+i).checked )
{
var format = i;
}
}
var days = 0;
var range = '';
for ( i = 0; i < 3; i++ )
{
if ( document.getElementById('pdr-'+i).checked )
{
switch(i)
{
case 0:
days = 30;
break;
case 1:
days = 90;
break;
case 2:
range = document.getElementById('pdr-arbitrary').value;
break;
}
}
}
if ( ! range )
{
var d = new Date();
var range2 = new Date( d.getFullYear(), d.getMonth(), d.getDate() - 1 );
var range1 = new Date( range2.getFullYear(), range2.getMonth(), range2.getDate() - days );
var range = range1.getFullYear()+''+(range1.getMonth()+1)+''+range1.getDate();
range += '-'+range2.getFullYear()+''+(range2.getMonth()+1)+''+range2.getDate();
}
alert("Prepare for download!\n(Sorry, it's manual)");
var tempWindow = new Array();
for ( i = 0; i < trackedRPTs.length; i++ )
{
var rptURL = "https://www.google.com/analytics/reporting/export?fmt="+format+"&id="+gaId+"&pdr="+range+"&cmp=average&rpt="+trackedRPTs[i];
window.open(rptURL);
}
return false;
}
</script>
</head>
<body>
<ol>
<li>Log in at <a href="http://www.google.com/analytics/">http://www.google.com/analytics/</a></li>
<li>
If you do not have one yet, go to a report and click <em>Export</em> then right-click the link
to the type of report you want and copy it to the box below<br />(If you already have a working
link, just edit this document save it as the value for the field below)
</li>
</ol>
<hr />
<form action="#" method="post" onsubmit="javascript:grabReports(document.getElementById('example').value);return false;">
<input id="example" value="" />
<input type="submit" value="Grab Report(s)" />
<hr />
<table style="display:inline;">
<tr>
<th> </th>
<th>Prefered Format</th>
</tr>
<tr>
<td><input type="radio" name="fmt" id="fmt-0" /></td>
<td><label for="fmt-0">PDF</label></td>
</tr>
<tr>
<td><input type="radio" name="fmt" id="fmt-1" /></td>
<td><label for="fmt-1">XML</label></td>
</tr>
<tr>
<td><input type="radio" name="fmt" id="fmt-2" /></td>
<td><label for="fmt-2">CSV</label></td>
</tr>
<tr>
<td><input type="radio" name="fmt" id="fmt-3" /></td>
<td><label for="fmt-3">TSV</label></td>
</tr>
<!-- "ERROR" -->
<input type="hidden" name="fmt" id="fmt-4" />
<!-- "ERROR" -->
<tr>
<td><input type="radio" name="fmt" id="fmt-5" checked="checked" /></td>
<td><label for="fmt-5">CSV for Excel</label></td>
</tr>
</table>
<table style="display:inline;margin-left:20px;">
<tr>
<th> </th>
<th>Date Range</th>
</tr>
<tr>
<td><input type="radio" name="pdr" id="pdr-0" checked="checked" /></td>
<td><label for="pdr-0">Last 30 days</label></td>
</tr>
<tr>
<td><input type="radio" name="pdr" id="pdr-1" /></td>
<td><label for="pdr-1">Last 90 days</label></td>
</tr>
<tr>
<td style="vertical-align:top;"><input type="radio" name="pdr" id="pdr-2" /></td>
<td>
<label for="pdr-2">Arbitrary</label><br />
<input type="text" size="17" maxlength="17" name="pdr-arbitrary" id="pdr-arbitrary" /><br />
<pre>YYYYMMDD-YYYYMMDD</pre>
</td>
</tr>
</table>
</form>
</body>
</html>
Check out http://megalytic.com. Cloud based. Lets you set up exports to Excel of data from one or more accounts. Also handles Facebook Insights, Twitter, Omniture and others coming.
It is not yet completely automated to run a weekly job (as you describe). But, we'd be happy to work with you to build in that feature.
