A brand was judging its Google Ads account on clicks. This project rebuilt the reporting around revenue — showing which search terms, devices and locations actually make money.
PAID SEARCH // GOOGLE ADS API → BIGQUERY → LOOKER
| TERM | CLICKS | CONV | CPA | ACTION |
|---|---|---|---|---|
| buy {product} near me | 2,410 | 214 | $11.2 | LIVE |
| {brand} pricing | 1,820 | 168 | $8.90 | LIVE |
| {category} reviews | 1,240 | 61 | $28.4 | LIVE |
| free {category} | 980 | 4 | $96.0 | EXCL |
CPA — MOBILE: $14.90
The four tiles show the key account numbers at a glance.
The bars compare cost and conversions for each campaign type, side by side.
The table shows which search terms make money and which waste it.
Performance Max spent money without showing where it went. Brand campaigns looked great because people were already searching for the brand. Everything was judged on clicks, so everything looked fine.
We pulled the account data into BigQuery down to individual search terms, and rebuilt the reports around revenue. Now wasted spend is visible and easy to remove.
Google Ads data is pulled into BigQuery every day, down to individual search terms. SQL compares cost with revenue for each campaign type, and Looker Studio shows the results with alerts on top.
Campaigns, ad groups, keywords, search terms, devices, geos.
Daily pulls with change-history capture.
Term-level value models · waste detection · QS tracking.
Six pages: account, types, terms, devices, geos, alerts.
Wasted-spend flags and negative-keyword queue to Slack.
Two more pages: the search term table and the alerts page. Built in HTML with sample data.
PAID SEARCH // TERMS JUDGED BY VALUE
| TERM | CLICKS | CONV | CPA | QS | ACTION |
|---|---|---|---|---|---|
| buy {product} near me | 2,410 | 214 | $11.2 | 8 | ● LIVE |
| {brand} pricing | 1,820 | 168 | $8.90 | 9 | ● LIVE |
| {category} reviews | 1,240 | 61 | $28.4 | 7 | ● LIVE |
| {category} template | 1,080 | 22 | $41.6 | 5 | ○ TRIM |
| free {category} | 980 | 4 | $96.0 | 4 | ○ EXCL |
AUTOMATION // WASTE + QS RULES → SLACK
$96 CPA across 980 clicks. Queued for exclusion with three sibling terms.
Conversion value +11% WoW at steady cost after the asset-group refresh.
Google Ads API landed 07:00. 22,815 rows. 0 errors.
Brand and near-me terms did the earning; the template and free clusters did the burning — $612 flagged, exclusions queued.
PMax responded to the new assets: value up 11% at flat cost. Watch devices — mobile CPA still 22% under desktop.
Campaign types are compared on cost and results, so Performance Max is no longer a black box.
Money-wasting search terms show up weekly with a simple exclusion list.
Device and location decisions are based on their own cost-per-sale numbers.
NOTE: CLIENT DETAILS GENERALISED · ALL NUMBERS ARE SAMPLE DATA · DASHBOARDS ARE HTML RECREATIONS
If your Google Ads reports are all clicks and no clarity, this same setup can be built for your account.