Funnel Drop-off → Focus Group Investigation

import os, requests, time

MP_SA = os.environ["MIXPANEL_SERVICE_ACCOUNT"]
MP_SECRET = os.environ["MIXPANEL_SECRET"]
MP_PROJECT = os.environ["MIXPANEL_PROJECT_ID"]
MV = os.environ["MAVERA_API_KEY"]
MB = "https://app.mavera.io/api/v1"
MH = {"Authorization": f"Bearer {MV}", "Content-Type": "application/json"}
mp_auth = (MP_SA, MP_SECRET)

FUNNEL_ID = os.environ.get("MIXPANEL_FUNNEL_ID", "12345")

r = requests.get(
    "https://mixpanel.com/api/query/funnels",
    auth=mp_auth,
    params={
        "project_id": MP_PROJECT,
        "funnel_id": FUNNEL_ID,
        "from_date": "2026-02-15",
        "to_date": "2026-03-17",
        "unit": "month",
    },
)
if r.status_code == 429:
    time.sleep(60)
r.raise_for_status()
funnel = r.json()

steps = []
meta = funnel.get("meta", {}).get("dates", {})
date_key = list(meta.keys())[0] if meta else None
funnel_data = funnel.get("data", {})

if date_key and date_key in funnel_data:
    step_data = funnel_data[date_key].get("steps", [])
    for step in step_data:
        steps.append({
            "name": step.get("event", "Unknown"),
            "count": step.get("count", 0),
            "avg_time": step.get("avg_time", None),
            "goal": step.get("goal", False),
        })

drop_offs = []
for i in range(1, len(steps)):
    prev = steps[i - 1]["count"]
    curr = steps[i]["count"]
    rate = (prev - curr) / max(prev, 1)
    drop_offs.append({
        "from_step": steps[i - 1]["name"],
        "to_step": steps[i]["name"],
        "lost": prev - curr,
        "drop_rate": rate,
        "avg_time": steps[i].get("avg_time"),
    })

drop_offs.sort(key=lambda d: d["drop_rate"], reverse=True)

funnel_summary = "\n".join(
    f"  {i+1}. {s['name']}: {s['count']} users" + (f" (avg {s['avg_time']:.0f}s to reach)" if s['avg_time'] else "")
    for i, s in enumerate(steps)
)
drop_summary = "\n".join(
    f"  - {d['from_step']} → {d['to_step']}: {d['lost']} users lost ({d['drop_rate']:.0%})"
    for d in drop_offs
)

PERSONA_IDS = os.environ.get("FUNNEL_PERSONA_IDS", "").split(",")
if not PERSONA_IDS[0]:
    for name, desc in [
        ("New Free User", "Just signed up on a free plan. Exploring the product for the first time. Limited patience."),
        ("Team Admin", "Setting up the tool for their team. Needs onboarding to be fast and clear. Accountable for adoption."),
        ("Technical User", "Developer or analyst. Expects API-first workflows. Impatient with GUIs."),
    ]:
        p = requests.post(f"{MB}/personas", headers=MH, json={"name": name, "description": desc}).json()
        PERSONA_IDS.append(p["id"])
        time.sleep(0.3)

worst = drop_offs[0] if drop_offs else {"from_step": "Unknown", "to_step": "Unknown", "drop_rate": 0}

fg = requests.post(f"{MB}/focus-groups", headers=MH, json={
    "name": "Mixpanel Funnel Drop-off Investigation",
    "persona_ids": [pid for pid in PERSONA_IDS if pid],
    "questions": [
        f"You just completed '{worst['from_step']}' in our product. What would prevent you from continuing to '{worst['to_step']}'? Be specific about what you'd be thinking or feeling.",
        "Rank these barriers from most to least likely to make you quit: (1) Confusing UI, (2) Too many steps, (3) Need to invite teammates first, (4) Can't see value yet, (5) Technical error. Explain your #1.",
        "What's the ONE thing you'd need to see or experience to keep going through the onboarding?",
        "If you abandoned this flow, would you come back later or never return? What would trigger a return?",
    ],
    "context": f"""Mixpanel funnel analysis for our product onboarding flow:

FUNNEL STEPS:
{funnel_summary}

DROP-OFF ANALYSIS:
{drop_summary}

Worst drop-off: {worst['from_step']} → {worst['to_step']} at {worst['drop_rate']:.0%}.""",
    "responses_per_persona": 2,
}).json()

for _ in range(24):
    time.sleep(5)
    data = requests.get(f"{MB}/focus-groups/{fg['id']}", headers=MH).json()
    if data.get("status") == "completed":
        break

print(f"Focus Group: {data.get('id')} — {data.get('status')}\n")
for resp in data.get("responses", []):
    print(f"[{resp.get('persona_id','?')}] {resp.get('question','')[:70]}")
    print(f"  → {resp.get('answer','')[:250]}\n")

const MP_SA = process.env.MIXPANEL_SERVICE_ACCOUNT;
const MP_SECRET = process.env.MIXPANEL_SECRET;
const MP_PROJECT = process.env.MIXPANEL_PROJECT_ID;
const MV = process.env.MAVERA_API_KEY;
const MB = "https://app.mavera.io/api/v1";
const MH = { Authorization: `Bearer ${MV}`, "Content-Type": "application/json" };
const mpAuth = "Basic " + Buffer.from(`${MP_SA}:${MP_SECRET}`).toString("base64");
const FUNNEL_ID = process.env.MIXPANEL_FUNNEL_ID || "12345";

const funnelRes = await fetch(
  `https://mixpanel.com/api/query/funnels?project_id=${MP_PROJECT}&funnel_id=${FUNNEL_ID}&from_date=2026-02-15&to_date=2026-03-17&unit=month`,
  { headers: { Authorization: mpAuth } }
).then((r) => r.json());

const dateKey = Object.keys(funnelRes.meta?.dates || {})[0];
const stepData = dateKey ? (funnelRes.data?.[dateKey]?.steps || []) : [];
const steps = stepData.map((s) => ({
  name: s.event || "Unknown", count: s.count || 0, avgTime: s.avg_time || null,
}));

const dropOffs = [];
for (let i = 1; i < steps.length; i++) {
  const prev = steps[i - 1].count;
  const curr = steps[i].count;
  dropOffs.push({
    fromStep: steps[i - 1].name, toStep: steps[i].name,
    lost: prev - curr, dropRate: (prev - curr) / (prev || 1),
  });
}
dropOffs.sort((a, b) => b.dropRate - a.dropRate);
const worst = dropOffs[0] || { fromStep: "Unknown", toStep: "Unknown", dropRate: 0 };

const personaIds = [];
for (const [name, desc] of [
  ["New Free User", "Just signed up free. Exploring, limited patience."],
  ["Team Admin", "Setting up for team. Needs fast, clear onboarding."],
  ["Technical User", "Dev/analyst. Expects API-first. Impatient with GUIs."],
]) {
  const p = await fetch(`${MB}/personas`, { method: "POST", headers: MH,
    body: JSON.stringify({ name, description: desc }) }).then((r) => r.json());
  personaIds.push(p.id);
  await new Promise((r) => setTimeout(r, 300));
}

const funnelSummary = steps.map((s, i) => `  ${i + 1}. ${s.name}: ${s.count} users`).join("\n");
const dropSummary = dropOffs.map((d) =>
  `  - ${d.fromStep} → ${d.toStep}: ${d.lost} lost (${(d.dropRate * 100).toFixed(0)}%)`
).join("\n");

const fg = await fetch(`${MB}/focus-groups`, { method: "POST", headers: MH,
  body: JSON.stringify({
    name: "Mixpanel Funnel Investigation",
    persona_ids: personaIds,
    questions: [
      `You completed '${worst.fromStep}'. What prevents you from continuing to '${worst.toStep}'?`,
      "Rank barriers: (1) Confusing UI, (2) Too many steps, (3) Need teammates, (4) No value yet, (5) Error. Explain #1.",
      "What ONE thing would keep you going through onboarding?",
      "Would you come back later or never return? What triggers a return?",
    ],
    context: `Funnel:\n${funnelSummary}\n\nDrop-offs:\n${dropSummary}\n\nWorst: ${worst.fromStep} → ${worst.toStep} at ${(worst.dropRate * 100).toFixed(0)}%.`,
    responses_per_persona: 2,
  }),
}).then((r) => r.json());

let data;
for (let i = 0; i < 24; i++) {
  await new Promise((r) => setTimeout(r, 5000));
  data = await fetch(`${MB}/focus-groups/${fg.id}`, { headers: MH }).then((r) => r.json());
  if (data.status === "completed") break;
}

for (const resp of data.responses || []) {
  console.log(`[${resp.persona_id}] ${(resp.question || "").slice(0, 70)}`);
  console.log(`  → ${(resp.answer || "").slice(0, 250)}\n`);
}