Seamlessly Blend SEO Keyword Research with Quality Content Creation Blog

In today’s digital landscape, businesses face the challenge of creating a significant amount of high-quality content while ensuring it aligns with SEO keyword research.

Website URL Form

Seamlessly Blend SEO Keyword Research with Quality Content Creation Blog

In today’s digital landscape, businesses face the challenge of creating a significant amount of high-quality content while ensuring it aligns with SEO keyword research.

On the one hand, they have to strive for authentic content that speaks directly to their audience. And on the other, they have to wrestle with SEO keyword research, trying to make sure their content ranks well on search engines. Moreover, brands are often provided with extensive lists of keywords and content topics without the necessary tools to kick-start content creation.

In this article, we’ll explain why it’s so important to strike a balance between SEO and quality content creation. We’ll use real-life data from a company called Good Time Coffee to show you how to leverage data-driven insights from SEO research to scale content creation effectively. By following these steps, your brand can quickly create a substantial volume of engaging and optimized content!

Objective

The objective of Good Time Coffee’s competitor analysis, alongside audience and keyword research, is to strategically develop a prioritized list of content. This list includes transactional pages like “Shop Coffee Beans” and informative resources such as “How to Make Cold Brew Coffee,” all designed to develop a strong digital presence.

Approach

Step 1: Outline the Information Needed for Content Creation

The first step in scaling content creation is to clearly outline the information required by different stakeholders involved in the process. This includes writers, designers, reporting analysts, and other key contributors. Here’s a breakdown of the recommended metrics for each stakeholder:

Step 2: Collect Required Information

Leveraging the right tools is crucial to streamline the data collection process and ensure accuracy. Semrush’s API integration with Google Sheets provides a reliable and efficient solution. By importing data from Semrush, you can access valuable information such as:

  • Target keyword search volume, difficulty, cost-per-click
  • Top 10 related keywords
  • Top 10 related questions
  • Top 10 organic ranking competitors
  • Top PPC Ad

The method with the least amount of coding required is to use Google Sheets “import data” API calls to Semrush. Its API documentation guide shows all the necessary steps to build calls. For instance, let’s say you’re looking to pull related phrase keywords based on a target keyword. If that target keyword is found in cell B2 of your Google Sheets, you can effortlessly extract the related terms. It’s all about leveraging technology to simplify and streamline your SEO efforts.

=transpose(importdata(“https://api.semrush.com/?type=phrase_this&key=d6ca4f00021acdd7037ab03d3ce04f0a&phrase=”&B2&”&export_columns=Nq,KD,CP&database=us”))

Google Apps Scripts is available to those with coding experience or resources to improve speed and efficiency. Here is the code for the top 10 related questions:

				
					//// Related Keyword; Search Volume; Keyword Difficulty Index; CPC
var url = "https://api.semrush.com/?type=phrase_related&key=d6ca4f00021acdd7037ab03d3ce04f0a& phrase="+oneKeyword+"&export_columns=Ph,Nq,KD,CP&database=us& display_limit=10&display_sort=nq_desc&display_filter=%2B%7CNq%7CLt%7C1000"; // change limit to 10
var response = UrlFetchApp.fetch(url, options);
var firstData = Utilities.parseCsv(response.getContentText());
var potentialError = firstData;
//Logger.log([oneKeyword,potentialError])
if (potentialError != "ERROR 50 :: NOTHING FOUND") {
firstData.shift();
//Logger.log(firstData)
for (var r=0; r<firstData.length; r++) {
 var data = firstData[r][0].split(";")
//Logger.log(data)
 var relatedKeyword = data[0];
 var searchVolume = data[1];
var keywordDifficultyIndex = data[2];
var costPerClick = data[3];
//Logger.log([relatedKeyword,searchVolume,keywordDifficultyIndex,costPerClick])
allMetricsArray.push(relatedKeyword,searchVolume,keywordDifficultyIndex,costPerClick)
}
} else {
allMetricsArray.push('','','','') // 10*4
}
				
			

As the top 10 organic search competitors for each target keyword are pulled into the database, their landing pages are exported to another tab to be crawled by Screaming Frog. With the help of Google Scripts, this competitor data can be seamlessly integrated into your Google Sheets document, creating a centralized research repository including competitors’:

  • Page titles, descriptions, and headers
  • Word counts
  • SERP features 

With an entire landscape of data, you can request GPT to make data-driven decisions on content recommendations such as title tags, meta descriptions, and keywords.

For our demo client Good Time Coffee, we had to run a variety of tests to get the output we desired in the title tags and descriptions. The ideal prompt outline includes having GPT:

  1. Create an informative, actionable, and relevant title tag
  2. Include our target keyword
  3. Include the information from our competitors
  4. To keep the title tag within the allowed character limits on Google search results
				
					var prompt = "Follow the instructions below and respond with the result only, don't explain it. 1. Create an SEO-friendly, informative, and actionable title tag for the landing page"+landing+". 2. The SEO title tag must be more than 45 characters long but less than 60 characters long. 3. Include the target keyword «"+targetKeyword+"» in the SEO title tag. 4. Never use a pipe symbol «|». Use hyphens «-». 5. «"+currentTitle+"» is the page's current SEO title tag. 6. «"+competitorTitle1+"», «"+competitorTitle2+"», «"+competitorTitle3+"» are the best SEO title tags from competitors. 7. «"+cta+"» is the best call to action.";
				
			

It’s important that the prompts are extremely specific. We noticed:

  1. Many title tags were using pipes (|). However, this outdated tactic does not work as well as a hyphen (-) in today’s landscape.
  2. To make the content actionable, we added paid search data to understand what calls to action were being used.
  3. The content is high-quality (we scored it 8 out of 10) but needs some manual lift for a perfect 10 out of 10 score.
Step 3: Create a Content Calendar

Developing a content publication calendar is crucial to maintain a structured approach and ensure visibility into the project’s progress. Leveraging the versatility of the Google Sheets database, you can manipulate the data to generate an editorial calendar based on:

  • Monthly search volume
  • Difficulty
  • Cost-per-click
  • Relevance to business goals

This strategic approach guides content creation efforts, enabling efficient and targeted content production while giving all stakeholders visibility into the progress.

Now, anyone can see at a glance:

  • Which landing pages are being worked on (new or existing)
  • Where the content brief is located
  • What the current project status is
  • The project start and end dates

Here is the Google sheets template for your own content calendar!

Step 4: Begin Work Collaboration

With incredible data comes incredible responsibility. While data analysts prefer working in Google Sheets, other stakeholders, such as content writers, designers, and executives, may find it challenging. The data must be exported into a Google Doc to facilitate collaboration.

There are 700+ lines of code for these documents to be created automatically. We also pull in SEO metrics for existing pages, including impressions, clicks, click-through rates, and average position over the past 12 months from Google Search Console. This helps inform the writer if the existing page is already doing well or not and how flexible the recommended changes may be.

By creating a content brief in Google Docs, you can attach all the relevant research materials, enabling copywriters to commence their work promptly. This collaborative document serves as a centralized hub, fostering seamless communication and ensuring everyone involved has access to the necessary information.

Here is the Google document template for your own content brief outline!

Final Thoughts

Scaling content creation requires a systematic approach to maintain quality while increasing efficiency. By bridging the gap between SEO research and content creation, brands can scale their content production effectively. The steps we have outlined above can help streamline the process, ensuring that stakeholders have access to the necessary information and tools.

Good Time Coffee can now embark on its digital journey, armed with valuable insights gained from research. Over the next three months, the brand plans to create over 60 new SEO-optimized landing pages based on these insights. As it continues to develop its online presence, we look forward to witnessing the brand’s evolving story.

Ultimately, each brand should tailor its approach based on its unique goals and requirements. By implementing these strategies and fostering collaboration among team members, brands can generate a continuous flow of high-quality content that resonates with their target audience.

Here are the Google slides that you can repurpose for your own research: https://docs.google.com/presentation/d/1V3k8_jTVhJ2wKCyycFBs8fSG7W5zb-cn_RQrswHbrVc/edit?usp=sharing

About James

James is the CEO of Piper Marketing, LLC, a full-service marketing agency creating seamless experiences for brands and consumers. For more information, you can reach out to him at james@piperdigitalmarketing.com.