Cohort Analysis → Content Strategy

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"]
mp_auth = (MP_SA, MP_SECRET)

def query_engage_segment(filter_expr, props):
    r = requests.post("https://mixpanel.com/api/query/engage", auth=mp_auth,
        json={
            "project_id": MP_PROJECT,
            "filter_by_cohort": filter_expr,
            "output_properties": props,
            "page": 0,
        })
    if r.status_code == 429: time.sleep(60)
    r.raise_for_status()
    return r.json().get("results", [])

PROFILE_PROPS = [
    "plan", "role", "company_size", "signup_source",
    "total_events", "sessions_count", "features_used",
    "days_active_last_30", "last_seen",
]

retained = query_engage_segment(
    '(properties["days_active_last_30"]) >= 10', PROFILE_PROPS
)
time.sleep(2)
churned = query_engage_segment(
    '(properties["days_active_last_30"]) <= 1 and (properties["sessions_count"]) >= 5', PROFILE_PROPS
)

def summarize(users, label):
    if not users:
        return f"{label}: No users found"
    props_list = [u.get("$properties", {}) for u in users]
    avg_events = sum(int(p.get("total_events", 0) or 0) for p in props_list) / len(props_list)
    avg_sessions = sum(int(p.get("sessions_count", 0) or 0) for p in props_list) / len(props_list)
    avg_days = sum(int(p.get("days_active_last_30", 0) or 0) for p in props_list) / len(props_list)

    from collections import Counter
    plans = Counter(p.get("plan") for p in props_list if p.get("plan")).most_common(3)
    roles = Counter(p.get("role") for p in props_list if p.get("role")).most_common(5)
    sources = Counter(p.get("signup_source") for p in props_list if p.get("signup_source")).most_common(3)
    features = Counter(
        f for p in props_list
        for f in (p.get("features_used", "").split(",") if isinstance(p.get("features_used"), str) else [])
        if f.strip()
    ).most_common(10)

    return (
        f"**{label}** ({len(users)} users)\n"
        f"  Avg events: {avg_events:.0f} | Avg sessions: {avg_sessions:.0f} | Avg days active (30d): {avg_days:.1f}\n"
        f"  Plans: {', '.join(f'{p[0]} ({p[1]})' for p in plans)}\n"
        f"  Roles: {', '.join(f'{r[0]} ({r[1]})' for r in roles)}\n"
        f"  Sources: {', '.join(f'{s[0]} ({s[1]})' for s in sources)}\n"
        f"  Top features: {', '.join(f'{f[0]} ({f[1]})' for f in features)}"
    )

retained_summary = summarize(retained, "Retained (10+ days active/30d)")
churned_summary = summarize(churned, "Churned (≤1 day active, had 5+ sessions)")

mave = requests.post(
    "https://app.mavera.io/api/v1/mave/chat",
    headers={"Authorization": f"Bearer {MV}", "Content-Type": "application/json"},
    json={"message": f"""Research strategies to convert churning user behaviors into retained user behaviors.

RETAINED USERS:
{retained_summary}

CHURNED USERS:
{churned_summary}

Analyze:
1. Key behavioral differences between retained and churned
2. Which features correlate with retention? Which are missing from churned?
3. Content strategies to drive feature adoption among at-risk users
4. Onboarding improvements to increase early activation
5. Re-engagement campaign ideas for users showing churn signals
6. Messaging recommendations: what to say at each stage of the retention curve"""},
).json()

print("--- Cohort Content Strategy ---")
print(mave.get("content", "")[:3000])

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 mpAuth = "Basic " + Buffer.from(`${MP_SA}:${MP_SECRET}`).toString("base64");

const PROPS = ["plan", "role", "company_size", "signup_source",
  "total_events", "sessions_count", "features_used", "days_active_last_30"];

async function querySegment(filter) {
  const res = await fetch("https://mixpanel.com/api/query/engage", {
    method: "POST",
    headers: { Authorization: mpAuth, "Content-Type": "application/json" },
    body: JSON.stringify({ project_id: MP_PROJECT, filter_by_cohort: filter, output_properties: PROPS, page: 0 }),
  });
  if (res.status === 429) { await new Promise((r) => setTimeout(r, 60000)); return querySegment(filter); }
  return (await res.json()).results || [];
}

const retained = await querySegment('(properties["days_active_last_30"]) >= 10');
await new Promise((r) => setTimeout(r, 2000));
const churned = await querySegment('(properties["days_active_last_30"]) <= 1 and (properties["sessions_count"]) >= 5');

function summarize(users, label) {
  const props = users.map((u) => u.$properties || {});
  const avg = (key) => props.reduce((s, p) => s + parseInt(p[key] || "0"), 0) / (props.length || 1);
  const topN = (key, n = 3) => {
    const c = {};
    props.forEach((p) => { if (p[key]) c[p[key]] = (c[p[key]] || 0) + 1; });
    return Object.entries(c).sort(([, a], [, b]) => b - a).slice(0, n).map(([k, v]) => `${k} (${v})`).join(", ");
  };
  return `**${label}** (${users.length} users)\n  Avg events: ${avg("total_events").toFixed(0)} | Sessions: ${avg("sessions_count").toFixed(0)} | Days active: ${avg("days_active_last_30").toFixed(1)}\n  Plans: ${topN("plan")} | Roles: ${topN("role", 5)} | Sources: ${topN("signup_source")}`;
}

const mave = await fetch("https://app.mavera.io/api/v1/mave/chat", {
  method: "POST",
  headers: { Authorization: `Bearer ${MV}`, "Content-Type": "application/json" },
  body: JSON.stringify({
    message: `Research strategies to convert churning behaviors into retained behaviors.\n\nRETAINED:\n${summarize(retained, "Retained (10+ days/30d)")}\n\nCHURNED:\n${summarize(churned, "Churned (≤1 day, had 5+ sessions)")}\n\nAnalyze: 1) Behavioral differences 2) Feature correlations 3) Content strategies 4) Onboarding improvements 5) Re-engagement campaigns 6) Messaging at each retention stage`,
  }),
}).then((r) => r.json());

console.log("--- Cohort Content Strategy ---");
console.log((mave.content || "").slice(0, 3000));