It’s the early days in your content marketing program. Maybe you’ve chosen a CMS and your site is just about ready. Surely you’ve visualized what success ultimately looks like. 100,000 monthly visitors! Ranking first for keywords related to your industry! Passive sales from SEO!
But do you know what the path to 100,000 visitors looks like?
Do you have a keyword list that was guided by data?
Don’t fret. We’re here to help.
Whenever we kick off a client engagement, we focus on how we can improve that client’s existing pages and identify opportunities for new content. We’ll then come up with a keyword list that will ultimately guide our content strategy, and we’ll set KPIs along the way to make sure we’re delivering on your goals.
When we make these recommendations, we don’t couch them in opaque reasoning like “data-driven insights” without saying where that data comes from, or “proprietary algorithms” when they’re really just spreadsheets. We show our clients how we use data and how we keep track of progress.
And now we’re sharing these spreadsheets and docs with you. Bookmark this page so you’ll always have these templates handy along with the instructions on how to use them.
To use these Google Sheets and Google Docs templates, click on each one, then in the top left, click on File > Make a Copy.
If you want your blog to rank well, you need to write some of the best articles on that topic online. And to make sure that all your effort is worth it, you need to make sure that customers are actually looking for that content. No pressure, right?
One of our favorite content strategies is something we call the “Wiki Strategy” because it’s modeled after the success of Wikipedia, a resource we all know has high-quality content and tons of it.
Here’s how you use it:
The first step is coming up with a goal for your content strategy. Most likely, you have a sales goal you ultimately want to achieve.
For example: Sell 300 widgets per month to blog readers within six months.
From there, reverse engineer the conversion and traffic goals. Average e-commerce conversion rates are 1-2%. But for our example, let’s be conservative with 1%. The math works out like this:
Sales goal: 300 widgets sold per month in six months
Conversion goal: 1% of all blog visitors
Traffic goal: 300 / 0.01 = 30,000 visitors per month in six months
Don’t worry; we have another spreadsheet template for traffic goals further down the page.
Here’s where you can start chipping away at an early keyword strategy. Have this sheet open in one window and Ahrefs in another. (If you’re feeling fancy, use Spectacle to resize your windows with quick keyboard shortcuts).
In Ahrefs, start looking for keywords within your niche. Aim for at least 50, and copy and paste the keyword, its volume, and its difficulty into the spreadsheet. You can also use these metrics to filter down the list to the best opportunities:
Keyword volume: The goal is to find high search volume. As a rule of thumb, we recommend keywords with at least 500 monthly searches.
Keyword difficulty: Ideally, find low difficulty keywords. Ahrefs offers a scale of 1-100 of how hard it is to rank for a given keyword, where “1” is the easiest. To be super safe with a new site, stay below 20.
To get an even deeper understanding of our Wiki Strategy, we recommend giving our guide a close read while you incorporate the rest of our templates into your workflow.
Once you have a sense of your initial topics and keywords, you’ll want to identify a longer list of keywords to go after. This will give you more blog post ideas and ultimately help you fill in your editorial calendar.
If you can write similarly themed articles for different subjects, you can mix and match those themes and subjects to create article ideas.
For instance, at Cup & Leaf, we wrote a lot of “how to brew” and “health benefits of” articles for the various types of tea.
Use this spreadsheet to input your ideas, and watch the concatenate magic, where a Google Sheets formula links together two cells to reveal new topic ideas.
A style guide isn’t just a tool for designers to stay on brand. Think of it as a training resource for you and your writing team to ensure that you’re sticking to SEO and company best practices. Ultimately, having this standard will enable you to create content more efficiently.
A helpful content style guide includes the following elements:
SEO best practices: Provide guidance on heading and subheading structure, incorporating keywords, and including links. Some of our best practices include using H2 headers in subsections, using the exact key term in the intro paragraph of your article, being sure not to include key terms too many times (no more than 30) to avoid keyword stuffing, and including at least three internal links and one external link.
Grammar guidance with common mistakes: If your marketing team has a strong stance on how to use the Oxford comma (we are decidedly pro), be sure to include that in your style guide. If you have a specific style for how certain words are spelled (WiFi vs. Wi-Fi, for instance), include that too.
Guidance on writing style: There are some general best practices that apply to any company, such as ensuring you always write an interesting, attention-getting introduction. But perhaps you always want to conclude your articles with a specific call to action.
As much as you can’t take a “wait and see” approach to your traffic, you also can’t choose a goal like “100,000 visitors a month” just because it’s a nice, round number.
Aside from digging deeper into things like paired metrics or conversion goals, one way to set good content marketing goals is to create a traffic predictor to check your progress every three months or so.
1. Remember your Wiki Strategy Spreadsheet? Open that up side-by-side with this one. Then copy and paste your keywords and search volume from that sheet into Columns A and B of this traffic predictor spreadsheet.
2. The rest of the spreadsheet will automatically update to make your goals for you.
3. Here’s something you may want to edit: Go over to Columns L and M, where you’ll see we indicated a monthly pace of 12 articles per month (about three articles per week). If you need to, change that number according to your writing output. If you’re feeling aggressive with your content strategy, you might even set this to 16.
The line chart looks at how many articles you’ll be publishing each month, then calculates the average traffic per article, multiples it by the total number of articles you’ll have, and discounts the total traffic by how long you’ve been working on the content. It then spits out a number for the total “discounted traffic,” or how much traffic you could be getting if you did everything perfectly.
You’ll also see ratings of Low, Good, Great, and Excellent to add a little more context to those numbers. Typically, we recommend aiming for the Good number. It’s attainable (especially since you’re planning to create the best content anyway!) without being too lofty.
All that being said, don’t get discouraged if you’re below these goals in the first few months. Remember that content is a long game. But if you get to month six and still can’t get past the “Good” number, you might need to reassess your strategy.
Or by then, you might want to reach out to us to help you strategize and create your content.
Do you have any favorite go-to marketing spreadsheet templates? I’d love to hear from you on Twitter at @growthmachine__.
You did the keyword research strategy and published great content. Now what? Check out these six pieces of advice to make sure you're optimizing your overall content strategy for SEO.
A wave of writer’s and creator’s block hits full force, as you proceed to spend the rest of your afternoon staring at a blank screen. The answer? Swipe files.