Stop 6 of 9 · Weekly
Marketing spend against booked revenue
Three ad exports joined to booked jobs across both locations, one channel table ranked by booked revenue per dollar, and a budget move Sam makes by hand.
The number nobody can see from inside one platform
Local Services Ads reports leads. Google Ads reports conversions. The Business Profile reports calls. ServiceTitan reports booked jobs and invoiced revenue. Every one of those numbers is true and none of them answers Sam's question, which is what a marketing dollar came back as revenue this month, per channel, per location.
Joining them is an hour of spreadsheet work Sam has never once done on a Friday. It is the thing to hand over.
The four exports
| Export | What it contributes |
|---|---|
| Local Services Ads leads | Leads, charges, and the tracking number that took the call |
| Google Ads, by campaign | Spend, calls and form fills, split by location |
| Business Profile insights, per location | Calls and direction requests from the profile |
| ServiceTitan booked and invoiced jobs | The revenue side, with booked date and city |
Matching is the honest part. Where a tracking number exists, the match is clean. Where it does not, Claude matches by first name, date and city, and has to say so. A table that hides its match confidence is a table that ends in a confident wrong decision.
[upload the Local Services Ads leads export, the Google Ads campaign report, the Business Profile insights for both locations, and the ServiceTitan booked jobs export, all for the same 28 days] Join these into one table by channel and location. Columns: spend, leads or calls, booked jobs, booked revenue, booked revenue per dollar spent, and match confidence as high, medium or low with the reason. Match on tracking number first, then first name plus date plus city. Where a booked job cannot be attributed, put it in an unattributed row rather than spreading it across channels. Rank by booked revenue per dollar, then propose one budget move naming the dollars and the campaign they come from and a reversal trigger with an outcome date. Change nothing.
Reading the table
Three things pull Sam's eye, in this order. The unattributed row, because if it is a third of the revenue the ranking is a guess. The channel where booked revenue per dollar has moved two weeks running, because one week is weather. The location split, because a campaign that pays in the Valley and loses in Santa Clarita is two decisions, not one.
Booked revenue per dollar on the Local Services Ads channel fell from 5.80 to 3.40 over the last two weeks against the trailing four weeks. Using the same exports, tell me which of these it is: fewer leads, worse leads, the same leads booking less often, or the same bookings at a lower ticket. Show the rows that decide it, and name the job types and the cities behind the change. If the data cannot separate two of these, say which two and what export I need.
The budget move
Claude writes the proposal, Sam opens the platform. Five lines: the change, the evidence with the date range, the dollars moved and where from, what breaks if this is wrong, and the date the move gets read. The reversal trigger is the sentence people skip and the one that matters, because a budget move with no trigger quietly becomes permanent.
Turn the top ranked row and the bottom ranked row of this week's channel table into one budget proposal. Five lines: the change, the evidence including rows and date range, the dollars and which campaign they come from, what we lose if the ranking is wrong, and the date I read the result plus the number that would make me reverse it. One proposal only. Log it in the guardrail sheet as proposed, not applied.