Keyword Research in Autopilot by Google Spreadsheet Macros (#brightonSEO)

— Hello, everybody.

— Hello.

— Um, do you know where this picture is taken?

— Istanbul.

— Yes, thank you. I came from Istanbul to speak at Brighton SEO; it's my second Brighton SEO speech, so I'm very excited because, uh, talking to the British audience is so hard. I even, uh, get- it's getting harder to order a coffee. I'm so bad at understanding English accents; I hope that you understand me. In this presentation I'm going to talk about keyword research in Autopilot, but it's a bit, uh, advanced SEO topic, and I will start to assume that you know what Search Console is, what Search Analytics is working for, and who knows what's the meaning of Search Analytics and Search Console.

Okay, Webmaster Tools, maybe. Previously. Okay, great. Uh, the first one, the first thing I need to understand is that it's the keyword session. Uh, maybe you are comes from SEO or content background, maybe PPC analytics. Who comes from SEO background? Who? Who? Who comes from PPC? Yeah, that's cool. That's what I'm waiting for. Okay, uh, there are apples and oranges, and, uh, I really, uh, prepared my presentation according to SEO mindset, but I think that keyword research is mixed with apple and orange, so I'm going to talk about, uh, the mixture of, uh, the situation.

Uh, the main problem about keyword research is just that most of the people hate to do keyword research because it takes too much time, and when you make this more and more, it gets boring, like you see in this orange. So I will not, uh, continue because it's a bit disgusting. And I hate keyword research as well, and that's the reason why I, uh, picked this topic, to speak at Brighton SEO, because it's too much repetitive task. You are going to the keyword planner, just put into the seed keywords, copying maximum 800, pasting to the Excel, doing this again, again, and again.

And also, slowness kills you because Google is, uh, the giant technological provider is just doing, uh, this, uh, stuff is so bad. Just, you need to wait for 10 seconds to just get the results, and after that, 10 or 12 queries, it also don't answer to you. And that's the reason, uh, most of the SEO agencies, they prefer to give keyword research to the beginner SEOs, mostly interns. So the veteran SEO, if you have to do, uh, keyword research, feels like this. So, uh, that's the reason why I want to make this basic keyword research stuff a bit advanced.

So, uh, I will talk about the smarter solutions so we will get our hands dirty. Uh, I will allow to spend my time on Reddit, and if somebody comes to the Reddit and talks about a story which is not so, uh, correct, people say that picks or it didn't happen. And I also believe in that in presentation, like, content is good, create content. If you create content, you will get thousands of domain backlinks, and this kind of stuff are so boring. That's the reason I will use lots of screenshots, and I will also share my extra document after then this presentation so that you can take notes or just skip it.

You know, keyword planner is the main, uh, thing on keyword research, uh, on the Google dominated markets like UK, Turkey, US, maybe. We love keyword planner, but, uh, if keyword planner removes the search volume data, it gets a bit boring, and we don't like such a situation like this, and it feel a bit anxious, and yeah, we don't like it. And, uh, when I talk with Calvin about keyword research topic, this thing is not published yet. Google is just throttling keyword data with low spending accounts. What it means, uh, as you can see in here, if there is a keyword which has 500 search per month, you will see in a range like 100 to 1 key.

It means 10 times more and it meaningless data. So, uh, in here, there is some workarounds, and as you can see here, Google said that for more detailed view, set up and run a campaign. And as you can see in there, all of the monthly searches are just range-based. And if there is a solution, uh, to every problem, Tr- Trups always find a solution to the problems, and as you can see in here, it's some kind of Turkish gadget. And yeah, this solution is not found by Turkish people, but, uh, it's very nice solution.

After than that, keyword research scrapers are just going to the adverse accounts, and then scrapers just change their workflows and then set up account and get exact search volume again. So that, just run a campaign is enough. So, uh, game is not over yet, so we always find workarounds. And in this case, uh, I will talk about this, but firstly we just need to know, uh, where to use keyword research. Uh, my friend had an ex-client, it's mainly my client, but I'm always saying this to not get fired, my agency clients. Uh, just enter to a different market, and it's like Arabic market, and after then they hire us as a con hired my friend as a consultant.

I'm so, so bad on this kind of things. Uh, we just made a keyword research and shows that the potential market is not that much big. But they hired, uh, five Arabic guys to this company. Uh, they speaking Arabian very well. They spent too much money, open the site, but market is too slow, and it's not unexpected thing for them. So, uh, keyword research is crucial for understanding your market. And the second thing is, uh, if you want to measure KPI, you need to track ranks, and maybe you have target, uh, keywords, you need to group them and the major KPIs. And you need to align your strategy by using this data.

You need to use this for content marketing actions, and creating a prioritization needs a keyword research. And also, uh, I didn't forget the oranges, uh, the PPC also get benefits from better adverse account management. So, uh, my thing is that, uh, my idea is that modern keyword research is not just finding keywords. It's just like a, uh, bullshit list, and you can just, uh, copy and paste and load on the Excel or spreadsheet, and I hate this, and we need to do something a bit different. In the most of the agencies, when they onboard a client, the first thing they just make a keyword search and they will not use it again.

And we just need to change it. So we make something like this. No, we didn't make this, but, uh, we just enrich our keyword data and create something way more different than our previous modules. So, uh, the ancient keyword research in 2007, when I start in SEO industry, maybe one years ago, uh, we are not, uh, it's not so many new, it's not new thing, it's just getting range of search volumes. And as you can see in here, in 2006 or 7, Google is not showing search volumes of the keywords. After than that, they decided, they changed their decision and then add the search volume, even search trends, and our keyword research are enriched.

We just get keywords, positions, search volumes, and search trends. But these years are a bit different. There are lots of different tools. If you are interested in enterprise SEO, okay, you just need to buy Searchmetrics. And if you want to track ranks, okay, you need to buy a SEO monitor and, uh, lots of tools, lots of data, lots of meaningful inference. But we just need to get them together. And that's why in SEO ZEO, the name is nearly ZEO, we are using 12 different metrics from 4 different data sources. The first one is keyword planner, the second one is Search Console, the third one is SEO monitor, and the fourth one is the crawling.

Uh, these trackable metrics are these 12. Uh, I will go over one by one, but, uh, I just want to be, I want, I just want to be sure about we are on the same page. The keywords are keywords, the search volumes are monthly, current positions means that the current position comes from your rank tracker, not Google Search Console. And, uh, average Google position comes from Google Search Console, CTR of current keyword, and average CTR of other same position keywords, opportunity, difficulty, impression, clicks, landing URL, and search volume. I will go one by one, step by step.

But if we filter one by one, we can easily see that Google is just providing five of these metrics. But I have still seven missing metrics in here, and I'm just using any other tools to get them and collect them, and I will show you how I did it one by one. And the last two is just made by manual contracts. And the firstly, we need to be, uh, same, we need to be same page with all other tools, and we need to collect all the keyword data in one bucket. But if you are just lazy and just want to do boring stuff and copying and paste and doing the same thing, you can just work with Google Keyword Planner or any other keyword intelligence tools, and they are doing amazing jobs.

But if you want to go deep dive, you just need to use your own data and Google's data. There are lots of amazing keyword intelligent tools. My favorite one is Searchmetrics in here, and SEMrush systems, Linktex, Conductor, BrightEdge, and some of them are working in Turkey market, some of them are just UK and US based. But in general, I love Searchmetrics more. In here, you are just writing SEO conference and pressing enter. They have millions of, hundreds of millions keywords on their database and then shows that, okay, these are just 21 SEO conference keywords and its search volumes listed in here.

But in here, I'm just looking for something different. I just want to capture all the data from their original resource, such a Google Search Console, Google Analytics, and I just, I just don't want to get prepared data. I just want to create a prepared one. So if I just need the prepared one, I can work with our interns. So we just need to do something which is highly pro, high profile and qualified. So that our framework is based on three modules. Firstly, fetch and scrape. Secondly, request some additional data.

And the third one, collect and visualize. And I always hate such a diagram like this, but I hate to use this. But okay, I will go one by one and then don't make you tired by just trying to understand it. Let's start with fetch and scrape. In here, we will use two data. The first one are own data. What I mean in here by own data is your own search analytics by Google, your own Google Analytics data, and your audience data. When we collect three of them, it means that it's your own data. So, uh, if you don't hear that search analytics short summary, uh, it's one of the best things Google does in last three years for SEOs.

It just shows all the queries which directly goes to your site and then showing them in here. I also don't want to get fired by one of my clients, so there is lots of delivery, sorry for that. There is nothing confidential, but you know, I need to eat something. So in here, Google is giving all the data to you, but you can just download 1,000 keyword. You can say that, okay, just show me related keywords with SEO. If there is more than 1,000 keyword, it still shows you 1,000 keyword. But it's like a huge bucket, but you can only see the top 1,000.

You can send different query, but only get 1,000 answer. So that, uh, we need to find any workarounds and, you know, how Trups are working on these situations. So we will find some workarounds in here. And the main workaround is Search Analytics API. But when we say API, everybody gets scared because nobody is, uh, don't want to involve with IT department. But, uh, before continue to talk like this, can I ask that how many of you comes from IT background? Sorry. In here, maybe you can write a code in APIs and then scrape the data, but majority of people in here cannot do it.

That's the reason why I will continue to share spreadsheets with you. There isn't any link, but I will send it to you, like I mentioned in the starting. This spreadsheet is just doing that. You are connecting your search analytics. Okay, it says that verified site SEO ZEO. You can select your date range. It's only allowed last 90 days. And then you can group, filter, whatever you can do. But in here, Google shows five times more data providing to you. In here, you can create different queries and then collect them together and reach more and more data.

But you can see in here how many clicks you get from this keyword, how many impression shown on the search, click to CTR rate, click through rate, and then position and the average position. And as you know, Google Analytics is also, was also great data provider, but after then, Google's latest advancements, not latest, maybe four and five years ago, there is a new thing in our life, not provided, because Google don't want to share data with us. Okay, what we can do in here, there is a great workaround, but it's not well known in SEO community. If you just connect your Google Analytics account with Search Console, it looks like so movish and so simple. But if you go to the Search Console tab, like I show in here, you can see all the data in here.

For instance, there is a client which has more than 70,000 keywords in last 90 days, different keywords, and I can directly download it and then work with my own data. Just connect your Search Console with Analytics and then download the data. And then the third one, to use your own data, which is always written in the slides, but nobody is using, but I also wrote this as well. You are just research optimizing for BrightEdge SEO speaker keywords. There is a tool which is called Seed Keywords.

Principle is great. You are saying that I want to reach BrightEdge SEO speaker list. You are creating the this page, sending to your colleges, your friends, and saying that if you are looking for BrightEdge SEO speakers, how did you search on Google? After how do you search on Google? After than that, it collects all the data and creates something like this. And if you just want to collect the data from the different sources and make your reports is much more cooler to your boss, it's great idea.

Clients also loves this. But in my honest opinion, it's a bit useless, but you can still use it in your reports. It's cool. Boss loves this. The second one is Google's public data. In here, you can get the adverse data, search suggest, and topical relevancy. And adverse data, you know, keyword planner, like we mentioned. But if you just want to get hundreds of thousands keywords by Google keyword planner, you will die in a few hours because it's trashy trash and it takes too much time and you just need to find another solution.

And I found a hacky way in here which is called Term Explorer. These guys are just hacking background in Google's keyword planner tool and you are just writing all the seed keywords in one time and then saying that, okay, Term Explorer, just give me 90,000 keywords. Okay, it says that, okay, I get it. And then first asking to Google SEO conference, taking the 800 keywords, and then asking these 800 keywords one by one to Google again. If you just want to make this one query of job in a day, you need to spend minimum five or six hour and it means that you will lost two or three years from your life.

So I don't recommend to you. I make this so many times. Maybe it's last years of my life. So in Term Explorer, it also has great filtering, positive negative filtering, and competition and search volume filtering. And the data is exactly same with what you see in Google keyword planner. It's not that much expensive and you can also use it free for five query per day. I think five query per day is purely enough for most of us. And you can continue over that. And the second one is just using your own adverse performance data.

If you are a well-known brand, spending too much money on your adverse, maybe it will be nice to talk with your PPC department. Maybe they know some unexplored benefits comes from PPC accounts and you can just get benefit over there. And the other one, instant search suggest. In here, it's so interesting. I will tell another story from my friend's client. They said they worked with client and said that client has some intern. They are not using rank trackers, believe me. And they are checking 100 keywords and these intern are just searching on Google each day, going there, remember that moment, going there, copying and pasting each position.

After them working with my friend, not us, they understand that, wow, there is a rank tracking solution. Amazing. And we just don't need to spend four hours per day and it's amazing and so interesting. So you don't need to get them one by one. So always there is a genius solution and smart solution for the situations. The cheap one is Ubersuggest. And I'm sure that maybe you heard about it. Ubersuggest is great and you are just writing keywords and it turns back to you. A bit hacky one and the pricey one is Keywordtool.io.

These guys are doing amazing job. Also getting same data 101, 101. But they can also show you the search volumes if you pay enough. And also, we write a scraper, modify the scraper in here and you can just write the keywords by using their APIs and my API key is open. But I'm sure that nobody can just take this on the mind. It's impossible. And then you can positive keyword, negative keywords, and then use it. I will edit this before publishing this. My SEO team can kill me because they are using hardly and I don't want to spend their credits.

And the last one, the previous one, not last, Moz Keyword Explorer is a new tool, three months old. They are doing something different. If you are looking for sports shoes, they are saying that some different keywords related with sports shoes, but not including sports shoes. And it's such a great technology which is based in semantic keyword indexing and I highly recommend that to use this. It's two queries free per day. I'm not sure what you can do in two queries, but there is another great solution from SEO Monitor.

They just released it, Topic Explorer. It's not that much restricted. And also, you can get all the queries results. And if you write your own website, you can get your visibility score, your potential estimated monthly visit with specific keyword. It's great tool and you should try and they also have an exhibition in here. And the first step is just like this. And we all get data and collect in the same bucket. But some data has search volume, but some data don't have search volume.

Some data has click amount from Search Console, but some data don't have that much details. But if we want to get all these 12 metrics together, we just need to request and process this data one by one. In here, we are using two different APIs. And the great news, we are sharing all the spreadsheets in here and you can just copy this to your own Google Ads and then use it. One of them is exclusively written for BrightEdge SEO. The volume API is made by my friend Alec. They are just doing different stuff and then catching the search volume data from Google.

I don't know where they are catching this data. And you need to just work around APIs, but you just need to work with developers and marketing department don't like developers so much. That's the reason I wrote a volume API spreadsheet macro. You are just writing your keywords in here. Volume API automatically gets that average monthly search volumes and search volumes one by one. And by using this data, after then taking the data from Search Console, for instance, you have clicks, impressions, CTRs, but where is search volumes?

In here, you can just take the search volume data by using volume API. And also Keywordtool.io API is doing something similar. And I also shared their spreadsheet in my presentation documentation. And by using this data, you can easily understand what's going on around these keywords. And another step is calculating visibility. If you just know that there is a keyword, but it has 10,000 search. But is this 10,000 search means something for me? What's my ranking? If my ranking is seven, how many clicks can I get from these keywords?

In SEO industry, they call it SEO visibility and most of the SEO tools are using something like this. And you can use search metrics. You can use SEO Monitor. Depends on you. And in here, you can also get different metrics. In here, I really like the SEO Monitor's visibility. As you can see here, there is search volumes and there should be listed keywords, but it's blurred to not get fired. Also ranks in here. And it also calculates your search volume. Okay, you are ranking in number 10, but this keyword is listed searching more than 100,000, you will get this much traffic.

And according to this, I can align my strategy in a faster way and the accurate way. The best one is opportunity score. What it calls, assume that I'm listing for two keywords. The first one is 10,000 search volume, has 10,000 search volume and I'm ranking number three. The second one is 100,000 search volumes, but I'm ranking number 11. Instead of optimizing three to five, three to one in 10,000 keyword search volume, or instead of optimizing 10 to nine or eight with 100,000 search volume, it understands that I'm saying that, okay, these keywords are searching for more than 100 and you are ranking number 10.

If you just increase your rankings one or two numbers, you can get more and more traffic. And this opportunity score helps us a lot. And you can see the landing page based opportunity score as well. After then we align our link building strategy according to opportunity data. We really increased our profits and organic earnings in our clients and I highly recommend this to you. And the difficulty, I'm not using this that much, but sometimes you just need to pick relative keywords in SEO difficulty. If you don't want to pay to any other tool, it's completely acceptable.

In here, you just need to open your Excel and patch the keywords over, search volumes by volume API, the keywords from Search Console, and then create a table like this and then get the CTR numbers and then multiply them. But it's like scraping keywords from keyword planner so that I really recommend this to you. Just take the smallest package of any other SEO tool and then use it. And the last one is collecting and visualizing. And we just get lots of data. We solve all the problems, missing points. That's fixed. Okay, what's next? We just need to use this data because this data with its own, it's just useless.

In here, firstly we just need to eliminate keyword. Maybe unrelated keywords written in here. Firstly we need to eliminate. There is a new tool which is called Keyword Hiter. Keyword Hiter is doing you are just uploading your they are just one alternative. There isn't any alternative. That's why I had to add. You are just adding your keywords, adding positive and negative filters. And then same process. It's if you add something positive like SEO, it says that, okay, I will just list the keywords which includes contains SEO.

If you just add something negative, it automatically removes them. And it's the ancient version of keyword separator. And we also shared our internal tool which is a ZEO keyword separator. In ZEO, we are just collecting hundreds of thousands keywords first. After then that, we need to eliminate them and then group them. Because if we don't group them, we couldn't get any insight from this data. In here, as you can see, there is a keyword search about iPhones. Numbers are random. And our tool is just showing keyword density to you.

You upload just your big data in here. The next step, you are just writing your sheet titles, accessories, chargers, batteries, and then positive keywords and negative filters. This is what Keywordshitter is doing, Hiter. They are just doing this one by one. But we can easily separate our big data by using these filters. And after then doing positive and negative things, I'm saying that search and filtering, it automatically creates different Google Sheets for me. And by the way, I can easily get the data from here and then import my rank tracker, Google AdWords account, wherever I can do it.

So the next version will be released in two weeks. We will make this free for all BrightEdge SEO people. And you can just eliminate and filter. And the filtering is just based on text filters and number filters. There are some turkey stuff. And the grouping is also could be done in the web interface as well. And then you can make that happen. And the last one is just bring all the metrics together. Like I said, we have four data sources, Keyword Planner, Search Console, SEO Monitor, etc.

If we want to merge all the keywords together, we need to be Excel ninja. Who knows VLOOKUP? Okay. Who heard VLOOKUP? Who tried and failed on VLOOKUP? Me. Yeah, it's the most complex thing in the world. But after then doing one successful attempt, you just catch it. And there is a great tutorial in here. I'm really failed when I first tried in three or four years ago, but after then taking this tutorial, I get it. And after then VLOOKUP, maybe you consider to import your own Excel into Google Spreadsheet.

You can just get the API data. SysTricks has amazing guide in here. You can just import SysTricks API to the Google Sheets. After then that, you can do any API. It's freaking easy. Just do it for one time. And the third formula is import JSON. You can just get the JSON data into Google Sheets as well. And like I said, how to use this data. And we are human and our brains are so smaller than when we compare with computers. So that we just need to visualize something in our mind. That's why I'm using visualization.

We are using this in high charts. Like you see in here, there is a tree map view. Just imagine that. It's not the keyword research, but you can apply this. And the GIF is playing back. All the groups are listed like this. And then you can see total search volumes, click, and then see other subgroup, and then click. You can see the keywords. By the way, you can understand which keyword group has the largest search volume. What I need to do in my strategy. Should I focus on the dresses? Should I focus on the shoes? It helps me to understand in the visual way.

It's very beneficial and I highly recommend this to you. High charts. And the second one is bubble view. It's also beneficial, but I prefer the tree map. And also you can see the word cloud. I'm using this on the presentation in subgroups. People love to see this data. And the best conditional formatting I have ever made is this. In here, you can see all the iPhone keywords. All the search volume data comes from volume API. And I just colored them according to horizontal line of each keyword.

As you can see in here, in 210 for iPhone 5C, which means that in the sixth month, it gets the peak time of the year. That's the reason why we are using this. If you just filter on the Excel by cell color, you can easily see that in the first month of the years, which product has the highest amount of search volume into its own history. Then you just know all the 12 months history of the keywords. You are just doing conditional formatting according to this 12 months and you are getting the top one and then mark this as a green. And then listing greens shows you, okay, you just need to optimize for new year for these keywords. It's amazing thing.

If you are working with clients in fashion vertical, it's really important then. The winter is coming. You just need to sell different things, not bikinis and not other things. So it really helps you to see the things. And also you can like this. In here, I have a keyword like iPhone 7 charger. And then I'm listing position six. And the CTR is just 4%. But according to my data, my own position six keywords has average 6.3 CTR. What does it mean?

It means that I need to reconsider this keyword because it looks like less people clicking this search than others. Maybe my competitors are using Google Rich Snippets or my titles and descriptions not that much succeed. So I just need to optimize them and make them in better way. And also another example, keyword size as well. I'm finishing. In here, you can see iPhone 7 discounts. Go to the iPhone 7 chargers page, keyword versus landing page. You can get another data. And you can also see position differences on these cases.

There are lots of different scenarios in here. You can create your own scenario, but it will be 100% of correct. Will be much more funny than doing ordinary keyword research stuff. If you are interested in all this stuff, I couldn't finish my documentation yet. Just drop me a line and I will answer to you. Yigit@zeo.org. And after then that, I will send all the documentation to you. If possible, maybe I can get your email later from BrightEdge SEO and then send to you. And thank you so much. If you need, you can just drop me a line by email.

Thank you for the opportunity.

— Okay. We're going to move straight on to the second speaker.

— Yes.

— It is Laura from Rice Media who's going to discuss the common myth.