Marketing que se mide. Sistemas que escalan.

# Webhook de Meta Lead Ads  →  lead en el CRM de Odoo (con UTM)import os, xmlrpc.client URL, DB = os.environ["ODOO_URL"], os.environ["ODOO_DB"]USER, KEY = os.environ["ODOO_USER"], os.environ["ODOO_API_KEY"] uid = xmlrpc.client.ServerProxy(f"{URL}/xmlrpc/2/common").authenticate(DB, USER, KEY, {})odoo = xmlrpc.client.ServerProxy(f"{URL}/xmlrpc/2/object") def call(model, method, *args, **kw):    return odoo.execute_kw(DB, uid, KEY, model, method, list(args), kw) def utm(model, name):    """Busca la fuente/medio/campaña UTM y la crea si no existe."""    ids = call(model, "search", [("name", "=", name)], limit=1)    return ids[0] if ids else call(model, "create", {"name": name}) def lead_from_meta(ev: dict) -> int:    f = {x["name"]: x["values"][0] for x in ev["field_data"]}    return call("crm.lead", "create", {        "name": f"Meta · {ev['campaign_name']}",        "contact_name": f.get("full_name"),        "phone": f.get("phone_number"),        "email_from": f.get("email"),        "source_id": utm("utm.source", "Facebook"),        "medium_id": utm("utm.medium", "Paid Social"),        "campaign_id": utm("utm.campaign", ev["campaign_name"]),    })

// Venta confirmada en Odoo  →  evento Purchase a la API de Conversiones de Metaimport { createHash } from "node:crypto"; const sha256 = (v) => createHash("sha256").update(v).digest("hex");const email = (v) => sha256(v.trim().toLowerCase());const phone = (v) => sha256(v.replace(/\D/g, ""));        // 591XXXXXXXX export async function purchaseToMeta(order) {  const event = {    event_name: "Purchase",    event_time: Math.floor(Date.now() / 1000),    action_source: "physical_store",    event_id: `sale-${order.id}`,                         // deduplica con el píxel    user_data: {      em: order.email ? [email(order.email)] : undefined,      ph: order.phone ? [phone(order.phone)] : undefined,    },    custom_data: { currency: "BOB", value: order.amount_total },  };   const url = `https://graph.facebook.com/v21.0/${process.env.META_PIXEL_ID}/events`;  const res = await fetch(`${url}?access_token=${process.env.META_CAPI_TOKEN}`, {    method: "POST",    headers: { "Content-Type": "application/json" },    body: JSON.stringify({ data: [event] }),  });  if (!res.ok) throw new Error(`CAPI ${res.status}: ${await res.text()}`);  return res.json();                                      // { events_received: 1 }}

-- ROAS real por campaña: ventas confirmadas en Odoo vs. gasto en anuncios-- ad_spend: tabla propia que n8n llena cada noche desde Meta y Google AdsWITH ventas AS (  SELECT so.campaign_id,         COUNT(*)                AS pedidos,         SUM(so.amount_untaxed)  AS ingresos  FROM sale_order so  WHERE so.state = 'sale'    AND so.date_order >= date_trunc('month', now())  GROUP BY so.campaign_id),gasto AS (  SELECT campaign_id, SUM(amount) AS gasto  FROM ad_spend  WHERE day >= date_trunc('month', now())  GROUP BY campaign_id)SELECT c.name                                         AS campania,       v.pedidos,       ROUND(v.ingresos, 2)                           AS ingresos_bs,       ROUND(g.gasto, 2)                              AS gasto_bs,       ROUND(v.ingresos / NULLIF(g.gasto, 0), 2)      AS roas,       ROUND(g.gasto / NULLIF(v.pedidos, 0), 2)       AS cac_bsFROM utm_campaign cJOIN ventas v ON v.campaign_id = c.idLEFT JOIN gasto g ON g.campaign_id = c.idORDER BY roas DESC NULLS LAST;