Customer Profile Data Readiness

A file named "Email Marketing Analysis" was being used as the starting point for a customer segmentation. It contains 10,000 customer profiles and 11 columns. It contains no email address, no campaign, no send, no open, no click and no consent field. It cannot answer an email marketing question. What it can answer is whether the customer master is ready to support one — and that turned out to be the more useful question.

The decision

The CRM team needed to decide whether the current customer profile could support segmentation and campaign measurement, and if not, what to fix first and what to request from the systems that hold the missing data. The output was a readiness assessment, not a segmentation.

What we worked with

10,000 customer profiles with 11 fields:

• Identity: a GUID, entity type and account status.
• Profile: gender, birth date, marital status and living status.
• Location: city, state and postal code.
• Lifecycle: an enrolment timestamp.

The traps we designed around

• The file name is not the content. There is no email address, campaign, send, impression, open, click, purchase or consent field. Any "email marketing analysis" built from this file would be invented. The honest deliverable is a data readiness review.
• Status has almost no variance and two spellings. 9,900 profiles are Active, 73 are labelled "InActive" and 27 "Inactive". A naive group-by reports three statuses; two of them are the same state. And a field that is 99% one value cannot explain any outcome.
• Birth dates are invalid for a share of records. 109 dates fall after 2000 in a file of adult customers, the earliest is 1900-01-01 and the latest is 2018. Worse, 73 records have a birth date that exactly equals their enrolment timestamp — a copy or default artefact, not a real date. Any age calculation built on this column will be wrong for those rows.
• Postal code is stored as a number. Values appear as 500024.0. Six-digit codes with meaningful leading zeros lose them when stored numerically, and one record is missing entirely.
• Geography is incomplete. City is missing for 73 records and state for 126. Those profiles cannot be used in a geographic segment, and the gap is not random enough to ignore.
• One schema holds two entity types. 264 records are Businesses and 9,736 are Individuals. Marital status, gender and living status do not apply to a business, so those fields carry meaningless values for that subset.
• Categorical values are inconsistently cased. Living status appears as "couple with children" in lower case and "Couple without children" capitalised. Grouping without normalisation splits the same concept into separate categories.
• Rare categories need a decision. Gender includes 189 "Not Mention" and 17 "OTHER" records. These need a documented mapping rather than being silently folded into a majority category.
• Segmentation is possible; activation is not justified. The profile supports grouping by age band, location and household type. It does not support contacting anyone, because contactability and consent are absent from the file.

How the analysis was built

1. Inventory fields against the decision. Each column was marked as usable, needs cleaning, or missing relative to the campaign question the team actually had.
2. Split the entities. Individual and Business profiles were separated so that person-level attributes were not applied to organisations.
3. Normalise the categories. Status spellings and living-status casing were standardised, with the original values retained in a mapping table.
4. Validate dates. Birth dates equal to the enrolment timestamp were quarantined, implausible dates were flagged, and age was derived only where the source date passed the check.
5. Treat postal code as text. The field was converted back to a string so leading zeros survive.
6. Score readiness by dimension. Identity, contactability, consent, profile completeness, geography and behaviour were assessed separately, because a single "data quality score" hides which gap blocks the decision.
7. Write the data request. The missing fields were prioritised by which decision each one unblocks.

What this changes

• The engagement is reframed. It is a readiness assessment, not a campaign analysis — and that is a defensible deliverable rather than a substitute for one.
• The gap list becomes the first output. Consent and contactability are named as prerequisites, not discovered halfway through a segmentation.
• Duplicate status values stop splitting reports. Normalisation is documented and reversible.
• Invalid birth dates no longer distort age bands. The quarantined records are visible instead of silently mis-aged.

What you receive

• A field dictionary marking each column as usable, needs cleaning or missing.
• A readiness scorecard by dimension, with the blocking gaps named.
• A cleaned profile table with normalisation and quarantine rules.
• An entity model separating individuals from businesses.
• A prioritised data request covering consent, contact and engagement history.

Where this applies

The method fits CRM migrations, marketing analytics readiness checks and customer data platform onboarding. The minimum input is a customer master extract with identity, profile, location and lifecycle fields. The first deliverable is usually the field inventory, because the most expensive mistake in a segmentation project is discovering at the end that the file never contained the variable the decision depended on.

This is an illustrative case study using an anonymized public customer profile dataset; it does not report results from a specific client engagement. It reflects our standard method for customer data readiness assessment.