Your Audit Spreadsheet

What To Do First?

First, before you forget, consider making a backup copy of your spreadsheet. Add “backup” to the name so you can confidently make changes knowing that you have the original copy if you need to revert.

Important Note: Due to our limited data storage, Reconalist will only retain your data for ten business days after delivery of your final report. It is imperative that you save a copy.

Next, use as an editorial calendar for your team, you can import your data it into Google Sheets and invite them to edit so your team can use it as your annual published editorial calendar.

Then… familiarize yourself with the process and put your mind at ease.

The Four Tabs

Your Content Prioritization Audit spreadsheet has four tabs:

  1. Editorial Calendar: Consolidated all sources into 1 URL per post.
  2. Content to Create: Track keyword research and brainstorm ideas for future content.
  3. Definitions: How each URL in your audit was assigned a priority.
  4. All Data: ALL of your aggregated data in it’s original format.

On this page, we’ve defined the purpose of each tab, the data included, and ideas on how you may get the most out of each.

Beginners Tip: If you are unfamiliar with Excel check out these spreadsheet tips and tricks.

The first tab is your Editorial Calendar with one keyword per URL

Watch each video for insiders tips and tricks! If you have a question, we’ve most likely covered it on a video. {high fives}

TAB: Editorial Calendar

This is what you need to know.  We spent hours collecting and consolidating several sources of your data. Then, multiple URL structures from multiple sources were converted into the “editorial calendar” tab. Lastly, we auto-populated our data with your data to create custom fields {sub-categories} and dates {month to amplify}. It’s quite the process.

This way every URL on this tab is listed once and assigned a priority of importance. You can view all the keywords for each URL under the “all data” tab.

To Do: We go into detail on explaining the “to do” in each individual priority # which can be accessed from My Recon.

Month To Amplify: You will find videos on how the “month to amplify” column works next in your To Do List. {cuz we love a list. HA!}

To sum it up, the numbers correspond with months.

  • 1 = January
  • 2 = February
  • 3 = March

and so on.

Q: When you say in the video “sort by month” what do you mean?

A: When I say “sort by month” I mean to sort the spreadsheet by “month to amplify” the third column in your spreadsheet, column C.

Tip: The month to amplify is always two months BEFORE it’s needed. For instance, any holiday or things to know about in April (4) would be in your audit for February (2).

This way you sort by 2 (February) and only do what’s listed. Your evergreen data and your seasonal data are treated the same. Less decisions to make and things to think about.

How are the keywords in the spreadsheet generated?  

We used multiple sources to get indexed data on your site so you can see what the search engines are seeing.  If the URL has a keyword, then it will also have a rank number.  Listed is the keyword with the highest search volume.  

Tip:  You can see all the keywords for the URL in your “all data” tab in your spreadsheet.  Simply, sort by URL, a-z.

Why are some keywords missing? 

Not every URL will have a keyword because the indexed data is not displaying a rank.  We added the URL because of it’s social results and/or backlinks.  

Clarification: For you advanced SEO guru’s you may have caught that we’ve added the highest search volume for each URL in the ‘editorial calendar’ tab. Since you already know that there are multiple keywords per URL and it’s important to know each one and their rank so you don’t mess anything up ~ we’ve added all of them into the ‘all data’ tab. Yes, since this is a common error newbies make, we’ve also took that into consideration when creating our algorithm. However, we still encourage you to know what those keywords are before making any big changes.

What’s the difference between an internal and external link?

  • What is an internal link?  Internal links are links that go from one page on a domain to a different page on the SAME domain.  So anytime you link to your own website, that’s an internal link.
  • What is an external link? A backlink. External links are links that go to a DIFFERENT domain.  So anytime you link to  website that’s not your own, that’s an external link.  Likewise, if another website links to you, it’s considered an external link to your site.

Leverage Your Evergreen Content:

  • Assign a ‘month to amplify’ to your evergreen content. Otherwise it can get ignored since it’s not being sorted. {for instance adding a zero won’t work because then it’s not being re-published on a specific month}
  • Sort column by “Evergreen” so you can distinguish what you should be amplifying every month.  This way you can quickly sort to make sure everyone is assigned a month for feature and all are being amplified socially as often as possible.

Learn About SERP Featured Snippets and how to view them. {advanced}

Time Saving Tip: In the beginning, consider only amplifying Priority #1, #2, and #3 on social media. As you edit and fix your other priorities you can let your team know. This way you aren’t having your team add outdated content into their schedule that they later can’t use. That’s just a waste of time.

Detailed explanation of Content to Create tab

TAB: Content to Create

This sheet is meant to help you brainstorm and identify ideas for future content. Please don’t take this list and start writing! {giggle} The categories are general topics and you can sort by associated category, volume, and competition.

About the ‘Content to Create’ Tab:

  • This is not an attempt to teach you how to do keyword research.
  • Helps you keep a running list of keywords and topics you might want to write about.
  • As you make decisions to research specific topics / keywords, you can assign a month in this tab and come back to sort by month to see what you decided to write about that month.
  • The results are broad and generic keywords from AdWords.

How To Use It?

In a nutshell the ‘Content to Create’ tab is a template to show you how you can keep track of your:

  • keyword research
  • topics you’ve already decided to write about

Since we didn’t want that section to be blank, we auto-populated it for you with some high-volume generic terms.  This way you have an example of how to you use it.  In the hope that you’ll change it to what works best for you {and your team}.

We’ve added several links for keyword research in The SEO Toolbox.  Keyword research is its own area of expertise and not something that we teach.  Why?  Because we have found that it’s more important to pick the right post to update first and then focus that post on user intent. 

Which is why we built a tool to help you pick the right post. {high fives} There are tons of people already teaching keyword research.

Frequently Asked Question

Q: Under the “Content to Create” tab, there is a general topic of “chicken recipes” that includes seafood, cookies, pork, beef….why are these all under the chicken topic?

A: This section is simply a data dump from Google AdWords.

That tab is NOT to be taken as concrete keywords to use nor SEO research. This data is…

  • a brainstorm of general topics
  • running list of things that might interest you
  • a list of things you might not have thought about yet

It’s simply to show you an easier way to track your topic ideas so they are all in one place, can be sorted, so you can make your decisions faster!

  • sort by month = what to write about this month and add to your editorial calendar
  • sort by search volume and/or competition = potential traffic
  • sort by category = to compare what you have and have NOT written about yet {fill in those gaps for your readers journey}

You can change anything you want and organize that tab any way you want.

I prefer to have all my keywords in one place so I when I make a decision about it, I give it a month to post and move on.

  • highlight in yellow = topics you want to write about and add a month
  • highlight in red = topics you do not want to write about

TAB: Definitions

This tab explains why each URL in your audit was assigned that specific priority, what to do, and estimated time to update.

  • We wanted to make it really clear how each priority was defined
  • You can change and add priorities, we hope you will make these exactly what works for you!
  • Page views are not factored into priorities.

Estimated Time: We wanted to give you an idea of how each priority ranked against itself in time.

Therefore, each priority is also given an estimated time to update using a very generic rank of short, medium, long, and very long.

For instance, finding a new keyword has more steps than updating a post that already ranks with a current keyword. Therefore using that logic, finding a new keyword would typically take longer than updating a post without having to find a new keyword. {you get the drift}

TAB: All Data

Hmmm, pretty straight forward {wink} … this is ALL of your indexed data. You’ll find that URLs are listed more than once with different associated keywords, and other data points used in the overall analysis are included here.

What’s cool about the “all data” tab is that you can sort the sheet by URL to see every keyword that post is ranking for. This is important when you’re updating a post as you don’t want to remove a good ranking status.

How to see all of your ranking keywords for 1 URL

Please note, if there were multiple ULR structures they have been combined into one to allow effective sorting.

Here is a comparison of the all the data we pulled online, raw. To what we created for the ‘editorial calendar” with all the data organized, prioritized, and auto-populated.

BEFORE ~ You’ll notice the ‘all data’ that we pulled from your site is unorganized, majority have blank fields, and duplicate content. If you did this yourself, this is what your data will look like.

AFTER ~ In the ‘editorial calendar’ section of your audit we consolidated, prioritized, auto-populate these fields for you. Recon is done!

Uplevel Your Spreadsheet Game

In the end, we love data maybe more than you do {cheezy grin}, so while traffic is not the focus of our audit, here are instructions on how to add traffic data into your spreadsheet, if you want to be able to quickly sort those posts as well.

How to track rank progress with your team