Prerequisites:
Make or Zapier
Middleware makes this a very straightforward, no-code build. All tools integrate with them directly. Just connect all your data sources and use the middleware to pass the data to Google Sheets.
› Pull mentions: Octolens offers API access is on every plan (no enterprise upsell). It is purpose-built to track AI visibility-- Reddit, Hacker News, GitHub, LinkedIn, YouTube, podcasts -- and one POST to its /api/v2/mentions endpoint returns clean JSON where each mention already carries what the score needs: an AI relevance score to filter noise, plus engagement metrics and follower count to do the reach-weighting. A mention in a 50k-view thread outweighs one in a dead post. Filter by relevance first, then weight by engagement.
› Get branded search: Us the Google Search Connector in your middleware to send your branded-query volume -- name, products, key people -- with a category-term row for context. This is the Share-of-Search input.
› Decide on the best review site for your brand category and connect: From your category's platforms -- G2 and Capterra for B2B software, Trustpilot or Google for service businesses -- pull count, average rating, and month-over-month change.
› Normalize each input: Create and connect your Google Sheet. On a Score tab, score every input 0–100 against whoever leads it. If column B holds each brand's reach-weighted mention total: =ROUND(B2/MAX(B:B)*100, 1). Same for search. The leader lands at 100; everyone else is a share of them.
› Blend the reviews input: Nobody should win on three five-star reviews -- volume 70%, rating 30%. Count in B, average rating (out of 5) in C: =ROUND(0.7*(B2/MAX(B:B)*100) + 0.3*(C2/5*100), 1).
› Weight-sum the score: Weights in a header row -- 0.45 mentions, 0.33 search, 0.22 reviews -- then: =ROUND(SUMPRODUCT(B2:D2, $B$1:$D$1), 1). Chart that column over time and you've got the whole instrument.
› Quarterly prompt check: Check some prompts across the platforms, making note when a competitor is mentioned. Use these prompts to test the score as you move forward.