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.