In order to produce high-quality original content we pull in copious amounts of data related to search trends, user behavior, and search engine marketing spend. By analyzing this data we can anticipate, infer, and predict, which topics and keywords are most advantageous for marketers to target during content strategy sessions.
In this post we’ll break down our content recommendation spreadsheets and provide tips & tricks for getting the most out of the data we’ve distilled into topic and keyword suggestions.
What Are SEO Insights & Content Recommendations?
We developed our SEO Insights to help marketers and content producers determine what topics and keywords to focus on week-to-week. There are plenty of SEO tools on the market but we’ve found most are too basic (they don’t incorporate trending data, paid marketing spend, etc.) or they are too complicated (huge sheets of data that require a data science background to extrapolate actionable information).
Our worksheets are a happy medium, offering a quick glimpse of which topics and keywords are trending plus a tab of the raw data for those who like manipulating the data themselves
Breaking Down The Spreadsheet
Each SEO recommendation spreadsheet include six main components:
- Trending Topics
- Associated Keywords
- Search Traffic Volume Chart
- Spreadsheet Summary
- Detailed Pivot
- Raw Data (hidden tab)
Trending Topics are “trending keywords” in Google search within various time frames, such as a day, week, month or quarter. Because of their popularity, these keywords can be treated as “sub-topics” of a parent subject. For example, “how did the coronavirus start” and “is the coronavirus airborne” are trending keywords that can be treated as sub-topics of the parent topic, Coronavirus. This is an effective way to group keyword data into a “data hierarchy” that can easily be incorporated into an overall content strategy.
Expand Main Topics to Reveal Secondary Topics
What is Topical Relevance Score?
Currently in beta, our Topical Relevance Score helps prioritize keyword data that is closely aligned with a sub-topic or parent subject. In short, this score is based on the “similarity” of a keyword to a topic, or how “related” a keyword is to a topic you would like to write about. From there, our platform generates a similarity score (“Topical Relevance Score”) for each keyword discovered and sorts them in descending order under each topic.
What is Average Search Volume?
Average search volume refers to the average number of times a keyword is used in Google within the last 30 days. This is a commonly used metric that SEO professionals rely on to help determine which keywords to prioritize in their content strategy. However, it’s important to mitigate expectations when targeting keywords with very large search volume averages. This metric needs to be weighed against other opposing metrics, such as “difficulty to rank” or how relevant a high-volume keyword is to the topic you are writing about. In some cases, you may want to target a low-volume keyword with high transactional intent (that is, a keyword with a high cost-per-click). Transactional keywords, while typically lower in search volume, often lead to higher conversions on your landing page (should it rank well organically), especially for E-Commerce sites.
Using automation, our platform discovers topically related keywords that have historically or recently attracted visibility in Google search for a given subject (i.e., “Coronavirus”). The platform maps all discovered keywords to their respective sub-topics. From there, it sorts all keyword results by how relevant they are to each sub-topic.
In turn, this keyword-level data can be used to inform how your content is structured; how your HTML tags are worded; or how your URL path is designed, among many other SEO uses-cases. In addition, there are multiple ways to segment the keyword data you see by the included filters listed above each pivot table. See sections below for more information on each.
What is Average CPC?
Short for “cost per click”, CPC is the accepted price marketers pay for each click in their pay-per-click (PPC) keyword ad campaigns. The most popular platforms for bidding on keywords are Google AdWords and Bing Ads. The Data Skrive platform calculates the average CPC for each keyword in a dataset. This metric can be used to determine which keywords are more transactional in nature (i.e., “apply for a bank loan”) or informational in nature (i.e., “how does the coronavirus spread”).
What is Average Number of Google Results?
This is the average number of search listings that appeared in Google for each keyword shown in the workbook. For example, the keyword “where does the coronavirus come from” shows an average of 7.8M search listings in Google within the last month. This metric can be used to help determine which keywords carry a high or low level of “search interest” in Google, or estimate how much a topic is being written about across the Web in the U.S.
Search Traffic Volume Chart
This chart helps visualize which topics to write about for a given parent topic. It gives the user a birds-eye view of each topic’s relevance to your parent subject. The chart’s secondary axis allows you to compare how often (on average) each topic is searched for on a monthly basis in Google with its rising popularity. In some cases, this may reveal topics that sit in a “sweet spot”, where both popularity and search volume are relatively strong.
This chart is dynamically connected to the “Top Topics to Write About” pivot table and will change based on the display filters used in that table.
This section provides a summary of high-level insights or takeaways that can be gleaned from the data provided in the pivot tables. This can be used to add context to the data you are seeing when making decisions on which topics or keywords to target. In addition, this section highlights some basic instructions on how to interpret and utilize the data you are seeing in both pivot tables.
Top vs Rising Keywords
Filter keyword data associated with top trends or rising popularity in Google. “Top” trending data refers to search that consistently shows interest in Google, whereas “Rising” data refers to topics that are showing a recent spike in search activity.
Only display keywords that are implicitly or explicitly phrased as questions. For example: “how long does the coronavirus last”.
Superlatives or Comparison Keywords
These keywords can be leveraged to increase your brand’s probability of earning rich search engine results page (SERP) features in Google, such as quick answer boxes or carousels:
- Superlative keywords are search queries that include words like “top”, “most”, “best”, “worst” (i.e., “most dangerous viruses in the world”).
- Comparison keywords are search queries that infer an interest in finding resources that compare similar products or subjects, such as “is coronavirus worse than the flu”, “coronavirus vs influenza” or “ps4 pro vs xbox one x specs”.
Potential SERP Features
This filter can be used to reveal keywords that – if effectively used to optimize your landing page – can lead to varying types of SERP features in Google, such as Knowledge panels, Top Stories boxes, Quick Answer accordions, or Image carrousels.
This filter defaults to “True”. We recommend leaving this filter as-is. Selecting “False” on this filter will yield a much larger scope of keyword data, but the majority of the suggested keywords will likely be irrelevant to your parent or sub-topic; or might prove competitively unrealistic to target. In other words, our platform automatically evaluates the search data we pull to prioritize keywords that are:
- Relatively easier to target; and
- More topically aligned with the parent our sub-topic you want to write about.
This level of automation helps remove the “clerical” work associated with SEO and yields more time to focus on analysis and strategy.
This pivot table is designed for the SEO subject matter expert on your team. It houses the same data expressed in the “Summary” tab, but reveals a higher level of detail that may interest an SEO specialist, strategist or manager on your team. As such, it displays metrics that are relevant to SEO professionals, such as average Google rank, Trust Flow and Referring Domains.
What is Trend Data?
The Trend Data section of the detailed pivot table displays metrics specific to topical-level data, such as topical relevance scores, average search volume numbers, and CPC averages.
What is Keyword Data?
The Keyword Data section displays metrics associated with the suggested keywords found underneath each topic listed in the detailed pivot table. These metrics include average Google rank, average search volume and average CPC.
What is Link Equity Data?
The Link Equity Data section of the detailed pivot table focuses on the number and quality of backlinks associated with each topic or keyword shown in the table. These metrics are used to determine how “authoritative” a topic or keyword is in Google, or how “difficult” it might be to target a specific topic or keyword based on how much backlink authority it currently has.
This tab contains all the source data used to generate the pivot tables found in the provided workbook. By default, this tab is hidden. However, feel free to “unhide” this tab to manipulate the data in a way that works best for you or your marketing team.