How to build a database of local companies that updates itself
A table of the businesses in your city that adds new ones, flags closures and keeps phones current on a timetable, without anyone retyping a row.
Part of the guide Location intelligence automation
To build a database of local companies that stays current, start from a places search of your area, save every result with a stable id, and let a scheduled job add what is new and mark what changed. The first run fills the table; every run after that touches only the rows that moved. Nobody copies listings by hand, and a closed shop or a new phone number shows up in the table within a refresh rather than at the next annual clean-up.
This is written for anyone who keeps a list of businesses for a living: a chamber of commerce, a B2B sales team, a franchise's local marketing lead, a city's business support office. It covers how to design the table, where the rows come from, how updates work, a made-up example in Lyon, the usual traps, and the AI employee that runs it.
Design the table before you fill it
A self-updating table needs one thing above all: a key that stays the same from run to run. Names change and addresses get reformatted, so they make poor keys. Every place from xMap's places database carries its own id, poi_id, and that is the column to match on. Around it, keep the fields you will actually use:
- Who: name, category, the wider category group, brand and whether it belongs to a chain.
- Where: address, city, postal code, latitude and longitude.
- How to reach them: phone and website, when listed.
- How they are doing: rating, number of reviews, open or closed.
- Your own columns: first seen, last seen, the rep who owns the account, notes.
Decide where the table lives. Smaller teams keep it in Google Sheets. Teams that feed other systems keep it in their own PostgreSQL, BigQuery or Snowflake database, which the Save to my database block writes to, adding new rows or updating existing ones by the key you choose.
Where the rows come from
The first fill comes from Nearby places searching your city through an Area of interest. Pick the types your database is about (law firms and accountants, or restaurants and cafes, or every kind of place if you leave the types empty) and switch on the filters that suit you: only open places, only those with a phone or website, only independents. Each search returns the nearest 500 of a type within up to 25 km, so for a big city draw a few districts rather than one circle.
How the updates work
Three blocks do the maintenance, each answering one question on every run:
- Only new: which of these has this flow never seen? Those are the additions, each stamped with the day it was first found.
- What changed: which of the known ones closed, changed phone or website, moved address or had a rating move? Each comes with a plain line saying what changed and the value before.
- Place classifier: tidy categories into one clean list and merge duplicates, so the same bakery listed twice becomes one row.
Additions are inserted; changes update the row with the same id. A closure is a status change, not a delete, so your history keeps the business and the day it closed. That history is often the most useful part of the table after a year.
A made-up example: independent shops in Lyon
Invented, to show the method. A small business association in Lyon wants a member prospect list of every independent shop and food business in four central arrondissements, refreshed monthly. Its coordinator draws the four districts, picks twelve types from bakery and butcher to bookstore and florist, and filters to independents only.
| Made-up month | Rows in the table | Added | Closed | Phone or website changed |
|---|---|---|---|---|
| First run | 1,180 | 1,180 | 0 | 0 |
| Second month | 1,203 | 31 | 8 | 14 |
| Third month | 1,219 | 22 | 6 | 9 |
Invented figures to explain the reading; not data about Lyon.
After the first fill, the monthly email is short: twenty or thirty new shops to welcome, a handful of closures to mark, a few changed numbers. The coordinator reads it in five minutes instead of auditing the whole list.
Traps to avoid
- Keying on the name. "Boulangerie du Parc" and "Boulangerie Du Parc" become two companies. Key on the place id.
- Deleting closures. Mark them closed and keep the row; you lose the history otherwise.
- One circle for a whole metro. Searches stop at the nearest 500 of a type. Draw districts.
- Mixing your edits with the feed. Keep your own columns (owner, notes) separate from the columns the job overwrites, so a refresh never wipes a rep's notes.
- No owner. Somebody should read the monthly change email. A database nobody looks at is a cost, not an asset.
The AI employee that keeps it
The New openings into my database template is a ready version of this job. Built by hand, it looks like this:
- Schedule: every Monday, or on the first of the month.
- Area of interest: your city or the districts you drew.
- Nearby places: the types your database covers.
- Only new and, on a second branch, What changed.
- Place classifier: clean categories and no duplicates.
- Save to my database or Google Sheets, then an email to you with the additions, closures and changes.
To read your own table back into another flow, use My data for an uploaded table, or Database query for your own database. The approach suits B2B sales teams and local government business offices alike, and the guide to location intelligence automation shows where it fits.
Questions
How often should the database refresh?
Monthly matches the places database's own refresh. Weekly suits teams that also scan online maps live for the newest openings.
What happens when a business closes?
What changed reports it with the word closed and the date, and the row is updated, not deleted, so you keep its history.
Can I keep my own notes in the same table?
Yes. Keep them in columns the flow does not write, and the refresh will update the place's own fields only.
Where should the table live?
Google Sheets for a small team, or your own PostgreSQL, BigQuery or Snowflake database when other systems read it.
Templates that do this
Read next
More on business and places data · Every article · Templates