Daily SEO keyword rank tracker with biggest movers report

Every weekday at 7am, run an SEO keyword rank tracker that reports the biggest movers for our domain and emails the report to our marketing distribution list.

Step 1. Read the tracked keyword list from Google Sheets using Get Values. The sheet has a tab called 'keywords' with one keyword per row in column A. Our domain is stored in cell B1 of the same tab.

Step 2. For each keyword, call the ScrapingBee Google Search operation to pull the top 10 organic results as structured JSON. Find the position of our domain in the results, or record 'not in top 20' if it does not appear. Also capture the URLs in positions 1 through 5 so we can detect new competitors.

Step 3. Append a new row to the 'history' tab in the same spreadsheet using Append Values, with today's date, the keyword, our domain's position, and the current top 5 URLs.

Step 4. Read yesterday's rows from the 'history' tab with Get Values and compare. For each keyword, identify whether our position moved up or down by 3 or more, and which URLs in the top 5 are new compared to yesterday.

Step 5. Write a short narrative report (a few paragraphs, not a raw table) covering top gains, top losses, notable new competitor URLs that broke into the top 5, and any keywords where our domain dropped out of the top 20 entirely. Keep the tone factual and concise.

Step 6. Send the report via Gmail (Send a Message) to our marketing distribution list with a subject line like 'SEO rank report for {date}, X gains and Y losses'.

If a keyword has no prior history row (for example, it was added yesterday), skip the comparison for that keyword and just note that it is newly tracked.