May 4, 2020

How To Build A Data-Driven Editorial Calendar For SEO (With Template)

Cody West
Cody is the Founder and Chief Strategist at Assisted Reach where he helps brands grow using business intelligence-focused SEO and content marketing.
Editorial Calendar for SEO Template

COntents

    Add a header to begin generating the table of contents

    For many brands, content marketing can be an investment that yields incredible growth.

    The idea is simple.

    Create great content, promote that content, and then Google will send more traffic to your site.

    However, the process to actually do that has a lot of moving pieces and leaves considerable room for error (there’s a reason 91% of web pages receive no traffic).

    The best way to reduce this error is to:

    1. Utilize data to strategically plan your content marketing campaigns
    2. Utilize great people and process to ensure consistency and quality across your campaigns

    In this post, I will show you how to use data to strategically plan your content marketing efforts using an editorial calendar for SEO.

    The editorial calendar is a Google Sheets template that:

    • Can be worked on simultaneously by your marketing team
    • Provides backlink estimates needed to rank each piece of content you plan on creating
    • Provides traffic potential estimates for each piece of content
    • Includes on-page SEO recommendations based on competitor data
    • A process to ensure you’re internally linking to your most important pages
    • A data-driven approach to prioritizing the creation of content with priority scoring
    • And more!

    Download your copy of the template here and let’s dive into it!

    What Is An Editorial Calendar For SEO?

    An editorial calendar for SEO is a tool used to manage the content production process, specifically for content that is created to acquire organic search traffic.

    Why You Need An Editorial Calendar For SEO

    Here are a few of the reasons you should be using an editorial calendar:

    1. Hit long-term goals with short-term milestones: Content marketing and SEO is a long-term investment. The expression “Rome wasn’t built in a day” is perfect in describing content marketing. An editorial calendar allows you to hit long term goals (i.e. driving 100,000 visits to your website each month) by accomplishing short term milestones (i.e. publishing 2 blogs posts per week).
    2. Make sure your content is optimized for search engines: SEO has become more complicated over the years and there are many moving pieces. Using our editorial calendar for SEO ensures the content your team is creating is optimized for search engines every time.
    3. Obtain consistent results through process and planning: Inconsistent content can be a waste of resources at best or brand damaging at worst. The best way to ensure consistent high-quality, ROI-driven content is through process and planning. Our editorial calendar provides the framework for a good process, which helps ensure you’re producing great content, every time.

    Why Some Brands Fail At Content Marketing

    Like I mentioned in the introduction, 91% of web pages receive zero organic traffic from Google. This implies that brands are wasting a LOT of resources creating content that no one is even seeing.

    The biggest reasons their content isn’t driving organic traffic to their site is:

    1. Reason 1: Their pages aren’t optimized for topics with organic search traffic potential
    2. Reason 2: Their pages don’t have enough high-quality backlinks
    3. Reason 3: There are technical SEO issues preventing their pages from ranking

    The editorial calendar we will be building will ensure you’re not failing due to reason 1.

    If your problem is backlinks, check out our link intelligence report. If you have a technical SEO problem that you need help addressing, schedule a consult here.

    Tools You’ll Need

    Here are the tools you’ll need to build your editorial calendar for SEO using our template:

    • Ahrefs (required)
    • Screaming Frog (required)
    • Clearscope (optional, but recommended)

    How To Use The Editorial Calendar Template

    Make sure you’ve downloaded a copy of the template and then let’s begin!

    Step 1) Find The Main Keyword For Each Content Topic

    Keyword research and finding content topics is outside the scope of this article. I’m assuming you already have a list of keywords that you want to create content for.

    If you don’t, here are some guides to get you started:

    Once you have your list of keywords, you’re going to determine what the main keyword is for each piece of content you plan on creating.

    At Assisted Reach, we use a keyword gap analysis tool I built that automates the process of selecting a main keyword for each topic. If you want us to run an analysis for you using our tool, schedule a consultation.

    If not, here’s how to do this manually (download the Ahrefs toolbar for this).

    Let’s say we have the following 4 keywords mapped to a page:

    • Sample editorial calendar
    • Blog editorial calendar template
    • Editorial calendar
    • Editorial calendar template

    To find the main keyword, Google the first keyword: “sample editorial calendar” and then click “KW” in the Ahrefs toolbar:

    Note: Make sure to click on a URL that is similar to the type of content you would create. In this case, I’m writing a guide on how to create and editorial calendar, NOT providing an article that links to a bunch of different editorial calendar template. So rather than clicking on the first link, I choose the second one.

    This opens the Ahrefs Organic Keywords report, which is sorted by keywords driving the most traffic, to least. Ahrefs traffic metrics also include clicks-per-search, which is crucial to determining the main keyword.

    The keyword “editorial calendar” sends 407 visits per month to the article we’re analyzing by contentmarketinginstitute.com, so we’ll consider this our main keyword. You will want to repeat this exercise for each keyword you’ve mapped to each topic to validate that this is in fact the main keyword.

    Enter the main keyword into the “Main KW” column in the template and then repeat this process for all your keywords. When you are finished, you’re template should look like this:

    Step 2) Fill In The Tactical Elements of Your Editorial Calendar

    Now that we have our main keywords, we’re going to fill in the more tactical elements of our editorial calendar for SEO.

    Competing URL

    The competing URL is a URL ranking on the first page of Google for your main keyword AND that you believe you can outrank.

    Here’s how to determine the competing URL:

    First, Google your main keyword (making sure your Ahrefs toolbar is turned on).

    Next, look at the Domain Ratings (DR) of the competing websites compared to your websites.

    We just started the Assisted Reach blog, so we have a low DR of 26. The first 5 websites ranking for “editorial calendar” all have a DR of 85 or higher. This is going to be tough to compete with, at least initially.

    However, in position #6 is a DR 55 website.

    We’ll select the DR 55 website as our competing URL because it is the highest ranking URL that we can reasonably compete with.

    Using DR is the simplest way to conduct this type of SERP analysis, but you should also consider the following factors:

    • Topical relevance of the ranking website
    • Backlink quality of the ranking website
    • How well the content matches searcher intent
    • The quality of the ranking website’s on page SEO

    I could write an entire article on the process for selecting a competing URL because there are so many one off cases.

    The more pages you rank, the better you will get at guessing where you can reasonably rank in a SERP.

    Secondary Keywords

    Once you find the competing URL for your main keyword, click on “KW” on that URL:

    Ahrefs Organic Keywords report will open. Go through this list of keywords and add any keyword that would make sense as a Secondary KW and that should be included in your article:

    We’ll usually only add secondary keywords that contain unique phrases.

    For example, if we already have “content marketing editorial calendar” added as a secondary keyword, we wouldn’t add “marketing editorial calendar”.

    We use secondary keywords when we outline the article because we want to make sure that we are addressing all secondary keywords within various sections of the article.

    Content Type

    Next, select the content type. Simply Google the main keyword and analyze the SERP to determine if you’re going to want to create more of a landing page, or a blog post.

    Content Tactic

    Next, determine the content tactic.

    Again, use Google to see what the competition is doing and do your best to outdo them while still matching searcher intent.

    Final URL

    Now enter an SEO friendly URL:

    Here’s some resources for writing SEO friendly URLs:

    Funnel

    Choose where the keyword falls in the marketing funnel.

    The abbreviations I use are:

    • TOFU: Top of the funnel
    • MOFU: Middle of the funnel
    • BOFU: Bottom of the funnel

    For more information about where a search is in the marketing funnel, check out these resources:

    Category (i.e. Hub)

    When we build out content for our clients, we use Hubspot’s topic cluster methodology.

    Enter the topic cluster the content belongs to. If it is a pillar piece of content, add a * to the end like this:

    Public Facing Author

    Enter the public facing author of the post, so you can manage more than one author writing for your blog.

    Rinse and Repeat

    Repeat this process for each main keyword you have in your editorial calendar before moving onto step 3.

    Step 3) Use Screaming Frog To Pull In Competitor Data

    The next step in building the editorial calendar is to copy all the competing URLs in column F of the template:

    Open up Screaming Frog then select Mode → List:

    Select Upload → Enter Manually.

    Paste in the competing URLs and then crawl them:

    Crawl the URLs and then export the results as an Excel file.

    Delete every column except Address, Title, and H1.

    Now copy the data (excluding the headers) and paste it into the tab titled sf_data.

    This will populate columns I and L on the Editorial Calendar tab.

    Now, write the title tag and heading 1 for each main keyword. Use the competing title and competing h1 for inspiration.

    Column K is the length of the title you’re going to write. Try to keep it under 60 characters so the full title shows in the SERP. If you go over 60 characters, the cell will turn red like in the example above.

    Here are some good resources on writing titles and heading 1s:

    Step 4) Add Your Main Keywords To Clearscope

    Clearscope is an AI-powered platform that makes it easy to get more out of the content we’re already producing.

    Clearscope is crucial to our content production process.

    Our writers use it in both the outlining and writing phase to ensure the content they are creating is search engine friendly.

    If you want us to train your team and writers on the exact content production process we use at Assisted Reach, schedule a consultation.

    In Clearscope, enter your main keyword and then hit “Run report”.

    Once the report finishes, click into the report, select Share → Enable shared link and then copy the shared link:

    Paste it into column R:

    We provide this URL to our writers along with a few different standard operating procedures for using Clearscope before they begin outlining the content they will be writing.

    Next, copy the word count that’s being recommended by Clearscope:

    Paste it into column S:

    This is a recommendation to our writers NOT a requirement. We want our content to be free of fluff, but comprehensive. The word count recommendation is a guide, but in the end, it’s up to the writer to determine how many words they need to create the best piece of content on the internet for the main keyword we provide them.

    Finally, copy the top content example from Clearscope:

    Paste this into column T:

    Our writers will use this URL as their starting point when they begin outlining because Clearscope is saying this is the best competitor content.

    This is helpful to the writer, but does NOT replace the research phase when producing content.

    Repeat this process for all your main keywords.

    Step 5) Build The Existing Pages Tab

    When you create new content, you need to know what content already exists on your website.

    We built a tab in the editorial calendar template called Existing Pages for this purpose.

    We mainly use this for providing recommendations to our writers for which pages they should be internally linking to.

    First, crawl your website and add all the URLs that you would like to rank for in Google into column A.

    Next, add the categories (i.e. hub) for each URL into column B (this often corresponds to your blog category you have them organized under) and the main keyword for each page in column C.

    Next, find the competing URL for each main keyword of your existing pages, like we did above.

    Now, copy and paste the Competing URLs into Ahrefs Batch Analysis and export the results.

    Delete every column except Target, Domain Rating, Ref domains Dofollow, and Total Traffic.

    Paste these values into the tab titled traffic_data:

    Now head over to Ahrefs Rank Tracker.

    Add your website as a project and then hit “Add keywords”.

    Once you have your keywords added, export them and then delete every column except Keyword and Position.

    Update any keyword tagged with a position of “>100” to “100”.

    Now copy and paste this data to the rank_data tab.

    If you head back to the Existing Pages tab, you’ll notice that the following data pulled through:

    We’ll be using this in the next section when adding internal link recommendations.

    Step 6) Add Internal Links

    The next step is to provide your writer with URLs they should internally link to.

    If you aren’t familiar with the importance of internal linking in content marketing, check out these resources on the topic:

    Rather than randomly internally linking out to various pages on your website, it’s best to control how the link equity is being passed throughout your site.

    Let’s say we are creating a page in the content marketing hub and want to pull internal link recommendations.

    First, in the template select Data → Sort range on the Existing Pages tab.

    Next sort it like this:

    This will sort the Existing Pages sheet by category and then by traffic potential.

    Now, copy the URLs under the Internal Links column (i.e. column H) that have the category “Content Marketing”, so we internally link to other relevant content in the content marketing hub. Next, click on the Editorial Calendar tab and paste these URLs into the Internal Links Recommendations column (column U).

    Our writers know that the URLs we paste into the Internal Link Recommendations column are ordered by importance (i.e they are sorted by relevance and traffic potential). They also know that when internally linking, to use words and phrases similar to the main keyword (which is why we have it appended to the URL they’d internally link to).

    Depending on the content we’re producing, we’ll filter and sort the existing pages tab to bulk select the URLs we want.

    You can use the Rank column to filter out URLs that are already ranking well and that don’t need the extra link equity.

    Traffic potential is a great initial indicator of whether or not you should prioritize internally linking to a page, but also keep in mind the following:

    • Position in the funnel
    • Conversion value
    • Competition

    Another thing to mention is that after you publish an article from your editorial calendar, add that URL to the Existing Pages tab so you know to internally link to it.

    Step 7) Check For Featured Snippets

    Head over to Google and search for the main keyword and all the secondary keywords.

    You’re looking to see if any of these keywords generate a featured snippet.

    If they do, analyze how Google is populating the snippet and then provide notes to your writer in Column W on how they should structure the article to optimize for the snippet.

    Featured snippet optimization is beyond the scope of this article, so check out these resources to learn more about stealing featured snippets:

    Step 8) Use Ahrefs To Pull Traffic Potential & Keyword Difficulty

    In the Editorial Calendar tab, copy all the competing URLs in column F and run paste them into Ahrefs Batch Analysis, click Analyse, and export the results like we did above.

    Delete every column except Target, Domain Rating, Ref domains Dofollow, and Total Traffic.

    Paste these values into the tab titled traffic_data:

    Step 9) Priority Scoring

    After you paste in the data above, you’ll see the following section autofill on the Editorial Calendar tab.

    The last column titled Priority Score is what all this data was for. This score is a means of prioritizing the order in which you create the content. The higher the priority score, the better of an opportunity the post is to create.

    The logic is simple.

    We are ranking the following values and then summing them up to get the priority score:

    • DR
    • Traffic Potential
    • RDs (follow)

    We are NOT weighting one more than another, but feel free to do this if you feel one is more important than another given the website you’re working on.

    When we build these editorial calendars for our clients, it’ll often contain 50+ topics. Once we have priority scores, we like to sort all the data by the Priority Score column in descending order to give us an idea of the topics with the most opportunity so we can create those first.

    Step 10) Developing The Strategy

    The final step in building out this editorial calendar is to add any specific notes you have related to how the content should be approached.

    Again, this is outside the scope of this post, but check out these guides on content strategy to learn more:

    Paste your notes into column V on the Editorial Calendar tab.

    Complete the content strategy and fill out the General Notes section for each topic.

    Once you’re finished, you’re done building the editorial calendar!

    So, what next?

    Will Content Marketing Provide You With A Positive ROI?

    Now that you have an editorial calendar, the next step is to determine if the creation of all this content is worth the investment.

    Calculating SEO ROI is beyond the scope of this post, but you can download our free eBook on how to use a Monte Carlo simulation to calculate the return on SEO.

    Project Management & Content Marketing

    Now that you have the numbers and are confident in your investment into content, the next step is to determine how to manage the content production process.

    You have two options:

    1. Build project management into the editorial calendar template
    2. Use a 3rd party project management system

    Building Project Management Into The Editorial Calendar Template

    Due to the fact that this template is built in Google Sheets, it’s very collaborative. You have the ability to leave comments and have multiple people working in the sheet.

    You can use the status column (i.e. column A) to manage this.

    If you want to edit the steps in the Status column, head over to the data-validation tab and make your changes beneath the status column. This will automatically update the data validation used on the Editorial Calendar tab.

    Note: You can also edit the Content Production Month, Content Type, and Content Tactic from here as well.

    Using A 3rd Party Project Management App

    We prefer managing our content production process in Asana.

    We’ve set up a Zapier that automatically pulls all the information from our editorial calendar into Asana. We also pull in all the process documentation and SOPs, so everything we need to create amazing content is in one place.

    We’ve found this is a MUCH better way to manage the content production process (rather than trying to use Google Sheets).

    If you want help building an in-house content production process, contact us and we can help make that happen.

    Final Thoughts

    Using our editorial calendar template is an excellent way to ensure you’re getting the most out of the content you create by utilizing data to develop the strategy for each post.

    If you have any questions, leave them in the comments and I’ll get back to you ASAP.

    If you want an editorial calendar to drive your content marketing strategy, but don’t want to build one yourself, schedule a consultation.

    related posts