Savannah Casto - Brand Visibility Reporting When No Single Data Source Covers It

1. Brand Visibility Reporting When No Single Data Source Covers It Savannah Casto 1
2. Savannah Casto Google Cloud Professional Machine Learning Engineer Business Intelligence Lead at LOCOMOTIVE Google Cloud Associate Data Practitioner 2
3. Savannah Casto Betty White Professional Cat Petter at MY HOUSE Peanut Allergies 3
4. The Goal for this presentation We’ll cover why and how we built a cool data pipeline to report on Brand Visibility, so you can build your own and pull insights that actually matter. 4
5. What are we going to talk about Why is Brand Visibility so hard to report on? The data The data pipeline Pulling cool insights 5
6. 01 Why is Brand Visibility so hard to report on?
7. Four questions that Brand Visibility needs to answer 01 02 03 04 Demand Conversation Performance Context How much are people searching for what we offer? What are people saying and seeing about these brands? At what rate are people clicking on our site and our ads? How does all of this change by area, and compared to our competitors? 7 - Why is Brand Visibility so hard to report on?
8. No single data source says it all We need a mix of first party and third party data First party Third party Data we own Data we gather GA4 DataForSEO Site engagement and sessions Search volume, Google Trends, search results Search Console Cloro/Peec AI Organic brand and category clicks AI answers in ChatGPT, Gemini, AI Overviews Google Ads Syften Spend, clicks, and impression share CRM Leads and opportunities by channel 8 - Why is Brand Visibility so hard to report on? Reddit mentions
9. Data sources may become gated Reddit API restrictions 9 - Why is Brand Visibility so hard to report on?
10. Data sources become less reliable Google poisoning SERP results 10 - Why is Brand Visibility so hard to report on?
12. Data may have never been available, or is highly limited Google Search Console offers limited Generative AI reports…but doesn’t provide this data via API Bing Webmaster Tools offers AI Performance Report… but it also isn’t available via API Major LLM providers offers AI Performance Report… but it also isn’t (Anthropic, Gemini,available OpenAI) via API don’t provide any data on brand mentions (for now) 12 - Why is Brand Visibility so hard to report on?
13. So we gotta cobble it together ourselves 13 - Why is Brand Visibility so hard to report on?
14. 02 The data
15. Find sources that answer our questions 01 Demand Brand and category search volume, Google Trends interest, and service keyword demand DataForSEO 02 Conversation AI answers in ChatGPT, Gemini, and AI Overviews, weekly AI visibility, and Reddit mentions Peec AI/Cloro, and Syften 03 Performance Site engagement, organic clicks, Google Ads, leads and opportunities GA4, Search Console, Google Ads, CRM 04 Context Competitor ad copy, who advertises on our brand searches, and organic strength, by market, state, and city 15 - The data DataForSEO
16. Data is messy 16 - The data
17. Location granularity varies From city level down to no location at all 17 - The data Google Ads DataForSEO search volume Listed cities only For selected cities City GA4 State and region DataForSEO search volume Google Trends For selected states Region Country Search Console None Syften CRM Matched to markets by brand Depends on whats set up AI answers Competitor ads
18. The data doesn't share a schema Starting with time: five different grains 01 Daily totals 02 Weekly 03 Monthly estimates GA4, Search Console, Google Ads Peec AI visibility DataForSEO search volume 18 - The data 04 Point in time snapshots 05 Individual events Search results, AI answers Syften, one row per matched post
19. Brand names don’t always resolve clearly Same name, different meaning - A Montreal agency with the same name The railway meaning of the word Old names, same brand + + Exclude IMS Marketing Adapt Partners 19 - The data Merge in Subject brand
20. 03 The data pipeline
21. Formula for success A B C D’s Always 21 - The data pipeline Be Creating Dependable and verifiable systems that you can easily change and modify based o of the changing work environment and the business needs
22. How that influenced the way we built this pipeline We want to be able to adjust this process as new sources become available/wanted later We want to make it easily reusable for different clients 22 - The data pipeline
23. We made it modular Found a great new data source? Plug it in without touching the rest 23 - The data pipeline A provider changes its API or shuts down? Don't need a source anymore? Replace or remove Remove it Different brands, markets, or clients? Configuration handles it, not a rebuild
24. Standardized the data 01 Keep each source at the finest location level it really has Land every source on shared dimensions: date, country, location, subject brand 03 Turn events into counts, never sum indexes, never mix currencies Combine into different tables, one size won’t fit all 24 - The data pipeline 02 04
25. The Google Cloud Platform flow, end to end Cloud Scheduler one schedule per job BigQuery starts Data Studio Cloud Run jobs one per source stores Raw tables every answer as it came what to fetch Input sheet brands, keywords, prompts 25 - The data pipeline cleans Clean rows parsed, never counted twice builds Reporting tables reads rebuilt and checked Claude
26. The Google Cloud Platform flow, end to end Cloud Scheduler one schedule per job BigQuery starts Data Studio Cloud Run jobs one per source stores Raw tables every answer as it came what to fetch Input sheet brands, keywords, prompts 26 - The data pipeline cleans Clean rows parsed, never counted twice builds Reporting tables reads rebuilt and checked Claude
27. The Google Cloud Platform flow, end to end Cloud Scheduler one schedule per job BigQuery starts Data Studio Cloud Run jobs one per source stores Raw tables every answer as it came what to fetch Input sheet brands, keywords, prompts 27 - The data pipeline cleans Clean rows parsed, never counted twice builds Reporting tables reads rebuilt and checked Claude
28. The Google Cloud Platform flow, end to end Cloud Scheduler one schedule per job BigQuery starts Data Studio Cloud Run jobs one per source stores Raw tables every answer as it came what to fetch Input sheet brands, keywords, prompts 28 - The data pipeline cleans Clean rows parsed, never counted twice builds Reporting tables reads rebuilt and checked Claude
29. 04 Pulling cool insights
30. 30 - Pulling cool insights
31. 31 - Pulling cool insights
32. 32 - Pulling cool insights
33. 33 - Pulling cool insights
34. Vertex AI 34
35. She just finished learning the Google ML ecosystem… Rebrand Vertex AI
36. Vertex AI Gemini Enterprise Agent Platform
37. BigQuery ML Connect BigQuery data to a Gemini Enterprise Agent Platform Gemini model directly via SQL code. - AI.CLASSIFY Sort text into provided categories 37 - Pulling cool insights - AI.SCORE Rates text based off of a scale you provide - AI.GENERATE Write text based off of other text given
38. sentiment_score = AI.SCORE( Reddit comment sentiment analysis CONCAT( 'Rate the sentiment this Reddit comment expresses toward the brand: ', brand, '\n', 'Use a scale from -5 to 5.\n', '-5 is extremely negative toward the brand (hostile, furious, warning others away).\n', '0 is neutral, purely factual, or evenly mixed about the brand.\n', '5 is extremely positive toward the brand (enthusiastic, loyal, recommending it).\n', - AI. CLASSIFY 'Score only the attitude toward this brand, not the overall mood of Classify competitor ads by theme the commenter ', - AI.SCORE 'or their feelings about other brands. In comparisons, score how this brand comes off.\n', See sentiment for how your brand (and competitor brands) are being mentioned 'Score sarcasm by its intended meaning, not its literal words.\n\n', 'Comment: ', full_text ), endpoint => 'gemini-3.1-flash-lite', connection_id => 'XXXXXXX' ) 38 - Pulling cool insights
39. 39 - Pulling cool insights
40. Competitor ad messaging message_theme = AI.CLASSIFY( ad_description, categories => [ ('price_value', 'competes on affordability and getting more for less'), ('innovation_leader', 'leads with new technology or being first'), ('trusted_expert', 'emphasizes experience, credentials, and reliability'), ('award_winner', 'leads on winning awards in the field'), ('effortless_convenience', 'sells ease, speed, and saving - AI.CLASSIFY time'), ('other', 'does not fit any theme above') Classify competitor ads by theme ], endpoint => 'gemini-3.1-flash-lite', connection_id => 'XXXXXXXXX' ) 40 - Pulling cool insights
41. 41 - Pulling cool insights
42. AI.GENERATE( Extract mentioned awards CONCAT( 'List every award, ranking, certification, or honor this ad says the advertiser has received.\n', 'award_name: the official name of the award, without the year.\n', 'awarding_body: the organization that gave it, or null if not stated.\n', 'award_year: the year as a number, or null if not stated.\n', 'Only include awards that are explicitly named. Return an empty list if none are.\n\n', - AI.GENERATE Take all competitor ads that mention awards from the last year and pull out the specific awards mentioned 'Ad title: ', IFNULL(c.ad_title, ''), '\n', 'Ad text: ', IFNULL(c.ad_description, '') ), endpoint => 'gemini-3.5-flash', connection_id => 'us.vertex_llm_conn', output_schema => 'awards ARRAY<STRUCT<award_name STRING, awarding_body STRING, award_year INT64>>' ) 42 - Pulling cool insights
43. 43 - Pulling cool insights
44. page_type = AI.CLASSIFY( CONCAT('Classify this web page that an AI search engine cited as a source, based on its URL.\nURL: ', cited_url), Categorize cited websites categories => [ ('best_of_list', 'ranked roundups, top 10 lists, buyer guides, alternatives pages, or X vs Y comparisons on a third party site'), ('directory', 'catalogs or listings of companies or vendors, such as industry or local business directories'), ('review_platform', 'sites built around user reviews and ratings of products or companies'), ('news_media', 'news articles, press coverage, or trade publication stories'), ('blog_editorial', 'how to guides, explainers, or expert articles on a third party site'), - AI.CLASSIFY ('forum_community', 'forums, Q and A threads, or community discussions'), Take cited URLs from AI answers and classify them by page purpose ('reference', 'encyclopedic, government, academic, or standards content'), ('vendor_other', 'the website of a company selling a product or service, other than a listing or review page'), ('other', 'does not fit any category above') 44 - Pulling cool insights ] )
46. Cited ranked lists URLs = Outreach opportunities 46 - Pulling cool insights
47. Insights that lead to wins Ask your team members what they need Data needs to lead into actionable insights Pull some easy wins with simple data 47 - Pulling cool insights
48. 05 Final Thoughts
49. Limits of this data Tracked prompts aren’t perfect AI isn’t perfect • These are our tracked prompts, not our users • Careful human reviews are needed on any • It’s a snapshot of data extracted information
50. Takeaway 1 2 3 4 There's a lot of great data sources out there to get what you need Data is messy, but is workable Making your data pipeline flexible can help save a lot of trouble in the future BigQuery ML makes it easy to augment the data and pull out meaningful insights
51. Try out this pipeline yourself Use our code to create your own brand visibility data pipeline go.locomotive.agency /brand-visibility-pipeline
52. Want to see more cool stuff? Scan the code, sign in with Google, and add six of our tools to Claude or any AI that supports MCP. Free for 30 days. » Query Fan-Out Model » ChatGPT Step Trace » Cannibalization Risk » Content Brief » Agent Readiness Scanner Toolhub.locomotive.agency /techseoconnect
53. Thank You www.linkedin.com/in/savannah-casto/