Google Ads report template

Five widgets, plus the paid-and-organic overlap most templates never show.

Most Google Ads report templates show you the account: spend, clicks, conversions, cost per acquisition, best and worst campaigns. That is the right core, and the five widgets below cover it against your connected account.

The sixth is the one templates usually skip, because it needs a second source. If you also connect Search Console, you can put paid and organic performance for the same site next to each other — and find the queries where you are paying for clicks you already earn, or paying nothing for demand you never see. That comparison is the argument for having both sources in one queryable place rather than two dashboards.

Connect the source

npm i -g @tablebi/cli
tablebi login
tablebi install

tablebi connect google_ads
tablebi connect gsc --site sc-domain:example.com

Source for this template: Google Ads (plus Search Console for the last widget). One connected source and one live dashboard fit inside the free tier.

The widgets

1. Account health

The month in one row.

WITH w AS (SELECT MAX(date) AS anchor FROM google_ads_raw)
SELECT SUM(cost) AS spend,
       SUM(clicks) AS clicks,
       SUM(conversions) AS conversions,
       cpc(SUM(cost), SUM(clicks))       AS cpc,
       cpa(SUM(cost), SUM(conversions))  AS cpa,
       roas(SUM(revenue), SUM(cost))     AS roas
FROM google_ads_raw, w
WHERE date > w.anchor - 30

2. Campaign table

Every campaign on the metrics that decide budget.

WITH w AS (SELECT MAX(date) AS anchor FROM google_ads_raw)
SELECT campaign,
       SUM(cost) AS spend,
       ctr(SUM(clicks), SUM(impressions)) AS ctr,
       cpc(SUM(cost), SUM(clicks))        AS cpc,
       cpa(SUM(cost), SUM(conversions))   AS cpa,
       roas(SUM(revenue), SUM(cost))      AS roas
FROM google_ads_raw, w
WHERE date > w.anchor - 30
GROUP BY campaign ORDER BY spend DESC

3. Spend with no conversions

Money that produced nothing this period.

WITH w AS (SELECT MAX(date) AS anchor FROM google_ads_raw)
SELECT campaign, SUM(cost) AS wasted_spend, SUM(clicks) AS clicks
FROM google_ads_raw, w
WHERE date > w.anchor - 30
GROUP BY campaign
HAVING SUM(conversions) = 0 AND SUM(cost) > 50
ORDER BY wasted_spend DESC

4. Daily pacing

Whether the budget is landing evenly or burning early.

WITH w AS (SELECT MAX(date) AS anchor FROM google_ads_raw)
SELECT date, SUM(cost) AS spend, SUM(conversions) AS conversions
FROM google_ads_raw, w
WHERE date > w.anchor - 30
GROUP BY date ORDER BY date

5. Efficiency trend

Is cost per acquisition drifting?

WITH w AS (SELECT MAX(date) AS anchor FROM google_ads_raw)
SELECT date_trunc('week', date) AS week,
       cpa(SUM(cost), SUM(conversions)) AS cpa,
       cpc(SUM(cost), SUM(clicks))      AS cpc
FROM google_ads_raw, w
WHERE date > w.anchor - 90
GROUP BY 1 ORDER BY 1

6. Paid against organic

Where you buy clicks the site already earns — and where demand exists that ads never touch.

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com'),
     organic AS (
       SELECT SUM(clicks) AS clicks
       FROM search_console_raw, w
       WHERE site = 'example.com' AND date > w.anchor - 30
     )
SELECT g.campaign,
       SUM(g.cost)   AS paid_spend,
       SUM(g.clicks) AS paid_clicks,
       MAX(organic.clicks) AS site_organic_clicks
FROM google_ads_raw g, w, organic
WHERE g.date > w.anchor - 30
GROUP BY g.campaign
ORDER BY paid_spend DESC LIMIT 15

The brand-term question this makes answerable

Every account eventually argues about brand-term bidding: you are paying for clicks on your own name that organic search would have delivered free. The counter-argument is that a competitor bids on your name, so stopping cedes the position.

Both sides are usually arguing without the data, because paid and organic live in different tools. With Search Console and Google Ads in the same store, you can put the branded campaign spend next to the same site organic clicks over the same window and at least argue about a number. It is not a clean incrementality test — only a holdout is that — but it is better than opinion.

Raw against unified for a single platform

The queries here run against google_ads_raw rather than the unified metrics view, deliberately. For a single-platform report the raw table keeps everything the connector returned, and there is no cross-platform reconciliation to worry about.

Switch to metrics when the report has to include Meta or another source, so the definitions line up. That is the two-altitude idea in one sentence: unified when you are comparing across platforms, raw when you are inside one.

Freshness worth stating on the page

Google Ads syncs on a three-hour cooldown, so an intraday number is close to current. Search Console runs two to three days behind and anonymises rare queries. When a report mixes the two, the older source sets the honest date for the whole thing — every TableBI answer carries per-source freshness so that date is not something you have to remember.

Pin the set as one live URL

tablebi pin --title "Google Ads report template"
→ https://you.tablebi.com/d/dsh_…  # public, read-only, refreshes itself

The URL needs no login from whoever you send it to, and it re-runs its own queries rather than freezing a moment. Here is one running on real data. It carries no branding of yours — there is no white-label or custom-domain option today, which is the trade against a reporting platform.

Questions people ask

Other templates

Want the arithmetic before the wiring? The free marketing calculators run these same formulas in the browser.