Traffic analysis with Sheets, SEMRUSH and Ahrefs

[tongfkymm44]

Why is it important to analyze traffic in detail?
Although the first analysis can be complex and time-consuming, it is one of the most important tasks of a web audit. Indeed, no matter how powerful they are, the tracking tools we use never give 100% reliable results and we have to be able to detect capture problems with Google Analytics data, since it is the only real data we have.

Whenever a sharp drop is detected in Sistrix, it is usually checked whether this has had an impact on actual traffic. Indeed, there may be several reasons:

Change in the way SERP positions are counted (see this Twitter thread )
Keyword database update (see this news from Sistrix although in this case it has journalist email database not had an impact on the index as they explain)
Drops in keywords outside the Top 10
And more than once we've seen visibility rates go up or down with no impact on a client's actual traffic.

Traffic analysis with Google Sheets
Google Sheets is one of the most powerful tools in the Google suite. There are several formulas that we will see throughout this post that do not exist in Excel and that allow us to save a lot of time and work more efficiently. Also, some extensions such as Google Analytics or Search Console that will allow you to recover the data that interests you without leaving your spreadsheet.

Retrieving data with the Analytics extension
Improve your SEO!
The first stage of our analysis is to obtain traffic data. In our first example, we will analyze data from January 1, 2018.

The steps are as follows:

Install the Google Analytics extension by going to Add-ons > Download Add-ons
google-sheets-extensions-store
Once installed, you can display the report settings sidebar by going to Add-ons > Google Analytics > Create New Report
create-new-report-extension-google-analytics
In the sidebar, you need to configure:
The name of the report which will be the name of the spreadsheet that the extension will create
The view you want to use to extract the data
The metrics/dimensions you want to extract. Note that some dimensions cannot be combined, but if this is the case, the extension will notify you.
In our example, we want to get the organic sessions per landing page and per month. The configuration would be as follows:

interface-google-analytics-extension
When we click the “Create Report” button, a new tab is created as in the example shown below:

configuration-analytics-extension-dates
Various comments:

Each column starting from column B corresponds to a different report. If necessary, you can create more than one report by copying and pasting the information automatically created by the extension without having to repeat the step-by-step instructions we have just detailed.
By default, the extension generates a report using the last 30 days as the period, but you can obviously change this as we have done. Remember to use the American format for dates ( YYYY-MM-DD ).
If you want to use more than one dimension or metric, you must separate them with a comma (,). You can find the names using Google's dimension and metric exploration tool .
For filters, see the filters section of this page
As you can see, the format of the segments is a bit strange. To find out the ID of a segment, use the Query Explorer .
id-segments-query-explorer
Finally, you can change the limit based on the number of URLs you plan to have. You can leave the default number for the first run as each tab will include the number of lines the API can return. If you see a big difference between the limit and this number, you can easily increase it.

sampling-google-analytics-extension
Once you have the report(s) configured in the “Report Configuration” tab, you can launch the execution:
run-reports-google-analytics-extension
The execution may take a while depending on the number of lines per report and the number of reports you have configured, but the process generally takes less than a minute.

Data manipulation
If you are analyzing traffic for a small website, you can create a single report with the dimensions and metrics I mentioned at the beginning ( landing page, month and year, and sessions ), but in the vast majority of cases, if you are analyzing traffic for more than one year, you will have a sampling problem that will indicate the extension at the top of the report.

sampling-report-extension-google-analytics
This sampling is a problem, as Analytics estimates how many sessions our pages generate based on only 43% of traffic. The lower the sampling percentage, the more likely we are to have inconsistent data.

In this case, we need to split our reports by month (or more/less depending on your sampling) as shown below:

example-configuration-reports-sampling
The main problem with this configuration is that we end up generating several tabs when we want to get a single tab with all the data. However, there is a powerful formula that we can use to solve this problem: QUERY .

Improve your SEO!
Below I will explain the process step by step:

To simplify the use of this formula, we recommend using named intervals. What you need to do is select the range with data (starting from line 15):
results-data-extraction-extension-google-analytics
And then by going to Data > Named Intervals, you can assign a name to this interval.
interval-with-name-google-sheets
Once done for each of the tabs you have, create a new tab and put the following formula in cell A1.
=QUERY({interval_1;interval2;…};”select *”;-1)

In detail:

Query : This is one of the most powerful formulas in Sheets and it uses a simplified version of SQL to manipulate data. I won't go into detail about its uses since this post is not about that, but I advise you to take a look at it because it will save you a lot of time