Daily SEO keyword rank tracker report by email

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.

Additional information

What does this prompt do?

Reads your tracked keyword list from a Google Sheet every weekday at 7am and looks up where your domain ranks in Google for each one.
Logs the daily position for every keyword to a history tab so you keep a record of where you started and where you ended up.
Calls out the biggest movers, climbs and drops of three or more positions, plus any keyword where you fell out of the top 20 entirely.
Spots new competitor URLs that broke into the top 5 since yesterday so you know who is suddenly taking your space.
Writes a short narrative report covering top gains, top losses, and notable new competitors, and emails it to your marketing distribution list.

What do I need to use this?

A Google account with a sheet that holds the keywords you want to track and a second tab for the daily history log.
A ScrapingBee account, used to pull the top 10 Google results for each keyword.
A Gmail account that can send the daily report to your marketing distribution list.

How can I customize it?

Change the schedule, send it daily, three times a week, or only on Mondays, instead of every weekday at 7am.
Adjust the movement threshold, for example flag changes of five or more positions instead of three.
Swap the recipient list or send the report to a Slack channel instead of email.

Frequently asked questions

Where does the list of keywords come from?

From a Google Sheet you control. You add or remove keywords whenever you want, and the next morning's run picks up the new list automatically.

Which search engine does this check?

Google. ScrapingBee pulls the top 10 organic results for each keyword as Google would show them, so the rankings match what a real searcher sees.

Will it work if my domain is not in the top 10?

Yes. The report looks at the top 10 organic results for movers and competitor changes, and separately calls out any keyword where your domain has slipped out of the top 20 entirely.

Can I track more than one domain?

Yes. You can add a column to the sheet for each domain you care about, and ask Geni to write the report from the perspective of all of them or only one.

How do I change who receives the email?

Tell Geni the new recipient list, for example a distribution alias or a set of individual addresses, and the workflow will send future reports there.