If your struggling for inspiration on creating a blog content strategy, follow our step by step guide below to devise an actionable content plan in 1.5 hours.
What Tools will you need?
AHREFS – if you’ve never used before or you don’t have it, you can sign up for a 7 day trial to complete this task. We highly recommend getting a subscription though!)
Screaming Frog – again if you’ve never used before, you can download the free version which allows you crawl 500 URLs which should be enough for this task
Microsoft Excel or Google Sheets – You will need good old Microsoft Excel or Google Sheets if you prefer!
*Please note, this solution won’t work if blog content sits directly off a root domain e.g. https://www.site1.co.uk/content-piece-1/. Were working on another solution for this and will update this post accordingly!
Compiling the Data
Right, first part of this task is to start compiling all the data you need to create the plan.
1. Get a list of your closest competitors (5 mins)
If you haven’t got a list of competitors to hand, follow the steps below. If you have skip this step.
• Head into AHREFS
• Put your domain in the site explorer
• Hit ‘competing domains’ on the left-hand navigation >
• Download the list of competitors.
• Grab the top 5 competitors from the downloadable sheet.
2. Identify Your Competitors Blog Content (10 mins)
Grab your first competitor and find out where their blog content sits on their site. Does all the blog content sit off a sub folder? E.g. https://www.site1.co.uk/blog/content-piece-1/ or is there several different patterns depending on the type of content. E.g. https://www.site1.co.uk/news/content-piece-1/, https://www.site1.co.uk/advice/content-piece-1/ etc. Rinse and repeat for all 5 competitors.
3. Grab Your Competitor Data from AHREFS (10 mins)
• Head back into AHREFS and put the identified sub folder(s) for where the blog content sits into ‘site explorer’
• Change the drop down setting in the search bar to ‘prefix’, we only want to be look at the sub folder where the blog content is
• Click on the ‘top pages’ option on the left-hand navigation and you will be presented with the screen below
• Hit the export button
• Open a new excel or Google sheet, copy and paste your data into this sheet.
• Put each competitor’s data into 1 tab each and name these appropriately
• Repeat the steps above for each competitor until you have each of these in a tab
4. Grab Your Competitor Data from Screaming Frog (10 mins)
• Open up Screaming Frog
• Hit ‘mode’ on the top dropdown and click on ‘list’
• Go back to your excel sheet with the AHREFS data and copy the URLs from the first competitor
• Go back to Screaming Frog and click on the upload drop down > paste in your URLs
• Screaming Frog will crawl all these URLs, once it’s finished hit the export button
• Open the export and copy + paste this into a new Excel or Google Sheet called Screaming Frog data.
• Rinse and repeat the steps above for each competitor. Create a tab for each competitor in the excel or Google Sheet
• In each of the tabs with the competitor data, delete all the columns except the title tag
You should now have:
• An Excel or Google Sheet with a list of AHREFS competitor data
• An Excel or Google Sheet with a list of Screaming Frog competitor URL data
5. Combine the AHREFS and Screaming Frog Competitor Data Together (10 mins)
• Firstly, delete some irrelevant columns from the AHREFS data sheet. Delete the columns #, traffic %, search volume and position.
• In your AHREFS data sheet, go to your first competitor tab and add a new column, name this new column ‘Keywords to consider’
• In the ‘top keyword’ column, do a VLOOKUP on each blog URL in the AHREFS sheet with the same URL in the Screaming Frog sheet. to pull the title tags of the URLs into the AHREFS data sheet. Find out more on how to do a VLOOKUP if your unsure. Copy and paste as values once the VLOOKUP is done
• Rinse and repeat for each of the competitors.
6. Pick Your Content Ideas & Compile a Plan (45 mins)
You now have all the data at hand to create a blog content plan based on actionable data.
The next part is to pick your content ideas based on the competitor data and create the plan. This is the most important part and where you should spend the most time, that’s why we have added 45 minutes next to this part of the guide!
• On each competitor tab, add a filter to the top row now which means you can sort by the different metrics – e.g. ‘estimated traffic’, how many referring domains the page receives, the estimated conversion value of the blog post.
• The best metrics to use are ‘estimated traffic’ and ‘referring domains’. This means you can find posts which you know will potentially drive traffic and also have the opportunity to gain backlinks
• I would suggest going through each competitor tab and sorting by these 2 metrics and using the colour shade function in Excel to highlight the best posts
• Once you’ve been through each tab, filter all the ideas you have highlighted and copy + paste into a new Excel/Google Sheet. Make sure you copy all of the columns over so you have all the data of each post
• You now have a list of content ideas which you know have a high estimated traffic value + potential for backlink growth along with keyword topics to target for each post. The column for ‘page URL inside’ will be helpful to find if the blog posts have any search result features such as featured snippets, related questions etc. This will be something you can re-visit and explore more when it actually comes to writing the post.
• You can now plot this list into an actionable calendar plan based on the priorities of the metrics
If you have any questions about this guide or need help with compiling a blog content plan, contact our SEO team at Pedro.