Updated: June 27, 2019
Is blogging part of your marketing strategy? Have you been blogging for awhile? If so, you probably have accumulated a few blog posts at this point. It might be time to do an analysis of how well your blogging strategy is working. Or get a list of everything you have already blogged about? So you don’t risk repeating yourself.
Either way, you need to start with a list of your published blog posts. Here are a few ideas on how to export a list of urls or permalinks for all of your blogs. The majority of options on this list can work for any website platform.
Creating a List of Published Blog Posts.
Here are a few free tools that you can use to create your list. None are going to be a perfect solution. Be prepared to spend a little time cleaning up your spreadsheet once you have created the export.
Option 1 – Export a report from Google Analytics
- Login to your existing Google Analytics account.
- Go to the “Behavior” section in the left side tool bar.
- Click on “Site Content”
- Click on “All Pages”
- At this point, I would recommend expanding the date range. You can do that by clicking on the dates in the top right hand corner. I did two years worth of data for my analysis.
- Next- move down to the bottom of the page and expand the number of rows on your report to capture all of your pages. Look for the drop down box next to “Show Rows”. Note: if your site is bigger than 5000 pages, you may need to pull down multiple reports.
- Scroll back to the top of the page and click on the Export menu (button in the top-right above date). The drop-down box will give you several options to export your report. I usually do the .csv version, it tends to come out cleaner, but Excel would work fine or if you use Google Docs, use the Google Sheets option.
Once you have the export pulled down, you will need to do a little clean up. The report will contain all pages of your site. If you want to analyze only blogs, you will need to do a little clean-up of your spreadsheet to remove urls for core pages.
Why this report really works– Not only will you get a list of pages, but it will also give you some of the key metrics you may need to analyze a blog post’s performance.
The problem with this report – A blog post will only show up here if the post has generated traffic, if that has not happened in the timeframe you selected, you will be missing a few urls. Maybe the post is too new or too old. If you are missing older posts, try expanding the date range if you have the data. Also, if you deleted or redirected any pages in the timeframe you have selected, so you may also need to clean out urls that are now 404 errors or missing pages. If you changed the address of a blog post and the old url got any traffic, the post will be in this report twice. Just keep the current url for your list.
Option 2 – Export data from Google Search Console (a.k.a Webmaster Tools)
- Login to your Google Search Console account.
- From the left side navigation, click on “Performance.”
- Look in the middle of the page, under the line graph and click on “Pages”.
- Make sure to click all the buttons above the graph: Total Clicks, Total Impressions, Average CTR and Average Position are checked. You’ll want all the data.
- Scroll to the bottom of the page. Check the number of pages included in the report. It will be listed as “1-10 of ?”. If that number looks low, try expanding the date range. There is a grey button at the top of the screen. The default is “Date: Last Three Months”. But you do have option to do more.
- Look for a down arrow button in the middle of the screen, on the left edge. Right below where it says “Search Appearance”. Click that button to export your page list.
- Again, I usually select CSV, but Google Sheets are fine too.
Why this report really works– You will get great SEO analytics that export with your page list. Plus, if you don’t have this tool setup, if you setup an account today, in about 24 hours you will have data.
The problem with this report – This list of pages represents the urls that are currently indexed by Google. If any of your blog posts are have not been indexed, you will be missing urls.
Option #3 – Run a Screaming Frog Scan (my favorite option)
If you have not tried to run a Screaming Frog scan on your company website, this is your perfect excuse to do so. Go to their website to download the free software for your Mac or PC. For most sites, the free version may be enough, but if you have a larger site, 1000 pages or more, you may need to pay for the subscription. Totally worth it.
Screaming Frog scans your website just like a search engine spider. Once the software is installed, you type in the domain name or address of your website and press scan. This may take a few minutes. If your site is having speed issues, you may even want to run this after hours. Once it is done, you will see an “export” button appear at the top of the screen, click that to save the scan to a spreadsheet.
Why this report really works– This will be the most up-to-date list of urls from your website. Not to mention the data it collects is really mind blowing.
The problem with this report – You will need to clean up the report to get it down to just blog posts. The scan will pull every url it finds including images, tags, categories and some script or theme files. What I typically do is save the original export so I can go back to it later and make a copy of the spreadsheet before I start deleting things.
Look at the second column in your spreadsheet called “Content”. Sort the sheet by that column and delete everything that is not a “text/html page”. Then go back and further clean-up the list to get it down to just the blog posts.
WordPress Users
There are a few plugins you can use to export a list of all urls on your WordPress site. You may find this works well for you because most will allow you to select only blog posts. The export file will typically have the title of the blog, the url/permalink and the blog category. This will save you from having to remove Urls from your report, but you will have to correlate all your analytics data from other sources manually.
I do these types of reports all the time. Sometimes you can discover a list of tweaks for your site, just by looking this list. I often find titles that need correcting or maybe too many blog titles that are just too similar. Either way, having this list will be really handy as you begin to work on improving your site content.
