Analytics propio en tu landingsaber qué sección leen y dónde se van, sin Google Analytics

export type TrackerEventType =
  | "session_init" | "heartbeat" | "section_view" | "section_leave"
  | "click" | "scroll" | "form_focus" | "conversion" | "exit";

export interface TrackerEvent {
  type: TrackerEventType;
  target?: string;
  value?: string;
  section_id?: string;
  scroll_pct?: number;
  dwell_ms?: number;
  meta?: Record<string, unknown>;
  t?: number;
}

export interface SessionContext {
  session_id: string;
  visitor_id: string;
  device_type: string;
  viewport_w: number;
  viewport_h: number;
  language: string;
  timezone: string;
  referrer_host: string;
  landing_path: string;
  utm_source: string;
  utm_medium: string;
  utm_campaign: string;
  utm_content: string;
  utm_term: string;
  fbclid: string;
  gclid: string;
}

const ENDPOINT = "/api/track";
const FLUSH_MS = 3_000;
const FLUSH_SIZE = 10;
const HEARTBEAT_MS = 10_000;
const SCROLL_MARKS = [25, 50, 75, 90, 100];

function uuid(): string {
  if (typeof crypto !== "undefined" && "randomUUID" in crypto) return crypto.randomUUID();
  return "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx".replace(/[xy]/g, (c) => {
    const r = (Math.random() * 16) | 0;
    return (c === "x" ? r : (r & 0x3) | 0x8).toString(16);
  });
}

function storedId(storage: Storage, key: string): string {
  try {
    const existing = storage.getItem(key);
    if (existing) return existing;
    const fresh = uuid();
    storage.setItem(key, fresh);
    return fresh;
  } catch {
    return uuid();
  }
}

function deviceType(): string {
  const ua = navigator.userAgent;
  const tablet = /iPad|Tablet|Silk/i.test(ua) || (/Android/i.test(ua) && !/Mobile/i.test(ua));
  if (tablet) return "tablet";
  return /Mobile|Android|iPhone|iPod/i.test(ua) ? "mobile" : "desktop";
}

function buildContext(): SessionContext {
  const q = new URLSearchParams(window.location.search);
  const get = (k: string): string => q.get(k) ?? "";
  let referrer_host = "";
  try { referrer_host = document.referrer ? new URL(document.referrer).host : ""; } catch { /* vacío */ }
  return {
    session_id: storedId(sessionStorage, "_sid"),
    visitor_id: storedId(localStorage, "_vid"),
    device_type: deviceType(),
    viewport_w: window.innerWidth,
    viewport_h: window.innerHeight,
    language: navigator.language ?? "",
    timezone: Intl.DateTimeFormat().resolvedOptions().timeZone ?? "",
    referrer_host,
    landing_path: window.location.pathname + window.location.search,
    utm_source: get("utm_source"),
    utm_medium: get("utm_medium"),
    utm_campaign: get("utm_campaign"),
    utm_content: get("utm_content"),
    utm_term: get("utm_term"),
    fbclid: get("fbclid"),
    gclid: get("gclid"),
  };
}

class Tracker {
  private ctx: SessionContext;
  private queue: TrackerEvent[] = [];
  private flushTimer: number | null = null;
  private sectionEnter: Record<string, number> = {};
  private maxScroll = 0;
  private lastTick = Date.now();
  private totalMs = 0;
  private visible = true;
  private started = false;
  private exited = false;

  constructor() { this.ctx = buildContext(); }

  start(): void {
    if (this.started) return;
    this.started = true;
    this.track({ type: "session_init", meta: { ctx: this.ctx } });

    window.setInterval(() => this.heartbeat(), HEARTBEAT_MS);

    document.addEventListener("visibilitychange", () => {
      if (document.visibilityState === "hidden") {
        this.heartbeat();
        this.visible = false;
        this.flushBeacon();
      } else {
        this.visible = true;
        this.exited = false;
        this.lastTick = Date.now();
      }
    });
    window.addEventListener("pagehide", () => this.flushBeacon());

    let ticking = false;
    window.addEventListener("scroll", () => {
      if (ticking) return;
      ticking = true;
      requestAnimationFrame(() => {
        const pct = this.scrollPct();
        if (pct > this.maxScroll) {
          const crossed = SCROLL_MARKS.filter((m) => m > this.maxScroll && m <= pct);
          this.maxScroll = pct;
          for (const m of crossed) this.track({ type: "scroll", scroll_pct: m });
        }
        ticking = false;
      });
    }, { passive: true });

    this.observeSections();

    document.addEventListener("click", (ev) => {
      const el = (ev.target as HTMLElement | null)?.closest<HTMLElement>("[data-track], a, button");
      if (!el) return;
      this.track({
        type: "click",
        target: el.getAttribute("data-track") ?? this.labelOf(el),
        value: el instanceof HTMLAnchorElement ? el.href : "",
        section_id: this.sectionOf(el),
      });
    }, { capture: true });

    let formFocused = false;
    document.addEventListener("focusin", (ev) => {
      if (formFocused) return;
      const el = ev.target as HTMLElement;
      if (!el.matches("input, textarea, select")) return;
      formFocused = true;
      this.track({
        type: "form_focus",
        target: el.getAttribute("name") ?? el.id ?? "campo",
        section_id: this.sectionOf(el),
      });
    });
  }

  conversion(meta: Record<string, unknown> = {}): void {
    this.track({ type: "conversion", meta });
    this.flush();
  }

  track(ev: TrackerEvent): void {
    this.queue.push({ ...ev, t: Date.now() });
    if (this.queue.length >= FLUSH_SIZE) this.flush();
    else this.scheduleFlush();
  }

  private heartbeat(): void {
    if (!this.visible) return;
    const now = Date.now();
    const delta = now - this.lastTick;
    this.lastTick = now;
    this.totalMs += delta;
    this.track({ type: "heartbeat", dwell_ms: delta, scroll_pct: this.maxScroll, meta: { total_ms: this.totalMs } });
  }

  private scrollPct(): number {
    const h = document.documentElement;
    const total = (h.scrollHeight - h.clientHeight) || 1;
    return Math.min(100, Math.max(0, Math.round((h.scrollTop / total) * 100)));
  }

  private observeSections(): void {
    const sections = document.querySelectorAll<HTMLElement>("[data-section]");
    if (!sections.length) return;
    const obs = new IntersectionObserver((entries) => {
      for (const e of entries) {
        const id = (e.target as HTMLElement).dataset.section ?? "";
        if (!id) continue;
        if (e.isIntersecting) {
          this.sectionEnter[id] = Date.now();
          this.track({ type: "section_view", section_id: id });
        } else if (this.sectionEnter[id]) {
          const dwell = Date.now() - this.sectionEnter[id];
          delete this.sectionEnter[id];
          this.track({ type: "section_leave", section_id: id, dwell_ms: dwell });
        }
      }
    }, { threshold: 0.5 });
    sections.forEach((s) => obs.observe(s));
  }

  private closeOpenSections(): void {
    const now = Date.now();
    for (const [id, since] of Object.entries(this.sectionEnter)) {
      this.queue.push({ type: "section_leave", section_id: id, dwell_ms: now - since, t: now });
      this.sectionEnter[id] = now;
    }
  }

  private sectionOf(el: HTMLElement): string {
    return el.closest<HTMLElement>("[data-section]")?.dataset.section ?? "";
  }

  private labelOf(el: HTMLElement): string {
    const text = (el.textContent ?? "").trim().slice(0, 60);
    return el.getAttribute("aria-label") ?? (el.id || text || el.tagName.toLowerCase());
  }

  private scheduleFlush(): void {
    if (this.flushTimer != null) return;
    this.flushTimer = window.setTimeout(() => this.flush(), FLUSH_MS);
  }

  private flush(): void {
    if (this.flushTimer != null) { window.clearTimeout(this.flushTimer); this.flushTimer = null; }
    if (this.queue.length === 0) return;
    const batch = this.queue.splice(0, this.queue.length);
    const body = JSON.stringify({ ctx: this.ctx, events: batch });
    fetch(ENDPOINT, { method: "POST", headers: { "Content-Type": "application/json" }, body, keepalive: true })
      .catch(() => { this.queue.unshift(...batch); });
  }

  private flushBeacon(): void {
    if (this.exited) return;
    this.exited = true;
    this.closeOpenSections();
    this.queue.push({ type: "exit", scroll_pct: this.maxScroll, dwell_ms: this.totalMs, t: Date.now() });
    const body = JSON.stringify({ ctx: this.ctx, events: this.queue });
    this.queue = [];
    if ("sendBeacon" in navigator) {
      navigator.sendBeacon(ENDPOINT, new Blob([body], { type: "application/json" }));
    } else {
      fetch(ENDPOINT, { method: "POST", headers: { "Content-Type": "application/json" }, body, keepalive: true }).catch(() => {});
    }
  }
}

let singleton: Tracker | null = null;

export function getTracker(): Tracker {
  if (!singleton) singleton = new Tracker();
  return singleton;
}

export function initTracker(): void {
  if (typeof window === "undefined") return;
  getTracker().start();
}

"use client";

import { useEffect } from "react";
import { initTracker } from "@/lib/tracker";

export function TrackerMount(): null {
  useEffect(() => { initTracker(); }, []);
  return null;
}

// En app/layout.tsx (o el layout de la landing):
// <body>{children}<TrackerMount /></body>
//
// En el formulario, tras el submit exitoso:
// import { getTracker } from "@/lib/tracker";
// getTracker().conversion({ form: "registro" });

import { NextRequest, NextResponse } from "next/server";
import { createClient } from "@supabase/supabase-js";

const FUNNEL = process.env.FUNNEL_SLUG ?? "landing";

const db = createClient(
  process.env.SUPABASE_URL as string,
  process.env.SUPABASE_SERVICE_ROLE_KEY as string,
  { auth: { persistSession: false } },
);

interface EventIn {
  type: string;
  target?: string;
  value?: string;
  section_id?: string;
  scroll_pct?: number;
  dwell_ms?: number;
  meta?: Record<string, unknown>;
  t?: number;
}

interface ContextIn {
  session_id: string;
  visitor_id?: string;
  device_type?: string;
  viewport_w?: number;
  viewport_h?: number;
  language?: string;
  timezone?: string;
  referrer_host?: string;
  landing_path?: string;
  utm_source?: string;
  utm_medium?: string;
  utm_campaign?: string;
  utm_content?: string;
  utm_term?: string;
  fbclid?: string;
  gclid?: string;
}

interface PayloadIn { ctx: ContextIn; events: EventIn[] }

interface SessionSnapshot {
  total_time_ms: number;
  max_scroll_pct: number;
  click_count: number;
  event_count: number;
  converted: boolean;
  converted_at: string | null;
}

const BOT_UA = /bot|crawler|spider|headless|lighthouse|pagespeed|preview|pingdom|uptimerobot|wget|curl/i;

function cut(s: string | undefined, max: number): string {
  return (s ?? "").toString().slice(0, max);
}

function utm(value: string | undefined): string {
  let s = (value ?? "").trim();
  try { s = decodeURIComponent(s.replace(/\+/g, " ")); } catch { /* se queda como llegó */ }
  s = s.trim();
  return /^\{\{.+\}\}$/.test(s) ? "" : s;
}

function geo(req: NextRequest, header: string): string {
  try { return decodeURIComponent(req.headers.get(header) ?? ""); } catch { return ""; }
}

export async function POST(req: NextRequest): Promise<NextResponse> {
  let payload: PayloadIn;
  try {
    payload = (await req.json()) as PayloadIn;
  } catch {
    return NextResponse.json({ ok: false, error: "invalid_json" }, { status: 400 });
  }

  const { ctx, events } = payload;
  if (!ctx?.session_id || !Array.isArray(events) || events.length === 0 || events.length > 100) {
    return NextResponse.json({ ok: false, error: "bad_payload" }, { status: 400 });
  }

  const userAgent = req.headers.get("user-agent") ?? "";
  const ip = (req.headers.get("x-forwarded-for") ?? "").split(",")[0].trim();

  const sessionRow = {
    session_id: cut(ctx.session_id, 64),
    funnel_slug: FUNNEL,
    visitor_id: cut(ctx.visitor_id, 64),
    ip: cut(ip, 64),
    ip_country: geo(req, "x-vercel-ip-country"),
    ip_region: geo(req, "x-vercel-ip-country-region"),
    ip_city: geo(req, "x-vercel-ip-city"),
    user_agent: cut(userAgent, 512),
    device_type: cut(ctx.device_type, 16),
    viewport_w: ctx.viewport_w ?? 0,
    viewport_h: ctx.viewport_h ?? 0,
    language: cut(ctx.language, 16),
    timezone: cut(ctx.timezone, 64),
    referrer_host: cut(ctx.referrer_host, 256),
    landing_path: cut(ctx.landing_path, 512),
    utm_source: cut(utm(ctx.utm_source), 128),
    utm_medium: cut(utm(ctx.utm_medium), 128),
    utm_campaign: cut(utm(ctx.utm_campaign), 256),
    utm_content: cut(utm(ctx.utm_content), 256),
    utm_term: cut(utm(ctx.utm_term), 256),
    fbclid: cut(ctx.fbclid, 256),
    gclid: cut(ctx.gclid, 256),
    is_bot: BOT_UA.test(userAgent),
  };

  const { error: sessErr } = await db
    .from("sessions")
    .upsert(sessionRow, { onConflict: "session_id", ignoreDuplicates: true });
  if (sessErr) {
    return NextResponse.json({ ok: false, error: sessErr.message }, { status: 500 });
  }

  let dwell = 0;
  let maxScroll = 0;
  let clicks = 0;
  let converted = false;
  const dwellBySection: Record<string, number> = {};

  const rows = events.map((ev) => {
    if (ev.type === "heartbeat" && typeof ev.dwell_ms === "number") dwell += ev.dwell_ms;
    if (typeof ev.scroll_pct === "number" && ev.scroll_pct > maxScroll) maxScroll = ev.scroll_pct;
    if (ev.type === "click") clicks += 1;
    if (ev.type === "conversion") converted = true;
    if (ev.type === "section_leave" && ev.section_id && typeof ev.dwell_ms === "number") {
      dwellBySection[ev.section_id] = (dwellBySection[ev.section_id] ?? 0) + ev.dwell_ms;
    }
    return {
      session_id: sessionRow.session_id,
      funnel_slug: FUNNEL,
      event_type: cut(ev.type, 32),
      event_target: cut(ev.target, 256),
      event_value: cut(ev.value, 512),
      section_id: cut(ev.section_id, 64),
      scroll_pct: ev.scroll_pct ?? null,
      dwell_ms: ev.dwell_ms ?? null,
      meta: ev.meta ?? {},
      created_at: ev.t ? new Date(ev.t).toISOString() : new Date().toISOString(),
    };
  });

  const { error: evErr } = await db.from("track_events").insert(rows);
  if (evErr) {
    return NextResponse.json({ ok: false, error: evErr.message }, { status: 500 });
  }

  const { data: curr } = await db
    .from("sessions")
    .select("total_time_ms, max_scroll_pct, click_count, event_count, converted, converted_at")
    .eq("session_id", sessionRow.session_id)
    .maybeSingle<SessionSnapshot>();

  const c: SessionSnapshot = curr ?? {
    total_time_ms: 0, max_scroll_pct: 0, click_count: 0, event_count: 0, converted: false, converted_at: null,
  };

  await db.from("sessions").update({
    total_time_ms: c.total_time_ms + dwell,
    max_scroll_pct: Math.max(c.max_scroll_pct, maxScroll),
    click_count: c.click_count + clicks,
    event_count: c.event_count + rows.length,
    last_seen_at: new Date().toISOString(),
    converted: c.converted || converted,
    converted_at: c.converted_at ?? (converted ? new Date().toISOString() : null),
  }).eq("session_id", sessionRow.session_id);

  for (const [sectionId, ms] of Object.entries(dwellBySection)) {
    await db.rpc("upsert_section_dwell", {
      p_session_id: sessionRow.session_id,
      p_section_id: sectionId,
      p_ms: ms,
    });
  }

  return NextResponse.json({ ok: true, count: rows.length });
}

-- 1. Una fila por sesión, con el contexto y los agregados de esa sesión
CREATE TABLE IF NOT EXISTS sessions (
  session_id      TEXT        PRIMARY KEY,
  funnel_slug     TEXT        NOT NULL DEFAULT 'landing',
  visitor_id      TEXT        NOT NULL DEFAULT '',
  first_seen_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  total_time_ms   BIGINT      NOT NULL DEFAULT 0,
  max_scroll_pct  INTEGER     NOT NULL DEFAULT 0,
  event_count     INTEGER     NOT NULL DEFAULT 0,
  click_count     INTEGER     NOT NULL DEFAULT 0,
  converted       BOOLEAN     NOT NULL DEFAULT false,
  converted_at    TIMESTAMPTZ,
  ip              TEXT        NOT NULL DEFAULT '',
  ip_country      TEXT        NOT NULL DEFAULT '',
  ip_region       TEXT        NOT NULL DEFAULT '',
  ip_city         TEXT        NOT NULL DEFAULT '',
  user_agent      TEXT        NOT NULL DEFAULT '',
  device_type     TEXT        NOT NULL DEFAULT '',
  viewport_w      INTEGER     NOT NULL DEFAULT 0,
  viewport_h      INTEGER     NOT NULL DEFAULT 0,
  language        TEXT        NOT NULL DEFAULT '',
  timezone        TEXT        NOT NULL DEFAULT '',
  referrer_host   TEXT        NOT NULL DEFAULT '',
  landing_path    TEXT        NOT NULL DEFAULT '/',
  utm_source      TEXT        NOT NULL DEFAULT '',
  utm_medium      TEXT        NOT NULL DEFAULT '',
  utm_campaign    TEXT        NOT NULL DEFAULT '',
  utm_content     TEXT        NOT NULL DEFAULT '',
  utm_term        TEXT        NOT NULL DEFAULT '',
  fbclid          TEXT        NOT NULL DEFAULT '',
  gclid           TEXT        NOT NULL DEFAULT '',
  is_bot          BOOLEAN     NOT NULL DEFAULT false
);
CREATE INDEX IF NOT EXISTS idx_sessions_first_seen ON sessions (funnel_slug, first_seen_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_source     ON sessions (funnel_slug, utm_source);

-- 2. Eventos crudos. Es la tabla que crece; nunca se consulta entera desde el dashboard
CREATE TABLE IF NOT EXISTS track_events (
  id           BIGSERIAL   PRIMARY KEY,
  session_id   TEXT        NOT NULL,
  funnel_slug  TEXT        NOT NULL DEFAULT 'landing',
  event_type   TEXT        NOT NULL,
  event_target TEXT        NOT NULL DEFAULT '',
  event_value  TEXT        NOT NULL DEFAULT '',
  section_id   TEXT        NOT NULL DEFAULT '',
  scroll_pct   INTEGER,
  dwell_ms     BIGINT,
  meta         JSONB       NOT NULL DEFAULT '{}'::jsonb,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_events_session ON track_events (session_id);
CREATE INDEX IF NOT EXISTS idx_events_type    ON track_events (funnel_slug, event_type, created_at DESC);

-- 3. Dwell acumulado por sesión y sección (lo que alimenta el mapa de calor)
CREATE TABLE IF NOT EXISTS section_dwell (
  session_id   TEXT        NOT NULL,
  section_id   TEXT        NOT NULL,
  total_ms     BIGINT      NOT NULL DEFAULT 0,
  view_count   INTEGER     NOT NULL DEFAULT 0,
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (session_id, section_id)
);
CREATE INDEX IF NOT EXISTS idx_section_dwell_section ON section_dwell (section_id);

-- 4. Upsert atómico: dos lotes de la misma sesión no se pisan
CREATE OR REPLACE FUNCTION upsert_section_dwell(p_session_id TEXT, p_section_id TEXT, p_ms BIGINT)
RETURNS VOID AS $$
BEGIN
  INSERT INTO section_dwell (session_id, section_id, total_ms, view_count, last_seen_at)
  VALUES (p_session_id, p_section_id, p_ms, 1, now())
  ON CONFLICT (session_id, section_id) DO UPDATE
    SET total_ms     = section_dwell.total_ms + EXCLUDED.total_ms,
        view_count   = section_dwell.view_count + 1,
        last_seen_at = now();
END;
$$ LANGUAGE plpgsql;

-- 5. Rollup diario: una fila por embudo y día (en tu zona horaria). El dashboard lee de aquí
CREATE TABLE IF NOT EXISTS stats_daily (
  funnel_slug        TEXT    NOT NULL,
  day                DATE    NOT NULL,
  sessions           INTEGER NOT NULL DEFAULT 0,
  qualified_sessions INTEGER NOT NULL DEFAULT 0,   -- >= 3 s visibles
  conversions        INTEGER NOT NULL DEFAULT 0,
  avg_time_ms        BIGINT  NOT NULL DEFAULT 0,
  avg_scroll_pct     NUMERIC NOT NULL DEFAULT 0,
  refreshed_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (funnel_slug, day)
);

-- 6. Refresca los últimos N días (los lotes tardíos siguen llegando después de medianoche)
CREATE OR REPLACE FUNCTION refresh_stats_daily(p_days INTEGER DEFAULT 4, p_tz TEXT DEFAULT 'America/Mexico_City')
RETURNS VOID AS $$
BEGIN
  INSERT INTO stats_daily (funnel_slug, day, sessions, qualified_sessions, conversions, avg_time_ms, avg_scroll_pct, refreshed_at)
  SELECT
    funnel_slug,
    (first_seen_at AT TIME ZONE p_tz)::date                       AS day,
    COUNT(*)                                                      AS sessions,
    COUNT(*) FILTER (WHERE total_time_ms >= 3000)                 AS qualified_sessions,
    COUNT(*) FILTER (WHERE converted)                             AS conversions,
    COALESCE(AVG(total_time_ms), 0)::bigint                       AS avg_time_ms,
    ROUND(COALESCE(AVG(max_scroll_pct), 0), 1)                    AS avg_scroll_pct,
    now()
  FROM sessions
  WHERE NOT is_bot
    AND first_seen_at >= ((now() AT TIME ZONE p_tz)::date - p_days) AT TIME ZONE p_tz
  GROUP BY funnel_slug, (first_seen_at AT TIME ZONE p_tz)::date
  ON CONFLICT (funnel_slug, day) DO UPDATE
    SET sessions           = EXCLUDED.sessions,
        qualified_sessions = EXCLUDED.qualified_sessions,
        conversions        = EXCLUDED.conversions,
        avg_time_ms        = EXCLUDED.avg_time_ms,
        avg_scroll_pct     = EXCLUDED.avg_scroll_pct,
        refreshed_at       = now();
END;
$$ LANGUAGE plpgsql;

SELECT
  d.section_id,
  COUNT(DISTINCT d.session_id)                                AS sesiones_que_la_vieron,
  ROUND(AVG(d.total_ms) / 1000.0, 1)                          AS segundos_promedio,
  ROUND(100.0 * COUNT(DISTINCT d.session_id)
        / NULLIF((SELECT COUNT(*) FROM sessions s2
                  WHERE s2.funnel_slug = s.funnel_slug AND NOT s2.is_bot
                    AND s2.total_time_ms >= 3000), 0), 1)     AS alcance_pct
FROM section_dwell d
JOIN sessions s ON s.session_id = d.session_id
WHERE s.funnel_slug = 'landing' AND NOT s.is_bot
GROUP BY d.section_id, s.funnel_slug
ORDER BY alcance_pct DESC;

SELECT
  COALESCE(NULLIF(utm_source, ''), NULLIF(referrer_host, ''), 'directo') AS fuente,
  COUNT(*)                                                    AS sesiones,
  ROUND(AVG(max_scroll_pct), 0)                               AS scroll_promedio,
  ROUND(100.0 * COUNT(*) FILTER (WHERE max_scroll_pct >= 75) / COUNT(*), 1) AS llegan_al_75_pct
FROM sessions
WHERE funnel_slug = 'landing' AND NOT is_bot AND total_time_ms >= 3000
GROUP BY 1
HAVING COUNT(*) >= 30
ORDER BY sesiones DESC;

SELECT
  day,
  sessions,
  qualified_sessions,
  conversions,
  ROUND(100.0 * conversions / NULLIF(qualified_sessions, 0), 1) AS cvr_calificada_pct, -- para optimizar la página
  ROUND(100.0 * conversions / NULLIF(sessions, 0), 1)           AS cvr_total_pct       -- para comparar con la pauta
FROM stats_daily
WHERE funnel_slug = 'landing'
ORDER BY day DESC
LIMIT 30;

SELECT
  utm_source, utm_campaign, utm_content,
  COUNT(*) FILTER (WHERE total_time_ms >= 3000)               AS calificadas,
  COUNT(*) FILTER (WHERE converted)                           AS conversiones,
  ROUND(AVG(total_time_ms) / 1000.0, 0)                       AS seg_promedio,
  ROUND(100.0 * COUNT(*) FILTER (WHERE converted)
        / NULLIF(COUNT(*) FILTER (WHERE total_time_ms >= 3000), 0), 1) AS cvr_pct
FROM sessions
WHERE funnel_slug = 'landing' AND NOT is_bot AND utm_source <> ''
GROUP BY 1, 2, 3
HAVING COUNT(*) >= 50
ORDER BY conversiones DESC;