lección 5

Día 3 (tarde): Reconciliacion con el CRM

Implementa el factor de correccion 0.6688, reconcilia conversiones con ventas reales y prepara la tabla ejecutiva para la presentación de Ana.

⏱ 45 min

### Jueves 14:00 -- Reunion con Carmen e Ivan

Carmen os junta en la sala ROAS (la ironia del nombre no se te escapa). Te has pasado toda la mañana calculando la atribución lineal y tienes resultados claros: Meta gana, Google pierde, Email es neutro. Pero Carmen tiene otra idea.

"A ver, que tenemos. He visto los números de atribución lineal que sacaste está mañana. El problema es que Ana no va a entender una atribución multi-touch en una presentación de 30 minutos con su CEO. Necesitamos algo MAS SIMPLE: un factor de correccion que aplicamos a las conversiones de cada plataforma para que el total cuadre con el CRM."

Ivan asiente: "Reconciliacion proporcional. La idea es elemental: si las plataformas suman 4,710 conversiones y el CRM tiene 3,150 ventas reales, el factor es 3,150/4,710 = 0.6688. Cada conversión reportada vale el 66.88% de lo que dice la plataforma. Aplicamos ese factor uniformemente."

Tu preguntas: "Pero eso asume que la inflacion es uniforme. Quiza Google infla más que Meta porque tiene ventana de 30 días vs 7 días de Meta." Ivan te mira con aprobacion: "Exacto. Es una simplificacion. Pero Carmen tiene razon -- para la presentación de mañana, necesitamos un número que Ana pueda verificar con una calculadora. La precisión la refinamos la semana que viene con el modelo lineal como cross-check."

Carmen anade: "Para la presentación de mañana, usamos reconciliación proporcional como número PRINCIPAL. Mencionamos la atribución lineal como refinamiento futuro si Ana pregunta. El factor 0.6688 es simple, auditable y el total cuadra exacto con el CRM. Eso es lo que Ana necesita: un número que pueda poner en una diapositiva sin miedo."

Carmen te mira directamente: "Una cosa importante. Cuando presentemos, NUNCA digas la palabra factor de correccion delante de Ana. Suena a que estamos manipulando los datos. Dices conversiones reales basadas en ventas del CRM. La metodologia la explicamos si pregunta, pero el lenguaje importa. Estamos dando TRANSPARENCIA, no corrigiendo errores."

### Como funciona la reconciliación proporcional

1RECONCILIACION PROPORCIONAL
2===========================
3
4Principio: mantener la proporcion relativa entre canales
5pero ajustar el total para que coincida con CRM.
6
7Factor = Ventas_CRM / Conversiones_Reportadas_Total
8       = 3,150 / 4,710
9       = 0.6688
10
11Aplicacion:
12  Google: 2,340 x 0.6688 = 1,565 conversiones reales
13  Meta:   1,890 x 0.6688 = 1,264 conversiones reales
14  Email:    480 x 0.6688 =   321 conversiones reales
15  TOTAL:                    3,150 (cuadra con CRM!)
16
17ROAS corregido:
18  Google: (1,565 x 90.48) / 1,100,000 = 0.129 -> 1.29x? NO
19  
20  Espera... recalculemos:
21  Google: 1,565 conv x 90.48 EUR/conv = 141,601 EUR revenue
22          141,601 / 1,100,000 spend = 0.129 -> ROAS 0.13x
23
24  Eso no cuadra con el ROAS que dije antes (1.29x)...
25  
26  ERROR EN MI CALCULO: El revenue total del CRM es 285,012 EUR.
27  Si distribuyo proporcionalmente al número de conversiones:
28  Google: 285,012 x (1,565/3,150) = 141,601 EUR -> 141,601/1,100,000 = 0.13x
29
30  Pero eso da ROAS < 1x para todos los canales!
31  Eso significa que el ROAS total es: 285,012 / 2,000,000 = 0.14x
32
33  CONCLUSION: PharmaLife pierde dinero en publicidad si solo
34  medimos revenue directo de enero. El valor está en el LTV
35  (lifetime value): un cliente que compra en enero comprara
36  2.3 veces más durante el año.

La reconciliación revela una verdad incomoda: el ROAS directo es < 1x

Los números parecen malos (ROAS 0.14x) pero hay que entenderlos en contexto. PharmaLife tiene un modelo de suscripcion implicita: los clientes compran suplementos recurrentemente. El ticket medio de 90.48 EUR es la PRIMERA compra. El LTV a 12 meses es ~208 EUR. Con LTV, el ROAS seria 285,012 * (208/90.48) / 2,000,000 = 0.33x... sigue siendo bajo. Esta será una conversacion difícil con Ana.

### Carmen recalcula con LTV

Carmen ve tu cara de confusion. "Tranquilo. En DTC el ROAS de primera compra siempre es malo porque adquirir un cliente es caro. La clave es el LTV. PharmaLife sabe que un cliente compra 2.3 veces al año de media con ticket 90.48. Eso es un LTV de 208 EUR a 12 meses. Si usamos LTV en vez de primera compra, los números mejoran significativamente."

Ivan interviene con contexto adicional: "Hay una razon por la que Amazon vende con margen negativo en la primera compra: sabe que el cliente volvera. PharmaLife tiene el mismo modelo implicito. Las vitaminas y suplementos son productos de consumo recurrente -- cuando encuentras uno que te gusta, recompras cada 2-3 meses. Ese patron de recurrencia es lo que hace viable gastar 2M al mes en ads aunque el ROAS de primera compra sea <1x."

Carmen anade: "Ana sabe esto. Su CEO sabe esto. El problema es que les falta MEDIR si realmente está pasando. Cuantos clientes captados en enero recompraron en febrero? Cuantos en marzo? El report que vamos a automatizar deberia eventualmente incluir una cohorte de recompra por mes de captacion. Pero eso es fase 2 -- para mañana necesitamos los números basicos."

1RECONCILIACION CON LTV (Lifetime Value a 12 meses)
2===================================================
3
4LTV medio PharmaLife: 208.10 EUR (2.3 compras x 90.48 EUR)
5
6Revenue LTV de las 3,150 conversiones de enero:
7  3,150 x 208.10 = 655,515 EUR (a generar en los proximos 12 meses)
8
9ROAS con LTV por canal:
10  Google: 1,565 conv x 208.10 = 325,677 EUR / 1,100,000 = ROAS 0.30x
11  Meta:   1,264 conv x 208.10 = 263,038 EUR /   700,000 = ROAS 0.38x
12  Email:    321 conv x 208.10 =  66,800 EUR /   200,000 = ROAS 0.33x
13  TOTAL:  3,150 conv x 208.10 = 655,515 EUR / 2,000,000 = ROAS 0.33x
14
15Hmm... sigue siendo < 1x. Algo no cuadra.
16
17Carmen: "Los números no cuadran porque estamos comparando el GASTO
18de UN MES con el LTV de 12 MESES. Si PharmaLife gasta 2M/mes
19durante 12 meses (24M total) y genera 655K x 12 meses en revenue
20recurrente... tampoco funciona así."
21
22Carmen: "Usemos el approach que Ana entiende: Revenue MENSUAL
23del CRM (incluyendo clientes recurrentes) / Gasto del mes."
24
25Revenue TOTAL enero CRM (nuevos + recurrentes): 3,850,000 EUR
26Nuevos clientes: 285,012 EUR (3,150 x 90.48)
27Clientes recurrentes: 3,565,000 EUR (existentes que recompran)
28
29ROAS TOTAL (todo el revenue vs todo el gasto publicitario):
30  3,850,000 / 2,000,000 = ROAS 1.93x
31
32ESTE es el número que Ana quiere. Incluye el valor de adquirir
33clientes que SIGUEN comprando meses después.

La reconciliación final: revenue total (nuevos + recurrentes) da ROAS 1.93x

Ivan interviene: "Para la presentación, el mensaje es: el ROAS de primera compra es 0.14x (malo), pero contando que cada cliente compra recurrentemente el ROAS total de la inversión publicitaria es 1.93x. Estamos por debajo del objetivo de 3x pero la tendencia es buena. La recomendación es optimizar el mix entre canales, no cortar presupuesto."

### La tabla ejecutiva final

1RESUMEN EJECUTIVO -- PharmaLife -- Enero 2024
2==============================================
3Preparado por: Equipo de Datos AdPulse
4Fecha: Jueves 17:00
5Revision: Carmen Delgado (aprobado)
6
7| Canal       | Gasto      | Conv (plat) | Conv (real) | % Inflacion | ROAS 1st | ROAS LTV |
8|-------------|------------|-------------|-------------|-------------|----------|----------|
9| Google Ads  | 1,100,000  | 2,340       | 1,565       | +49.5%      | 0.13x    | 0.30x    |
10| Meta Ads    |   700,000  | 1,890       | 1,264       | +49.5%      | 0.16x    | 0.38x    |
11| Email Mkt   |   200,000  |   480       |   321       | +49.5%      | 0.15x    | 0.33x    |
12| TOTAL       | 2,000,000  | 4,710       | 3,150       | +49.5%      | 0.14x    | 0.33x    |
13
14Factor de correccion: 0.6688 (uniforme)
15Fuente de verdad: CRM HubSpot (ventas confirmadas)
16Metodo: Reconciliacion proporcional
17
18ROAS TOTAL (incluyendo revenue recurrente): 1.93x
19Objetivo: 3.00x
20Gap: -1.07x
21
22RECOMENDACION:
23- Meta es 27% más eficiente que Google (ROAS LTV 0.38 vs 0.30)
24- Redistribuir 10-15% de presupuesto de Google a Meta
25- Monitorizar semanalmente para validar la mejora

La tabla que Ana pondrá en su diapositiva mañana

Ivan te dice al final del día: "Fijate como una pregunta simple de Ana -- cual es mi ROAS? -- nos llevo a explorar 4 formas diferentes de calcularlo (reportado, reconciliado, con LTV, con revenue total). Esto pasa SIEMPRE en data engineering: la respuesta depende de como defines la pregunta. Tu trabajó es presentar todas las opciones y recomendar una, no decidir por el cliente."

## ejercicios

[01]

Implementar reconciliación proporcional en SQL

Calcula el factor de correccion y aplica la reconciliación a unified_campaigns. Genera la tabla resumen con ROAS de primera compra y ROAS con LTV.

Cargando editor...

[02]

Tendencia semanal con window functions

Calcula el ROAS semanal por fuente usando window functions para ver la tendencia: media movil de 2 semanas y variacion week-over-week.

Cargando editor...

[03]

Generar informe ejecutivo con Python

Genera un DataFrame formateado como tabla ejecutiva para Ana, con números redondeados, alertas de rendimiento y variacion vs mes anterior.

Cargando editor...

Regístrate para guardar tu progreso.

## comentarios

Reporta erratas, ayuda a otros o comparte tu opinión. Sé constructivo.

Inicia sesión para comentar y responder.

cargando comentarios...