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

We’re going to dive show you how to use data to strategically plan your editorial calendar to get more traffic from search engines.

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

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

Note: We use our topic clustering algorithm to identify what topics should be grouped together, separate, what a page’s potential value will be, as well as what order to create content to reduce the time it takes to rank based on your individual topical authority.

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.

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 in descending order.

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.

Update: This method of finding main keywords is less than optimal. Read how to find the best keyword to target and rank in less time.

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, your 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, 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, 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 the searcher’s intent
  • The quality of the ranking website’s on-page SEO

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

The more pages you rank, the better you will get at determining 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 what type of page you need to create—for example, a landing page, product page, or article.

Content Tactic

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

Next, determine the content tactic.

Again, use Google to see what it can tell you about what type of content people are expecting when they search for the topic.

Ahrefs covers how to understand and identify search intent very well in this video.

Final URL

Now enter a URL that’s optimized for users and search engines:

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

Here are 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] 9

The abbreviations I use are:

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

See the Guru card from our knowledge hub to learn how we approach mapping a topic to various stages of the funnel.

Category (i.e. Hub)

When we create content for our clients, we use HubSpot’s topic cluster methodology taken to its full potential with our topic clustering tool.

If you don’t have a way to cluster topics at scale, you can do what we did for years: rely on your gut.

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] 10

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] 11

Rinse and Repeat

Repeat this process for each main keyword you have in your editorial calendar before moving on to 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] 12

Open up Screaming Frog then select Mode → List:

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

Select Upload → Enter Manually.

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

Paste in the competing URLs and then crawl them:

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

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

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

Delete every column except Address, Title, and H1.

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

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] 18

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] 19

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 as 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 a fantastic tool that uses natural language processing and machine learning to make it easy to get more out of the content you’re already producing.

Clearscope is crucial to our content production process. We’ve tried others like Marketmuse, Frase, and Surfer—but Clearscope proved to deliver the best results and the best user experience of them all.

Our writers use it in both the outlining and writing phase to ensure the content they are creating is optimized for search engines without sacrificing user experience.

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

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

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] 21

Paste it into column R:

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

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] 23

Paste it into column S:

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

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] 25

Paste this into column T:

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

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

Important: 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] 27

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

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

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] 29

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] 30

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] 31

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] 32

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] 33

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

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] 34

Next, sort it like this:

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

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] 36

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.

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] 37

Step 9: Priority Scoring

I’ll preface this by saying that the priority score we use for our editorial calendars is proprietary.

We developed a unique way to calculate the topical authority Google perceives your domain as having and blend that with the potential value a topic will have for your business using your specific business metrics (e.g. conversion rates, ACV, pipeline velocity, etc.).

The byproduct is a priority score that has enabled us to publish content for highly competitive topics and rank for them on the first page in 2-3 weeks on average.

Since we can’t give you that priority score, we wanted to give you a replacement that will at least point you in the right direction based on the data you already have.

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] 38

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

Once you have your priority scores calculated, sort all the data by the Priority Score column in descending order to give you an idea of the topics with the most opportunity so you 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:

In general, you’ll want a subject-matter expert to weigh in on the topic prior to drafting it. If you’re just giving briefs to random freelance writers and expecting them to produce high-quality content, you’re living in a fantasy world.

If your freelancers aren’t returning high-quality content, it’s usually because you lacked sufficient detail on how to approach the topic in a way that only an SME can know…

Otherwise, the writer is left to Google their way to a post…producing more of the garbage that we’ve all seen proliferate the search results over the years as a byproduct of SEOs lacking creativity and a deep understanding of your target customers.

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

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

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 check out our article 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 on 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] 40

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] 41

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

Using a 3rd Party Project Management Tool

We prefer managing our content production process in Asana. It’s not the most robust out there and lacks the ability to do many basic things, but its ubiquity makes it easy to adopt and onboard new people.

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.

Tip: Guru is probably one of the best tools to store your SOPs and make them easily accessible by your whole team.

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

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

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.

Want to share this?