At the beginning of every month, I spend the better part of a day pulling traffic stats for each piece of content on our blog and then analyzing those stats. The manual work of searching Google Analytics and copying and pasting data into a spreadsheet is just as time-consuming and boring as you might expect it to be. There's got to be a better way. And there is!
My teammate Adam DuVander, Zapier's developer marketer and engineering blog author, shared his workflow that automatically pulls in the list of his blog articles and grabs the pageviews data for them from Google Analytics into one spreadsheet. So I copied him. And now I have a self-updating spreadsheet that gets the list of our blog articles and the traffic information I need to look at each month.
In this tutorial, you'll learn how to automatically get the list of your blog articles from your RSS feed put into Google Sheets. Then we'll have Google Analytics pull the traffic to those articles on a schedule and update the corresponding rows in the spreadsheet. The secret sauce here is Zapier's automated app workflows that tie RSS, Google Sheets, and Google Analytics together. Once you set up the Zap (our word for these workflows), the spreadsheet fills itself.
Note: Because this Zap uses multiple steps--15 in fact--you'll need a paid account in order to use it. However, if you sign up to Zapier, you'll start with a free 14-day trial that will give you access to all the paid features, including multi-step Zaps, so you'll be able to try this out and see if it saves you time and agony. If you're already on a free plan, you can contact our support team and mention this article to activate a 14-day trial to try this out.
Ready? Let's set this up. It's best to open another browser window so you can do the steps below while reading this page.
Step 1: Create the Google Sheets Spreadsheet
In a new Google Sheets spreadsheet, label the top row with the fields from the RSS feed you want to track, such as post published date, title, URL, and author. Date and URL are required for this to work.
Also add columns for the traffic timeframes you want to track. In the example below, I want to collect the traffic for the post's first week, first two weeks, and first month.
Step 2: Set Up the Zap to Start Whenever There's a New Item in an RSS Feed
Now we'll have Zapier monitor your blog's RSS feed. Every time a new post goes up, the Zap will add it to a new row in the spreadsheet.
Click the "Make a Zap!" button at the top of Zapier.com and choose RSS by Zapier as the Trigger app.
Select "New Item in Feed" and click "Continue."
And then finally for this step, paste in the URL for your RSS feed.
When you click "Continue," Zapier will test the step for you to make sure everything looks good.
Step 3: Format the Blog URL for Google Analytics
The URL for each post in the RSS feed is the full link to the page, such as https://zapier.com/blog/hello-world. However, Google Analytics doesn't search on the full URL but rather on the path (e.g., /blog/hello-world). So we're going to format the URL to remove the protocol (http://) and domain name.
In the next step, choose Formatter as the Action app and then select "Text" for the kind of data you'll be working with.
Next, choose "Replace" for the transformation type. For the Input field, click the plus (+) button on the right of the field and select "Link" from the first step to get the URL value. Then in the Find field, enter the first part of the URL that you want to delete. When you're done, the form should look like this:
Click "Continue" to test and go on to the next step.
Step 4: (Optional) Format Other Parts of the RSS Feed Results
You can add additional Formatter steps to pretty up what will get sent into your spreadsheet. For example, instead of the date coming in as `Thu, 09 Feb 2017 06:00:00 -0000," you can use Formatter's Date/Time option to change the date style.
Step 5: Create a New Spreadsheet Row
Now we'll send the info from RSS to Google Sheets. Click the plus sign (+) in the left sidebar to add a new Action, and choose Google Sheets as the app.
Select "Create Spreadsheet Row" and choose your Google account (or connect a new Google account).
Then use the fields' dropdown boxes or selection buttons to fill out the form. You'll need to select the spreadsheet you just created and the worksheet within that spreadsheet. Match up the title and URL fields with the RSS results in Step 1. If you formatted other parts of the RSS feed, such as date or author name, select "Output" for those Formatter steps.
Step 6: Add a Delay Action for the Period You Want to Collect the Traffic For
It's time to tell the Zap to wait before pulling the traffic stats from Google Analytics. If you want to record the post's traffic for the first week, we'll set Zapier's Delay app to wait 7 days and then look up the traffic from the date the post was published to the end date for the delay. Similarly, if you want to track the post's traffic for the first 30 days, set up Delay to wait 30 days. (You can actually do both and add any number of timeframes by repeating this step and the ones that come after it, but let's set up the first timeframe.)
After clicking the plus sign to add a new action, choose Delay by Zapier for the Action app. Then select "Delay For" as the type of delay you want.
Enter a number for the amount of time you want to delay and either minutes, hours, days, or weeks for the unit value. In this example, we want to get the first week's traffic, so we'll set the end date for 7 days from the time the post was published to RSS.
After clicking Continue and running the test, we're ready to get the report from Google Analytics.
Step 7: Run the Google Analytics Report
Click the plus sign to create a new Action and choose Google Analytics as the app. Select "Run Report" as the action and click Continue. Then connect your Google Analytics account to Zapier.
The next step--editing the template for your report--is the trickiest and most customizable.
First you'll need to select the Google Analytics account associated with your Google account, the property (as named in GA), and the view (if you have multiple views set up).
For the start date for the report, choose either the published date from the RSS step or the formatted date from the Formatter output, if you used that in step 4 above.
Because there's only one number that we're trying to get for one page to add to one row in Google Sheets, use "Individual data points" for the output format.
You can choose up to 10 metrics to include in the report: Unique pageviews, total pageviews, new users, total users, sessions, time on page, and so on. And you can include dimensions and dimension filters that narrow down those results to what you're looking for specifically.
In my example, I'm only looking for landing page sessions by new blog posts--how many times that page was the first one a visitor interacted with the site (landing page) until they became inactive for 30 minutes (sessions). So for metrics, I selected "Sessions," and for dimensions, I selected "Landing Page."
The dimensions filter helps me narrow down the page to just those on the blog and not to any other area of the site Zapier.com. For the filter, I choose "Landing Page" for the dimension, "Begins with" for the comparison, and the modified URL we formatted in step 2 (/blog/hello-world) as the value (page) we are filtering for. This way, Google Analytics will find the landing page sessions for all of the pages that match that specific page path.
Note: You'll have to play around with this to get the report to pull the data you need. For example, a Zapier customer recently wanted to use this Google Analytics integration to track pageviews for all of their vendors, where each vendor had a subdirectory on their website like /vendorname/. So in their case, they used "Page" (which looks up the path of a page) for the dimension and also for the dimension filter. Then they matched the value field with the vendor name. And, finally, they chose "contains" for the comparison--so that anything tracked in Google Analytics that matched that vendor name would get pulled for the report.
Step 8: Update the Google Sheets Row with the Google Analytics Number
Finally, we'll update our spreadsheet with this traffic stat.
Add another Action step, and as you did above when connecting your Google Sheets spreadsheet, choose: Google Sheets for the app, your Google account, and the spreadsheet and worksheet you're working on.
For Row ID, select "Use a Custom Value." For the Custom Value for Row ID, select the step that contained the "Create Spreadsheet Row" (in my example, it's Step 6, but yours might be different depending on if you added Formatter steps) and the row ID. This tells the Zap to write to the same row that you started writing to with the RSS details.
And then for the column that you set up--first week, first month, etc.--map that field to the last step--Google Analytics and the report item you're looking for.
Click "Continue" to test and you should see your spreadsheet look like this:
Important! Don't delete that initial "Added by Zapier" row or otherwise change the spreadsheet after setting this up. Changes to the spreadsheet could (will likely) mess up the Zap.
Step 9: (Optional) Repeat Steps 6 to 8 with Any Additional Timeframes
You can add more reporting periods by adding the same steps from 6 to 8 above--delay, run Google Analytics report, and update spreadsheet row--for each one.
For example, I'm tracking the first 7 days for the first reporting period, and then for the 14-day period, I added a Delay step with another 7 days after the last step, plus the Google Analytics report step and the Google Sheets update step. If you do this, make sure you choose the correct end date for the report to match the Delay step date output. To get the 30-day report, I added a 16-day Delay step after that.
Step 10. Save, Name, and Begin Using Your Zap.
Finally, follow the prompts to save your Zap. We recommend giving it a name and putting it in a folder so it's easier to find in the future.
Now, every time a new post goes up on your blog, it'll automatically be entered into a spreadsheet and you'll get automated traffic reports within that spreadsheet.
So you can spend more time deciding what that data means and what you can do with it--and less time searching for it and copying and pasting.
Try Other Google Analytics integrations or share how you're automating your website tasks with us in the comments.
Title illustration designed by Freepik.
