Article summary
A three-layer CRM design that separates customer facts, WA/WS activity observations and contact eligibility so fresh data cannot overwrite identity or bypass permission.
“WhatsApp-active phone” is not an executable marketing segment. It is a platform observation with a timestamp. A durable CRM separates customer facts, WA/WS activity observations and present contact eligibility. Layer one answers who this record represents, layer two states what a system observed, and only layer three decides whether contact is currently allowed for a named purpose.
Why a single-table design fails
If contacts contains only active=yes, the next import overwrites history without an observation date. A mapped phone may replace the customer’s primary number, and marketers can mistake activity for permission. The convenient table has silently discarded time, provenance and veto conditions.
Layer one: relatively stable customer facts
The identity layer stores contact_id, raw and normalized phones, country evidence, acquisition source and the necessary relationship. A phone is not a permanent identity, so change has history. This layer does not contain “recently active” and never merges two people because of one workbook.
Layer two: append-only activity observations
WS Activity can return phone, activity time, active days and mapped WhatsApp phone. Append an observation for each task instead of updating one definitive column. Store task_id, checked_at, input phone, returned phone, outcome, unknown reason and schema_version so change remains reconstructable.
| Layer | Representative fields | Update mode |
|---|---|---|
| Customer facts | contact_id, phone_normalized, source | Governed change |
| Activity observations | activity_time, active_days, checked_at | Append only |
| Contact eligibility | purpose, channel_scope, opt_out, valid_until | Computed now |
Layer three: the eligibility view
Eligibility is not an activity field. The view evaluates user choice, purpose, regional rules, suppression, customer stage, frequency and observation expiry. Any hard veto outranks an activity score. A recently observed contact who opted out can therefore never enter the queue.
The import path from TXT to observations
Generate a TXT containing one phone per line from an approved cohort and retain an internal batch_row-to-contact_id crosswalk. AIPUSH exports Excel. Land it in staging, validate row counts, schema, types, duplicates and reconciliation, then append observations. Hash the TXT and Excel so the wrong version cannot be joined later.
Resolve mapping collisions in a queue
| Collision | Possible cause | Treatment |
|---|---|---|
| Input differs from mapped phone | Format, migration or historic entry | Retain both and review |
| One return joins several people | Shared or duplicate customer | Forbid automatic merge |
| One person has several returns | Multiple phones or change | Build a timeline |
| No reconciliation | Lost row or format exception | Quarantine batch record |
Use decay rather than a permanent label
“Fresh” has a different meaning for each business. Divide observations into fresh, aging and stale using thresholds aligned with the sales cycle. Stale records stop contributing to rank. Do not rerun an entire database to keep a dashboard green; refresh a small set only while purpose remains valid and a decision is near.
An explainable scoring example
Scoring is allowed only after eligible=true. Then combine need fit, customer stage, service interaction and activity observation, displaying a reason for each contribution. Activity receives limited weight, unknown is not “bad lead,” and mapping conflict routes to review. The queue explains why a record entered instead of showing a mysterious total.
Express business action as a state machine
A record can move from candidate to eligible, review or suppressed. After a message it enters cooldown; opt-out sends it immediately to suppressed; observation expiry moves it to needs_refresh or removes that signal. Every transition has an event and actor, preventing teams from inventing conflicting spreadsheet definitions.
What belongs on the operations dashboard
Track reconciliation, unknowns, collision backlog, observation-age distribution, permission vetoes, frequency caps, reply quality and complaints—not a vanity count of active phones. If prioritization does not produce better customer conversations, change the use case or stop using the signal.
The real value of three layers
The organization can delete expired observations without damaging customer history, change permission logic without rerunning a check, and explain why a list was allowed or blocked. WA/WS Activity becomes an auditable operations signal rather than an ever-expanding marketing label.
