Skip to content

Mensajería · Atributos de envío (SendGrid + Bird → BigQuery → dbt)

Trae los atributos de envío de los dos canales salientes —email transaccional (SendGrid) y WhatsApp (Bird/MessageBird)— a BigQuery vía API, los aterriza en RAW, los modela en staging por fuente y los unifica en un mart sin datos personales (messaging.messaging_daily) para responder cuánto se manda por día, de qué templates, y qué pasa con eso.

Alcance: atributos de envío, no conversaciones

El pedido original fue volumen, templates, entregas y engagement. No se ingesta el contenido de los mensajes ni las respuestas de los clientes: de WhatsApp solo se guardan mensajes de template (type=hsm) y nunca los parámetros del template, que es donde van nombres, montos y RFCs.

Las dos mitades no tienen la misma historia ni la misma precisión

WhatsApp tiene historia desde 2023-06-27 y su fecha de envío es exacta. Email empieza el 2026-08-27 —el día del despliegue— y su fecha de envío es estimada. Email Activity retiene 3 días, así que la historia anterior no es recuperable por ningún medio. Ver Limitaciones.

¿Llegaste hasta acá desde…?

Si ves… Eso sale de… La lógica está en…
Cuántos mensajes se mandaron por día / template / canal solvento-adv-analytics-prod.messaging.messaging_daily dbt-etls → models/advanced_analytics/messaging/messaging_daily.sql
Open rate o click rate de un email Columnas open_rate / click_rate de messaging_daily …/messaging_daily.sql (numerador en stg_sendgrid_message_activity)
Read rate de un template de WhatsApp Columna read_rate de messaging_daily …/messaging_daily.sql (was_read en stg_bird_messages)
Un email individual (destinatario, estado, aperturas) solvento-adv-analytics-prod.sendgrid.stg_sendgrid_message_activity dbt-etls → models/advanced_analytics/sendgrid/stg_sendgrid_message_activity.sql
Un mensaje de WhatsApp individual (teléfono, estado) solvento-adv-analytics-prod.bird.stg_bird_messages dbt-etls → models/advanced_analytics/bird/stg_bird_messages.sql
El nombre de un template de email (viene como d-<hex>) …sendgrid.stg_sendgrid_templates Email Activity devuelve el id pero no el nombre; el join vive en messaging_daily.sql
"¿Estamos capturando todo el email?" …messaging.email_coverage_daily dbt-etls → models/advanced_analytics/messaging/email_coverage_daily.sql
El dato crudo de un envío solvento-data-prod.sendgrid.* / solvento-data-prod.bird.* Cloud Run (sin lógica de negocio)
Campo Valor
Origen SendGrid API v3 (/v3/messages, /v3/stats, /v3/templates, /v3/tracking_settings) · Bird Conversations API (/conversations, /conversations/{id}/messages)
Patrón Cloud Run agendado (pull con ventana, RAW append, dedup en dbt)
Destino RAW solvento-data-prod.sendgrid.* (4 tablas) · solvento-data-prod.bird.* (2 tablas)
Destino stg solvento-adv-analytics-prod.sendgrid.* (4) · …bird.* (2) — con PII
Destino mart solvento-adv-analytics-prod.messaging.* (2) — sin PII
Frecuencia SendGrid diario 07:00 MX (+ sweep sábados 08:00) · Bird diario 07:30 MX
Criticidad 🟡 Media (alimenta métricas de negocio; no bloquea producto ni cobranza)
Dueño Martin (data eng) · negocio: Oscar, Diego
Repos sendgrid-extractor · bird-extractor · dbt-etls · terraform-gcp-infrastructure

Diagrama de linaje

graph LR
    SCH1[["⏰ sendgrid-extractor-daily<br/>0 7 * * * (MX)"]]
    SCH2[["⏰ bird-extractor-daily<br/>30 7 * * * (MX)"]]
    SG[["📧 SendGrid API v3"]]
    BD[["💬 Bird / MessageBird<br/>Conversations API"]]
    CR1["Cloud Run<br/>sendgrid-extractor-prod"]
    CR2["Cloud Run<br/>bird-extractor-prod"]
    RAW1[("solvento-data-prod.sendgrid<br/>message_activity · stats_daily<br/>templates · tracking_settings")]
    RAW2[("solvento-data-prod.bird<br/>messages · conversations")]
    STG1[("adv-analytics.sendgrid<br/>stg_sendgrid_* (4) · PII")]
    STG2[("adv-analytics.bird<br/>stg_bird_* (2) · PII")]
    MART[("adv-analytics.messaging<br/>messaging_daily<br/>email_coverage_daily<br/>SIN PII")]
    CONS["Oscar / Diego<br/>(métricas de envío)"]

    SCH1 -->|dispara| CR1
    SCH2 -->|dispara| CR2
    SG -->|"Bearer · 6 req/min"| CR1 --> RAW1 -->|dbt-etls| STG1 --> MART
    BD -->|"AccessKey · limit<20"| CR2 --> RAW2 -->|dbt-etls| STG2 --> MART --> CONS

    classDef src fill:#fde68a,stroke:#b45309,color:#000;
    classDef store fill:#bfdbfe,stroke:#1e40af,color:#000;
    class SCH1,SCH2,SG,BD src;
    class RAW1,RAW2,STG1,STG2,MART store;

Paso a paso

  1. Cloud Scheduler dispara POST /extract sobre cada servicio (sendgrid-extractor-daily 07:00 MX, bird-extractor-daily 07:30 MX). retry_count = 0 a propósito: la ventana solapada hace que la corrida de mañana repare la de hoy, así que un reintento inmediato solo gasta rate limit.

  2. Cloud Run sendgrid-extractor-prod (repo, API key desde Secret Manager sendgrid_api_key). Pide en este orden:

    • /v3/stats — agregados diarios de la cuenta, ventana de 30 días.
    • /v3/tracking_settings — si el open/click tracking está prendido.
    • /v3/templates — catálogo (nombres).
    • /v3/messages — actividad por template, ventana de 3 días. Es la parte cara: 6 requests por minuto y tope de 1.000 resultados sin paginación, así que el extractor consulta template por template (solo los con actividad en 21 días) y parte la ventana en tramos cuando la página se llena. La corrida tarda ~13 min.

  3. Cloud Run bird-extractor-prod (repo, key desde Secret Manager messagebird_api_key):

    • lista conversaciones ordenadas por lastReceivedDatetime y corta al salir de la ventana de 2 días (limit debe ser < 20, no documentado).
    • por cada conversación pide sus mensajes en orden descendente y corta la paginación al salir de la ventana de 4 días. Sin ese corte se bajaba el historial completo de cada conversación (antigüedad media ~200 días).
    • guarda solo los type=hsm, sin contenido ni parámetros.

  4. RAW — las 6 tablas son WRITE_APPEND con duplicados permitidos, particionadas por ingested_at. Sin lógica de negocio. El mismo mensaje aparece varias veces porque su estado es mutable (processed→delivered en email, sent→delivered→read en WhatsApp).

  5. dbt-etls (models/advanced_analytics/{sendgrid,bird,messaging}/) deduplica quedándose con la última foto por clave, clasifica estados, y une los dos canales en el mart. Todo el dominio está tagueado: dbt build --select tag:messaging.

  6. Consumo — Oscar y Diego sobre messaging_daily; cualquier métrica que el mart no cubra sale de las stg, que están al grano del mensaje.

Tablas que alimenta

Tabla Contenido Cómo se carga
sendgrid.message_activity (RAW) Un evento-mensaje por fila (Email Activity) Cloud Run (append)
sendgrid.stats_daily (RAW) Agregados diarios de la cuenta Cloud Run (append, 30 días por corrida)
sendgrid.templates (RAW) Catálogo de templates Cloud Run (foto por corrida)
sendgrid.tracking_settings (RAW) Estado del open/click tracking Cloud Run (foto por corrida)
bird.messages (RAW) Un mensaje de WhatsApp por fila (solo hsm) Cloud Run (append)
bird.conversations (RAW) Conversaciones activas en la ventana Cloud Run (append)
sendgrid.stg_sendgrid_message_activity 1 email por fila, deduplicado. Con PII dbt-etls (table)
sendgrid.stg_sendgrid_stats_daily 1 día por fila (cuenta). Control del proceso dbt-etls (table)
sendgrid.stg_sendgrid_templates Catálogo deduplicado (nombres) dbt-etls (table)
sendgrid.stg_sendgrid_tracking_settings Flags de tracking de la cuenta dbt-etls (table)
bird.stg_bird_messages 1 mensaje de WhatsApp por fila. Con PII dbt-etls (table)
bird.stg_bird_conversations 1 conversación por fila dbt-etls (table)
messaging.messaging_daily MART. (send_date, channel, template_key) + tasas. Sin PII dbt-etls (table)
messaging.email_coverage_daily MART de control. Capturado vs. lo que reporta SendGrid dbt-etls (table)

Datos personales y frontera de acceso

La frontera es el dataset, no una columna enmascarada:

Dataset Contiene Acceso
solvento-data-prod.sendgrid / .bird (RAW) email, asunto, teléfono, contact_id en claro Restringido
solvento-adv-analytics-prod.sendgrid / .bird (stg) lo mismo Restringido
solvento-adv-analytics-prod.messaging (marts) solo conteos y tasas Se puede abrir

RAW se mantiene fiel a la fuente a propósito: destruir el dato en la ingesta es irreversible y el tratamiento de PII se va a definir de forma transversal para todo el warehouse, no proceso por proceso. Hasta que esa definición exista, la protección es por permisos y el filtrado se hace en dbt.

Limitaciones que hay que conocer antes de usar la data

  1. La fecha de envío de email es un estimado. La API de SendGrid no la devuelve: devuelve last_event_time, el momento del último evento. send_date se deriva del primer evento observado, con sesgo hacia adelante acotado a ~24 h. En WhatsApp es exacta (createdDatetime). La columna send_date_is_exact de messaging_daily lo dice fila por fila.

  2. Todo el dominio usa el calendario America/Mexico_City, no UTC. last_event_time viene en UTC pero /v3/stats agrega en la zona de la cuenta (UTC−6), y como los blasts salen a la tarde-noche de México, tomar el día en UTC corría envíos enteros al día siguiente. Medido el 2026-09-07: la cobertura del sábado pasaba de 3,85 (UTC) a 1,13 (México). Ambos canales usan el mismo huso para que sumar envíos por día no mezcle calendarios.

  3. open_rate y read_rate son pisos, por motivos distintos. En email, Email Activity retiene 3 días: una apertura al cuarto día no existe para nosotros. En WhatsApp, solo se re-consultan las conversaciones activas: si el cliente lee y no responde, el cambio de estado puede no llegar a verse. runs_observed_min mide cuánto se llegó a mirar. Medido el 2026-09-07, el sesgo es fuerte en email (12,8% de apertura en mensajes vistos 1 vez vs. 30-35% en los vistos 4-5 veces) y despreciable en WhatsApp.

  4. Un 0% de apertura con open_tracking_enabled = FALSE significa que no estábamos midiendo, no que nadie abrió. Un template con 0% de apertura Y 0% de clicks es indistinguible de uno sin tracking. La columna zero_engagement marca el caso.

  5. Solo comparar entre sí días con is_final = TRUE. Un día todavía abierto tiene los contadores a medio llenar y siempre se ve peor de lo que fue.

  6. El backfill de WhatsApp está incompleto y el sesgo crece hacia atrás. Tenemos 1.876 conversaciones; el extractor estima el total en ~9.600 (confirmar con /preview?full=true). Bajamos el historial completo, pero solo de los contactos que estaban activos cuando corrió el backfill, así que los meses de 2024 solo tienen mensajes de clientes que siguen vivos hoy. Sirve para la tendencia de los que se quedaron, no para "cuánto mandábamos en 2024".

  7. stats_daily tiene ~39 días solo porque nunca se corrió full=true. SendGrid guarda 37 meses de agregados y el extractor ya tiene STATS_FULL_START_DATE=2023-08-01: una corrida trae 3 años de volumen/aperturas/clicks a nivel cuenta (sin dimensión de template). Pendiente.

  8. La cobertura de email cae en los días de mayor volumen. Medido el 2026-09-07: 0,90–1,13 en días normales, pero 0,62 el viernes de 5.608 requests. Es el síntoma del tope de 1.000 por consulta cuando un blast grande satura la ventana. Se prueba subiendo ACTIVITY_MAX_SPLIT_DEPTH en el extractor. Pendiente.

Alternativas descartadas

Camino Por qué no
Export a CSV de Email Activity (/v3/messages/download) El download_uuid llega por correo a una casilla a la que no tenemos acceso. No es automatizable.
Airbyte + Meta / WhatsApp Business Platform Requiere acceso a la cuenta de Meta que nunca se gestionó. Además conversation_analytics devuelve costo nulo en cuentas de Solution Partner.
Endpoint /reporting de Bird Devuelve HTML con HTTP 200, no datos.
Webhooks (SendGrid Event Webhook / Bird) Es el camino correcto a futuro para near-real-time y para no depender de ventanas de retención. No implementado.

Consumo aguas abajo

  • Oscar y Diego — volumen diario por template y canal, tasas de entrega, apertura y lectura, sobre messaging_daily.
  • Analytics / Data — cualquier corte que el mart no cubra sale de las stg, que están al grano del mensaje individual (destinatario, estado, timestamps).
  • Monitoreo — 5 log-based metrics en el dashboard "Data Processes": messaging-extract-rows-total, messaging-extract-duration-seconds, messaging-extract-rate-limit-hits, messaging-extract-runs, messaging-bird-messages-without-template. Todas salen del log estructurado event="extract_run" que emite cada corrida (campos de jsonPayload, no regex sobre el texto).

Si falla 🛠️

Impacto: las métricas de envío quedan desactualizadas. No bloquea producto, cobranza ni facturación. En email el dato perdido no se recupera: si el extractor está caído más de 3 días, esos envíos no vuelven a estar disponibles nunca.

Síntoma Dónde mirar Acción
messaging_daily desactualizada Cloud Scheduler + logs de Cloud Run Ver si corrió; usar GET /preview para dry-run sin gastar rate limit
Métrica messaging-extract-runs sin puntos Dashboard "Data Processes" Es la alerta que importa: la ausencia, no un valor alto. El servicio no corrió
messaging-extract-rate-limit-hits alto Logs de sendgrid-extractor-prod 429 de Email Activity (6 req/min). Revisar ACTIVITY_DELAY_SECS
messaging-bird-messages-without-template alto Columnas template_path_found / content_keys en bird.messages Bird cambió la forma del bloque hsm; agregar la ruta nueva a RUTAS_NOMBRE_TEMPLATE
Test accepted_values de status o direction en warn dbt test --select tag:messaging Bird agregó un estado que no clasificamos; se cae en is_pending y desaparecen entregas del embudo. Agregarlo al modelo
Test test_sendgrid_coverage_vs_stats en warn messaging.email_coverage_daily Dejamos de cubrir algo. Mirar primero activity_templates: si bajó, el extractor no está consultando templates que sí enviaron
Test test_bird_messages_vs_conversation_total en warn — Nuestro conteo superó al de Bird: la dedup por message_id está rota. Verificar si unique sobre message_id también falló
401/403 contra las APIs Logs de Cloud Run Secrets sendgrid_api_key / messagebird_api_key en Secret Manager (creados a mano, no por Terraform)
Todo en cero para un template de email stg_sendgrid_tracking_settings Si open/click están apagados, el número no significa "nadie abrió"

Escalación: extractores / modelos dbt / infra → Martin · métricas y definición de negocio → Oscar y Diego.

⚠️ Antes de tocar el rate limit: las API keys son las mismas que puede estar usando el backend para enviar. Saturar el rate limit de lectura puede afectar envíos reales. No subir la frecuencia sin verificar con backend.

Enlaces

Última actualización: 2026-09-09 · Pendientes: backfill de stats_daily con full=true (3 años, 1 request); backfill completo de conversaciones de Bird; cobertura de email en días de alto volumen; webhooks. Si cambia el flujo, actualizar esta ficha en el mismo MR.