How to choose promising keys for SEO based on scenario forecasting in Google Data Studio (+ template)

A typical situation: you need to promote the site for 800 requests, but only 100 are enough. How to choose from the total mass phrases that will provide a good increase in traffic and conversions within the budget?

One option is a scenario-based traffic and conversion forecasting model using Data Studio. We tell how to collect data for the forecast, build a model for Yandex / Google and select queries for promotion.

A predictive model was proposed by John McAlpin in an article for SEJ . The model answers two questions:

• how much traffic and conversions you would receive for the previous period (in the context of devices), if the positions on the keys increased by 1, 2, 3, 4, 5, and 10 lines;
• which specific keys give a good increase in traffic and conversions with a small “kick” of positions (in low budgets this is just the case).

Important! The model does not take into account seasonality and does not build a monthly forecast. This is a scenario forecast for understanding the prospects of key promotion.

How the forecasting model using Data Studio works

Data Studio allows you to set up an interactive dashboard to assess the potential effect of line item growth for individual keywords. Report and graphs are automatically updated when updating the source data in Google Sheets.

We will work on a concrete example. Copy the SEO Forcasting Data document right away . We will introduce the initial data here.

Data collection and template filling in Google Sheets

For the forecast, you need such data:

• keywords and positions on them;
• frequency;
• average% of organic desktop and mobile traffic;
• average conversion rate for desktops and mobile devices;
• CTR of search results in the organic search engine Yandex / Google.

Keys and positions on them

Keys can be downloaded from Yandex.Webmaster or Google Search Console (depending on which PS you need a forecast for).

To unload from the Google Search Console, go to the "Efficiency" section, turn on the display of the average position, set the period to "Over the past 28 days" and load the data in a table form.

')

To unload keys from Yandex.Webmaster, go to the section “Search Queries” / “Query Statistics”. We set the display of data for the last month, add the indicator “Avg. position ”and load the data in XLSX format.

The problem is that after the keys are unloaded from the panels for webmasters, the position is reflected in decimal.

We need an integer. Have to round it up.

Another way to download keys is by using the free tool in the PromoPult SEO module . It collects phrases for which the site is in the TOP 50 of Yandex and / or Google.

We add the site to the system and run the search engine promotion. In addition to the positions here, for each word, the forecast of the number of clicks will be automatically calculated - this data is useful if you need to quickly estimate traffic on a promoted website.

Manually copy the table with the keys in Excel.

Go back to our template.

The SEO Forcasting Data template provides 150 turnkey “seats”. Why? Explains John McAlpin:

The objective of this predictive model is to show what effect SEO can give even when working with a limited number of requests.

The advantage of the model is that it evaluates the potential effect of promoting specific keys, and not the entire set of requests for which traffic to the site arrives. You analyze only 150 top keys, the promotion of which, in your estimation, will be most effective.

This is the logic. Outside TOP-20 and especially TOP-30 traffic tends to zero. An increase in position, say, from 35 to 30 will not affect the influx of visitors as much as, for example, from 13 to 8 (and even more so from 8 to 3).

There are 896 keys in our file. Choose 150 pieces like this:

• we sort keys by positions in the interesting PS (let it be Yandex) as it grows;

• we select requests within the TOP-30 (we do not take into account the keys in the first position);
• in fact, we have more than 150 requests within the TOP-30 - we filter the keys with a low frequency (below 20).

If you have less than 150 keys within the TOP-30, use how many are, and leave the rest of the template cells empty.

Important! If you want to add more keys to the model or change scripts (for example, to predict growth not by 10 positions, but by 15), make changes to the template.

Frequency

After selecting the keys, you need to collect their frequencies. If you parsed keys using PromoPult, then you will already have frequencies from Wordstat. Use Google Keyword Planner (a forecasting tool for impressions and clicks) to collect frequencies on Google. Also suitable for Google, for example, Keywordtool.io and KeyCollector.

So, we have keys, positions on them and frequencies. Put them in the copied SEO Forcasting Data template on the Main Worksheet. Fill in column A (keys), B (position), C (frequency) and lines 2-151 (the area is highlighted in blue). Below 151 lines there is also data, they will be updated automatically - after filling in lines 2-151. Do not change anything else.

Next, fill out the second sheet in the template.

Distribution of organic traffic between devices

This information is available in Google Analytics (Audience / Mobile Devices / Overview). We create a segment "Search traffic". The type of device “tablet” is mobile.

To obtain similar information in Yandex.Metrica, go to the “Reports” / “Standard Reports” / “Technologies” / “Devices” section. We set the segment "Search traffic". When calculating interest, the category “Tablets” is combined with “Smartphones”.

The numbers in Yandex.Metrica and Google Analytics are slightly different, but not critical. Take for yourself any option.

Average conversion rates for desktops and mobile devices

See conversions where goals or ecommerce are set up.

For example, on our site, the main goal is to place an order. We have a goal set in Yandex.Metrica. Go to the “Reports” / “Standard reports” / “Conversions” section, set the period - the last month (although you can do more). We set the conditions: “Type of the first source: Transitions from search engines” and “Devices: Smartphones or Tablets”. We get the conversion rate for mobile at the level of 1.24%.

To view data on desktops, simply change the “Device:” condition from “Smartphones or Tablets” to “PC”. In our example, the conversion here is 1.68%.

We enter the data on traffic distribution and conversion between devices on the Average CTR Data & Inputs sheet of the SEO Forcasting Data template.

CTR of Yandex / Google organic search results

Clicks are not evenly distributed between search results. And for forecasting, you need to know what proportion of clicks each position in the organic receives.

There are no problems with Google. AdvancedWebRanking service collects data by device, region (USA / Worldwide), types of queries (branded / non-brand), search intent (commercial, informational, etc.) and categories (home and garden, finance, sports and fitness, etc.) .).

It’s clear that the clickability is different from region to region and from query to query, but the overall picture of the CTR distribution for Google is clear.

More difficult with Yandex. There are no similar online monitoring services. There are several ways to get data:

• Take advantage of the results of third-party CTR studies (here is one of the last - but here CTR is indicated only within the TOP-10).

Results of CTR research in Yandex (presented at the CyberMarketing 2018 conference)

• Analyze CTR using your own site database. But this method is suitable only for large agencies and online services, which have hundreds (or even better - thousands) of sites in the database. The only way to get a more or less reliable result.
• Accept the hypothesis that CTR in Yandex is distributed approximately the same as in Google, and use AdvancedWebRanking data. For lack of other data, this is not a bad decision.

CTR data is entered on the Average CTR Data & Inputs sheet of the SEO Forcasting Data template (to the right of the table with traffic and conversion). For positions 1 to 20, we use AdvancedWebRanking data. For positions 21-29 - 0.005, for positions 30 and below - 0.002.

So, the data was collected and the template in Google Sheets was filled. It remains to download the Google Data Studio template and connect a data source to it.

Connect data to a Google Data Studio template

Open the Data Studio SEO Impact Analysis Scenarios template and create a copy (the original template contained errors - we fixed them). When copying a template as a data source, specify the SEO Forecasting Data - Main Worksheet template loaded and filled with your data.

If you correctly specified the source and it does not contain errors, then the report in Data Studio will be filled automatically.

At the bottom of the report you will see a table with data on position growth scenarios. Available sorting in ascending / descending order for each indicator.

At the top of the template are two blocks of diagrams:

• traffic and conversions for each growth scenario;
• Distribution of traffic and conversions by device.

In our example, a general growth of 1-4 positions does not give a significant increase in traffic. An increase of 5 positions is more noticeable, but the most significant - with an increase of 10 positions. Everything seems to be logical, but in the context of individual keys the picture is completely different - and this is the most interesting.

Let's look at the dynamics of traffic and conversions for goon diapers. To do this, click on the “Keyword” button in the report header and click on the “only” button opposite the request.

It turns out that an increase of 10 positions on demand will lead to a decrease in traffic and conversions. This is due to the fact that, according to AdvancedWebRanking statistics, the CTR of the results of delivery at positions 14-18 is higher than at positions 10-13. That is, if you “kick” the request from position 18 to position 13, we get less traffic.

What to do with such requests? Everything is simple here. If the budget is very limited, put them off until better times. If resources allow, we take it to work and put it in TOP-5, and even better - TOP-3.

An opposite example is the “diaper allergy” query. Here, with an increase of 10 positions, there is a multiple increase in traffic and conversions.

Particular attention should be paid to queries like "Libero diapers." Even with the growth of positions by 3 points, a noticeable growth begins. We give such requests priority during promotion.

Another similar example is the query “Russian diapers”.

Of course, in the final selection of requests, one must take into account their intent, level of competition, and the cost of promotion.

Nevertheless, the scenario analysis that we examined allows us to identify queries that give a good increase in traffic and conversions with a slight increase in positions. On them you can achieve quick results.

Of course, this is not the only true method for selecting prospective keys. However, its use together with other proven methods will allow both forecasting and achieving results with lower costs.