Google Sheets Dashboard Template for Marketing: Track 6 KPIs with AI

Choose the six marketing KPIs

Choose a conversion definition before importing data. For ecommerce, that might be purchases. For lead generation, it might be the lead actions your account records.

An account-level conversion total can contain several actions. Label that total accurately if it includes a mixture of purchases, enquiries or other events.

ROAS measures recorded conversion value relative to advertising cost. To assess profit, you also need product costs, margins and other expenses.

Create five tabs

Use these names so the formulas below match your workbook.

Connect the workbook using the setup guide for Google Sheets in Claude or Google Sheets in ChatGPT.

Then connect the advertising account you want to report on. SourcetoMCP's Google Sheets connector supports reading and updating ranges, adding tabs, formatting cells and creating supported charts.

Set the reporting dates and account

Enter the following layout in Settings, starting at A1.

Enter B4:B7 as real spreadsheet dates. Format the account ID as plain text.

Choose periods with the same number of complete days. A Monday-to-Sunday comparison keeps the weekday mix consistent.

Record the account's reporting time zone in Notes. Also record the extraction time, conversion definition and whether recent conversions remain provisional.

Add the raw data structure

Paste this tab-separated header into Raw!A1:

Date	Account ID	Campaign ID	Campaign name	Currency	Spend	Impressions	Clicks	Conversions	Conversion value

Each row represents one campaign on one date.

Keep account and campaign IDs as text. Store dates as real spreadsheet dates and the five measurement columns as numbers.

Use currency units consistently. A spend value of 125.50 should mean 125.50 in the stated currency. Mixing that with a value expressed in micros would corrupt the dashboard.

Keep campaign totals, keyword reports and device breakdowns in separate tables. Combining several report levels in the same raw table would count some spending more than once.

Request data for campaigns that contributed to the reporting periods, including campaigns that are currently paused or removed.

Calculate the period totals

Paste this header into Summary!A1:

Period	Start	End	Spend	Impressions	Clicks	Conversions	Conversion value	CTR	CPA	ROAS

Set up the first two data rows:

Enter this formula in D2:

=SUMIFS(Raw!F$2:F$10001,Raw!$A$2:$A$10001,">="&$B2,Raw!$A$2:$A$10001,"<="&$C2,Raw!$B$2:$B$10001,Settings!$B$2,Raw!$E$2:$E$10001,Settings!$B$3)

Copy D2 across to H2, then copy D2:H2 down to row 3.

The sum range moves from spend through impressions, clicks, conversions and conversion value. The date, account and currency conditions stay fixed.

Google Sheets' SUMIFS function adds values that meet multiple conditions. Here, it selects the specified account, currency and date range. Google's SUMIFS documentation.

These formulas cover 10,000 data rows. Expand every related range together if your table grows beyond row 10001.

Next, enter these formulas:

Copy I2:K2 down to row 3.

Format CTR as a percentage, CPA as currency and ROAS as a number such as 4.00x.

The formulas leave a ratio blank when its denominator is zero. Missing source data needs a separate check before you interpret the totals.

Calculate ratios from the summed components. For example, total CPA is total spending divided by total conversions. Averaging campaign CPAs would give each campaign equal weight regardless of its spending or conversion volume.

Build the dashboard

Create four columns in Dashboard:

KPI	Current	Previous	Change

Use these formulas for the six rows:

For D2, enter:

=IF(AND(ISNUMBER(B2),ISNUMBER(C2),C2<>0),B2/C2-1,"")

Copy that formula to D3 and D5:D7. Format those change cells as percentages.

For CTR in D4, use:

=IF(AND(ISNUMBER(B4),ISNUMBER(C4)),(B4-C4)*100,"")

Display D4 as a number labelled "percentage points". A change from 3.5% to 4.0% is an increase of 0.5 percentage points.

Use conditional formatting according to the metric. A lower CPA can be favourable. Higher spending needs an assessment of the outcomes that accompanied it.

Check the dashboard with a worked example

These hypothetical figures show how the calculations behave.

The current period has 60,000 impressions and $9,600 in conversion value. The previous period has 50,000 impressions and $7,000 in conversion value.

The current CPA calculation is $2,400 divided by 48 conversions, or $50. Current ROAS is $9,600 divided by $2,400, or 4.00x.

The example shows spending increasing alongside more conversions, a lower CPA and a higher ROAS. Investigate conversion quality and margins before treating the result as a business improvement.

Use AI to prepare and update the workbook

Start by asking your assistant to inspect the workbook and propose the setup.

Inspect connected spreadsheet [name and ID]. Prepare a plan for a paid marketing dashboard with Settings, Raw, Summary, Dashboard and Notes tabs. Use the six KPIs and formulas in this template. Show the proposed ranges and any existing content they would replace. Check the connected Google Ads account's currency and reporting time zone. Make no spreadsheet changes yet.

After reviewing the plan, request the agreed setup. Your assistant can add the required tabs, write values and formulas, format cells and create supported charts.

For updates, use a prompt that names the destination and reporting scope:

Refresh the approved dashboard in [spreadsheet]. Read the account, currency and periods from Settings. Retrieve campaign-by-date Google Ads data for both periods using the five raw measures. Include campaigns that contributed during those dates regardless of current status. Check report warnings and row limits. Before writing, show the proposed replacement ranges, row count and totals. Preserve the formulas and unrelated cells.

Refreshing the same period by appending all its rows again would create duplicates. Replace the agreed source range, or update existing rows using date, account ID and campaign ID as the row key.

Recent conversions can change earlier results. Re-fetch the comparison period when appropriate and record the refresh in Notes.

Before approving the write, compare raw-data totals with an account-level report for the same dates and measures. Investigate discrepancies caused by incomplete results, duplicated rows or inconsistent reporting definitions.

SourcetoMCP can return a stored result when an inline response contains only part of the retrieved data. A stored result still reflects the source request's limits, so a large account can require narrower date ranges or campaign-specific requests.

Add charts that answer a specific reporting question

A daily spending chart helps you find unusual spending days. A daily conversions chart helps you see whether those days also produced more recorded outcomes.

Create daily totals from the same raw data and account filters before charting them.

SourcetoMCP supports basic bar, line, area, column and scatter charts with one data series per chart. Use separate charts for spend and conversions when creating them through the connector.

Place the six KPI rows above the charts so readers can find the period results first.

Ask AI to explain the changes

Once the workbook totals reconcile with the source report, request the written summary.

Read the validated Dashboard and Summary tabs. Write a short explanation of the changes in spend, clicks, CTR, conversions, CPA and ROAS. Include the figures behind each statement. Express CTR changes in percentage points. Identify campaigns that contributed to the changes using Raw. Separate observations from possible causes, and propose three checks for the next review. Save the agreed summary in Notes.

For a statement such as "CPA improved because the new landing page converted better", the dashboard alone establishes the CPA change. Testing the explanation requires landing page performance, change dates and other relevant evidence.

Keep other marketing sources distinct

You can extend the workbook with GA4, Search Console or other connected advertising platforms.

Give each source its own raw table and definitions. Search Console clicks, GA4 sessions and advertising clicks measure different activities. Platform-reported conversions can also credit the same customer action across multiple advertising platforms.

A combined platform conversion figure should therefore be labelled as platform-reported conversions. Use a defined analytics or CRM measure when you need a business-wide total.

For a report focused on organic search, use the SEO report template for Google Sheets and Docs.

Refresh when you run the workflow

Connecting Google Sheets gives your assistant access to the selected workbook. Run the refresh prompt whenever you want updated data.

Begin with one account, validate the totals, and record the first refresh in Notes. Add further sources after you have agreed how their dates, currencies and conversion definitions will be reported. Use the weekly Google Ads optimisation workflow to decide which campaign changes to investigate from the dashboard.