Debugging de All Contacts con SQL

Un contacto aparece en tu Data Extension pero no en All Contacts. O al revés. O el mismo contacto muestra Active en tu DE de subscribers y Held en All Contacts. O marketing piensa que desuscribió a alguien y el DE todavía dice activo. La vista All Contacts en Marketing Cloud es el record canónico de cada contacto entre canales (email, SMS, push, web), y cuando no concuerda con un Data Extension del que sos dueño, el DE está mal — pero entender cómo divergió es la parte que toma tiempo.

Este es el playbook de reconciliación. Cinco queries que comparan tu DE contra los stores canónicos de contactos y surfacean dónde realmente vive el estado de cada contacto.

Qué es realmente "All Contacts"

Es una agregación virtual entre tablas de contacto channel-específicas. Las SDVs relevantes:

La UI de "All Contacts" joinea estas más el modelo de Contact Builder (que usa tablas subyacentes distintas no siempre queryeables vía SQL Activity). Para la mayoría de los propósitos de reconciliación, _Subscribers es la SDV que queryeás — es el record canónico del canal email.

Step 1 — Cross-referenciá tu DE contra _Subscribers

Encontrá contactos en tu DE que no existan en _Subscribers (o al revés).

-- En tu DE pero NO en _Subscribers (el contacto canónico de email)
SELECT
  d.SubscriberKey,
  d.EmailAddress,
  d.Status AS YourDEStatus
FROM your_master_de d
LEFT JOIN _Subscribers s
  ON LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
WHERE s.SubscriberKey IS NULL;

-- En _Subscribers pero NO en tu DE (típicamente el problema más grande)
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.Status AS CanonicalStatus
FROM _Subscribers s
LEFT JOIN your_master_de d
  ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
WHERE d.SubscriberKey IS NULL;

El LTRIM(RTRIM(CAST(...))) es obligatorio — SubscriberKey es un string y la trampa de coerción de tipo + whitespace silenciosamente rompe el join si no (ver JOIN, gotchas — #9).

La primera query generalmente devuelve cero o un set chico: contactos que agregaste a tu DE que todavía no fueron procesados por un Send (así que no están en _Subscribers). La segunda query devuelve la lista de recovery — contactos que existen en MC pero tu SQL de audience-build se está perdiendo.

Step 2 — Auditoría de mismatch de status

Para contactos que aparecen en los dos, encontrá donde los statuses no concuerden.

SELECT
  d.SubscriberKey,
  d.EmailAddress,
  d.Status      AS DEStatus,
  s.Status      AS CanonicalStatus
FROM your_master_de d
INNER JOIN _Subscribers s
  ON LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
WHERE d.Status <> s.Status
   OR (d.Status IS NULL AND s.Status IS NOT NULL)
   OR (d.Status IS NOT NULL AND s.Status IS NULL);

Patrones comunes en el resultado:

El status Held es el que sorprende a los equipos — es la supresión automática de MC después del threshold de bounce-out. Si tu audience DE no filtra los Held, estás mandando a direcciones que MC no va a delivear.

Step 3 — Snapshot de unsubscribes / bounces recientes

Levantá los eventos que dispararon cambios de status en los últimos 30 días, así podés correlacionar contra el cadence de refresh de tu DE.

INSERT INTO de_log_contact_events
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  u.EventDate         AS UnsubscribedAt,
  NULL                AS BouncedAt,
  'unsubscribe'       AS EventType,
  GETDATE()           AS SnapshotAt
FROM _Unsubscribe u
INNER JOIN _Subscribers s
  ON u.SubscriberID = s.SubscriberID
WHERE u.EventDate >= DATEADD(day, -30, GETDATE());

INSERT INTO de_log_contact_events
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  NULL                AS UnsubscribedAt,
  b.EventDate         AS BouncedAt,
  CASE WHEN b.IsUnique = 1 THEN 'hard-bounce' ELSE 'soft-bounce' END AS EventType,
  GETDATE()           AS SnapshotAt
FROM _Bounce b
INNER JOIN _Subscribers s
  ON b.SubscriberID = s.SubscriberID
WHERE b.EventDate >= DATEADD(day, -30, GETDATE());

Después queryeá el snapshot para encontrar contactos cuyo status en tu DE no refleja un evento reciente:

-- Contactos que se desuscribieron o bouncearon en los últimos 30 días pero
-- tu DE todavía los tiene como Active
SELECT
  e.SubscriberKey,
  e.EmailAddress,
  e.EventType,
  COALESCE(e.UnsubscribedAt, e.BouncedAt) AS EventAt,
  d.Status AS YourDEStatus
FROM de_log_contact_events e
INNER JOIN your_master_de d
  ON LTRIM(RTRIM(CAST(e.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
WHERE d.Status = 'Active';

Cada fila es un contacto al que estás todavía intentando mandarle algo que MC no va a delivear realmente. Actualizá el audience-build de tu DE para honrar esos eventos.

Step 4 — Checks de BU y de la lista All Subscribers

Si estás en un tenant multi-BU, un contacto puede aparecer en el _Subscribers del BU padre pero no en la vista del BU hijo. Corré el diagnóstico desde cada contexto de BU para comparar.

El status de la lista All Subscribers (Active / Bounced / Held / Unsubscribed) es el que controla la elegibilidad de Send — si un contacto es Held o Unsubscribed a nivel All Subscribers, ningún Send va a alcanzarlo sin importar lo que diga tu DE.

-- Status de All Subscribers para una lista específica de SubscriberKeys
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.Status         AS AllSubscribersStatus,
  s.DateUndeliverable AS BounceOutDate
FROM _Subscribers s
WHERE s.SubscriberKey IN (
  SELECT SubscriberKey FROM your_master_de
);

La columna DateUndeliverable es no-NULL cuando MC hizo hard-bounce o desuscribió al contacto. Filtrá tu audiencia por DateUndeliverable IS NULL si querés solo contactos delivereables.

Step 5 — Snapshot de reconciliación

Una vez auditado, snapshot los conteos de diagnóstico así la próxima vez que algo se vea raro tenés un baseline.

INSERT INTO de_log_contact_reconciliation
SELECT
  GETDATE()                                AS SnapshotAt,
  'your_master_de'                         AS DEName,
  (SELECT COUNT(*) FROM your_master_de)    AS DERowCount,
  (SELECT COUNT(*) FROM _Subscribers)      AS CanonicalRowCount,
  (SELECT COUNT(*)
   FROM your_master_de d
   LEFT JOIN _Subscribers s
     ON LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
      = LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   WHERE s.SubscriberKey IS NULL)         AS InDeNotInCanonical,
  (SELECT COUNT(*)
   FROM _Subscribers s
   LEFT JOIN your_master_de d
     ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
      = LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
   WHERE d.SubscriberKey IS NULL)         AS InCanonicalNotInDe;

Corré esto semanalmente (o diariamente para sending de alta cadencia). Saltos grandes semana-a-semana significan que algo cambió en el audience-build o en el record canónico.

Causas comunes rankeadas por frecuencia

Playbook de remediación

1. Filtrá la audiencia por status canónico. Agregá el join desde tu SQL de audience-build a _Subscribers (con el patrón trim/cast), filtrá WHERE s.Status = 'Active' AND s.DateUndeliverable IS NULL.
2. Snapshot eventos recientes diariamente. Corré el Step 3 como una Automation scheduleada que escribe a de_log_contact_events, después tu audience-build joinea contra el snapshot en lugar de _Unsubscribe / _Bounce directamente (ver FROM).
3. Agregá alertas de reconciliación. Si InCanonicalNotInDe salta más de X% semana-a-semana, disparar una notificación.
4. Para multi-BU: construí el master subscriber DE a nivel BU padre y propagá hacia abajo vía Shared Data Extensions.
5. Documentá el lifecycle de contactos en el runbook de tu equipo — quién marca contactos inactive en tu DE, qué dispara un sync de status, cuándo corre la reconciliación.

Relacionado

• FROM — _Subscribers y otras SDVs son inestables para lecturas de producción; primero snapshot
• JOIN — LTRIM(RTRIM(CAST())) para el join de SubscriberKey
• WHERE — filtrar por Status + DateUndeliverable IS NULL
• INSERT INTO — escribir los snapshots de diagnóstico
• MC SQL gotchas — ver #6 (rotación de SDV), #9 (casteo de SubscriberKey)
• Style Guide — la disciplina que evita drift de reconciliación repetida
• Debugging de email sends + Debugging de largo de valores — los otros dos snippets de debugging

Catálogo completo: con este snippet, las 19 páginas de la sección SQL están entregadas. La próxima capa de trabajo son las subcategorías SSJS, AMPscript, y Config — cada una de las cuales seguirá el mismo template (gotchas + catálogo de referencia + snippets de debugging) establecido acá.