Now it's time to combine the three datasets so we can get some nice insights
Posted: Sat Dec 07, 2024 8:53 am
We go to Manage relationships in the top menu and select New, create a relationship between the Search query columns and select Many-to-One.
If the datasets are not connecting, you will need to go back to your datasets and check that the columns are in the correct formats and make sure that any duplicates have been removed.
We have now imported, cleaned and connected the data, so we are ready to visualize it to find some valuable insights.
Step 3 - Visualize data and analyze data for insights
After establishing the relationship between data sets, we need to be able to combine the data.
Click on the Visualization tab on the left.
You will now see a white canvas. Select the scatter plot from the Visualizations section on the right.
In the GSC 12m dataset, drag and drop the Query into the Details area.
Then drag and drop Position into the X-axis area. Finally france telephone number data drag and drop Impressions into the Y-axis area.
The scatter plot will look strange, so make sure to select Minimum instead of the default Number of... for the last two data entries.
Now click on the Format icon to make final design improvements.
You need to turn on the Show Category Labels option and I would also recommend turning on the Color by Category option.
Below you now have the completed scatter plot using my example:
Each bubble is a keyword (I removed the keyword tag). On the x-axis you can see the organic ranking on Google from 1 to 100. On the y-axis you can see the number of impressions per year.
I’ve added some Cards from the visualizations. We ended up with 896 keywords represented across all datasets, representing 3 million impressions and 106,000 clicks per year.
This is already interesting, but this is where things get exciting.
Now drag Revenue from the GA 12m dataset to the Dimension field (select minimum).
Bum!
We can now see which search queries have brought us the highest revenue in Google Ads over the last 12 months.
However, keep in mind that we are looking at the average ranking for the last 12 months. The average ranking can be very different from the current ranking, so we need to combine the two datasets with the latest dataset GSC ranking to get the current ranking.
Go to Manage relationships and create a connection between the two GSC datasets. Once complete, add the Position from the GSC ranking as the X-axis. Now you have your top search queries.
Now you are ready to do some deep research and find the most interesting keywords to work with. You may already have some insights now, but I still recommend you dig deeper and look at the traffic and revenue potential as well.
There are some bubbles in the example above that look promising. If it’s a big bubble and is ranked 4-20 with decent traffic volume, then these might be the ones to go after.
However, with so many bubbles in the chart, it’s important to use the Filter options. You can use them to exclude or include certain keywords (e.g. brand queries), filter by position (e.g. only look at positions 5-20), or look at keywords with higher search volume (e.g. over 100).
I could talk more about data analysis, but for now, just do this exercise and build on it as you go.
To summarize:
When you combine data sets, you’ll gain unique insights that will make your app more precise. Instead of just looking at search volume, you can easily combine this data with a layer of revenue keywords from Google Ads to learn which keywords are generating orders. While it’s easy to get caught up in keywords that could bring in thousands of extra visits, this analysis can help you avoid going in the wrong direction.
Since we're on the AccuRanker blog, I've included a bonus section on how I use AccuRanker as part of the app.
BONUS - How do I use AccuRanker to apply my Power BI insights?
I have found keywords that are already generating income. Now I want to improve my rankings on Google. I finally added 851 search queries that are on the first four pages of Google to my account on AccuRanker.
So how do I deal with this huge list in the future?
What I would do is label each keyword in three groups (Defend, Attack or Build) based on their position.
Top 3 rankings: Defense
These are your cash cows. They are on your competitors’ radars so you need to actively defend them. And competitors are not sitting idle.
4-20 position: Attack
These are your future cash cows. If you rank at the bottom of page 1 on Google for one of these keywords, you’ll probably get 0.5-1% of the total search volume. If you make it to the top 3, you’ll get 5-20% of the traffic. Now that you know your Google Ads revenue, you can calculate your revenue potential from organic listings.
Outside the top 20: Create
If we exclude possible technical hurdles or low domain authority, the reason you are not ranking on the first two pages of Google is because you are not fulfilling user intent. In this case, you need to reposition your existing pages and create new pages that meet that need.
In our example, the 851 keywords are distributed like this:
Now you can routinely track the development to find out how well it is going. At the same time, you will know how to work with the given keyword.
When you want to check the status of your project, you select the Compare to start filter in AccuRanker. Then you export the data to Excel.
Add a column after the Tags column and add the following formula. My column is L and here is the full list of keywords:
=IF(L1:L852<=3; "Defend";IF(L1:L852<=20;”Attack”;IF(L1:L852>20; "Build"))
You will then have created an updated tag where you can create a chart like the one below and track the progress.
I found this to be a good way to know how well I was doing in making it an agile process.
That's all for now. I hope you're eager to try working in Power BI. If you have any questions, let me know in the comments section below.
If the datasets are not connecting, you will need to go back to your datasets and check that the columns are in the correct formats and make sure that any duplicates have been removed.
We have now imported, cleaned and connected the data, so we are ready to visualize it to find some valuable insights.
Step 3 - Visualize data and analyze data for insights
After establishing the relationship between data sets, we need to be able to combine the data.
Click on the Visualization tab on the left.
You will now see a white canvas. Select the scatter plot from the Visualizations section on the right.
In the GSC 12m dataset, drag and drop the Query into the Details area.
Then drag and drop Position into the X-axis area. Finally france telephone number data drag and drop Impressions into the Y-axis area.
The scatter plot will look strange, so make sure to select Minimum instead of the default Number of... for the last two data entries.
Now click on the Format icon to make final design improvements.
You need to turn on the Show Category Labels option and I would also recommend turning on the Color by Category option.
Below you now have the completed scatter plot using my example:
Each bubble is a keyword (I removed the keyword tag). On the x-axis you can see the organic ranking on Google from 1 to 100. On the y-axis you can see the number of impressions per year.
I’ve added some Cards from the visualizations. We ended up with 896 keywords represented across all datasets, representing 3 million impressions and 106,000 clicks per year.
This is already interesting, but this is where things get exciting.
Now drag Revenue from the GA 12m dataset to the Dimension field (select minimum).
Bum!
We can now see which search queries have brought us the highest revenue in Google Ads over the last 12 months.
However, keep in mind that we are looking at the average ranking for the last 12 months. The average ranking can be very different from the current ranking, so we need to combine the two datasets with the latest dataset GSC ranking to get the current ranking.
Go to Manage relationships and create a connection between the two GSC datasets. Once complete, add the Position from the GSC ranking as the X-axis. Now you have your top search queries.
Now you are ready to do some deep research and find the most interesting keywords to work with. You may already have some insights now, but I still recommend you dig deeper and look at the traffic and revenue potential as well.
There are some bubbles in the example above that look promising. If it’s a big bubble and is ranked 4-20 with decent traffic volume, then these might be the ones to go after.
However, with so many bubbles in the chart, it’s important to use the Filter options. You can use them to exclude or include certain keywords (e.g. brand queries), filter by position (e.g. only look at positions 5-20), or look at keywords with higher search volume (e.g. over 100).
I could talk more about data analysis, but for now, just do this exercise and build on it as you go.
To summarize:
When you combine data sets, you’ll gain unique insights that will make your app more precise. Instead of just looking at search volume, you can easily combine this data with a layer of revenue keywords from Google Ads to learn which keywords are generating orders. While it’s easy to get caught up in keywords that could bring in thousands of extra visits, this analysis can help you avoid going in the wrong direction.
Since we're on the AccuRanker blog, I've included a bonus section on how I use AccuRanker as part of the app.
BONUS - How do I use AccuRanker to apply my Power BI insights?
I have found keywords that are already generating income. Now I want to improve my rankings on Google. I finally added 851 search queries that are on the first four pages of Google to my account on AccuRanker.
So how do I deal with this huge list in the future?
What I would do is label each keyword in three groups (Defend, Attack or Build) based on their position.
Top 3 rankings: Defense
These are your cash cows. They are on your competitors’ radars so you need to actively defend them. And competitors are not sitting idle.
4-20 position: Attack
These are your future cash cows. If you rank at the bottom of page 1 on Google for one of these keywords, you’ll probably get 0.5-1% of the total search volume. If you make it to the top 3, you’ll get 5-20% of the traffic. Now that you know your Google Ads revenue, you can calculate your revenue potential from organic listings.
Outside the top 20: Create
If we exclude possible technical hurdles or low domain authority, the reason you are not ranking on the first two pages of Google is because you are not fulfilling user intent. In this case, you need to reposition your existing pages and create new pages that meet that need.
In our example, the 851 keywords are distributed like this:
Now you can routinely track the development to find out how well it is going. At the same time, you will know how to work with the given keyword.
When you want to check the status of your project, you select the Compare to start filter in AccuRanker. Then you export the data to Excel.
Add a column after the Tags column and add the following formula. My column is L and here is the full list of keywords:
=IF(L1:L852<=3; "Defend";IF(L1:L852<=20;”Attack”;IF(L1:L852>20; "Build"))
You will then have created an updated tag where you can create a chart like the one below and track the progress.
I found this to be a good way to know how well I was doing in making it an agile process.
That's all for now. I hope you're eager to try working in Power BI. If you have any questions, let me know in the comments section below.