SEO Report Template for Google Sheets and Docs, with AI Update Prompts

Define the report before collecting data

Choose the website, Search Console property, GA4 property, reporting periods and target country.

Use the last completed month for a monthly report. Record the exact dates, since calendar months have different lengths. Where those differences affect the interpretation, compare daily averages or include an equal-length comparison.

Define the business outcomes you will report. For an online shop, that can include purchases and revenue. For a service business, it can include qualified enquiries, provided your measurement distinguishes them from other recorded events.

Keep each source's time zone, filters and data status in the report. Search Console and GA4 can differ because of measurement methods, privacy, processing and reporting dates. Google's explanation of these differences.

Create six tabs in Google Sheets

Create a workbook with these tabs. Retain the same names each month so your update prompts can identify their destinations.

Select the workbook in SourcetoMCP after creating it. Connect the document you will use for the written report as a separate file.

Follow the setup guides for Google Sheets and Google Docs.

Build a scorecard with comparable figures

Use Search Console for Google search clicks and impressions. Use GA4 for recorded website activity from your chosen organic segment.

Keep Ahrefs estimates in the Research tab. An estimate of competitor traffic should not appear as a measured website result.

Here is a hypothetical scorecard:

The numbers illustrate the template. Replace them with your own returned results.

GA4's Organic Search channel includes traffic from multiple search engines. Label that segment clearly, and add a separate Google organic view if you need one. Google's channel definitions.

A key-event count can include repeated events. Define the events included before interpreting it as leads, purchases or customers.

Add formulas without hiding missing data

For the Scorecard columns above, put the current value in column C and the previous value in column D.

For valid numeric inputs, calculate the absolute change in E2:

=C2-D2

Calculate the percentage change in F2:

=IF(OR(NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2)),D2=0),"",(C2-D2)/D2)

Format column F as a percentage. This formula leaves the percentage blank when the previous value is zero or either input is unavailable.

Use the Status column to explain the blank, such as "Previous period was zero" or "Current result unavailable". A failed request should not become a zero in your report.

Report rate changes separately. In the example, Search Console CTR rises from 2.5% to 3%, a gain of 0.5 percentage points. GA4 engagement rate rises from 65% to 70%, a gain of five percentage points.

Calculate CTR from clicks divided by impressions at a consistent reporting scope. Use GA4's reported rates where available. Avoid averaging page-level rates to create an account-level rate.

Keep page and query results at their actual scope

Use the Pages tab to identify gains, losses and business outcomes associated with important landing pages.

Retain the full Search Console URL. GA4 landing pages can use a different representation, so compare the host and path before matching them. Record how you handled redirects, query parameters and canonical URLs.

A page receiving search clicks does not establish which individual query produced a purchase. Page-level results support a page-level comparison.

Use the Queries tab for search questions and content opportunities. Record its country and period alongside clicks, impressions, CTR and average position.

Search Console query reports omit some data, and its API does not guarantee every row. A selected query sample should remain labelled as a sample rather than presented as the property total. Google's API guidance.

Save Ahrefs research with its snapshot date

Use the Research tab for verified keyword candidates and competitor examples.

Include the target domain, country, snapshot date and returned ranking page. Label search volume and traffic figures as estimates.

Ask your assistant to check the Ahrefs API allowance before retrieving a large list. SourcetoMCP's organic-keyword operation uses roughly 34 units per returned row, so a 25-row request uses roughly 850 units when it returns the full sample.

Keep previous snapshots if you want to compare rankings or keyword coverage later. A keyword absent from a limited earlier sample should not automatically become a "new ranking" in the next report.

Copy this structure into Google Docs

Create a master document containing the following template. Use unique placeholders so your assistant can identify exactly which text to replace.

Monthly SEO Report
{{BRAND}} | {{REPORT_MONTH}}

Reporting scope
Current period: {{CURRENT_PERIOD}}
Comparison period: {{PREVIOUS_PERIOD}}
Market and source filters: {{REPORTING_SCOPE}}
Data retrieved: {{RETRIEVAL_DATE}}
Supporting workbook: {{WORKBOOK_LINK}}

Performance summary
{{PERFORMANCE_SUMMARY}}

Google Search results
{{SEARCH_RESULTS}}

Website activity from organic search
{{WEBSITE_RESULTS}}

Pages and queries to investigate
{{PAGE_AND_QUERY_FINDINGS}}

Keyword and competitor research
{{KEYWORD_RESEARCH}}

Work completed this month
{{COMPLETED_WORK}}

Next actions
{{NEXT_ACTIONS}}

Data limits and unresolved questions
{{DATA_LIMITATIONS}}

Duplicate the master document for each reporting month, then select the new file in SourcetoMCP.

This preserves the original placeholders for future reports. Once a placeholder has been replaced in a monthly copy, it is no longer available for the next replacement.

Record completed work from your team's actual change log. Traffic and ranking reports cannot establish which edits your team made.

Prompt 1: prepare the figures and draft commentary

Run the analysis before updating either file.

Prepare an SEO report for [brand] covering [current dates] against [previous dates]. Use Search Console web search for [property and country]. Retrieve daily results for the scorecard and separate page and query reports. Retrieve GA4 results by session channel, source and medium, then analyse the returned [chosen organic segment]. Check Ahrefs allowance before retrieving up to [number] keyword rows. Show the proposed scorecard and document text first. Include sources, filters, snapshot dates and incomplete-data warnings. Make no file changes.

Ask the assistant to explain the largest absolute changes, the important percentage changes and the findings affecting your business goal.

For example, an increase of 300 sessions and 12 recorded key events deserves a different explanation from increased sessions with declining key events. The report should show both observations without claiming that a particular SEO edit caused them.

Prompt 2: update the agreed Google Sheets ranges

After reviewing the figures, specify the workbook and exact ranges to update.

Update [spreadsheet ID] using the approved report figures. Read the destination ranges first. Write current and previous values to [Scorecard!C2:D6], keeping the formulas in columns E and F. Update [specified Pages, Queries and Research ranges] with the reviewed rows. Write text and source values as literal values. Keep headers and unrelated cells unchanged. Show how any leftover rows from a larger previous report will be handled. Report the ranges and cell counts updated, then read them back to verify.

Use the ranges that match your workbook. The example scorecard range assumes the five metric rows shown earlier.

Keep formula updates separate from imported text. Search queries and other source text should remain literal cell values. Enter formulas deliberately in their designated columns.

If you retain historical results, use a consistent key containing the reporting period, source and page or query. Check for that key before appending so rerunning a report does not duplicate it.

Prompt 3: fill the monthly Google Docs copy

Specify the document and, where relevant, its tab. Review the replacement text before applying it.

Read [document ID and tab]. Find the template placeholders and show their proposed replacements using the approved figures and commentary. Include sources and reporting limits. After I approve, replace only those placeholders in the specified tab. Report how many occurrences changed for each replacement. Flag any placeholder with zero replacements, and read back the completed sections.

SourcetoMCP supports replacing text in connected Google Docs files. A replacement can affect every matching occurrence within its scope, so use distinctive placeholders and specify the destination tab.

A response reporting zero replacements means the matching text was not found. Check the document before treating the update as complete.

Make the next actions specific

Every recommended action should name the page or query, the evidence, the owner and the result you intend to measure.

"Improve SEO" cannot be assigned or reviewed. "Inspect the canonical for this URL, confirm Google's selected version, then recheck its search performance" identifies a concrete task.

Use the Actions tab to distinguish an investigation from an approved change. Record the date of completed changes so future reports can refer to them accurately.

Repeat the report with the same definitions

For the next month, keep the properties, filters, metric definitions and reporting segments consistent. Record any change in measurement or scope beside the affected figures.

AI updates run when you request them. Recurring execution requires a separate scheduler or agent workflow. For more analysis prompts, use the guide to ChatGPT for SEO.

Connect your Search Console, GA4, Google Sheets and Google Docs files in SourcetoMCP, then prepare one report for review before updating the connected files.