In this tutorial, I will show you how to use KI overview data from Semrush, Ahrefs and Sistrix and combine them with GSC data that has been exported via the search console. The resulting table could uncover the actual effects on clicks and click rate based on Aios ranking in the SERPS for their queries.
At the I/O last week, Google explained this AI overviews expanded to over 200 countries and more than 40 languages. They also explained that AI mode is now available to all users in the USA without deciding on search laboratories. One thing is certain that Google runs both with the AIOS and KI mode …
In addition, Liz Reid from Google explained that the AI ​​mode is Future of the search. This is a big statement, and the AI ​​mode can radically have an impact on how users get in touch with the search results, how much traffic publishers receive from Google and more.
With regard to traffic from the search, we know that both AI overviews and AI mode can have a negative impact on clicks and click rate. But we also know that it is difficult to receive hard data that support this statement. This is because Google AIO data does not break out in the Google search console and the AI ​​mode data is not even pursued. That will come soon, but it’s not even in GSC.
AIO data is there In GSC, but it is mixed with 10 blue connection data. But as I have said many times, good luck with trying to analyze this data in GSC. With AIOS, mixed with 10 blue-left data (including cutouts), it is not easy to decrypt the effects of AI overviews.
What can a site owner do?
Understand AIO effects. There is more than one way to uncover the effects.
Based on AI overviews in the SERPS, site owners could very well see a decline in clicks, even if they rank in the same position before the party overview of the party joined. However, it is difficult to prove that without some data the front and after and afterwards performance numbers show.
Well, I’ll show you a way to get a stronger feeling for how Aios affect traffic for your website. It is not perfect, but can definitely give you a stronger feeling for the effects of Aios ranking for your questions.
Combination of AIO data from third-party providers with GSC data. We will do the following.
The following approach that I will document contains exporting all queries about tools from third-party providers that result in AI overviews if your website ranks for these queries in the SERPS. You don’t necessarily have to rank in the AIOBut this is exactly what appears when they are classified in the SERPS overall. Then we export all of the TSC via the search console -api. We will do this for two date areas – one in the past before the AIOs have classified, and then a date area recently classified in which AIOs is classified. With Vlookup Magic we combine the data to display changes to clicks and click rate (if available).
And if you find queries where your site ranks even better in the same position or in both areas of the date, you can check the clicks and the click rate to determine the effects of the AI ​​overview in the same SERP. I did this for some customers and it is a good way to get a feeling of how Aios affect clicks and click rate for these queries.
Note that this process for websites with a lot of visibility and heavy traffic from Google is definitely easier. If your website does not receive a lot of data traffic from Google and has no lot of visibility, this process may not provide the best results for you.
What you need: tools.
- Access to third -party visibility tools. I will pull AIO data from Semrush, Ahrefs and Sistrix for this tutorial and devote the data to Excel.
- You need the opportunity to export inquiry data via the search console. I used a number of tutorials Analytics Edge (What now has a free version and works with both Excel and Google leaves), but you can use any method or tool that you want to export with the data. You need the API if you want more than 1K lines from the GSC user interface (which most websites need).
- Excel or Google Sheets. I will use Excel for this tutorial.
Let’s start.
How to measure the effects of Aios ranking for your questions:
1. Export all AIO data across visibility tools.
Start the Semrush and enter the domain you want to analyze. Then click the positions tab. Under SERP functions “SERP functions on SERP” and then click on “AI overview”. The report shows all queries for which the site also ranks for a AI overview. Note, this does not mean that the website ranks in the AI overview. It simply means that the AIO ranks in the same seeds when your website also ranks …
We would like to know the effects of an AIO on clicks and CTR overall. Therefore we select this option. You can select “Domain Ranks” at any time instead of “SERP functions on SERP” to use Keyword data if your website is ranked in the AIO.
When displaying the position report, you can also click on the “Organic” tab in order to display only 10-blue left-hand data without other SERP functions such as PAA. And you can filter with the top 10 ranking lists if you want to receive a closer list of queries that rank well in the SERPS if Aios also ranks. As soon as you are ready, export the SEMRUSH data to Excel or Google leaves.
2. Make Ahrefs and enter the domain that you want to analyze.
In the left navigation, click the “Organic keywords” link. Then click on “Filter” at the top of the report and select “SERP functions”. Click on the option field for ‘Auf Serp’ and then on the check box for ‘AI overview’. The report now shows all queries for which the website also applies to a AI overview. This also does not mean that the website is in the AI ​​overview. This only means that a AI overview predicts in the SERPS if the website is submitted for the query as a whole. Here, too, you can always select “where targets” if you want to ask a list of queries where the website actually ranks in the AIO. Export the data when you are ready.
3. The next step is Sistrix and enter the domain that you want to analyze.
In the left navigation, click the “Keywords” link. You will find that there is a preset filter for a AI overview that you can click on. This enables keywords for which the site is called when a AI overview also receives in the SERP. Export the data.
4 .. Dedupe the data.
Use your choice of choice to combine all queries in a worksheet named “Aios Deduped” and then to Dedupe. In Excel you can do this by clicking on the data menu and then removing “duplicates” in the tape. The resulting list is your Kern -Aio -Abzfragel list for the website you analyze. These are all queries that arise in the SERPs when your website is a rank (based on visibility data from third-party providers).
5. Export GSC data via the search console -api for two different time frames.
Our goal is to compare the current performance data with Aios ranking in the SERPs with an earlier period if Aios did not rank (or at least one time in the past to recognize the difference in clicks and CTR). Note that this is not a precise science. I recommend exporting performance data last month and compared to a previous month of the data in the past. AI overviews officially rolled out In May 2024 you can think one month before this period or even later if you believe that AIOS did not go after these queries at the time.
If you use Analytics Edge, follow my previous tutorials to export the data while compare time frames. The resulting worksheets contain all performance data for the site for every period. I would “query” you for the current time frame and then “Query before” for the previous time frame. But that’s for All inquiry data And not just the inquiries where Aios rank. We will use Vlookup Magic to help in this situation. I will also cover the importance of the safe ranking over both time frames if you compare the data for individual queries. More on that soon.
6. Fast status test. What we have now.
We now have three worksheets in the table. The first worksheet contains all queries that Aios show in the SERPS for the website you analyze. Then we have two worksheets that contain all query data for the website for two different time frames (the current time frame and then a time frame in the past). Now we will use some vlookup magic to combine the data that give us a look at how Aios affect clicks and click rate.
7. Use Vlookup to collect GSC data for queries that result in AI overviews.
Vlookup is a powerful formula for retrieving data from another table or different worksheets based on a suitable cell in the current worksheet. For our purposes we will use every query in the first worksheet to refer the GSC data across both time frames. If there is a agreement with queries, we will be the performance data for this query from both time frames that we compare. We will also add columns to display the difference for every metric.
The first worksheet then contains all queries that AIOs result for the website we analyze, including performance data from the current time frame and the time frame in the past. This makes it easier to analyze the change in the clicks and the click rate for these queries (while the ranking lists have remained stable for these queries).
8. Add vlookup formulas for clicks, click rate and position.
In the worksheet, which contains all Deduped AI overview queries, add new columns for clicks, click Previously clicks, Change Clicks, CTR -Aktuelle, CTR -fore, CTR change and position current, position and position change change. I will provide the first set of Vlookup formulas for pulling clicks from the GSC exports for the current time frame and the previous time frame and determining the change in the clicks. Then you can easily adjust this formula for the other fields that you have just created for CTR and position.
First, here is the Vlookup formula that we will use for the column entitled “Clicks Current”:
= Iferror (vlookup (‘aios deduped’! A2, query! A: e, 2, wrong), 0)
The vlookup uses the query as a search and then checks the worksheet with GSC data from the current period. If there is a agreement, the second column is returned in this worksheet, which represents the number of clicks. The iseror part of the formula checks to determine whether an error is returned (e.g. if there is no value, since the query was not available). If so, only add number 0 into the cell. You can add the desired value, but I have included 0, which I can use for filtering the line without any problems.
9. Apply the formula to the entire column.
As soon as you have entered and apply the formula, switch via the cell and click on the lower right corner to apply this formula in the column to each cell. You should do this for each of the columns as soon as you have applied the formula to the first cell in this column. This is an easy way to copy this formula into each cell in the column (which can be many lines).
10. Copy the Vlookup formula for additional columns.
Copy this formula next and add it to the field as a previous insertion to click clicks beforehand. You must change the IT references worksheet to get the correct data. Make sure that from the worksheet with the title “Queries Prev” or whatever you mentioned the worksheet with the GSC data for the previous time frame. You can click on the lower right corner of this cell again to apply the new formula to each line in the column.
11. Now you can use this basic formula for the click rate.
Use the core formula for the additional columns again. Make sure that you refer the right worksheets for current time frame and change the field from 2 to 4 to pull the CTR data towards clicks. CTR is the fourth column. That is why we change 2 to 4.. Do this for both the CTR current and for the CTR -previous columns.
12. Finally, do this for the predecessor and position current slices.
Note that you have to change the field to 5. There is the position in the worksheets.
13. Don’t forget to add the differential columns.
Also ensure that you add columns to display the difference in clicks, click rate and position. This can help you to easily identify the gaps there when comparing the time frames. I added columns for click changes, CTR changes and position changes. Clicks and position uses a simple subtraction, while the CTR change gives you the percentage decline. You can subtract the current CTR from the previous CTR and then divide yourself through the previous CTR to create the percentage change.
14. Identify the effects.
Before you cut and check the data, you may want to copy the worksheet data into a new worksheet and “Insert as values”. Then you don’t have to deal with the formulas that cause problems when filtering the data. Then check the inquiries that search for Similar position When comparing time frames. If the position is very different, this can significantly influence the clicks and the click rate. For example, if a site was negatively or positive by a large algorithm update, the position could be far away from the past. And this change could send clicks and CTR deposits or a strong increase.
You would like to find queries where your website is roughly the same position. It is also worth noting that a decline in clicks can help identify a decline in the CTR, but only clicks can be influenced by other factors. For example, a new system could bring impressions onto the market, which could have influenced the number of clicks. You can also import impressions by using Vlookup, which can help with analyzing the data.
In the following you can see a number of queries in which the position remained stable, but the clicks and the click rate have dropped quite strongly.
Some recommendations and tips for site owners:
- When exporting GSC data, make sure that in the past you compare with a time frame if Aios * probably not *. You have 16 months of data, so use it. Remember that AIOS was officially introduced in May 2024.
- As I have already explained, this process for websites with a lot of visibility and traffic from Google works much better. If your website does not have much visibility and traffic from the search, this may not provide any great results.
- After executing the numbers, make sure that the position has remained relatively stable. If your website was influenced by a wide core update or an unconfirmed update, the ranking lists would have changed radically. And these changes can have a strong impact on clicks and click rate.
- If you identify good examples of queries that affect AIOS clicks and CTR, you can export the GSC -API in the last 12 or 16 months to the month to see the decline over time. You can also graphically present the trend of trends over time, which would be interesting to see.
- Make sure you discover the SERPs for questions where you have strong effects. You never know what you can find in relation to SERP functions. Or you can see variations of AI overviews, new options for how the data is presented, more visual and more.
- Not only carry this analysis once. I recommend doing this regularly to see the effects of AIOS on clicks and CTR for the queries for which your website ranks (especially when Google drives more changes with the AIOS and AI mode).
Summary: Combination of third-party to soldiers with GSC data to achieve visibility in AIO effects.
Google does not make it easy for the site owners to analyze the effects of AI overviews on clicks and click rate. However, you can use the available tools to get a stronger feeling for the effects. By combining AIO data from third-party providers with GSC data via the search console API, you can see the effects of the Aios ranking in the search results. I recommend spending some time soon and operating the numbers on your websites. Google has greatly expanded Aios over time, and you can surprise the results – or not. Feel free to ping me on social media to tell me what you find! Good luck.
Gg