Monley Homes

Monday, 1 February 2021

"“““““““““““““““““““““““““““““““““ Posted by Imogen_Davies At Google’s Search On event in October..."

““““““““““““““““““““““““““““““““““

At Google’s Search On event in October last year, Prabhakar Raghavan explained that 15% of daily queries are ones that have never been searched before. If we take the latest figures from Internet Live Stats, which state 3.5 billion queries are searched every day, that means that 525 million of those queries are brand new.

That is a huge number of opportunities waiting to be identified and worked into strategies, optimization, and content plans. The trouble is, all of the usual keyword research tools are, at best, a month behind with the data they can provide. Even then, the volumes they report need to be taken with a grain of salt – you’re telling me there are only 140 searches per month for “women’s discount designer clothing”? – and if you work in B2B industries, those searches are generally much smaller volumes to begin with.

So, we know there are huge amounts of searches available, with more and more being added every day, but without the data to see volumes, how do we know what we should be working into strategies? And how do we find these opportunities in the first place?

Finding the opportunities

The usual tools we turn to aren’t going to be much use for keywords and topics that haven’t been searched in volume previously. So, we need to get a little creative — both in where we look, and in how we identify the potential of queries in order to start prioritizing and working them into strategies. This means doing things like:

Mining People Also Ask
Scraping autosuggest
Drilling into related keyword themes

Mining People Also Ask

People Also Ask is a great place to start looking for new keywords, and tends to be more up to date than the various tools you would normally use for research. The trap most marketers fall into is looking at this data on a small scale, realizing that (being longer-tail terms) they don’t have much volume, and discounting them from approaches. But when you follow a larger-scale process, you can get much more information about the themes and topics that users are searching for and can start plotting this over time to see emerging topics faster than you would from standard tools.

To mine PAA features, you need to:

1. Start with a seed list of keywords.

2. Use SerpAPI to run your keywords through the API call – you can see their demo interface below and try it yourself:

3. Export the “related questions” features returned in the API call and map them to overall topics using a spreadsheet:

4. Export the “related search boxes” and map these to overall topics as well:

5. Look for consistent themes in the topics being returned across related questions and searches.

6. Add these overall themes to your preferred research tool to identify additional related opportunities. For example, we can see coffee + health is a consistent topic area, so you can add that as an overall theme to explore further through advanced search parameters and modifiers.

7. Add these as seed terms to your preferred research tool to pull out related queries, like using broad match (+coffee health) and phrase match (“coffee health”) modifiers to return more relevant queries:

This then gives you a set of additional “suggested queries” to broaden your search (e.g. coffee benefits) as well as related keyword ideas you can explore further.

This is also a great place to start for identifying differences in search queries by location, like if you want to see different topics people are searching for in the UK vs. the US, then SerpAPI allows you to do that at a larger scale.

If you’re looking to do this on a smaller scale, or without the need to set up an API, you can also use this really handy tool from Candour – Also Asked – which pulls out the related questions for a broad topic and allows you to save the data as a .csv or an image for quick review:

Once you’ve identified all of the topics people are searching for, you can start drilling into new keyword opportunities around them and assess how they change over time. Many of these opportunities don’t have swathes of historical data reported in the usual research tools, but we know that people are searching for them and can use them to inform future content topics as well as immediate keyword opportunities.

You can also track these People Also Ask features to identify when your competitors are appearing in them, and get a better idea of how they’re changing their strategies over time and what kind of content and keywords they might also be targeting. At Found, we use our bespoke SERP Real Estate tool to do just that (and much more) so we can spot these opportunities quickly and work them into our approaches.

Scraping autosuggest

This one doesn’t need an API, but you’ll need to be careful with how frequently you use it, so you don’t start triggering the dreaded captchas.

Similar to People Also Ask, you can scrape the autosuggest queries from Google to quickly identify related searches people are entering. This tends to work better on a small scale, just because of the manual process behind it. You can try setting up a crawl with various parameters entered and a custom extraction, but Google will be pretty quick to pick up on what you’re doing.

To scrape autosuggest, you use a very simple URL query string:

https://ift.tt/3aep6p5

Okay, it doesn’t look that simple, but it’s essentially a search query that outputs all of the suggested queries for your seed query.

So, if you were to enter “cyber security” after the “q=”, you would get:

This gives you the most common suggested queries for your seed term. Not only is this a goldmine for identifying additional queries, but it can show some of the newer queries that have started trending, as well as information related to those queries that the usual tools won’t provide data for.

For example, if you want to know what people are searching for related to COVID-19, you can’t get that data in Keyword Planner or most tools that utilize the platform, because of the advertising restrictions around it. But if you add it to the suggest queries string, you can see:

This can give you a starting point for new queries to cover without relying on historical volume. And it doesn’t just give you suggestions for broad topics – you can add whatever query you want and see what related suggestions are returned.

If you want to take this to another level, you can change the location settings in the query string, so instead of “gl=uk” you can add “=us” and see the suggested queries from the US. This then opens up another opportunity to look for differences in search behavior across different locations, and start identifying differences in the type of content you should be focusing on in different regions — particularly if you’re working on international websites or targeting international audiences.

Refining topic research

Although the usual tools won’t give you that much information on brand new queries, they can be a goldmine for identifying additional opportunities around a topic. So, if you have mined the PAA feature, scraped autosuggest, and grouped all of your new opportunities into topics and themes, you can enter these identified “topics” as seed terms to most keyword tools.

Google Ads Keyword Planner

Currently in beta, Google Ads now offers a “Refine keywords” feature as part of their Keyword Ideas tool, which is great for identifying keywords related to an overarching topic.

Below is an example of the types of keywords returned for a “coffee” search:

Here we can see the keyword ideas have been grouped into:

Brand or Non-Brand – keywords relating to specific companies
Drink – types of coffee, e.g. espresso, iced coffee, brewed coffee
Product – capsules, pods, instant, ground
Method – e.g. cold brew, French press, drip coffee

These topic groupings are fantastic for finding additional areas to explore. You can either:

Start here with an overarching topic to identify related terms and then go through the PAA/autosuggest identification process.
Start with the PAA / autosuggest identification process and put your new topics into Keyword Planner

Whichever way you go about it, I’d recommend doing a few runs so you can get as many new ideas as possible. Once you’ve identified the topics, run them through the refine keywords beta to pull out more related topics, then run them through the PAA/autosuggest process to get more topics, and repeat a few times depending how many areas you want to explore or how in-depth you need your research to be.

Google Trends

Trends data is one of the most up-to-date sets you can look at for topics and specific queries. However, it is worth noting that for some topics, it doesn’t hold any data, so you might run into problems with more niche areas.

Using “travel ban” as an example, we can see the trends in searches as well as related topics and specific related queries:

Now, for new opportunities, you aren’t going to find a huge amount of data, but if you’ve grouped your opportunities into overarching topics and themes, you’ll be able to find some additional opportunities from the “Related topics” and “Related queries” sections.

In the example above we see these sections include specific locations and specific mentions of coronavirus – something that Keyword Planner won’t provide data on as you can’t bid on it.

Drilling into the different related topics and queries here will give you a bit more insight into additional areas to explore that you may not have otherwise been able to identify (or validate) through other Google platforms.

Moz Keyword Explorer

The Moz interface is a great starting point for validating keyword opportunities, as well as identifying what’s currently appearing in the SERPs for those terms. For example, a search for “london theatre” returns the following breakdown:

From here, you can drill into the keyword suggestions and start grouping them into themes as well, as well as being able to review the current SERP and see what kind of content is appearing. This is particularly useful when it comes to understanding the intent behind the terms to make sure you’re looking at the opportunities from the right angle – if a lot more ticket sellers are showing than news and guides, for example, then you want to be focusing these opportunities on more commercial pages than informational content.

Other tools

There are a variety of other tools you can use to further refine your keyword topics and identify new related ideas, including the likes of SEMRush, AHREFS, Answer The Public, Ubersuggest, and Sistrix, all offering relatively similar methods of refinement.

The key is identifying the opportunities you want to explore further, looking through the PAA and autosuggest queries, grouping them into themes, and then drilling into those themes.

Keyword research is an ever-evolving process, and the ways in which you can find opportunities are always changing, so how do you then start planning these new opportunities into strategies?

Forming a plan

Once you’ve got all of the data, you need to be able to formalize it into a plan to know when to start creating content, when to optimize pages, and when to put them on the back burner for a later date.

A quick (and consistent) way you can easily plot these new opportunities into your existing plans and strategies is to follow this process:

Identify new searches and group into themes
Monitor changes in new searches. Run the exercise once a month to see how much they change over time
Plot trends in changes alongside industry developments. Was there an event that changed what people were searching for?
Group the opportunities into actions: create, update, optimize.
Group the opportunities into time-based categories: topical, interest, evergreen, growing, etc.
Plot timeframes around the content pieces. Anything topical gets moved to the top of the list, growing themes can be plotted in around them, interest-based can be slotted in throughout the year, and evergreen pieces can be turned into more hero-style content.

Then you end up with a plan that covers:

All of your planned content.
All of your existing content and any updates you might want to make to include the new opportunities.
A revised optimization approach to work in new keywords on existing landing pages.
A revised FAQ structure to answer queries people are searching for (before your competitors do).
Developing themes of content for hubs and category page expansion.

Conclusion

Finding new keyword opportunities is imperative to staying ahead of the competition. New keywords mean new ways of searching, new information your audience needs, and new requirements to meet. With the processes outlined above, you’ll be able to keep on top of these emerging topics to plan your strategies and priorities around them. The world of search will always change, but the needs of your audience — and what they are searching for — should always be at the center of your plans.

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

”

- http://monleyhomes.blogspot.com/2021/01/finding-keyword-opportunities-without.html
from Tumblr https://monleyhomes.tumblr.com/post/641558199341809664”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles.html
from Tumblr https://monleyhomes.tumblr.com/post/641569526841999360”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_28.html
from Tumblr https://monleyhomes.tumblr.com/post/641580849268178944”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_86.html
from Tumblr https://monleyhomes.tumblr.com/post/641592175644360704”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_32.html
from Tumblr https://monleyhomes.tumblr.com/post/641603500148752384”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_19.html
from Tumblr https://monleyhomes.tumblr.com/post/641614823011041280”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_63.html
from Tumblr https://monleyhomes.tumblr.com/post/641626147556327424”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_98.html
from Tumblr https://monleyhomes.tumblr.com/post/641637471270109184”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_29.html
from Tumblr https://monleyhomes.tumblr.com/post/641648797364125696”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_59.html
from Tumblr https://monleyhomes.tumblr.com/post/641660122032177152”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_96.html
from Tumblr https://monleyhomes.tumblr.com/post/641671447109091328”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_47.html
from Tumblr https://monleyhomes.tumblr.com/post/641682775554048000”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_8.html
from Tumblr https://monleyhomes.tumblr.com/post/641694099004686337”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_21.html
from Tumblr https://monleyhomes.tumblr.com/post/641705422392262656”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_11.html
from Tumblr https://monleyhomes.tumblr.com/post/641716746165813249”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_7.html
from Tumblr https://monleyhomes.tumblr.com/post/641728070283313152”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_30.html
from Tumblr https://monleyhomes.tumblr.com/post/641739395195666432”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_80.html
from Tumblr https://monleyhomes.tumblr.com/post/641750722045739008”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_22.html
from Tumblr https://monleyhomes.tumblr.com/post/641762045941923840”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_92.html
from Tumblr https://monleyhomes.tumblr.com/post/641773371190804480”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_72.html
from Tumblr https://monleyhomes.tumblr.com/post/641784696407293952”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_90.html
from Tumblr https://monleyhomes.tumblr.com/post/641796019055640576”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_78.html
from Tumblr https://monleyhomes.tumblr.com/post/641807342869069824”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_14.html
from Tumblr https://monleyhomes.tumblr.com/post/641818668495503360”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_31.html
from Tumblr https://monleyhomes.tumblr.com/post/641829994189979648”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_73.html
from Tumblr https://monleyhomes.tumblr.com/post/641841319921238016”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_20.html
from Tumblr https://monleyhomes.tumblr.com/post/641852643937075200”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_55.html
from Tumblr https://monleyhomes.tumblr.com/post/641863968241172480”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_2.html
from Tumblr https://monleyhomes.tumblr.com/post/641875295517949952”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_93.html
from Tumblr https://monleyhomes.tumblr.com/post/641886618133970944”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_42.html
from Tumblr https://monleyhomes.tumblr.com/post/641897943038853120”

- http://monleyhomes.blogspot.com/2021/01/posted-by-imogendavies-at-googles_66.html
from Tumblr https://monleyhomes.tumblr.com/post/641909267024248832”

- http://monleyhomes.blogspot.com/2021/02/posted-by-imogendavies-at-googles.html
from Tumblr https://monleyhomes.tumblr.com/post/641920592567762944”

- http://monleyhomes.blogspot.com/2021/02/posted-by-imogendavies-at-googles_1.html
from Tumblr https://monleyhomes.tumblr.com/post/641931916967313409

"Posted by Tom.Capper Way back in 2015, I published an article giving away a free, simple,..."

“

Posted by Tom.Capper

Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.

In short: you could enter in a series of data, and it would plot it out on a graph like the image above.

Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.

I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.

The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).

In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).

Types of SEO forecast

There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.

Broadly, I think you can put SEO forecasts into three groups:

“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).

Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.

What makes this an SEO forecast?

Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:

We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.

I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.

For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.

That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…

How to use the template

FREE TEMPLATE

The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.

Then, on the first tab, you’ll notice some cells have a green or blue highlight:

You should only be changing values in the colored cells.

The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.

On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:

You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.

Make sure you also delete any leftovers of my example data in column B.

Outputs

Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:

Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.

You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.

The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.

Advanced use cases

You may by now have noticed the “Advanced” tab:

Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.

In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.

You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:

It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.

Here’s some example use cases of this tab for you to consider:

Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)

Why are my estimates different to your old tool? Is one of them wrong?

There’s two major differences in method between this template and my old tool:

The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.

If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.

It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.

How does it work?

There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.

The inputs I’m using are:

Dependent variables
- Whatever you put as column B in the inputs tab (like traffic)
Independent variables
- Linear passing of time
- Previous period’s traffic
- Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
- Up to three “advanced” variables

The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:

“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)

You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.

Potential extensions

If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:

Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)

Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:

Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)

I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.

If you’ve made it this far, what would you like to see? Let me know in the comments!

”

- http://monleyhomes.blogspot.com/2021/02/seo-forecasting-in-google-sheets.html
from Tumblr https://monleyhomes.tumblr.com/post/641920593294491648

"“““““““““““““““““““““““““ Posted by amandamilligan If you need to get backlinks and generate brand..."

““““““““““““““““““““““““““

Posted by amandamilligan

If you need to get backlinks and generate brand awareness for clients, a great way to start is by creating original research and then pitching that research to writers. But the promotion of your work is probably the trickiest part, and a lot of it comes down to the pitch email you send to a writer.

To make this task a bit less daunting, in this episode of Whiteboard Friday, Amanda Milligan of Fractl walks you through a real pitch email that resulted in coverage of one of their stories.

Click on the whiteboard image above to open a high resolution version in a new tab!

Video Transcription

Hi, everyone. Welcome to another edition of Whiteboard Friday. My name is Amanda Milligan. I’m the Marketing Director at Fractl. Today I’m going to talk to you about the anatomy of the perfect pitch email.

This has to do with the digital PR space. The way that we get backlinks and brand awareness for our clients is by creating original research, new studies and surveys, and then pitching those things to writers. Now the pitching and the promotion is some of the trickiest part, and a lot of it comes down to this — the email you send to a writer.

So what I’ve done here is literally write out a real pitch email that was sent to a writer that resulted in a publication and coverage of the story. I’ll shout out to Skylar who wrote this one. What I’m going to do is walk through each piece of it, each element that we think is extremely important and that we include in all of our emails.

Human connection

So to start, I actually use this email because it didn’t delve too much into the personalization. I wanted to show an example of what happens if you can’t personalize as well. But personalization or any kind of human connection is extremely important, and it should be the lead into the body of your email.

So in this case, it’s a little more general. It says, “We all remember the horror flicks that left us sleeping with the lights on.” So that’s a more general human experience. I know I slept with the lights on when I saw “The Ring” for the first time. That’s just some way to connect with the person who’s reading it, to have them think of a memory.

However, if you actually have a chance to personalize an email, for example, if the writer has written something that resonates with you recently or you follow them on Twitter or LinkedIn and you like something that they shared, you can connect with them — you went to the same school, you have the same love of animals. We actually have a lot of people who pitched this year like pitching pictures of their animals and talking about how much they love dogs or cats.

Anything that is genuine can do really well. But remember that there’s a human being on the other side of the email that you’re sending, and just humanize this a little bit. So that should be about a sentence or two. As you can see here, it ends about here. So you don’t want to go into a whole life story, but touch on that a little bit.

Top-level project description

The next segment is a top-level project description. So the next sentence here says, “Could you imagine if one of those characters occupied the room next to yours?” So now we’re bridging the kind of anecdote to the actual project. “To explore this further, my team asked over 1,000 TV and movie fans about their most and least desirable fictional roommates.”

So right there you know exactly what the project is about. It’s about a survey we did asking people which fictional characters in all kinds of media they would like to live with. So it’s very fun. It’s a light piece. It’s a fun piece. However, the structure is still the same when creating these pitch emails. No matter if it’s hard news or something a little more lighthearted, this is a really effective way to go.

Main takeaways

So that covers human connection and top-level project description. This next piece is arguably the most important. This, as you can see over here, are the main takeaways, the biggest, most interesting, new insights from this study that you did. You don’t want the writer to be sifting through your content trying to figure out why they care or why any of their readers are going to care.

It’s your job to pull the two or three most interesting takeaways, literally create a bulleted list for them so that they can see it very quickly. So in this case, Skylar literally said, “Here’s what we found: The Beetlejuice home ranks as one of the most identifiable movie houses. However, Beetlejuice was the least desirable fictional roommate.”

Understandably. The reason why I can assume she called this out is because, in this particular pitch, she was pitching a home publication, so she’s talking about houses. The reason I highlight that is you shouldn’t have even the same body of a pitch that you send to everyone. It depends on who their readers are and the topics that they cover, the subtopics they cover.

Even if you know they’re relevant and you’re pitching them in the first place, make sure to tailor every aspect of the email to them specifically. You might have a list of 10 to 15 interesting takeaways, and you piece together which ones make the most sense per writer. So then some other facts. “Movie fans agree Norman Bates would have been equally undesirable as the Hulk would be as a roommate.”

Which is just fun. “Despite appearing in your dreams while you’re fast asleep, Freddy Krueger ranks as less desirable than Hannibal Lecter.” So the fun thing about this project and something I didn’t mention at the top is that we were pitching it around Halloween. So it makes a little more sense. You have that timeliness factor also.

This is fun, but they’re basically writing these bullet points thinking like, “What can the writer’s headline be? What are they going to say is the most interesting part of this project, and why do they think it’s going to be fun or funny or entertaining or useful or informative?” So that covers this section. It’s extremely important. Honestly, as you’re creating content, you should be thinking of these things, hypothesizing what these could look like.

Link to the content

If you can’t even imagine what little bullet points you’re going to be able to create after you do something, it might not be interesting enough, or you might not be on the right track. So then this is important. It’s small, but it’s important. “Here’s a link to the full study.” Linked. Some people do the tactic of kind of asking, “Oh, do you want to see the rest? We can send it to you.”

We don’t recommend doing that because you don’t want to add an extra step. You don’t want writers to have to work for anything. You want to give them everything they need to make a decision. So you’re making it easy for them by calling out the bullet points that are the most relevant. Then you’re saying, “But listen, look at the whole study if you want. If this is intriguing to you, here it is. You can view the whole thing and make a decision as to whether it’s a good fit for your audience.” So be sure to do that.

Direct ask

Then Skylar did a good job by saying, “It’s the very first day of October,” which it was at the time, “and your readers are gearing up for Halloween.” So she’s tying it back to the relevancy of the project to their readers, which is what you always have to think about. The writer only cares about whether something is going to resonate with their readership. That means that they’re doing a good job. So she kind of ties that up. “Any interest in sharing this exclusive study with [the publication]?” So I highlighted here a direct ask. So come out and say like, “So do you want to cover this?” In this case, we were pitching it as an exclusive, meaning nobody else hadn’t covered it yet, which makes it a little more appealing.

You’re saying, “You’re going to be the first ones to talk about this study.” You can say it’s exclusive, and you can highlight that in the email as well. But even if you’re not doing that, if you’re pitching it to a bunch of people or somebody has already covered it and you’re still pitching it, just make sure you directly ask, “Are you interested in covering this?” Don’t assume that they even know how to respond. So those are the four main components of a pitch email.

Conclusion

Now there’s a lot that goes into making this work. This is just one piece of a greater puzzle. Your content has to be fantastic, because, as I say, no fantastic pitch can salvage a terrible project. You just can’t pitch your way out of it. But also you need to be targeting the right people.

So sometimes we have fantastic pitch emails go out, or anybody has fantastic pitch emails go out, but the person, for whatever reason, can’t cover the content. That happens. It certainly happens. Sometimes people have full editorial calendars, or they just wrote about something recently similar. But you want to avoid the situation where they say, “Cool pitch, but this isn’t my niche.”

This happens all the time in the industry. We surveyed 500 publishers last year, in 2019, depending on when you’re listening to this, and they said that their number one pet peeve is being pitched content that does not match their niche. So they’re being pitched things that they don’t typically write about. So this is a fantastic way to increase the chances of your pitch being successful, but that doesn’t mean that it’s foolproof if you haven’t done all these other steps.

If you’re interested in learning about those things, check out my other content on Moz. I’ve talked about what makes great content. I’ve talked about some things to look at when it comes to who to pitch. All these things fit together. But I did want to break down for you exactly what that pitch can look like. So best of luck out there. I know it’s tough.

Talk to you soon.

Video transcription by Speechpad.com

”

- http://monleyhomes.blogspot.com/2021/01/the-anatomy-of-perfect-pitch-email.html
from Tumblr https://monleyhomes.tumblr.com/post/641637471687442432”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to.html
from Tumblr https://monleyhomes.tumblr.com/post/641648797715398656”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_29.html
from Tumblr https://monleyhomes.tumblr.com/post/641660122710589440”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_56.html
from Tumblr https://monleyhomes.tumblr.com/post/641671447588356096”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_63.html
from Tumblr https://monleyhomes.tumblr.com/post/641682776034246656”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_22.html
from Tumblr https://monleyhomes.tumblr.com/post/641694099332890624”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_76.html
from Tumblr https://monleyhomes.tumblr.com/post/641705422929166336”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_20.html
from Tumblr https://monleyhomes.tumblr.com/post/641716746787684352”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_97.html
from Tumblr https://monleyhomes.tumblr.com/post/641728070574800896”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_30.html
from Tumblr https://monleyhomes.tumblr.com/post/641739395510288384”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_35.html
from Tumblr https://monleyhomes.tumblr.com/post/641750722314207232”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_90.html
from Tumblr https://monleyhomes.tumblr.com/post/641762046620352512”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_45.html
from Tumblr https://monleyhomes.tumblr.com/post/641773371908063232”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_31.html
from Tumblr https://monleyhomes.tumblr.com/post/641784697325813760”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_59.html
from Tumblr https://monleyhomes.tumblr.com/post/641796019742474240”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_9.html
from Tumblr https://monleyhomes.tumblr.com/post/641807343596830720”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_36.html
from Tumblr https://monleyhomes.tumblr.com/post/641818669262962688”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_80.html
from Tumblr https://monleyhomes.tumblr.com/post/641829994493067264”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_95.html
from Tumblr https://monleyhomes.tumblr.com/post/641841320240037888”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_3.html
from Tumblr https://monleyhomes.tumblr.com/post/641852644341776384”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_62.html
from Tumblr https://monleyhomes.tumblr.com/post/641863968724516864”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_15.html
from Tumblr https://monleyhomes.tumblr.com/post/641875295933202432”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_21.html
from Tumblr https://monleyhomes.tumblr.com/post/641886618477821952”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_27.html
from Tumblr https://monleyhomes.tumblr.com/post/641897943904944128”

- http://monleyhomes.blogspot.com/2021/01/posted-by-amandamilligan-if-you-need-to_11.html
from Tumblr https://monleyhomes.tumblr.com/post/641909267370262528”

- http://monleyhomes.blogspot.com/2021/02/posted-by-amandamilligan-if-you-need-to.html
from Tumblr https://monleyhomes.tumblr.com/post/641920592915939328

"Posted by Imogen_Davies At Google’s Search On event in October last year, Prabhakar Raghavan..."

“““““““““““““““““““““““““““““““““

Posted by Imogen_Davies

Finding the opportunities

Mining People Also Ask
Scraping autosuggest
Drilling into related keyword themes

Mining People Also Ask

To mine PAA features, you need to:

1. Start with a seed list of keywords.

2. Use SerpAPI to run your keywords through the API call – you can see their demo interface below and try it yourself:

3. Export the “related questions” features returned in the API call and map them to overall topics using a spreadsheet:

4. Export the “related search boxes” and map these to overall topics as well:

5. Look for consistent themes in the topics being returned across related questions and searches.

This then gives you a set of additional “suggested queries” to broaden your search (e.g. coffee benefits) as well as related keyword ideas you can explore further.

Scraping autosuggest

This one doesn’t need an API, but you’ll need to be careful with how frequently you use it, so you don’t start triggering the dreaded captchas.

To scrape autosuggest, you use a very simple URL query string:

https://ift.tt/3aep6p5

Okay, it doesn’t look that simple, but it’s essentially a search query that outputs all of the suggested queries for your seed query.

So, if you were to enter “cyber security” after the “q=”, you would get:

Refining topic research

Google Ads Keyword Planner

Currently in beta, Google Ads now offers a “Refine keywords” feature as part of their Keyword Ideas tool, which is great for identifying keywords related to an overarching topic.

Below is an example of the types of keywords returned for a “coffee” search:

Here we can see the keyword ideas have been grouped into:

Brand or Non-Brand – keywords relating to specific companies
Drink – types of coffee, e.g. espresso, iced coffee, brewed coffee
Product – capsules, pods, instant, ground
Method – e.g. cold brew, French press, drip coffee

These topic groupings are fantastic for finding additional areas to explore. You can either:

Start here with an overarching topic to identify related terms and then go through the PAA/autosuggest identification process.
Start with the PAA / autosuggest identification process and put your new topics into Keyword Planner

Google Trends

Using “travel ban” as an example, we can see the trends in searches as well as related topics and specific related queries:

In the example above we see these sections include specific locations and specific mentions of coronavirus – something that Keyword Planner won’t provide data on as you can’t bid on it.

Moz Keyword Explorer

Other tools

The key is identifying the opportunities you want to explore further, looking through the PAA and autosuggest queries, grouping them into themes, and then drilling into those themes.

Keyword research is an ever-evolving process, and the ways in which you can find opportunities are always changing, so how do you then start planning these new opportunities into strategies?

Forming a plan

A quick (and consistent) way you can easily plot these new opportunities into your existing plans and strategies is to follow this process:

Identify new searches and group into themes
Monitor changes in new searches. Run the exercise once a month to see how much they change over time
Plot trends in changes alongside industry developments. Was there an event that changed what people were searching for?
Group the opportunities into actions: create, update, optimize.
Group the opportunities into time-based categories: topical, interest, evergreen, growing, etc.
Plot timeframes around the content pieces. Anything topical gets moved to the top of the list, growing themes can be plotted in around them, interest-based can be slotted in throughout the year, and evergreen pieces can be turned into more hero-style content.

Then you end up with a plan that covers:

All of your planned content.
All of your existing content and any updates you might want to make to include the new opportunities.
A revised optimization approach to work in new keywords on existing landing pages.
A revised FAQ structure to answer queries people are searching for (before your competitors do).
Developing themes of content for hubs and category page expansion.

Conclusion

SEO Forecasting in Google Sheets

Posted by Tom.Capper

In short: you could enter in a series of data, and it would plot it out on a graph like the image above.

I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.

Types of SEO forecast

There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.

Broadly, I think you can put SEO forecasts into three groups:

“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).

What makes this an SEO forecast?

We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.

How to use the template

FREE TEMPLATE

Then, on the first tab, you’ll notice some cells have a green or blue highlight:

You should only be changing values in the colored cells.

You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.

Make sure you also delete any leftovers of my example data in column B.

Outputs

Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:

The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.

Advanced use cases

You may by now have noticed the “Advanced” tab:

Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.

In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.

You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:

It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.

Here’s some example use cases of this tab for you to consider:

Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)

Why are my estimates different to your old tool? Is one of them wrong?

There’s two major differences in method between this template and my old tool:

The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.

It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.

How does it work?

There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.

The inputs I’m using are:

Dependent variables
- Whatever you put as column B in the inputs tab (like traffic)
Independent variables
- Linear passing of time
- Previous period’s traffic
- Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
- Up to three “advanced” variables

The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:

“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)

You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.

Potential extensions

If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:

Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)

Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:

Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)

If you’ve made it this far, what would you like to see? Let me know in the comments!