It’s easy to see what content gets traffic. It’s one of the most popular reports in Analytics. The All Pages report is sorted by pageviews so it’s all right there. Most marketers know their top traffic pages.
It’s hard to see what content gets conversions. There isn’t a report in Google Analytics that shows the conversion rates of your articles. That’s why very few marketers know their top converting pages.
Of all the articles you’ve published, which ones inspire visitors to subscribe to your newsletter? Which grow your list? Some are much better at this than others.
Your top converting articles are far more compelling than the rest. The conversion rate on the top 10 is probably 10x the average. They are your very best articles, according to your audience.
To find these champs, you’ll pull data from two reports:
So we have the total conversions in one report and the unique pageviews in another. If we divide the first number by the second, we’ll have the conversion rate. We’ll do all it using Google Analytics and Google Sheets. And through the magic of scheduled reports, we can do it once and it will stay up-to-date forever.
We’ll start from the very beginning. But this process assumes that you already have your Google Analytics Goals set up!
Open a new Google Sheet and click on “Add-ons” in the menu. One of the items in the dropdown is “Get add-ons.” Click it and you’ll see a gallery of add-ons with Google Analytics at the top. Add it!
Now that the add-on is in place, that same menu will show the GA add-on and a new submenu. Select “Create new report.”
For the first report, we’ll get the total conversions for each page, just as if we were looking at the Reverse Goal Path report. Name the report “Conversions” (a one-word name is best) and select your Google Analytics Account, Property and View.
Finally, we’ll choose the configuration options.
Note: this assumes the visitor can subscribe directly on the article page and subscribers land on a thank you page. So the article is previous step one in that process. But this isn’t always the case. Here are two scenarios:
It should look like this:
Click the blue “Create Report” button at the bottom. Once complete, Sheets will have a new tab called “Report Configuration.” We’ll come back to that in a minute.
Using the same process, we’ll import data for the second report we’ll need. We’ll name it “Pageviews” and use the same account, of course. Here are the configuration options:
Click the “Create Report” button and Sheets will add a new column to the “Report Configuration” tab. It should look like this:
In this screenshot, you can see I’ve adjusted the Start Date and Limit, to get more data. If at the end of step eight you don’t have much data, you may want to do the same.
Next, we’ll run the reports. From the Add-ons menu, select Google Analytics > Run Reports. Now Google Sheets imports the data from Google Analytics and makes a new tab for each report.
Once the data is in the spreadsheet, all of you Excel experts are free to run wild and start doing calculations. But for anyone who needs a bit more spreadsheet help (like me) here are the steps to get that conversion rate calculated…
It’s easiest to calculate if we can first get all the data into the same tab.
This one we’ll call “Conversion Rate Per Blog Post” since that’s what we’re going to calculate.
First, we’ll bring in the data from the “Pageviews” tab. We’ll have two columns, one for the page and one for the unique pageviews.
Here are the names of the columns and the values to enter in the cells below:
This should pull in all the pages and pageviews from the other tab. Next, we’ll do it for the next two columns, bringing in data from the conversions report.
Column C: Leave blank as a spacer
Now we have all the data we need in one place! But it’s still not ready for the calculation.
Right now, our pages are listed here in two rows. We need to match them up so we can show Pageviews in a new column. This is what the VLOOKUP function is for.
Without getting into the details of the parameters, here is what the values should look like based on everything we’ve done so far.
This tells Sheets to take everything from column D (converting pages) and look for exact matches in column A (pages), and when it finds them, to put the data from column B (pageviews) into column F. Make sense?
I’ll be honest, I got help from my wife on this. Thanks, Crystal!
You’re almost finished. All the data is in the right places. One last step…
Now the easy part. For the seventh and final column, tell Sheets to divide the Conversions (column E) by the Pageviews (column F). Calculating the conversion rate.
Actually, the conversion rate is the percentage of visits (not visitors) that included a goal completion. It is a sessionized metric, based on visits, not visitors. That’s why we’re using unique pageviews, which is the number of visits in which that page was viewed.
Another time, we can talk about conversion rate misconceptions and the common Analytics settings that make them inaccurate and/or meaningless.
Here’s what your report will look like.
We are very close! But there is still a lot of noise in the report. Outliers, useless data and #N/A values. It’s clean up time…
Again, if you’ve got the spreadsheet skills, you know what to do. For the rest of us, here are some tips for filtering and sorting.
Click on any cell in the conversion rate column, then click the Funnel icon in the menu to turn on filters.
Here it is. A list of your best stuff according to your visitors. These are the pages that inspire action and grow your list. If you like charts, make one!
Notice how much more compelling some posts are than others. The top post likely has 10x the conversion rate than the post at the bottom of the list. And most of your content converts so few visitors that it doesn’t appear on the list at all.
Now you know exactly what to prioritize, to promote, to push hard.
As a final step, you can run the reports behind this data on a regular schedule. It’s easy.
From the menu, go to Add-ons > Google Analytics > Schedule reports. Check the box to “Enable reports to run automatically.” Then choose the interval: monthly, weekly, daily or hourly.
Last screenshot. I promise!
I have mine run on Monday mornings at 5am. I’ll have conversions rates with my coffee.
The Orbit blog is filled with posts about driving traffic. But here are 10 to get you started.
Remember, visits to these posts are 10x to 100x more effective than your average article. In other words, one visit to these is worth 100 visits to your average post.
And of course, now that you know your greatest hits, you know what topics to focus on. Consider aligning your content strategy with these. Drop the topics that don’t appear and double down on the topics that do.
Did not find value ‘(entrance)’ in VLOOKUP evaluation.
Any ideas what the issue is here?
This is great – but what if you don’t want to use ga:goalCompletionsAll in the Metrics for Conversions? For example, I have a few different goals set-up in my Analytics – not all conversion related. How do I select specific goal(s)?
Fun stuff, Andy! I ran into a ‘ScriptError’ whilst trying to run the reports. It was because I was signed into two different google accounts. The issue was easily resolved with a quick search (you need to sign out of the one not associated with the GA account) but thought I’d share it here in case anyone else has the same issue. Thanks for the goods!
Leeee! Yes, I had similar issues. When the connection breaks, it was usually because I was suddenly logged in under a different account. Switch accounts, refresh and try again.
Thanks for sharing the troubleshooting tip!
Amazing and valuable content for a new blogger. Thanks so much
I think this would be valuable to see for all landing pages on a website, in addition to the blog posts.
You might even pull the Google Sheets data into a Google Data Studio client dashboard FTW!
Hi there, Eagan! In this example, I didn’t filter for type of page or type of conversion, so as long as your landing pages are visible within that view, they’ll show up. If your landing pages are on a subdomain and not visible in the view, you can set up a filter to make the entire URLs visible …constructor $A1$B1. Fancy, right? And useful!
I once tried to do this in Data Studio, but gave up. I think it would be harder to set up and not really any more useful. But I do have another idea in mind that would require DS (multiple data sources) so stay tuned to this channel.
And thanks for the comment!
While everyone talks about reusing and repurposing content, they overlook the key benefit isn’t to reduce content creation efforts.
Rather the true win with content transformation is to keep your BEST PERFORMING content visible and optimized for conversion goals.
In my experience the highest performing content is lower than the 80/20 rule. This means that less than 20% of your blog posts drive over 80% of your conversions (or other goal including sales.)
Thank you for showing us how to accomplish this with detailed step-by-step guide!
Heidi Cohen – Actionable Marketing Guide
It’s an excellent point, Heidi. And something I should have focused on more. Yes! This process reveals the content that is most worthy of repurposing. Absolutely. In the words of Mr. Kim, when you find a unicorn, make baby unicorns.
Very nice Andy, keep it up
What are your thoughts?