Last edited:

February 9, 2024

Last edited:

February 9, 2024

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:

Utilize data to strategically plan your content marketing campaigns
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:

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

Reason 1: Their pages aren’t optimized for topics with organic search traffic potential
Reason 2: Their pages don’t have enough high-quality backlinks
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.

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.

If you want us to do this for you, schedule a strategy call and we can knock it out in under 2-3 days typically.

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:

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.

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: It has dawned on us that this method of finding main keywords is not in line with the algorithm we developed to find them.

The best way to find main keywords is insanely time-consuming to do manually, so we'll leave this method in for now as the next best option for people attempting to plan an editorial calendar on a budget.

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:

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, 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 determining where you can reasonably rank in a SERP.

Note: Our topic clustering algorithm's priority score has now proven to make most of this step unnecessary as we've been able to consistently compete for popular topics without worrying about how authoritative the competitors are.

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 what type of page you need to create—for example, a landing page, product page, or article.

Content Tactic

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 user readability.

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

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

https://embed.getguru.com/card/ikpM9MpT/Determining-What-Stages-of-the-Marketing-Funnel-an-Article-Maps-To

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

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

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

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.

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.

Next, find the competing URL for each main keyword of your existing pages, as 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 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:

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

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.

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:

https://ahrefs.com/blog/how-to-write-a-blog-post/

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

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:

Build project management into the editorial calendar template
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.

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 Tool

We prefer managing our content production process in. 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.

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, reach out to me on LinkedIn and I’ll get back to you ASAP.

Authors:

Skyler Reeves