Are you tracking rankings in ChatGPT? Copy our template

In a world where people "search" on Bard or ChatGPT, is the ranking report extinct? 

One of the critical components of doing SEO is selecting keywords that tie back to your business's bottom line and tracking your visibility on the search results (SERPs), to determine how well you’ve done your job.

This is typically called an SEO ranking report.

The idea is simple...If I can help you become visible for the keywords applicable to your business, that visibility (rankings) will turn into traffic and thus conversions and revenue.

We're obviously undergoing tons of change as it relates to the disruption of ChatGPT on the searchers experience. And an SEO professional can look out at the changes coming with fear or denial, our preference is to lean in.

We're going to lean in...

Lower Traffic in Your Current Ranking Report ≠ Lower Revenue

If people search on Bard or ChatGPT instead of traditional Google or Bing, businesses will still sell their products and services.

The goal isn't to optimize a website, the goal is to grow the business - having a website is a necessary step in growing your business, but don't get it twisted.

The goal is  to grow revenues, growing rankings was an important tactic towards doing that, but it wasn't the job to be done*

*that logic doesn't apply to sites who sell ads or are affiliates.

Ranking reports lose value if chat-based search takes off

In a SGE (Search Generative Experience) or chat based search world, how does your visibility change? Is the ranking report useless? Potentially.

How do you know if you are showing up when queries get as specific as: 

“It’s a rainy day in Pittsburgh, I have 3 kids - one is 6 years old and is really into treehouses, one is 4 and is into dinosaurs, and my oldest is 10 and is into experiential play, if we only have time to go to 1 place where would you recommend we go where everyone will get a little something they would like?”

Well, I thought about this, and while queries will be highly personalized that is no excuse for not trying to track my clients rankings / visibility in ChatGPT (We’re not in Bard’s API yet, but imagine a world where Google makes BARD commands available in sheets natively 🤯).

Step-by-Step instructions on how to track your brand's ChatGPT visibility

Find the keywords where Google shows sites that are comparing solution providers:

For me I like looking at PAA’s for inspiration for queries.

I collaborated with my co-worker Jordan Strauss on this post and he realized that with the search data from paid search we can see the all the queries people have typed in to traditional Google search to trigger an ad we can then use the SEO data to look for domains like G2, Wirecutter, BobVila, CNET, Nerdwallet, and other types of review / comparison / affiliate sites, here are a few of the words to look out for.

1. Best
2. Vs
3. Which
4. Where
5. Top
6. Recommended
7. Compare

Not only can you look for those words in search terms but you can also (depending on your scrape vendor) look for those words in the following places, which I recommend you do:

1. PAA’s (People also ask)
2. Titles
3. Descriptions

Here is how we used Our data platform to pull that data and we’re planning on scaling this instantly to each client, that’s the beauty of tools at scale…we gain consistency across clients the minute we have the idea.

Referencing the numbers above you'll see how we combined the PPC and SEO data:

Filters (Numbers 1-4 above):

1. Cost (SEM) - we wanted to only see URLs (SEO) where we spend enough money in paid search to impact client strategy.
2. We can use any or all of the words above in the page title (SEO)
3. Limited by the last 3 months
4. Each client in our data warehouse

Results (Numbers 5-9 above):

5-6 - URL that has “Best” in its title with the number of top 20 search terms (SEO) that spent over $500 last month (SEM)

7-9 - Search term + cost last month (SEM) and how many URLs (SEO) had the word best in the top 20 (We’re including PAA links, ads too so that is why the # is over 20).

Take your keywords, wrap them in longer "natural language" style queries:

Now we can export out those search terms, and put natural language words around them to track if my client is visible in the answers in ChatGPT.

Why not use Generative AI to give me prompts?

Step 1: Turn keywords into prompts for ChatGPT & place into a Google Sheet:

Step 2: In Column B Enter =GPT(A5) and boom:

Step 0: Install the extension (It’s not going to be that easy y’all)

If you are wondering how that happened, thank the team at GPT for Work - for building a plugin for sheets that allows you to use ChatGPT in Google sheets. If you are like “How do I install this?” This video will help you install it quick and easy.

Now you’ve got all your questions and answers from ChatGPT in Google sheets!

Step 3: Copy and Paste.

Simply put in the following formula. Now that you've got the answer, you're going to need to copy the answer into column C as a text field, yup simple copy and paste (special).

Step 4: Create a column with your clients brand name

If this all seems too simple, it kinda is - isn’t that the point?

Step 5: Look up your brand’s presence in ChatGPT’s answer

Now I need to make a lookup to tell me with a Yes or NO if my brand is present in the answer. So I asked ChatGPT inside of sheets, how to do it. 🙂

Here is the sheets formula: =IF(ISNUMBER(SEARCH(D1,C1)),"Yes","No")

Pro-tip - have multiple brand names? You can search a list of values in another sheet. For example, this searches column C for any values listed in column A of Sheet2.

 
=ArrayFormula(IF(LEN(C2:C), REGEXMATCH(C2:C,".*(?i)("&TEXTJOIN("|", TRUE,Sheet2!A:A)&").*") ,""))

Now, you can calculate the percent your brand is visible for non-blank columns using the formula: =PERCENTIF(filter(E2:E,E2:E<>""),"Yes")

I recommend you run this on a regular basis to see how different the answers are. The answers definitely change and there’s value in you as an SEO professional knowing when it changes and actively deploying strategies to get you in there more consistently.

Now there's probably some more you could do in Google Sheets to trend this out over time, but my tool of choice is usually Power BI, so if you add in a date column you can just save the file weekly and dump it into Tableau, Power BI, Looker Studio, whatever. In Power Bi you can use a folder as source which lets you just save onto your network drive and boom.

But now you have a new report to show clients you are relevant as an SEO at this time and that you can build “ranking” reports for this new world and that you are a resource as they are making sense of this new world.

I am doing this for brands to start, but it's obviously extensible for all kinds of word mentions.