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 and studying for a final exam.
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, IFTTT, Dropbox, 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!
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.
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.
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.
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).
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, IFTTT, Dropbox, 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, 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.
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).