Monthly SEO opportunity brief in Google Docs and Slack

Build an agent workflow that runs on the first of every month at 8am in my local timezone and publishes a monthly SEO opportunity brief by cross-referencing Google Analytics 4 with Google Search Console, then writes the brief to a Google Doc and posts the summary in Slack.

Trigger: a cron schedule firing at 08:00 on the 1st of every month.

Step 1. Use Google Analytics Run Report (GA4) against my GA4 property to pull organic landing page performance for two date ranges: the last 28 days, and the 28 days before that. Dimensions: landingPage (or landingPagePlusQueryString), sessionDefaultChannelGroup. Metrics: sessions, engagementRate, conversions, averageEngagementTime. Filter to sessionDefaultChannelGroup = 'Organic Search'. Return the rows for both windows so we can compute month over month deltas per page.

Step 2. Use Google Search Console Search Analytics Query for my verified property over the same last 28 day window. Run it twice: once grouped by page (dimensions: ['page']) and once grouped by page and query (dimensions: ['page', 'query']) so we get clicks, impressions, CTR, and average position per page, plus the top queries per page. Use searchType 'web' and filter to country if I have a primary market. Page through results with startRow and rowLimit until rows are exhausted.

Step 3. Join GA4 rows to GSC rows on landing page URL (normalize trailing slashes and protocol). Then synthesize three prioritized buckets:

Bucket A, low CTR rewrites: pages with high impressions and a CTR materially below the site median for their position range. For each, draft a new meta title and meta description that leads with the highest-intent query already driving impressions, respects Google's length guidance (around 60 chars for the title, around 155 chars for the description), and stays on brand. Include the current title and description side by side with the suggested rewrite.

Bucket B, content refresh candidates: pages whose organic sessions declined more than 20% versus the prior 28 days. For each, list the specific queries that lost the most position or impressions, and a one-line refresh angle (for example: outdated stats, missing FAQ, thin section, new competitor on SERP).

Bucket C, striking distance: pages ranking in average position 5 to 15 on queries with meaningful impression volume. For each page, list two or three topical gaps to close based on the queries it is almost ranking for, and the single highest-leverage internal link to add.

Rank opportunities within each bucket by potential session lift, not raw impressions, so small high-intent pages do not get drowned out by big-but-saturated ones. A reasonable proxy is impressions multiplied by the gap between current CTR and a realistic CTR at the target position.

Step 4. Use Google Docs Create Document to make a new doc titled 'SEO opportunity brief, <Month> <Year>'. Then use Google Docs Batch Update Document to write the body: a one-paragraph executive summary up top, then three bucket sections, each with a table of the top 10 to 15 pages and a 'next action' column per page. End with a short 'how this was generated' footer noting the GA4 and GSC date ranges.

Step 5. Use Slack Send a Message to post a short summary in my marketing channel (let me pick the channel at setup). The message should include: the doc link, the month covered, total counts in each bucket, and the single highest-leverage opportunity called out by name (page URL plus expected lift).

If GA4 or GSC returns no rows (for example the property is brand new or unverified), still create the doc with a clear note explaining which dataset was empty, and post a Slack message saying the brief could not be generated this month and why. Do not silently skip.