I started in Google Analytics by logging into my account and navigating to Behavior / Site Content / All Pages. I had 12,077 results. Of course Google Analytics is showing me all pages, we’ll use this report though with another sheet I’ll show you later to only give us data for our blog posts.
I then went to the bottom of that report and changed my Show rows: value from 10 to 500.
Next I went to the the date range selector and selected everything since the first of the year. Your date range might vary, but choose something longer than just the previous month to get a more complete sample of data.
Finally I went to the Export option at the top menu and choose Excel (XLSX) and saved the new spreadsheet to my computer.
Additional Tip: Use segments to select only organic traffic if you want to see which pages aren’t getting organic traffic. Beware though because you may end up removing pages that have value for other sources of traffic so make sure before you remove your pages that aren’t getting any value from any sources before you just remove them.
To get a list of blog posts I simply went to my Yoast SEO sitemap at the url for the site I’m managing which is at /sitemap_index.xml.
This is the parent sitemap file, so select the post-sitemap.xml or just navigate to /post-sitemap.xml and then drag your cursor down all the pages and copy and paste them into a new excel workbook.
The Screenshot below shows the table I setup. I’m hiding my url columns for privacy purposes.
On the left hand side is the copy and paste from my post-sitemap.xml in rows B & C. I included the Blog URL and the Date Published.
On the right hand side is the data exported Google Analytics. I copied and pasted all my values into this sheet as plain text values so that it would be easier to work with.
You’re also going to need to do a find and replace in Column B where your blog urls are and remove the domain name from your urls because google exports your urls without your domain name.
Highlight the values in that row from B2 to B501 and do a find and replace. Add the domain name including the http:// in the Find what: field and put no value in the Replace with: and finally click Replace All.
I created two new columns for Unique Pageviews and Bounce Rate.
What we need to do now is match up the urls from Column B with the urls from column G. If those columns match, then populate the Unique Pageviews Value into Column D.
Then in cell D you add the formula =VLOOKUP(B2,$G$2:$L$327,2,FALSE).
The first part of the formula looks at the url in column B, the second part of the forumla after the first column looks at Cell G2 through “:” L327 for a match in the url field. In my case L327 was where my results ended at the bottom of my sheet. If there is a match then Excel will populate the field with the 2nd column value which is the Unique Pageviews value. That’s what you put after the second comma. The FALSE statement is for exact matching. You want the value to be FALSE because then it will only find exact matches. This was a helpful article on how to use the VLOOKUP formula.
TIP: The reason we use $ in the formula where the cell values are is because when we go to drag and copy the formula down all our rows we don’t want the cell value to change as we drag. Adding the $ in front of the cell values makes it so that they don’t change as you drag.
Do a similar formula but change the 3rd value to 5 to populate the Bounce Rate. I wanted to see the bounce rate to see if there were some posts that were getting traffic but not qualified traffic to see if we should update those posts with better CTA’s or if the post was bringing unqualified traffic and maybe for that reason we should bring down the post.
So that formula looks like this: =VLOOKUP(B2,$G$2:$L$327,5,FALSE)
That 3rd value is for how many columns you are away from the beginning of your formula.
Now I highlighted my 4 columns including the header and went over to Format as Table and selected a table type with header rows. This allows me to sort my table by Unique Pageviews Smallest to Largest values. Now I can see all the posts that are getting little to no pageviews. I can now go through them one by one and determine which ones I want to remove and then look through the list to see where I might want to redirect them to.
I have other worksheets for how to create massive 301 redirect lists so if you need help with that or if you have other feedback or comments let me know below or hit me up in a private message on Facebook.