Auto-generate retail site selection briefs from your Airtable pipeline

Build me an agent workflow that automatically writes a one-page site selection brief every time a new candidate property is added to my Airtable site pipeline.

Trigger: poll Airtable for a new record in my 'Site Pipeline' table. Each row carries a property address, a latitude, and a longitude. Skip any row that already has a value in the brief link field so we never produce duplicate documents.

For each qualifying new row, the agent should do the following:

1) Call Sites USA Request Trade Area Boundaries to compute 1, 3, and 5 minute drive-time polygons around the coordinate. Keep the WKT for each ring.

2) Call Sites USA Get Demographics by WKT for each ring and pull total population, median household income, age mix (for example shares of 18 to 34, 35 to 54, 55 plus), and daytime population. If a ring returns no data, flag that ring in the report as a data gap rather than failing the run.

3) Call Sites USA Get Merchant Counts by WKT for each ring to count competitors in the relevant retail category (the category should be configurable, but assume the user passes a category id or name).

4) Call Sites USA Get Store Visits by Shopping Center for the nearest shopping center to the candidate coordinate, and pull recent monthly visit counts so we can describe foot traffic context.

5) Create a Google Doc with Google Docs Create Document, titled with the property address, then use Batch Update Document to lay out a one-page brief with these sections: a short trade area summary, a demographic comparison table across the three rings, competitor density per ring, a foot traffic context paragraph for the nearest shopping center, and a recommended 'fit score' (0 to 100) with two or three sentences of reasoning. Note any data gaps inline.

6) Call Airtable Update Record on the originating row to write the Google Doc URL into the brief link field and the numeric fit score into the fit score field.

Other behavior: Sites USA enforces 1 call per second, so pace the calls. Treat any individual Sites USA call that returns no data as a flag in the report, not a fatal error. Do not retry the whole workflow on partial data. If the run fails before the doc is created, leave the row untouched so the next poll picks it up again.

Inputs the user should configure when setting this up: the Airtable base id and 'Site Pipeline' table id, the field names for address, latitude, longitude, brief link, and fit score, the retail category (id or name) used for competitor counts, and the list of demographic variables to include (default to total population, median household income, daytime population, and the age cohort breakdown).

Additional information

What does this prompt do?

Watches your Airtable 'Site Pipeline' table and kicks off as soon as a new candidate property is added with an address and coordinates.
Pulls 1, 3, and 5 minute drive-time trade areas and layers in population, median household income, age mix, and daytime population for each ring.
Counts competitors in your retail category inside each ring and pulls foot traffic for the nearest shopping center so you see the demand picture, not just the demographics.
Drops a one-page Google Doc brief with a recommended fit score and writes the doc link plus the score back to the originating Airtable row.

What do I need to use this?

An Airtable base with a site pipeline table that includes each property's address and latitude/longitude.
A Sites USA account with API access (part of a REGIS Online subscription).
A Google account with Docs access so the brief can be created and shared with your team.

How can I customize it?

Swap the drive-time rings (for example 5, 10, 15 minutes instead of 1, 3, 5) to match how customers actually reach your stores.
Change the competitor category and the demographic variables in the brief so it reflects how your team underwrites a market.
Adjust the fit score logic and the brief layout, or have the workflow also drop a Slack note when a new brief is ready.

Frequently asked questions

What do my Airtable rows need to contain?

Each row in the site pipeline table needs at least a property address and the latitude and longitude of the site. The workflow uses the coordinates to draw the trade areas and pull demographic and competitor data.

Will it rerun on rows that already have a brief?

No. The workflow checks for an existing brief link on the row and skips anything that is already done, so you can safely re-add or edit rows without producing duplicate documents.

What happens if a drive time has no demographic data?

Instead of failing the run, the brief calls it out as a data gap in that section. That way you still get a useful document and a clear note about which ring needs follow-up.

Can I use this for a category other than retail?

Yes. The competitor count is filtered by category, so you can point it at restaurants, fitness, medical, or any other vertical Sites USA tracks and the brief will reflect that mix.

Where does the fit score come from?

The agent combines the demographic match, competitor density, and foot traffic context into a recommended fit score with written reasoning. You can adjust the weighting or replace it with your own scoring rubric.