116: From Hours to Minutes: Google Ads SQRs on Autopilot with Claude Cowork

Danny Gavin (Host): 00:05

Hello everyone, I’m Danny Gavin, founder of Optidge Marketing Professor and the host of the Digital Marketing Mentor. What if I told you that a task that used to eat three to four hours of my week every single week now gets done automatically while I’m sleeping with zero input for me? And that the output is actually better than what I was producing manually. That’s not a hypothetical. That’s what happened when I built our auto search query report checker using Claude Cowork. And today I’m going to walk you through exactly how I did it, the problem it solved, the process of building it, what it actually does now, and what I learned along the way. So let’s get into it. So if you’re new here, I’m Danny Gavin, and I run Optage, a performance marketing agency. We live and breathe Google Ads, and a huge part of what we do is making sure our clients are getting the most out of every dollar they spend. One of the most important recurring tasks in Google Ads Management is something called a search term analysis report or search query report. At Optage, we call it an SQR. And for a long time, running SQRs was one of those things that was critical but painful. It was important enough that you could never skip it, but time-consuming enough that you kind of dreaded it. That all changed when I started building with Cloud Cowork. And I want to share that story today because I think a lot of people in the marketing and agency space are sitting on the same kind of problem, a repetitive, high-value task that they know that they should be doing more consistently, but just don’t have the bandwidth for. 

Danny Gavin (Host): 01:42

All right, let’s start with the basics. If you’re running Google ads, you’re bidding on keywords. But here’s the thing: the keyword you’re bidding on and the actual search terms someone typed into Google are not always the same thing. When you use broad match or phrase match keywords, and today sometimes even exact match, Google has a lot of discretion about which terms will trigger your ad. And Google is sometimes generous with that interpretation. So you might be bidding on DNA test, and your ad is showing up for people who searched, how do I find my biological parents, or free ancestry records. Sometimes that’s fine. Sometimes those queries actually convert, but a lot of the time you’re paying for clicks from people who are never going to buy from you. And the only way to find that out is to look at your search terms report. The SQR or search query report is essentially an audit of all the real searches that Trigger adds over a given period. You’re looking at every query, how much you spent on it, how many clicks it got, and critically whether it converted. Did anyone who came in through that search term actually buy something, fill out a form, or become a customer? From that analysis, you’re doing a few things. Number one, you’re identifying terms to add as negative keywords, so you stop wasting budget on irrelevant searches. Two, you’re finding high-performing terms that should be promoted to exact match keywords so you have more control. And three, you’re writing observations, actionable notes for the account manager about what’s happening and what to do about it. It’s one of the highest leveraged tasks in Google Ads Management. Done well, it can significantly reduce wasted spend and improve performance across the board. The problem is, for a real account with real volume, it’s a lot of data to go

Danny Gavin (Host): 03:15

 through. So here’s what running an SQR looked like before I built this tool. And I want to be really honest about this because I think a lot of people will recognize this pattern. Step one, you’ve got to export the data. You go into Google Ads or a third-party connector, pull the search term data for the last 30, 60, or 90 days, download it as a spreadsheet. Already, if you have multiple accounts, you’re doing this multiple times. Step two, format and sort. Now you’ve got a raw dump of data. Thousands of rows sometimes. You’re cleaning it up, formatting it, adding columns, sorting it first by conversions to find your winners, then by cost to find your biggest spenders, and then you’re color coding rows manually. Green for converting terms, yellow, which you’re unsure about, and red for high spend terms with zero conversions. Step three, actually analyzing the data, right? This is the hard part. You’re going through row by row, campaign by campaign, ad group by ad group, you’re identifying patterns, you’re writing observations in a separate tab, you’re populating a negatives list, and you’re supposed to be cross-referencing all of this against what you already know about the account. Is this keyword new? Is this CPA above or below the account average? Or is this an informational query or a purchase intent query? Step four, compile and share. Once you’ve done all of that, you’re formatting the final output, organizing everything into the right tabs, and sending it to the account lead. Start to finish for one account with meaningful traffic easily three to four hours. That’s the problem I set out to solve. 

Danny Gavin (Host): 04:35

I’ve been using Claude Anthropics AI for a while for various things: writing copy, answering questions, thinking through problems. It’s generally one of the most useful tools I’ve added to my workflow. But I’ve been mostly using it as a chat tool. Ask a question, get an answer. And then I got access to Claude Coork, which is a desktop app that lets Claude actually do things on your behalf, not just answer questions, but connect up to data sources, run scripts, create files, pull live data, and build automations. It’s a completely different category of tool. When I first started using it, I realized pretty quickly that this was something you could build real workflows with, not just automate simple stuff, but actually replicate the kind of analysis that a smart team member would do with access to your real data. And that’s when the SQR idea clicked. What if instead of having a person manually pull the data, sort it, analyze it, and write observations, Claude could do all of that for me. Not just generate a template, but actually pull live data from Google Ads, run the analysis, apply our methodology, and produce a finished Excel file. That was the goal. And the spoiler is it works. So how did I actually build

Danny Gavin (Host): 05:40

 this? The first thing I did was connect Claude Cowork to Windsor.ai, which is a marketing data connector that aggregates data from hundreds of sources, including Google Ads. That gave Claude the ability to actually query our Google Ads accounts and pull live data. Technically, you don’t need Windsor.ai. You could also manually export the data from Google Ads and then use that spreadsheet to start the process, but I was trying to automate everything from soup to nuts from the beginning until the end. Then the real work began, which was figuring out exactly how to instruct Claude to do what we needed. And this is the part people don’t always talk about when they talk about AI automation. The thinking isn’t just how do I get AI to do a task. It’s how do I take everything a smart, experienced analyst knows and does, and then articulate it precisely enough that Claude can replicate it. For us, that meant documenting our entire SQR methodology, the two-path sort method, first by conversions, then by cost, the color-coding logic, green for converting terms, yellow for terms with indirect conversions, red for high spend with zero conversions, the ad group roll-up analysis, the pattern-based negative keyword identification, things like automatically flagging any search term containing what is or how to as informational queries, the phrase match to exact match restructuring recommendations. We had all this knowledge in our heads. The task was turning it into something Claude could follow. One thing I learned early on is that you can’t just brute force the data pull. Windsor.ai has limits on how much data it can return in a single query. And the fields you want don’t always live on the same resource. So our first version actually had to split things into two queries, one for the metrics, which mean the impressions, the clicks, cost, conversions, cost per conversion, and a second one for the dimensions, the ad group, match type, and triggering keyword. Then we’d merge them in a script. The merge step matters more than it sounds because it’s what lets you analyze search terms in context instead of in isolation. Knowing that a specific search term was triggered by a phrase match keyword inside a specific ad group completely changes what you’re going to do with it, whether you add a negative or pause the phrase match and replace it with an exact match version. We eventually figured out the right Windsor.ai field names to pull everything, metrics, dimensions, and the triggering keyword all in a single query, with an impressions filter to stay under the row cap. The two-query approach is still in there as a fallback if the combined pull ever errors out, but day-to-day it’s one clean pull. The output is a formatted Excel file with six tabs, an instructions tab explaining the process, an observations tab with auto-generated analysis notes, a brand data tab, a non-branded ad group summary tab that rolls up performance by ad group, and a negatives tab pre-populated with recommended negatives at the right match type and level, account, campaign, or ad group. And the whole thing runs in Cloud Cowork. I just say run the SQR for Family Tree DNA, one of our clients, and a few minutes later I have a finished, formatted Excel file ready to review.

Danny Gavin (Host): 08:24

 Let me walk you through what the output actually looks like because I think that’s where the value really becomes tangible. When you open the Excel file, the first thing you see is an instructions tab. It’s basically a one-page guide to the optage SQR process, how to read the file, what the color coding means, how to use the observations and negative tabs. It’s useful for onboarding new team members, and it keeps everyone aligned on our methodology. The brand data and non-brand data tabs are where the actual search term data lives. Each row is one unique search term aggregated across the 30, 60, or 90 day period, with columns for the triggering keyword and its match type, so you can see exactly what inside the account is pulling each query in. For e-commerce accounts like Family Tree DNA, we also surface conversion value and row as, which is return on ad spend, alongside CPA or cost per acquisition, because a term with a high CPA but a strong ROAS is still profitable. CPA alone doesn’t tell the full story for e-commerce. The rows are color-coded automatically. Green rows are terms that generated conversions. Yellow rows are terms with indirect or assisted conversions, and red rows are terms that spent meaningfully but got zero conversions. Those red rows are your immediate priority. The non-branded ad group summary tab is the view we use before we ever look at individual search terms. It rolls cost conversions, CPA and ROS up to the ad group level and flags three patterns automatically: ad groups with zero conversions and meaningful spend, ad groups running below a one row as for e-comm accounts, and ad groups with a CPA more than two times the campaign average. Those three signals usually can’t be solved term by term. They need a structural fix at the ad group level. The observations tab is where things get really interesting. Claw doesn’t just dump the data and walk away. It generates written observations for you, framed around the client’s primary KPI. For family tree DNA, that means ROAS. For lead gen client, it could be CPA. It flags the high spend zero conversion terms with the actual spend, CPA and ROAS numbers, and tells you what to do about them. It identifies informational queries, the what is DNA and how to trace ancestry type searches that are almost never going to convert, and recommends phrase match negatives at the pattern level. It spots navigational terms, people trying to log into their existing account rather than buy, and flags those for account level negatives. It also does ad group level analysis before it writes individual term observations. If an entire ad group has zero conversions and meaningful spend, it calls that out specifically and recommends the right action, whether that’s our standard phrase matched exact match restructuring, pausing the phrase match keyword that’s pulling the messy traffic, and replacing it with an exact match version of the query that has actually converted, or pausing the ad group entirely, pending a longer review. It also builds in a caveat we always want a human to remember. Google Ads doesn’t report every search term. Low volume and privacy threshold of queries get rolled into an other bucket, so an ad group or term showing zero conversions at the search term level may still have conversions hiding an other. The observations prompt the account lead to check the ad group level view in Google Ads before making a final pause or negative decision. Finally, the negatives tab comes pre-populated, not a blank template waiting for you to fill in. Actual recommended negatives organized by campaign at the account, campaign, and ad group level, with the right match type formatting already applied and the core concept extracted rather than the full search term, so the negatives are efficient rather than narrowly scoped to one variant. The account lead still reviews everything. This isn’t a hands-off black box. But instead of starting from a blank spreadsheet and spending four hours doing the analysis themselves, they’re reviewing a finished document and spending maybe 45 minutes validating and adding context. That’s the difference. So what’s the actual

Danny Gavin (Host): 12:12

 impact? Here’s a few things that I’ve noticed. First, the obvious one time. This task went from three to four hours of manual work to under five minutes of setup plus a review. That’s not a 50% improvement. That’s a fundamental change in what’s possible. Second, consistency. When you’re doing this manually, the quality of the output depends on who’s doing it and how much time they have that week. When Claude runs it, the methodology is applied the same way every time. Every account gets the same level of analysis. That’s huge for an agency that’s trying to deliver consistent quality across multiple clients. Third, and this one surprised me a little, frequency. Because the task is now so fast, we can run SQRs more often. The tool isn’t perfect, and I want to be clear about that. What it does really well is the mechanical work, pulling the data, merging it, applying our methodology, flagging patterns, and writing first draft observations with the actual numbers attached. It can even pull longer look back windows, 60, 90, 180 days, when we want historical context on a term before recommending a negative. What it can’t do is the judgment layer. It doesn’t know that a particular client intentionally targets deal seekers, or that a specific ad group is being kept alive for strategic reasons even though the numbers look bad. It also can’t fully see around Google’s other search terms bucket, which means an account lead still needs to cross-reference ad group level performance in the Google Ads UI before pulling the trigger on a pause or a negative. And it can’t weigh the client relationship context, what the account lead discussed on the last call, which levers we’ve already pulled this quarter, and where the client’s priorities are shifting. So it gets us 80% of the way there automatically, and the last 20% is exactly the part where human judgment really matters.

Danny Gavin (Host): 13:53

 So if I were going to give someone advice about building something like this, here’s what I’d say. One, document your processes before you try to automate it. I know that sounds obvious. I know that sounds obvious, but I think a lot of people try to automate things they’ve never fully written down. And what I found is that the act of writing down the methodology in detail with specific rules and decision trees made me a better analyst. I found gaps in our own process that I hadn’t noticed before. Two, expect the first version to be rough. The first SQR this tool produced was not something I would have sent to a client, but it was 60% there. And 60% of the way there in five minutes is a great starting point to iterate from. Build version one, review it critically, update the instructions, run it again, the methodology gets better fast. 3. Keep humans in the loop for the judgment calls. I’ve seen people try to fully automate things that require genuine expertise, and it tends to backfire. The goal here was never to replace the analyst, it was to eliminate the parts of the job that don’t require expertise. Sorting rows, color coding cells, formatting tabs, that doesn’t require expertise. Writing the final observation on a borderline negative keyword, that does. AI handles the former, humans handle the latter. Four, the ROI compounds. The first time you build something like this, you save four hours. But then you run it 50 more times over the course of the year, and now you’ve saved 200 hours. Investment in building the automation pays dividends every single week. Alright, that’s the story of how I built the auto-SQR checker with Claude Cowork. If you’re running a marketing agency or you’re managing Google Ads accounts and you have a recurring analysis task that’s eating your time, I really encourage you to think about what it would take to build something like this for yourself. The tools are genuinely accessible now. You don’t need to be a developer, you don’t need to write code from scratch, you need to understand your own process well enough to describe it clearly, and then be willing to iterate. If you have questions or you want to talk through how to do something similar for your workflow, or you just want to geek out about Google Ads and AI tools, reach out. I’m always happy to talk about this stuff. Thanks for listening and see you next time on the Digital Marketing Mentor. Thank

Danny Gavin (Host): 16:03

 you for listening to the Digital Marketing Mentor Podcast. Be sure to check us out online at thedmmentor.com and at the DM Mentor on Instagram. And don’t forget to subscribe on Apple Podcasts, Spotify, or wherever you listen to your podcasts for more Marketing Mentor magic. See you next time.