Brokerage reporting across CRMs: one SQL table for every agent
· SyncYak team
Your brokerage has forty agents and at least four CRMs. The top team runs Follow Up Boss, two offices came over on Lofty, the marketing agency you hired put its clients on HighLevel, and a handful of veterans never left KW Command. When the owner asks "how many leads did we get last month, and how many are under contract", the answer takes a week of exports and a spreadsheet nobody trusts.
Standardizing on one CRM is the usual advice, and it rarely happens. Zillow's third-quarter shareholder letter, filed on November 4, 2026, puts Follow Up Boss at about 141,000 monthly active users, up 16% year over year (SEC filing). That is a lot of agents on one CRM, and it still leaves plenty of teams on the others. Agents pick a CRM, they bring it with them when they join, and they take it when they leave. Reporting has to work across whatever they use.
This post shows a way to do that without a connector per CRM: sync every agent's CRM into one database you own, in tables that share the same columns, and write the report once.
Why exports do not add up
The export route fails for predictable reasons:
- Every CRM names things differently. A stage in one is a status in another; the lead source is a free-text field in a third.
- Exports are snapshots. A lead that moved from "new" to "active" after the export is still "new" in your report.
- Agents own the exports. A report that depends on each agent sending a CSV on the first of the month depends on forty people remembering.
- Keys get collected. Someone on your staff ends up holding API keys for agents' CRMs, which neither the agent nor your compliance person wants.
A sync fixes all four: it runs on a schedule, it lands in one layout, and the agent connects their own CRM without handing anyone a key.
Step 1: one database, one layout
Pick a Postgres or MySQL database you control. Managed Postgres from any cloud provider is fine, as long as it is reachable from the internet. Give the sync a role that can create tables and views in one schema, plus select, insert, update and delete on what it creates. Keep agents' CRM data in its own schema so the rest of your data is untouched.
Then decide how the agents' leads should share space. There are two sensible options:
Shared tables. Every connection writes into the same people table. Each row carries which CRM it came from and which connection wrote it, and row ids are prefixed by connection so two CRMs that both use id 1001 never collide. This is the layout for brokerage-wide reporting: one query covers everyone.
One table per connection. Each agent gets their own numbered tables. Use this when an office or a client must be kept apart, for example an agency reporting to several brokerages.
You can mix them. Most brokerages run shared tables for the main roster and separate tables for the one or two accounts with a reason to be separate.
Step 2: let agents connect their own CRM
Do not collect keys. Send each agent an invite link instead. A good invite flow:
- Shows the agent which brokerage account is asking, so it does not look like phishing.
- Lets them pick their CRM and paste their API key, or sign in at the vendor for CRMs that use OAuth.
- Tests the credentials before connecting, so a typo fails on the spot instead of as an empty table next week.
- Connects one CRM and then closes. The link cannot be reused, and you can revoke it.
The link carries the connection name and the table mode you chose, so the agent never sees a settings screen. From their side it is a two-minute task.
Step 3: write the report once
With shared tables and a common set of columns, the report is ordinary SQL. The _flat view over the people table always has the same core columns whatever the CRM: firstName, lastName, emails_0_value, phones_0_normalized, addresses_0_city, source, stage, assignedTo and lastCommunication_date, plus _crm, _connection_id, _synced_at and _deleted_at.
Current pipeline by CRM and stage:
select _crm, stage, count(*) as leads
from to_syncyak_brokerage_people_flat
where _deleted_at is null
group by 1, 2
order by 1, 2;
Leads that have gone quiet, by agent:
select assignedTo, _crm, count(*) as stale_leads
from to_syncyak_brokerage_people_flat
where _deleted_at is null
and lastCommunication_date < now() - interval '30 days'
and stage not in ('Closed', 'Trash')
group by 1, 2
order by 3 desc;
Two things make these queries safe to schedule. Deleted leads are marked with _deleted_at rather than removed, so last month's totals do not shrink when an agent cleans their pipeline. And columns never disappear from the view once they have appeared, so a dashboard built in March still runs in September.
Stage names will still differ between CRMs. Keep a small mapping table of your own, (crm, stage, brokerage_stage), and join to it. The same goes for a month-by-month trend: each CRM names its created date differently, and the full record is kept as JSON in the base table, so read the date from there and keep the field name per CRM in the same mapping table. That is the only CRM-specific knowledge in the whole report, and it lives in your database where you can edit it.
Step 4: send something useful back
A report that only reads is half the value. Once you can score leads across the whole brokerage, the agent whose lead it is should see the score in the CRM they actually open.
Keep a second table, one row per lead id, with the columns you compute: a score, a brokerage tag, a "needs attention" flag. Map each column to a CRM field, and let the sync write changed rows onto the matching leads. Write-back works for Follow Up Boss, Lofty, HighLevel and Cloze. BoldTrail, CINC, Brivity and KW Command sync one way, into your database, so for agents on those CRMs the report stays in your dashboard.
What to check before you trust the numbers
- Sync status per connection. An agent who revoked an API key shows up as a connection with a growing backlog, not as a silent gap in the chart. Watch for it.
- Duplicates across CRMs. The same buyer can be in two agents' CRMs. Match on
phones_0_normalizedoremails_0_valuewhen you count unique people rather than leads. - Deleted rows. Filter on
_deleted_at is nullfor current pipeline, and leave the filter off for historical totals. - Freshness. Put
max(_synced_at)on the dashboard so everyone knows how old the numbers are.
How SyncYak helps
SyncYak CRM connects Follow Up Boss, Lofty, BoldTrail, CINC, HighLevel, Cloze, Brivity and KW Command into one Postgres or MySQL database you own, or a Google Sheet for smaller teams. Each connection writes to_syncyak_<name>_<entity> tables with a typed _flat view, in shared tables with prefixed ids or in a table per connection. Every table carries crm, connection_id, the full record as JSON, and deleted_at for leads removed in the CRM.
Agents connect from an invite link that expires in 1 to 30 days, shows which account asked, tests the credentials, and closes after one connection. Your staff never handles a key. The dashboard shows per-connection status, backlog and a request log.
Plans set the number of connections and the pace: 1 CRM and 100 leads every hour on Free, 2 CRMs and 1,000 leads every 30 minutes on Hobby at $29 a month, 5 CRMs on Pro, 15 on Business and unlimited on Enterprise, with extra connections as an add-on. From Hobby, CRM webhooks deliver changes within a batch interval, and field mapping writes values from a from_syncyak_ table back to Follow Up Boss, Lofty, HighLevel and Cloze, creating missing custom fields for you. The details are on the real estate CRM sync page, the CRM product page and pricing.
Start with the Free plan, connect your own CRM to a database, and run the first query above. Then send the first invite link: create an account.