Your CRM Is a Graveyard: The Database Audit

A one-hour count of what is usable, what needs cleanup, and what you still do not know.

11 min read

Export your real estate CRM as a CSV. Give every row an ID. Count duplicates, unreachable contacts, missing last-contact dates, suppressed records mixed into active lists, and rows you cannot explain yet. Reconcile every count to the export total. That is the database audit: one hour that turns a mess into a measured cleanup list before you work another name.

A CRM you do not update earns the job title of expensive contact list.

The expense is easy to ignore because the invoice belongs to software. The CRM mistakes real estate agents accumulate sit in the file: two records for the same past client, a phone number with no name, a “last contacted” field that nobody has touched since the import, and no record of whether a person asked for space.

You can feel that disorder without knowing its size. That is why opening the CRM creates resistance. Every row might be useful, broken, duplicated, or all three.

Real estate database management starts with a count. It does not rank past clients, draft a reconnection message, or decide your follow-up cadence. The other playbooks handle those jobs. This one lets you test whether the database underneath them can support the work.

The number you need first

Start with your cleanup count: the number of unique row IDs with at least one defect.

A defect means the row needs a human decision before you use it for prioritization or outreach. A missing last-contact date qualifies. So does an exact duplicate, an opt-out marked active, or a contact with neither a usable phone number nor a usable email address.

Count each affected row once in the cleanup total, even when it has four defects. Keep the four defect labels on the row. That gives you one honest workload number without hiding what is wrong.

Then calculate:

Cleanup rate = Rows needing attention ÷ Nonblank contact rows × 100

That percentage comes from your own export. It becomes the baseline for the next audit. If this month shows 184 rows needing attention and the next one shows 73, you can prove the database improved without pretending every record is fixed.

What to count

Use the thresholds below as working rules for the audit. “Trouble” means the file needs cleanup before another workflow relies on that field. It does not mean the whole CRM is worthless.

What to count	Why it matters	The number that means trouble
Export row difference	Your CRM may report one total while the CSV contains another. You need a stable starting number.	Any difference other than 0
Blank rows	Rows empty across every source field can distort totals and batch sizes.	1 or more
Extra exact-duplicate rows	A later workflow may score or count the same contact twice. Count copies beyond the first row.	1 or more
Possible duplicate groups	A shared phone number, email address, or close name match may split one relationship across records.	1 or more groups
Rows with no usable phone or email	The file has no current route for a personal reconnection. The audit cannot test whether an address will deliver.	1 or more
Missing last-contact dates	You cannot tell whether the relationship is warm, stale, or undocumented.	1 or more
Invalid or future last-contact dates	Sorting by recency will produce a false order.	1 or more
Suppressed contacts marked active	An opt-out, do-not-contact, or unsubscribe flag conflicts with the active status.	1 or more
Rows the audit cannot classify	A mystery row can disappear from every other total while the summary still looks polished.	1 or more
Unique rows needing attention	This is the cleanup count and the size of your first repair queue.	Any count above 0
Reconciliation gap	Clean rows plus rows needing attention must equal all nonblank contact rows.	Any difference other than 0

One bad row creates a task. It should not stop you from using the clean part of the file once you can separate the two.

CRM export → Row IDs → Defect counts → Verified exceptions → Cleanup queue → Ready for prioritization

Clean up your real estate database in one hour

1. Export and freeze the original

Export every contact as a CSV. Save the untouched file with the date in its filename, then make a working copy.

Do not clean while you export. Editing on the way out erases your baseline and makes the final count impossible to reconcile.

Record the contact total shown inside the CRM before you leave. Some systems count households, leads, archived contacts, or companies differently from the rows in an export. The difference is a finding you need to explain.

2. Give every row a permanent audit ID

Add a first column called AUDIT_ROW_ID. Number every data row from 1 through the end of the file, including rows with blank source fields.

Keep that ID attached to the row through every sort and review. A model can tell you that row 417 has a future contact date. You can find row 417 and judge it without trusting a fuzzy name match.

3. Remove fields the audit does not need

Keep the contact ID, first and last name, email, phone, status, last-contact date, created date, opt-out fields, and any existing duplicate or archive flag.

Remove freeform notes, message history, birth dates, protected-class information, financial details, and MLS-only fields. None of them helps count database defects. A smaller file also gives the model less room to wander into personal material that has nothing to do with the audit.

Save this trimmed copy as the audit file. Keep the original export unchanged.

4. Run the audit prompt

Upload the trimmed CSV with the prompt below. The one-hour estimate assumes your tool can inspect the file in one pass. If it cannot, use smaller batches and budget more time. Silent truncation ruins the count.

The prompt forces the model to list row IDs behind every count and return INCOMPLETE when it cannot inspect the entire file. That instruction helps. Your verification still decides whether the answer is usable.

You are auditing a CSV export from a licensed real estate agent's CRM.
Inspect the whole file. Do not draft outreach, score contacts, merge records,
or change source values.

If you cannot inspect every row reliably, stop and return:

INCOMPLETE AUDIT
Reason:
Last AUDIT_ROW_ID inspected:
Recommended next batch:

Never estimate a count.

FIELDS

AUDIT_ROW_ID; CRM contact ID; first and last name; email; phone; status;
last-contact date; created date; suppression fields; archive or duplicate flag.

Report your field mapping first. Mark a missing concept NOT AVAILABLE. A missing
column is different from a blank value.

If an AUDIT_ROW_ID is missing or repeated, return RECONCILIATION FAILED.

COUNT THESE ITEMS

1. Total data rows, rows blank across all source fields, and nonblank rows.
2. Extra exact-duplicate rows beyond the first. Compare normalized name, email,
   and phone. Ignore IDs.
3. Possible duplicate groups. Match normalized email or phone first. A close
   name match is a flag for human review.
4. Rows with neither a plausibly formatted email nor phone. Do not claim either
   field is deliverable.
5. Blank, invalid, or future last-contact dates. Count each type separately.
6. Suppression fields that conflict with active status.
7. Rows you cannot classify.
8. Unique nonblank rows with one or more defects.
9. Clean nonblank rows with no detected defect.

For every item, return:

Check | Count | Percent of nonblank rows | AUDIT_ROW_ID values | Notes

List every supporting AUDIT_ROW_ID. Group possible-duplicate IDs by contact.

RECONCILE THE AUDIT

Blank rows + nonblank rows must equal total rows. Clean rows + unique rows with
defects must equal nonblank rows. If either equation fails, return
RECONCILIATION FAILED and identify the gap.

FAIR HOUSING AND MLS GUARDRAILS

- Do not use, infer, repeat, or evaluate any protected characteristic. Do not
  describe neighborhood demographics or steer anyone.
- This audit needs no MLS data. Do not expose confidential or agent-only fields.
- Do not claim compliance with a law, rule, MLS policy, or brokerage policy.

FINAL SUMMARY

Return five numbers first: nonblank rows, clean rows, unique rows needing
attention, cleanup rate, and reconciliation gap. Then return the audit table,
possible-duplicate groups, and cleanup queue. Do not repair data.

5. Verify the counts before you touch the CRM

Check the two reconciliation equations first. A nonzero gap ends the audit. Rerun the file in smaller batches or use your spreadsheet's filters to find the missing rows.

Then choose five findings from each defect type and inspect the source rows yourself. For a category with fewer than five rows, inspect all of them. Confirm that the model used the right columns and did not mistake a shared household phone number for proof of a duplicate.

Use spreadsheet counts for the biggest categories. Filter blank last-contact dates and compare the visible row count with the audit. Sort normalized emails and phone numbers to expose repeated values. The model is useful for classification and explanation. The spreadsheet is better at arithmetic you can reproduce.

6. Build the cleanup queue

Copy the flagged row IDs into a new sheet. Add columns for Defect, Decision, Owner, Resolved date, and Notes.

Work in this order:

Suppression conflicts
Exact and possible duplicates
Unreachable records and broken dates
Missing last-contact dates
Everything the audit could not classify

Do not delete a contact because a model called it a duplicate. Open both records, compare their history, and decide whether to merge, archive, or keep both. The audit creates a review queue. You make the database change.

Four ways the audit fails

1. The model confidently miscounts duplicates

A model may report 36 duplicates after finding 36 rows that belong to 18 pairs. Another run may call that 18 duplicates because it counted groups.

Both answers can sound certain. Only one definition belongs in your audit.

Count extra rows beyond the first record as exact duplicates, and count possible duplicate groups separately. Require every supporting row ID, then verify that the IDs produce the stated total. A number without its rows is an opinion wearing a calculator.

2. The export creates duplicates that are not duplicate contacts

Some CRMs export one row per tag, transaction, phone number, or email address. One person with three tags can arrive as three rows even though the CRM holds one contact.

Inspect the CRM contact ID before merging anything. If the repeated rows share one internal ID, you may be looking at an export shape rather than three records. Change the export or group by contact ID, then rerun the audit.

3. Mixed date formats turn clean history into bad data

03/04/24 can mean March 4 or April 3. Spreadsheet serial numbers can look like five-digit contact dates. Blank cells sometimes arrive as zeroes or 01/01/1970.

The model may normalize those values without telling you. Tell it to flag ambiguity, then check the CRM's locale and export settings. Preserve the source value until you know what it meant.

4. “Dead” becomes a guess about the person

A contact with no recent date may still have a working email and a strong relationship. A row with a polished email address may bounce tomorrow.

Use narrower labels: missing last-contact date, no usable contact field, suppressed, or possible duplicate. The audit can inspect the file. It cannot know whether the person is reachable, interested, or ready to move.

What you should have after one hour

You should finish with the untouched export, the trimmed audit file, a reconciled count, and a cleanup queue tied to permanent row IDs.

You may still have 200 records to repair. That is progress because 200 is a workload. “My CRM is a mess” is a reason to keep avoiding it.

Run the audit again after cleanup and record the new cleanup rate beside the first one. Then send the clean rows into the past-client prioritizer. Ranking before the audit gives bad records a vote in who gets your attention.

The next operating layer is the two-year follow-up cadence. It gives each prioritized name a verified reason and a next review date instead of another generic check-in.

Engagement Manager is the AI employee planned for that combined job. It will keep the usable list prioritized and prepare the next draft for your review. Avenue Growth has deferred its release until after the first launch. Once you finish the audit, it or any system you use now gets a database worth reading.