Of course, these are relevant questions, but I have a better question for you:
"What are the most valuable keywords on Google for our business?"
The first two questions focused on getting more traffic. However, this does not necessarily lead to more revenue.
My question focuses on which keywords can bring us sales in the short term or long term.
In this article, I will explain how you can identify the france telephone number data most valuable keywords to rank for on Google and at the end I will explain how I used AccuRanker when implementing it.
Are you ready to earn more income?
Let's get started.
Should We Accept That SEO Is a Long Game?
Have you ever heard any variation of the following quote?
“SEO is a long game where you struggle for years before you reap the rewards.”
Such quotes appear to be the “truth” preached by SEOs to their colleagues and clients.
However, I do not agree with this.
I believe that SEO’s slow progress is often due to focusing on the wrong keywords. Many SEOs are too eager to go after keywords with high search volume instead of keywords with revenue generating potential.
Taking the wrong path will result in a lot of wasted time.
First, you will spend time trying to rank for the wrong keywords, and second, when you finally do succeed, you will spend time figuring out why they aren't bringing in any business.
This is a shame.
However, this can actually be avoided as two changes have occurred that help us do better SEO.
Two Big Changes to Better SEO
While many SEOs are blinded by new shiny things like voice search or daily Google updates on Search Engine Land, I believe there are two big shifts that are key to better SEO results.
1. We have better data than ever before
Google Keyword Planner is now just one of many data sources for understanding what people are searching for, and it’s not even the best source we have. Clickstream-based tools, analytics tools, and dedicated SEO tools now give us better insights. It seems like these tools will be around forever, but it wasn’t until a few years ago that an excellent rank tracking tool like AccuRanker made a big breakthrough.
2. There are now better tools for working with large datasets
Have you ever waited for Excel to crunch a large data set?
We all waited.
While Excel has become every SEO expert’s best friend in recent years, it’s time to face the facts. You shouldn’t read articles like the one below to solve your problem.
We can all become data scientists now. In fact, it’s a necessary skill if you want to be successful with SEO in the coming years.
Therefore, it is time to make a difficult but right decision.
Yes, I said it. You should skip Excel as your go-to tool.
I'm not saying it's still not useful, but it's not enough. And it's not the central tool for insights.
There are better alternatives and the best part is that they are free.
I've become a big fan of Microsoft's Power BI over the past year , and in this post, I'll show you why.
Thanks to Wil Reynolds of Seer Interactive for leading the way. Visionaries like him are rare in the industry.
How to Find the Most Valuable Keywords for Your Business?
After this rant, let's get back to the first question.
In the example below, I'll walk you through the step-by-step process of finding the most valuable keywords for your business through Power BI:
Step 1 – Get three datasets from Google Analytics and Google Search Console
We want to combine data from Google Ads search queries with our Google Search Console data. The idea is that if we can identify paid search queries that are generating action, we can look at our ranking organic keywords to look for opportunities.
First, we’re downloading the last 12 months of search queries from our Google Ads account. Make sure you sort by revenue. In the example below, I’ve taken 2,000 search queries that have generated transactions this year.
Secondly, we download the last 12 months of data from Google Search Console. If you pull the data directly from Google Search Console, you will only get 999 rows of search queries.
This is not enough.
Instead, you can use the API to get more data, but I use another workaround.
To get all search queries, get the add-on for Google Sheets called Search Analytics for Sheets. We connect this to our Google Search Console account and can request all queries from the last 12 months.
Instead, you can use the API to get more data, but I use another workaround.
To get all search queries, get the add-on for Google Sheets called Search Analytics for Sheets. We connect this to our Google Search Console account and can request all queries from the last 12 months.
This is better - instead of 999 I now get 18,295 search queries.
We download the data as an Excel file.
Since we want the current ranking, we need to get a second dataset from Google Search Console, and here we get the same data as before, except that the date range is today's date.
The new ranking dataset includes 16,727 of the 18,295 keywords we collected in the initial Google Search Console dataset.
No bad.
We now have all the data we need.
Before moving on to Step 2, let me briefly summarize what's going on:
A. We took the last 12 months of data for Google Ads search queries. In this example, we identified 2,000 queries that generated revenue for us during the period.
B. We have the full list of search queries that we rank for in organic search via Google Search Console. In this case, +18K search queries.
A. We have today's current rankings for +16K of the keywords via Google Search Console.
These three datasets provide a solid foundation to gain some insights from. Now let's go ahead and import them into Power BI.
Step 2 - Import, cleanse and connect data to Power BI
If you haven’t downloaded Power BI yet, then now is the time. Go ahead and download it now .
We need to enter three Excel files into Power BI via Get Data in the top menu.
There are two key factors to making data ready for further visualization: Data must be cleaned and then linked together.
Clean the data first. Click on Edit queries in the top menu. Review each dataset and make sure each column has the correct format (text or number). To avoid any issues, I also recommend converting all words to lowercase.
Change the names of each dataset to something you can understand so you don’t mix them up. In this example, I’m calling them GA 12m, GSC 12m, and GSC rank, as seen below:
The second part of the cleanup process is to make sure that only unique search queries are present in the two Google Search Console datasets so that you can connect them to the Google Search Ads dataset.
Therefore, we need to set the column position in both datasets to ascending to ensure that the best positioned queries appear first. Once this is done, mark the search query column and remove any duplicates.
