How To Create An Automated Google Spreadsheet Report From Analytics Data

Ryan Dube 18-10-2013

What’s working smart? Well, it’s constantly looking for ways to streamline and automate just about every single “process” in your life – from making coffee and tying your shoes, to creating slideshow presentations How to Make a Slideshow in Windows If you want to share your photos with the world, a slideshow is just what you need. Here's everything you need to know to create the perfect slideshow. Read More  and studying for a final exam 5 Tips to Prevent Internet Addiction and Study More Effectively Internet addiction affects most of us. What is internet addiction and how can you avoid it to study better and stay productive? Read More .


One very time-consuming task is analyzing information about your website in Google Analytics, and then somehow presenting that data in a way that makes sense to people who don’t pour through Google Analytics data and statistics all day. Creating Analytics reports can be time-consuming and at times extremely difficult, but by using some of the automation tricks I’m going to show you in this article, you can transform a laborious project with a couple of clicks.

This automation utilizes Google Analytics Your Guide To Google Analytics Do you have any idea where your visitors are coming from, what browsers most of them use, what search engines they use, or which of your pages is the most popular? Read More , IFTTT 5 Unusual IFTTT Recipes You May Not Have Thought Of You've got the folks at IFTTT who make web automation look like childs play. It's quite impressive. I decided to finally start digging through IFTTT to see if I could come up with a few... Read More , Dropbox What Is Dropbox? The Unofficial Dropbox User Guide What is Dropbox and how do you use it? Our Dropbox user guide will explain everything you need to know to get started. Read More , and Gmail, and in the end results in a high-quality report that you don’t have to put together yourself every month – it’ll virtually create itself!

Setting up Google Analytics

The starting point of the process is the data about your website. Since the vast majority of website owners use Google Analytics Google Starts Analytics Academy To Teach People About Google Analytics Google have set up an Analytics Academy, free online courses to teach you all about Google Analytics. They're starting with a three week course you can register for right now. Read More , that’s what we’re focusing on here.  In Google Analytics, you can create customized emails that contain data you’ve pieced together. You create those by going into the “Customization” tab in your Analytics account and clicking on the “New Custom Report” button.

On this screen, you’re going to put together the metrics, dimensions, and filters to generate the raw data output that you want. If you need more help doing this, you can read my article about Google Analytics Custom Reporting Automate Informative Google Analytics Reports With Custom Reporting One thing that I've always wanted to get working better with Google Analytics is an automated reporting feature that provides the information that I want and in the format that is easy for whoever needs... Read More .




Once you have created your custom report, it’s time to schedule the email that’ll send your data to your Gmail account. You do this by clicking on the “Email” button at the top of your Custom view page, and then filling in all of the fields. Be very specific with your Subject line, because this is the exact text that you’re going to use in Gmail to recognize this incoming data. Also, the frequency here shows “Once”, but you’ll actually want to eventually make that “Monthly”, or however frequently you want to generate these automated reports. Finally, make sure you select “CSV” format.


Once you’ve got your data stream going out to Gmail, it’s time to configure your Gmail account to accept that data.


Setting Up Gmail and IFTTT

In Gmail, you want to identify the incoming data streams with a specific label. In your Gmail account, go into settings and click on the “Labels” menu item. Click the “Create new label” button. In this example, I’ve created a filter called “MUO Traffic Types Report” under “Google Analytics Dashboard” folder. Finally, create an email filter that looks for any incoming emails with the subject like you created (“Google Analytics Traffic Types” in my case), and then apply the label you created. You may also want to click “Skip the Inbox” if you don’t want these cluttering your inbox.


Next, go to your IFTTT account and create a new recipe. For the Trigger side of the recipe, choose Gmail and select the option to create a trigger every time there’s an incoming email with the label you’ve defined.



For the Output side of the recipe, choose DropBox, and have it place the incoming attachment (the Google Analytics CSV file) into your Dropbox account in an accessible folder.



Once this is finished, the first part of your automated report is completed. Now, on a monthly basis (or whatever frequency you set), Google Analytics will send out the last month’s worth of data defined by your custom report, into your Gmail account. From there, IFTTT places the attachment into your Dropbox Account.


Go into your Dropbox account, right click on the file that IFTTT inserted into it, and then save the URL to the CSV file.



Connecting the Automated CSV File to Google Spreadsheet

The next part of automating this new reporting system is to get that data into Google Spreadsheet so that you can manipulate the data and create your final report. Not many people realize that you can directly import data from a CSV file, so long as there’s a URL to the file.

You do this by selecting the first cell of the sheet and typing “=importdata(“URL-LINK”)”  – obviously, replacing the “URL-LINK” with your actual URL of the CSV file.


Remember, your original email subject line you specified in Google Analytics will always remain the same, so the attachment will always be the same, and the IFTTT attached CSV file in Dropbox will be named the same. So, once you create this ImportData function, it’ll always work every time you open this Google Spreadsheet.

In my example above, once I hit enter, it goes out to Dropbox, and imports all of the data from the CSV file.


Note of caution:
this is also a slight security flaw. The fact that you need to use a publicly accessible URL to import the CSV file means that whoever knows your Dropbox public folder link would be able to see the data in these files. One way to reduce the chance of this is to immediately delete the files once your automated report is created. Anyway, it’s something to keep in mind if security is really high on your list of priorities.

Now that the data from Analytics is in Google Spreadsheets, the sky is the limit as to what you can do. You can move data around to other sheets, perform all sorts of calculations on them, and create awesome pivot charts to organize the data in ways that reveal a lot of information that otherwise wouldn’t be so clear.


Once you’re done creating your final report sheets in Google Spreadsheet, you’re done.  Your automated reporting system is ready to go the next month. You can create as many of the Google Analytics email reports as you like, and those will feed additional sheets in your Google Spreadsheet file. Again, that data can be used in your analysis for the final report.

All you have to do each month is wait until Google Anaytics has sent you all of your report emails. When you open the Google Spreadsheet, it’ll automatically import the data, perform all of the calculations, create the charts, and whatever else you’ve customized, and your report is done. No work on your part.

The only few clicks you do need to do is to go to “File” inside Google Spreadsheet, and select “Email as attachment…”




Personally, I think PDF reports look really cool, so I’ll go with the PDF format for the reports, add all of the recipients that the report should go to, and then click send. That’s it! Your automated report is finished.



All the folks that receive your reports will think that you slaved over creating all of that data and those awesome graphs and charts. Little do they know that, because you’re so clever, you only had to do all of that work once, but by automating your work, you never have to lift a finger again. (Just don’t tell your boss that).



Share Your Ideas

Have you ever made use of Google Analytics’ customized reporting? Did you know you could use those emails in this way, to feed the data outside of Google Analytics? How else do you think you might use this data when you get it into Google Spreadsheet? Share your own insights and ideas for further automation in the comments section below!

Related topics: Dropbox, Google Analytics, Google Drive, IFTTT, Spreadsheet, Webmaster Tools.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. tom h
    March 16, 2016 at 4:32 pm

    This is a genius way to automate this, but it isn't really necessary any longer.

    You can use the Google Sheets add on for Google Analytics which will update the a Google Sheets spreadsheet on a schedule you specify. Go into a Sheets document under Add-Ons into Get Add Ons, and look for the Google Analytics add on. Once you set it up, you can go under Add-Ons -> Google Analytics -> Schedule reports to specify when the data should be refreshed.

    Then you can just share the spreadsheet with your audience, or make a PDF if you want.

  2. Sebabrata Banerje
    February 28, 2016 at 5:41 am

    Hi, Thanks for your post. The IFTTT recipe worked. But it's importing gmail attachment in a different format than .csv. Can you look into that?

  3. Anonymous
    June 11, 2015 at 10:37 pm

    Thanks for this walkthrough! I'm not sure if something has been updated since the time of this article, but I'm having an issue with the importdata function. Specifically, whenever I try the importdata function in google spreadsheets to a dropbox csv URL, I just get long lines of code starting with:

    etc. Any thoughts on this?

  4. Bart
    February 6, 2015 at 1:32 pm

    Hi Ryan,

    Thanks for writing this report.
    However, the IFTTT recipe won't run, even though I have allowed both Gmail and Dropbox to access my account.
    Do you know what could the problem? Thank a lot in advance.


  5. Ryan Dube
    March 13, 2014 at 2:31 pm

    Hi Jen - yes, this was indirectly covered here:

    "One way to reduce the chance of this is to immediately delete the files once your automated report is created. Anyway, it’s something to keep in mind if security is really high on your list of priorities."

    What I do is copy over my automated spreadsheet to a new spreadsheet (copying the values over, not the update formula). Then I delete the original file in Dropbox so the next time it updates the process is the same. It does add a manual element to the process though.

  6. jen
    March 13, 2014 at 1:33 pm

    First of all, thank you. I think I got some useful information from your posting.
    I want to ask you, however, one thing. Once you've made a file on dropbox, let's say its name is 'statistics', the next csv file delievered from the IFTTT trigger will be 'statistics (1)', right?
    So since they're completely different csv files, the original spreadsheet won't be automatically updated, if I understood correctly.
    That's the situation I am in, and I need a breakthrough. I've tried to figure this out on my own, but it did not work. Help me one more time, please.
    Thank you.