Weekly Google Search Console digest to Slack every Monday

Every Monday at 8am, run my weekly organic search digest using Google Search Console, Google Sheets, and Slack.

Trigger: cron, every Monday at 8:00am in my local timezone.

Inputs the workflow should accept:

- siteUrl (optional): the exact verified Search Console property to report on (URL-prefix like https://www.example.com/ or domain property like sc-domain:example.com). If not provided, default to the first verified site returned by the Google Search Console "List Sites" operation.

- spreadsheetId: the Google Sheet that holds the rolling history.

- historyRange: the A1 range for the history tab, default "History!A:E".

- slackChannel: the Slack channel ID or name where the digest should be posted (e.g. #marketing).

Date ranges: Search Console data lags 2 to 3 days, so always end the reporting windows 3 days before the run date. The current week is the 7 days ending (today - 3 days). The prior week is the 7 days immediately before that. Format dates as YYYY-MM-DD.

Steps:

1. If no siteUrl was supplied, call Google Search Console "List Sites" and pick the first entry where permissionLevel grants access (siteFullUser, siteOwner, or siteRestrictedUser). Use that siteUrl verbatim for the rest of the run.

2. Call Google Search Console "Search Analytics Query" four times against the chosen siteUrl, all with type="web" and dataState="all":

a. Current week, dimensions=["query"], rowLimit=1000, sorted by clicks descending.

b. Prior week, dimensions=["query"], rowLimit=1000.

c. Current week, dimensions=["page"], rowLimit=1000, sorted by clicks descending.

d. Current week with no dimensions, to get headline totals (clicks, impressions, ctr, position). Also run the same headline call for the prior week.

3. Compute week over week deltas: absolute and percent change in clicks, impressions, CTR (percentage points), and average position (lower is better, so invert the sign for display).

4. Build the query gainers/losers by joining current and prior week query rows on the query string, computing clicks delta, then taking the top 5 gainers (largest positive delta) and top 5 losers (largest negative delta). Require at least 5 clicks in either week to filter out noise.

5. Take the top 5 landing pages by clicks for the current week directly from the page-dimension query.

6. Append one row to the Google Sheet history tab using Google Sheets "Append Values" with valueInputOption="USER_ENTERED" and the configured historyRange. Columns in order: run_date (today, ISO), clicks, impressions, ctr (as decimal), average_position.

7. Post a formatted digest to Slack using "Send a Message" to slackChannel. Use Slack mrkdwn formatting. The message should include: a header like "Weekly Search Console digest for {siteUrl}" and the reporting window dates; headline totals with week over week changes (clicks, impressions, CTR, position); a top 5 gainer queries section showing query, current clicks, and delta; a top 5 loser queries section in the same shape; a top 5 landing pages section with page URL and current clicks; and a link to the history Google Sheet at the bottom.

Error handling: if Search Console returns zero rows (often a sign the siteUrl is malformed or has no traffic in the window), post a short Slack message saying the digest had no data for {siteUrl} for the window and skip the sheet append. If the Slack post fails, log the error but do not retry the sheet append.