Weekly Google Ads Report to Google Sheets Automation

PromptCreate

Build a code workflow that runs on a cron trigger every Monday at 8am in the account's default timezone, and snapshots last week's Google Ads campaign performance into a Google Sheet so we have a living week over week log.

Prompt parameters the user should provide when launching the workflow:

1) googleAdsCustomerId, the 10 digit Google Ads customer id to query. 2) spreadsheetId, the target Google Sheet id. 3) sheetTab, defaulting to 'Weekly Performance', the tab to append rows to. 4) loginCustomerId, optional, for accounts accessed through a manager (MCC) account.

Step 1. Compute the prior ISO week window. Monday 00:00:00 through Sunday 23:59:59 of the week that just ended, in YYYY-MM-DD form. Store the Monday date as weekStart.

Step 2. Call Google Ads Search Stream (Streamed GAQL Query) against the customer id with this GAQL: SELECT campaign.id, campaign.name, campaign.status, metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value, segments.date FROM campaign WHERE segments.date BETWEEN '<weekStart>' AND '<weekEnd>'. Aggregate the streamed rows by campaign.id, summing impressions, clicks, cost_micros, conversions, and conversions_value across the segments.date rows. Keep the campaign name and status from the first row seen per campaign.

Step 3. For each aggregated campaign, compute: cost = cost_micros / 1,000,000, CTR = clicks / impressions (0 when impressions is 0), CPA = cost / conversions (null when conversions is 0), ROAS = conversions_value / cost (null when cost is 0). Round cost, CPA, and ROAS to 2 decimals and CTR to 4 decimals. Skip campaigns where impressions, clicks, and cost are all zero.

Step 4. Build one row per campaign in this exact column order: [weekStart, campaign_name, campaign_id, campaign_status, impressions, clicks, cost, conversions, conversions_value, ctr, cpa, roas]. Sort by cost descending so the biggest spenders are at the top.

Step 5. Call Google Sheets Append Values on spreadsheetId with range '<sheetTab>!A:L', valueInputOption RAW, insertDataOption INSERT_ROWS, and pass the rows as the values payload. Do not write a header row on every run; assume the sheet already has headers.

Return a small summary at the end with weekStart, weekEnd, number of campaigns written, total cost, and total conversions so the run log is easy to scan. No LLM reasoning is needed anywhere in this workflow, the schema is fixed and the pipeline is deterministic.

What does this prompt do?

Runs automatically every Monday morning and pulls last week's Google Ads campaign performance.
Grabs impressions, clicks, cost, conversions, and conversion value for every campaign, then computes CTR, CPA, and ROAS.
Appends one row per campaign to a Weekly Performance tab in the Google Sheet you choose, tagged with the week start date.
Gives you a historical, filterable log of campaign performance you can pivot, chart, or share with the team.

What do I need to use this?

A Google Ads account with permission to view campaign reports.
A Google account that can edit the target Google Sheet.
The Google Sheet you want to log into, with a tab named Weekly Performance.

How can I customize it?

Change the day or time the report runs, for example Monday 9am in your timezone.
Point it at a different spreadsheet or tab, or add extra columns like campaign type or bidding strategy.
Filter to only enabled campaigns, a specific account, or a set of campaign name patterns.

FAQs

How far back does each run look?

Each run pulls the full previous ISO week, Monday through Sunday, so Monday's run always covers a complete, closed week of data.

What columns get written to the sheet?

Week start date, campaign name, campaign ID, status, impressions, clicks, cost, conversions, conversion value, plus computed CTR, CPA, and ROAS. One row per campaign.

Will cost show up in real dollars?

Yes. Google Ads returns cost in micros, and the workflow divides by one million before writing so you see normal currency values in the sheet.

Can I use this for multiple Google Ads accounts?

Yes. Duplicate the workflow, connect it to the other Google Ads account, and point it at a different tab or spreadsheet so each account has its own log.

What if a campaign had no activity last week?

Campaigns with zero impressions are skipped by default. You can adjust that so paused or inactive campaigns still get a zero row if you want a fully consistent history.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.