Score and alert retail footfall opportunities with BestTime, Open-Meteo and Gemini

Quick overview

Youtube Video: https://youtu.be/peWJ-rxxt0w

This workflow monitors footfall opportunity for multiple retail locations by combining BestTime busyness, Open-Meteo weather, Ticketmaster events, and OpenStreetMap business density, then uses Google Gemini to generate recommendations, logs results to Google Sheets, and sends Slack alerts plus daily and weekly summaries.

How it works

1. Runs every 30 minutes and reads a list of locations from a Google Sheets “Locations” tab.
2. Filters for enabled locations that are within each site’s operating hours based on its timezone and open/close times.
3. For each eligible location, fetches current weather and short-term rain probability from Open-Meteo, live busyness from BestTime, upcoming events from Ticketmaster, and nearby business counts via the OpenStreetMap Overpass API.
4. Retrieves a weekday/hour baseline score from Redis and sends the full snapshot to Google Gemini to produce a structured JSON assessment with a footfall score, drivers, risks, actions, and confidence.
5. Appends the reading and recommendations to a Google Sheets “Footfall_History” tab and updates the Redis baseline using an exponential moving average.
6. If the opportunity level is not “medium” and confidence is at least 0.5, checks a Redis cooldown key and posts the appropriate Slack alert (urgent surge, opportunity, or quiet-slot promo idea), then sets a 90-minute cooldown.
7. At 8 AM daily, reads the last 24 hours from Google Sheets, summarizes performance per location, has Google Gemini write a digest, and posts it to Slack.
8. At 6 PM every Sunday, pulls locations with client email addresses, fetches 7-day weather and BestTime weekly forecasts, has Google Gemini write a weekly outlook, and emails it via Gmail.

Setup

1. Provide a Google Service Account credential with access to the Google Sheets document and ensure it contains “Locations” and “Footfall_History” tabs with the referenced columns (lat, lng, timezone, open_hour, close_hour, venue_id, location_id, enabled, alert_chat_id, and client_email for weekly emails).
2. Add API credentials for BestTime (query auth) and Ticketmaster (query auth), and confirm each location row includes the required BestTime identifiers (venue_id for live and venue_name/venue_address for weekly).
3. Add a Redis credential and ensure your Redis instance is reachable to store baseline keys and the per-location alert cooldown keys.
4. Add a Google Gemini (PaLM) API credential for the LLM steps and keep the structured output schema as-is if you rely on the downstream logging and alert formatting.
5. Add Slack credentials and update the target channel IDs/names in the Slack alert and digest steps.
6. Add Gmail OAuth2 credentials and confirm the sender mailbox is authorized to send the weekly outlook emails to the client_email addresses.