DIY LLM brand mention tracker
A low-cost solution for monitoring brand mentions across LLMs.
I recently wanted to try the new Ahrefs tool, the Brand Citation Checker, and at first I was put off by the price — $297 — steep.
Also, the accuracy seemed a bit off, especially for the medium-to-large-sized brands I’m working on.
But curiosity got the better of me, so I decided to dig into how they’re actually doing it.
My thoughts were that they are working with LLM systems and have access to some kind of back-end data.
Much to my surprise, the method is actually way, way simpler.
It turns out Ahrefs and other similar tools are prompting LLMs at scale and cross-referencing brand name with the prompt results.
The light bulb went on — I could build that myself - for just a fraction of the price.
I built the tool, and here’s how you can do it yourself.
You will need:
Make account
A spreadsheet
API credits (& keys) for OpenAI, Grok, Gemini, Perplexity, and Claude
Sheet setup
Tab#1: Keywords
Here, we’ll enter keywords that are important for your or your client’s business.
Tab#2: Questions
Enter a headline in A1 and leave it for now. This is where the questions/prompts are going to appear before they go into each LLM chat.
Example with the prompts filled in:
The prompt that creates prompts, as well as the number of new prompts, can be easily regulated — more on that a bit later.
Tab#3: Questions
In this tab, automation will populate prompt replies from each of the LLMs.
Setup is easy. Each of the brand columns has this formula, adjusted for the columns:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH("foot locker", C2))) > 0, 1, 0)
=IF(SUMPRODUCT(--ISNUMBER(SEARCH("nike", C2))) > 0, 1, 0)In this test case, we will be checking for mentions of Nike and Foot Locker.
After the automation has stopped running and all questions have been run through all the LLMs, it’s time to drag the formulas from below Brand and Brand 2 down — all the way to the row where we have the last reply.
This is how that process looks like:
Automation fills in the prompt replies:
(if you’re curious about the wonky placement, it’s because Make action adds new rows, so each reply has to be placed in a new row)
Formulas are dragged down, and brand mentions have been counted:
Tab#4: Summary
The 4th tab has a simple setup that, again, counts brand mentions in the tab before and displays them in a simple table.
As we can see in the screenshot below, Nike is mentioned in 100% of the LLMs’ answers, while Foot Locker appears less.
Voilà !
Now onto Make automation.
Make automation steps explained
Here’s the screenshot of the automation — if you get stuck, ChatGPT is all the help you might need.
Step 1: Search Rows
This action searches for keywords from Tab 1 of the sheet.
Step 2: Iterator
This action has the automation run through multiple rows, if there are multiple rows.
Step 3: ChatGPT Completion
Here we create the prompts — using this prompt in my case:
[ { "role": "user", "content": "Generate 3 natural-sounding search questions someone might type into Google when looking for a store, brand, or website about'Keyword'. Return only as a JSON array." } ]Step 4: Router
Step 5: Fill in the prompts from Step 3 into Tab 2 of the sheet.
Step 6: JSON
The JSON step converts a raw JSON string into separate data fields you can use in the next steps.
Step 7: Iterator
Makes sure we go through all the prompts created in previous steps.
Step 8: Router
Step 9: LLM Completions
This step prompts each of the LLMs in the screenshot with the prompt from the previous steps.
Step 10: Add Row
This action adds the LLM’s reply to the sheet.
TA-DA!
Have fun building!