Data Validation Before Building Automation

Overview

Before building any CRM automation that filters or segments contacts based on field criteria, validate that those fields are actually populated in the database. Skipping this step leads to automation that technically works but produces empty segments — a common failure mode when client-defined criteria outpaces the data they've actually collected.

This is especially likely when criteria are defined by sales or marketing stakeholders who know what should be in the CRM, not what is in it.

The Problem

Clients often define qualification criteria (e.g., for Sales Qualified Leads) based on ideal data states rather than actual database contents. When you build automation against these criteria without checking field coverage first, you get:

Zero-match segments — no contacts qualify because required fields are empty or unpopulated
Wasted build time — automation is technically correct but functionally useless
Stakeholder confusion — the system appears broken when the real issue is data gaps

Example: Citrus SQL Automation

Citrus defined 13 criteria for Sales Qualified Leads, including fields like "Are you already juicing?" When this field was filtered in HubSpot, zero contacts matched — the field existed but had never been populated at scale. Building the full SQL automation against all 13 criteria would have produced an empty segment from day one.

A second example from the same criteria set: requiring precise countertop dimensions as a qualification field. This is a sales conversation data point, not something contacts self-report. The underlying question ("do you have room for it?") is valid, but the field as defined cannot be automated against.

The Practice: Validate Before You Build

Step 1 — Enumerate All Criteria

List every field and condition the client has defined for the segment or automation trigger. For SQL definitions, this typically comes from a sales or ops stakeholder's spec sheet.

Step 2 — Check Field Coverage in the CRM

For each criterion, filter the contact database and record how many contacts match. In HubSpot, this can be done quickly using the Contacts view with Advanced Filters — no segment needs to be saved at this stage.

Build a simple spreadsheet:

Criterion	Field Name	Match Count
Are you already juicing?	`are_you_already_juicing`	0
Has counter space	`has_counter_space`	0
Volume > 500 units/mo	`monthly_volume`	47
...	...	...

Step 3 — Present Findings to the Stakeholder

Share the coverage report before building anything. Frame it clearly:

"You gave me 13 criteria. If I implement all of them as-is, zero contacts will qualify. Here's why, field by field."

This reframes the conversation from "the automation isn't working" to "we need to decide which criteria are ready to use now."

Step 4 — Propose a Phased Rollout

Rather than blocking on a full criteria overhaul, identify the subset of fields with meaningful coverage and propose starting there. A 4-criteria segment that produces real results is more valuable than a 13-criteria segment that produces nothing.

The remaining criteria become a backlog: either the data collection process needs to be updated to populate them, or the criteria need to be simplified to match what can realistically be captured.

Architectural Note: Separate Audience from Action

A related principle that makes this validation step more impactful: define who qualifies in a segment, not in the automation itself.

The segment answers: who meets the criteria?
The workflow/automation answers: what do we do with them?

This separation means you can iterate on qualification criteria by updating the segment without touching the automation logic. It also makes the zero-match problem immediately visible — an empty segment is obvious; broken automation logic is not.

See ^[1] for more on this pattern.

When to Apply This

Any time a client provides a list of qualification criteria for a segment or lead scoring rule
Before building enrollment triggers in workflow automation
When inheriting an existing automation that "isn't working" — check field coverage before assuming logic errors
When criteria were defined by someone outside the team that manages the CRM

^[2] — client where this issue was first documented
^[1] — architectural pattern for separating audience definition from automation actions
^[3] — source meeting where this practice was discussed