How to Build a Data-Driven Editorial Calendar for SEO [with Template]

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!

Fill out the form below to get your copy of the template and then let’s jump 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 requested a copy of the template using the form above 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 Ardent Growth, we use a keyword gap analysis tool we built that automates the process of selecting the 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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 1

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 an editorial calendar, NOT providing an article that links to a bunch of different editorial calendar templates. So rather than clicking on the first link, I’ll 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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 2

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 3

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.

When we started the Ardent Growth blog we had 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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 4

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 5

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 6

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 6

Content Tactic

Next, determine the content tactic.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 8

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 9

Here’s some resources for writing SEO friendly URLs:

Funnel

Choose where the keyword falls in the marketing funnel.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 10

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 11

Public Facing Author

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 12

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 13

Open up Screaming Frog then select Mode → List:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 14

Select Upload → Enter Manually.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 15

Paste in the competing URLs and then crawl them:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 16

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 17

Delete every column except Address, Title, and H1.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 18

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 19

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 20

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 H1 level headings:

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 Ardent Growth, schedule a consultation.

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 21

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 22

Paste it into column R:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 23

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 24

Paste it into column S:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 25

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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 26

Paste this into column T:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 27

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 28

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 29

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 30

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

Paste these values into the tab titled traffic_data:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 31

Now head over to Ahrefs Rank Tracker.

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 32

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 33

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 34

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 35

Next sort it like this:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 36

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).

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 37

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 as 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:

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 38

Step 9: Priority Scoring

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

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 39

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 40

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 41

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 42

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.

How to Build a Data-Driven Editorial Calendar for SEO [with Template] 43

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.

Want to share this?
Share on facebook
Share on linkedin
Share on twitter
Share on reddit
Share on pinterest
Share on email
Schedule a Free Strategy Session
Book a free strategy session with me & you'll get:
Competitor Info
Optimization Info
Custom Pricing