Weekly Google rankings and reviews check for every location

Every Monday at 7am, tell me how each of our physical locations is performing on Google.

Start by reading the location roster from Google Sheets using Get Values. The roster lives on a tab called Locations, and each row has the business name, the city, and the main search term customers use to find that kind of business. Skip the header row and skip any row missing one of those three fields.

For each row, first use the SerpApi Location Lookup operation to resolve the city into a canonical location name, so that later searches run from the right place. Then run a SerpApi Google Maps Search for the business name within that location and capture the current star rating, the total review count, the category Google lists it under, and the place identifier. Store the place identifier in the history so that in later weeks I am comparing the same listing rather than a similarly named business.

Next, run a SerpApi Google Search for that row's target search term using the canonical location as the search location. Look at the local pack in the results and record whether we appear in it, what position we hold, and which businesses are listed above us. If we are absent from the local pack, record the businesses that do occupy it.

Then read the history tab with Get Values and find the most recent earlier entry for each location. Compare this week against it: the change in star rating, the change in review count, and the change in local pack position, including dropping out of the pack entirely. If there is no earlier entry for a location, treat this run as its baseline and say so rather than reporting a change.

Append this week's figures to the history tab using Append Values, one row per location, with the run date, business name, city, target search term, star rating, review count, category, place identifier, local pack position or a clear marker that we were not in the pack, and the names of the businesses that ranked above us.

Finally, post a digest to Slack with the Slack Bot Send a Message operation. Lead with the locations that actually need attention and put them at the top: any location whose rating dropped, any whose review count has stalled with no new reviews since the previous run, and any that fell out of the local pack or lost position. For each of those, explain what changed with both the old and new numbers, and where a specific competitor now occupies the position we used to hold, name that business. Where the cause is ambiguous, say so instead of guessing. Keep every healthy location to a single summary line so the digest stays readable, and open the message with a one sentence overview of how many locations are healthy versus how many need attention.

If a city cannot be resolved, or the Maps search returns no confident match for a business name, do not guess. List those rows in a short section at the end of the digest so someone can fix the roster.