Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

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?

share|improve this question
    
@Su' going through every single report and clicking "export to csv" and then importing every said report as a csv file to an excel workbook is not a maintainable solution. –  Moses Sep 16 '11 at 19:25
    
Yeah, I was reading back over that comment a few minutes ago, trying to figure out what I wasn't seeing and realized that was what you meant. Deleted previous, as obviously useless. –  Su' Sep 16 '11 at 19:38

3 Answers 3

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.

share|improve this answer

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.

  1. Click on the Export button at the top of a report
  2. Right-click on the CSV for Excel link and copy the link target
  3. You should now have a link that looks something like this:

https://www.google.com/analytics/reporting/export?fmt=5&id=112233&pdr=20110816-20110915&cmp=average&rpt=TrafficSourcesReport

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>&nbsp;</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>&nbsp;</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>
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.