{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cohort Analysis with RLM + DataFrame\n",
    "\n",
    "This notebook demonstrates how to pass large DataFrames to a DSPy RLM (Recursive Language Model) for iterative data analysis.\n",
    "\n",
    "We'll give the RLM three DataFrames from a SaaS product — users, events, and subscriptions — and ask it to investigate why retention is dropping. The RLM will iteratively explore the data, writing and executing Python code in a sandbox, until it identifies the root cause.\n",
    "\n",
    "**Expected findings:**\n",
    "- Overall churn rate: ~23-25%\n",
    "- Worst channel: `paid_campaign_x` (~45% churn vs ~15% organic)\n",
    "- Key insight: churned users from `paid_campaign_x` never use `advanced_reports` — early usage of that feature is an activation signal\n",
    "\n",
    "**Prerequisites:**\n",
    "1. Run `generate_cohort_data.py` to create the parquet files\n",
    "2. Set your LLM API key (e.g. `OPENAI_API_KEY` or `ANTHROPIC_API_KEY`)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Generate the dataset\n",
    "\n",
    "Run `generate_cohort_data.py` to create three parquet files with embedded bias signals. You only need to do this once."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Generating users...\n",
      "Generating subscriptions...\n",
      "Generating events...\n",
      "\n",
      "Dataset stats:\n",
      "  Users:             10,000 rows\n",
      "  Subscriptions:     10,000 rows\n",
      "  Events:           250,918 rows\n",
      "  Total memory:        16.3 MB\n",
      "\n",
      "Embedded signal:\n",
      "  paid_campaign_x churn rate: 45%\n",
      "  organic churn rate:         16%\n",
      "\n",
      "Saved to: users.parquet, subscriptions.parquet, events.parquet\n"
     ]
    }
   ],
   "source": [
    "!python generate_cohort_data.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Imports and setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import dspy\n",
    "\n",
    "# Import the standalone DataFrame wrapper (implements SandboxSerializable)\n",
    "from dataframe import DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Load and inspect the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Users:         10,000 rows\n",
      "Events:        250,918 rows\n",
      "Subscriptions: 10,000 rows\n"
     ]
    }
   ],
   "source": [
    "users = pd.read_parquet(\"users.parquet\")\n",
    "events = pd.read_parquet(\"events.parquet\")\n",
    "subscriptions = pd.read_parquet(\"subscriptions.parquet\")\n",
    "\n",
    "print(f\"Users:         {len(users):,} rows\")\n",
    "print(f\"Events:        {len(events):,} rows\")\n",
    "print(f\"Subscriptions: {len(subscriptions):,} rows\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>email</th>\n",
       "      <th>name</th>\n",
       "      <th>signup_date</th>\n",
       "      <th>acquisition_channel</th>\n",
       "      <th>plan_at_signup</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>johnsonjoshua@example.org</td>\n",
       "      <td>Brian Yang</td>\n",
       "      <td>2024-01-29</td>\n",
       "      <td>organic</td>\n",
       "      <td>free</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>garzaanthony@example.org</td>\n",
       "      <td>Jonathan Johnson</td>\n",
       "      <td>2024-01-27</td>\n",
       "      <td>paid_campaign_x</td>\n",
       "      <td>pro</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>jennifermiles@example.com</td>\n",
       "      <td>Kevin Pacheco</td>\n",
       "      <td>2024-04-18</td>\n",
       "      <td>organic</td>\n",
       "      <td>free</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>blakeerik@example.com</td>\n",
       "      <td>Christopher Bernard</td>\n",
       "      <td>2024-01-07</td>\n",
       "      <td>paid_campaign_x</td>\n",
       "      <td>pro</td>\n",
       "      <td>AU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>curtis61@example.com</td>\n",
       "      <td>Lindsey Roman</td>\n",
       "      <td>2024-04-17</td>\n",
       "      <td>organic</td>\n",
       "      <td>starter</td>\n",
       "      <td>BR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id                      email                 name signup_date  \\\n",
       "0        1  johnsonjoshua@example.org           Brian Yang  2024-01-29   \n",
       "1        2   garzaanthony@example.org     Jonathan Johnson  2024-01-27   \n",
       "2        3  jennifermiles@example.com        Kevin Pacheco  2024-04-18   \n",
       "3        4      blakeerik@example.com  Christopher Bernard  2024-01-07   \n",
       "4        5       curtis61@example.com        Lindsey Roman  2024-04-17   \n",
       "\n",
       "  acquisition_channel plan_at_signup country  \n",
       "0             organic           free      US  \n",
       "1     paid_campaign_x            pro      US  \n",
       "2             organic           free      US  \n",
       "3     paid_campaign_x            pro      AU  \n",
       "4             organic        starter      BR  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>subscription_start</th>\n",
       "      <th>subscription_end</th>\n",
       "      <th>plan</th>\n",
       "      <th>mrr</th>\n",
       "      <th>status</th>\n",
       "      <th>cancellation_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-29</td>\n",
       "      <td>NaT</td>\n",
       "      <td>free</td>\n",
       "      <td>0</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-27</td>\n",
       "      <td>NaT</td>\n",
       "      <td>pro</td>\n",
       "      <td>79</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2024-04-18</td>\n",
       "      <td>NaT</td>\n",
       "      <td>free</td>\n",
       "      <td>0</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2024-01-07</td>\n",
       "      <td>NaT</td>\n",
       "      <td>pro</td>\n",
       "      <td>79</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2024-04-17</td>\n",
       "      <td>2024-05-09</td>\n",
       "      <td>starter</td>\n",
       "      <td>29</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>poor_support</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id subscription_start subscription_end     plan  mrr     status  \\\n",
       "0        1         2024-01-29              NaT     free    0     active   \n",
       "1        2         2024-01-27              NaT      pro   79     active   \n",
       "2        3         2024-04-18              NaT     free    0     active   \n",
       "3        4         2024-01-07              NaT      pro   79     active   \n",
       "4        5         2024-04-17       2024-05-09  starter   29  cancelled   \n",
       "\n",
       "  cancellation_reason  \n",
       "0                 NaN  \n",
       "1                 NaN  \n",
       "2                 NaN  \n",
       "3                 NaN  \n",
       "4        poor_support  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subscriptions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Wrap DataFrames for RLM sandbox injection\n",
    "\n",
    "The `DataFrame` wrapper implements `SandboxSerializable`, which tells the RLM how to:\n",
    "- **Serialize** the data (base64-encoded Parquet)\n",
    "- **Reconstruct** it inside the sandbox (`pd.read_parquet(...)`)\n",
    "- **Preview** it for the LLM (column names, dtypes, sample rows)\n",
    "\n",
    "The full data is injected once into the sandbox at the start of the session. The LLM sees only a compact preview in its prompt but can write code against the real DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame: 10,000 rows x 7 columns\n",
      "\n",
      "Columns:\n",
      "  user_id: int64\n",
      "  email: str\n",
      "  name: str\n",
      "  signup_date: datetime64[us]\n",
      "  acquisition_channel: str\n",
      "  plan_at_signup: str\n",
      "  country: str\n",
      "\n",
      "Sample (first 3 rows):\n",
      "   user_id                      email              name signup_date acquisition_channel plan_at_signup country\n",
      "0        1  johnsonjoshua@example.org        Brian Yang  2024-01-29             organic           free      US\n",
      "1        2   garzaanthony@example.org  Jonathan Johnson  2024-01-27     p...\n"
     ]
    }
   ],
   "source": [
    "wrapped_users = DataFrame(users)\n",
    "wrapped_events = DataFrame(events)\n",
    "wrapped_subscriptions = DataFrame(subscriptions)\n",
    "\n",
    "# This is what the LLM sees in its prompt:\n",
    "print(wrapped_users.rlm_preview())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Define the signature\n",
    "\n",
    "The signature declares three `DataFrame` inputs and four structured outputs. The docstring becomes the system prompt for the RLM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "class CohortRetentionAnalysis(dspy.Signature):\n",
    "    \"\"\"You are a data analyst investigating why user retention is dropping.\n",
    "\n",
    "    You have access to three DataFrames:\n",
    "    - `users`: user profiles with signup dates and acquisition channels\n",
    "    - `events`: feature usage events with timestamps\n",
    "    - `subscriptions`: subscription status, plan, MRR, and cancellation reasons\n",
    "\n",
    "    Investigate the data step by step. Compute retention by cohort, segment by\n",
    "    acquisition channel, compare feature usage between retained and churned users,\n",
    "    and identify the root cause of churn.\n",
    "    \"\"\"\n",
    "\n",
    "    users: DataFrame = dspy.InputField(desc=\"User profiles with signup_date, acquisition_channel, country\")\n",
    "    events: DataFrame = dspy.InputField(desc=\"Feature usage events with user_id, event_type, timestamp\")\n",
    "    subscriptions: DataFrame = dspy.InputField(desc=\"Subscription records with status, plan, mrr, cancellation_reason\")\n",
    "\n",
    "    overall_churn_rate: float = dspy.OutputField(desc=\"Overall churn rate as a decimal (e.g. 0.25 for 25%)\")\n",
    "    worst_channel: str = dspy.OutputField(desc=\"Acquisition channel with highest churn rate\")\n",
    "    key_finding: str = dspy.OutputField(desc=\"The main insight about what differentiates churned users\")\n",
    "    recommendations: str = dspy.OutputField(desc=\"2-3 actionable recommendations based on the analysis\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Configure the LM and run\n",
    "\n",
    "Update the model name below to match your provider. The RLM will run up to 15 iterations, writing and executing Python code in a sandboxed interpreter at each step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "lm = dspy.LM(\"openai/gpt-5.4\")  # or \"anthropic/claude-sonnet-4-5\", etc.\n",
    "dspy.configure(lm=lm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/kmad/dev/dspy-fresh/.venv/lib/python3.13/site-packages/pydantic/main.py:464: UserWarning: Pydantic serializer warnings:\n",
      "  PydanticSerializationUnexpectedValue(Expected 9 fields but got 6: Expected `Message` - serialized value may not be as expected [field_name='choices', input_value=Message(content='[[ ## re...: None}, annotations=[]), input_type=Message])\n",
      "  PydanticSerializationUnexpectedValue(Expected `StreamingChoices` - serialized value may not be as expected [field_name='choices', input_value=Choices(finish_reason='st... None}, annotations=[])), input_type=Choices])\n",
      "  return self.__pydantic_serializer__.to_python(\n",
      "2026/03/08 15:52:10 INFO dspy.predict.rlm: RLM iteration 1/15\n",
      "Reasoning: First step is to explore the data structure and key distributions: shapes, date ranges, status values, acquisition channels, plans, nulls, and some sample cancellation reasons/event types. Then I can define churn/retention properly and compute cohort analyses in later steps.\n",
      "Code:\n",
      "```python\n",
      "print(\"users shape:\", users.shape)\n",
      "print(\"events shape:\", events.shape)\n",
      "print(\"subscriptions shape:\", subscriptions.shape)\n",
      "\n",
      "print(\"\\nusers dtypes:\\n\", users.dtypes)\n",
      "print(\"\\nsubscriptions dtypes:\\n\", subscriptions.dtypes)\n",
      "print(\"\\nevents dtypes:\\n\", events.dtypes)\n",
      "\n",
      "print(\"\\nusers head:\\n\", users.head(3))\n",
      "print(\"\\nsubscriptions head:\\n\", subscriptions.head(3))\n",
      "print(\"\\nevents head:\\n\", events.head(3))\n",
      "\n",
      "print(\"\\nDate ranges:\")\n",
      "print(\"users signup_date:\", users[\"signup_date\"].min(), \"to\", users[\"signup_date\"].max())\n",
      "print(\"subscriptions start:\", subscriptions[\"subscription_start\"].min(), \"to\", subscriptions[\"subscription_start\"].max())\n",
      "print(\"subscriptions end:\", subscriptions[\"subscription_end\"].min(), \"to\", subscriptions[\"subscription_end\"].max())\n",
      "print(\"events timestamp:\", events[\"timestamp\"].min(), \"to\", events[\"timestamp\"].max())\n",
      "\n",
      "print(\"\\nAcquisition channels:\\n\", users[\"acquisition_channel\"].value_counts(dropna=False))\n",
      "print(\"\\nPlan at signup:\\n\", users[\"plan_at_signup\"].value_counts(dropna=False))\n",
      "print(\"\\nSubscription status:\\n\", subscriptions[\"status\"].value_counts(dropna=False))\n",
      "print(\"\\nSubscription plan:\\n\", subscriptions[\"plan\"].value_counts(dropna=False))\n",
      "print(\"\\nTop cancellation reasons:\\n\", subscriptions[\"cancellation_reason\"].value_counts(dropna=False).head(15))\n",
      "print(\"\\nTop event types:\\n\", events[\"event_type\"].value_counts().head(20))\n",
      "```\n",
      "2026/03/08 15:52:10 INFO dspy.predict.rlm: Output (3,297 chars):\n",
      "users shape: (10000, 7)\n",
      "events shape: (250918, 5)\n",
      "subscriptions shape: (10000, 7)\n",
      "\n",
      "users dtypes:\n",
      " user_id                         int64\n",
      "email                          object\n",
      "name                           object\n",
      "signup_date            datetime64[us]\n",
      "acquisition_channel            object\n",
      "plan_at_signup                 object\n",
      "country                        object\n",
      "dtype: object\n",
      "\n",
      "subscriptions dtypes:\n",
      " user_id                         int64\n",
      "subscription_start     datetime64[us]\n",
      "subscription_end       datetime64[us]\n",
      "plan                           object\n",
      "mrr                             int64\n",
      "status                         object\n",
      "cancellation_reason            object\n",
      "dtype: object\n",
      "\n",
      "events dtypes:\n",
      " user_id                      int64\n",
      "event_type                  object\n",
      "timestamp           datetime64[us]\n",
      "session_id                  object\n",
      "duration_seconds             int64\n",
      "dtype: object\n",
      "\n",
      "users head:\n",
      "    user_id                      email  ... plan_at_signup country\n",
      "0        1  johnsonjoshua@example.org  ...           free      US\n",
      "1        2   garzaanthony@example.org  ...            pro      US\n",
      "2        3  jennifermiles@example.com  ...           free      US\n",
      "\n",
      "[3 rows x 7 columns]\n",
      "\n",
      "subscriptions head:\n",
      "    user_id subscription_start subscription_end  ... mrr  status cancellation_reason\n",
      "0        1         2024-01-29              NaT  ...   0  active                None\n",
      "1        2         2024-01-27              NaT  ...  79  active                None\n",
      "2        3         2024-04-18              NaT  ...   0  active                None\n",
      "\n",
      "[3 rows x 7 columns]\n",
      "\n",
      "events head:\n",
      "    user_id     event_type           timestamp session_id  duration_seconds\n",
      "0     9910  report_export 2024-01-01 02:22:19   97b17398               263\n",
      "1     4854    file_upload 2024-01-01 04:24:55   cda2b255                28\n",
      "2     2520         search 2024-01-01 04:34:43   f833547a               183\n",
      "\n",
      "Date ranges:\n",
      "users signup_date: 2024-01-01 00:00:00 to 2024-05-30 00:00:00\n",
      "subscriptions start: 2024-01-01 00:00:00 to 2024-05-30 00:00:00\n",
      "subscriptions end: 2024-01-09 00:00:00 to 2024-06-30 00:00:00\n",
      "events timestamp: 2024-01-01 02:22:19 to 2024-06-29 23:59:11\n",
      "\n",
      "Acquisition channels:\n",
      " acquisition_channel\n",
      "organic            3030\n",
      "paid_campaign_x    2469\n",
      "social             1507\n",
      "paid_campaign_y    1499\n",
      "referral           1495\n",
      "Name: count, dtype: int64\n",
      "\n",
      "Plan at signup:\n",
      " plan_at_signup\n",
      "free          3936\n",
      "starter       2977\n",
      "pro           2074\n",
      "enterprise    1013\n",
      "Name: count, dtype: int64\n",
      "\n",
      "Subscription status:\n",
      " status\n",
      "active       7623\n",
      "cancelled    2377\n",
      "Name: count, dtype: int64\n",
      "\n",
      "Subscription plan:\n",
      " plan\n",
      "free          3936\n",
      "starter       2977\n",
      "pro           2074\n",
      "enterprise    1013\n",
      "Name: count, dtype: int64\n",
      "\n",
      "Top cancellation reasons:\n",
      " cancellation_reason\n",
      "None                   7623\n",
      "no_longer_needed        413\n",
      "switched_competitor     407\n",
      "poor_support            405\n",
      "other                   403\n",
      "too_expensive           382\n",
      "missing_features        367\n",
      "Name: count, dtype: int64\n",
      "\n",
      "Top event types:\n",
      " event_type\n",
      "api_call              25527\n",
      "report_export         25441\n",
      "search                25345\n",
      "dashboard_view        25266\n",
      "integration_setup     25235\n",
      "settings_update       25184\n",
      "file_upload           25105\n",
      "team_invite           25057\n",
      "notification_click    25052\n",
      "advanced_reports      23706\n",
      "Name: count, dtype: int64\n",
      "\n",
      "2026/03/08 15:52:21 INFO dspy.predict.rlm: RLM iteration 2/15\n",
      "Reasoning: Next I should define churn/retention precisely and compute the main aggregates. Since `subscriptions.status` already indicates active vs cancelled, overall churn rate can be cancelled / total users. For retention by cohort, monthly signup cohorts with retained = active likely works, but I should also be careful about censoring because later cohorts have less time to churn. A good first pass is to compute churn/retention by signup month and by acquisition channel, plus a cohort-by-channel pivot. Then I can identify the worst channel and inspect whether its churn differs by plan mix. After that I’ll compare feature usage between retained and churned users, ideally normalizing by account age / pre-churn activity windows. First, compute merged base table, churn by cohort/channel/plan, and some cross-tabs.\n",
      "Code:\n",
      "```python\n",
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "base = users.merge(subscriptions, on=\"user_id\", how=\"inner\", suffixes=(\"_user\", \"_sub\"))\n",
      "base[\"is_churned\"] = base[\"status\"].eq(\"cancelled\")\n",
      "base[\"is_retained\"] = base[\"status\"].eq(\"active\")\n",
      "base[\"signup_month\"] = base[\"signup_date\"].dt.to_period(\"M\").astype(str)\n",
      "\n",
      "overall_churn_rate = base[\"is_churned\"].mean()\n",
      "\n",
      "print(\"Overall churn rate:\", overall_churn_rate)\n",
      "\n",
      "channel_stats = (\n",
      "    base.groupby(\"acquisition_channel\")\n",
      "    .agg(\n",
      "        users=(\"user_id\", \"count\"),\n",
      "        churn_rate=(\"is_churned\", \"mean\"),\n",
      "        retained_rate=(\"is_retained\", \"mean\"),\n",
      "        avg_mrr=(\"mrr\", \"mean\")\n",
      "    )\n",
      "    .sort_values(\"churn_rate\", ascending=False)\n",
      ")\n",
      "print(\"\\nChurn by acquisition channel:\\n\", channel_stats)\n",
      "\n",
      "cohort_stats = (\n",
      "    base.groupby(\"signup_month\")\n",
      "    .agg(\n",
      "        users=(\"user_id\", \"count\"),\n",
      "        churn_rate=(\"is_churned\", \"mean\"),\n",
      "        retained_rate=(\"is_retained\", \"mean\")\n",
      "    )\n",
      "    .sort_index()\n",
      ")\n",
      "print(\"\\nChurn by signup cohort:\\n\", cohort_stats)\n",
      "\n",
      "cohort_channel = (\n",
      "    base.groupby([\"signup_month\", \"acquisition_channel\"])[\"is_churned\"]\n",
      "    .mean()\n",
      "    .unstack()\n",
      ")\n",
      "print(\"\\nCohort x channel churn rate:\\n\", cohort_channel)\n",
      "\n",
      "plan_channel = (\n",
      "    base.groupby([\"acquisition_channel\", \"plan\"])[\"is_churned\"]\n",
      "    .agg([\"mean\", \"count\"])\n",
      "    .sort_values([\"acquisition_channel\", \"mean\"], ascending=[True, False])\n",
      ")\n",
      "print(\"\\nChannel x plan churn:\\n\", plan_channel)\n",
      "\n",
      "print(\"\\nOverall churn by plan:\\n\", base.groupby(\"plan\")[\"is_churned\"].mean().sort_values(ascending=False))\n",
      "```\n",
      "2026/03/08 15:52:21 INFO dspy.predict.rlm: Output (2,661 chars):\n",
      "Overall churn rate: 0.2377\n",
      "\n",
      "Churn by acquisition channel:\n",
      "                      users  churn_rate  retained_rate    avg_mrr\n",
      "acquisition_channel                                             \n",
      "paid_campaign_x       2469    0.447550       0.552450  50.027542\n",
      "social                1507    0.230922       0.769078  51.277372\n",
      "organic               3030    0.157756       0.842244  50.890429\n",
      "referral              1495    0.151839       0.848161  50.537124\n",
      "paid_campaign_y       1499    0.146097       0.853903  47.946631\n",
      "\n",
      "Churn by signup cohort:\n",
      "               users  churn_rate  retained_rate\n",
      "signup_month                                  \n",
      "2024-01        2030    0.240887       0.759113\n",
      "2024-02        1828    0.241247       0.758753\n",
      "2024-03        2115    0.214657       0.785343\n",
      "2024-04        1991    0.243094       0.756906\n",
      "2024-05        2036    0.250000       0.750000\n",
      "\n",
      "Cohort x channel churn rate:\n",
      " acquisition_channel   organic  paid_campaign_x  ...  referral    social\n",
      "signup_month                                    ...                    \n",
      "2024-01              0.142626         0.456979  ...  0.159509  0.253521\n",
      "2024-02              0.176580         0.446009  ...  0.163763  0.245487\n",
      "2024-03              0.143077         0.423301  ...  0.124138  0.190031\n",
      "2024-04              0.161392         0.425703  ...  0.164875  0.238854\n",
      "2024-05              0.168634         0.485207  ...  0.146965  0.231511\n",
      "\n",
      "[5 rows x 5 columns]\n",
      "\n",
      "Channel x plan churn:\n",
      "                                     mean  count\n",
      "acquisition_channel plan                       \n",
      "organic             enterprise  0.176101    318\n",
      "                    starter     0.159817    876\n",
      "                    free        0.155629   1208\n",
      "                    pro         0.149682    628\n",
      "paid_campaign_x     free        0.456050    967\n",
      "                    enterprise  0.447581    248\n",
      "                    starter     0.443700    746\n",
      "                    pro         0.437008    508\n",
      "paid_campaign_y     starter     0.174946    463\n",
      "                    pro         0.162338    308\n",
      "                    enterprise  0.131387    137\n",
      "                    free        0.118443    591\n",
      "referral            free        0.181973    588\n",
      "                    enterprise  0.141935    155\n",
      "                    starter     0.131403    449\n",
      "                    pro         0.128713    303\n",
      "social              enterprise  0.245161    155\n",
      "                    pro         0.235474    327\n",
      "                    free        0.230241    582\n",
      "                    starter     0.223476    443\n",
      "\n",
      "Overall churn by plan:\n",
      " plan\n",
      "enterprise    0.241856\n",
      "free          0.238821\n",
      "starter       0.238495\n",
      "pro           0.232401\n",
      "Name: is_churned, dtype: float64\n",
      "\n",
      "2026/03/08 15:52:45 INFO dspy.predict.rlm: RLM iteration 3/15\n",
      "Reasoning: We’ve established overall churn and clearly identified `paid_campaign_x` as the worst acquisition channel, with materially higher churn across every plan and every cohort. Next, I should investigate feature usage differences between retained and churned users, especially whether campaign_x users engage less with key product features. To avoid bias from post-churn inactivity, compare usage in a standardized early-life window (e.g., first 14 days after signup/start) and also check basic activity metrics like number of events, active days, sessions, and feature adoption rates. Then examine cancellation reasons by channel to see if they align with behavior (e.g., missing features, no longer needed, too expensive). This should help identify a concrete root cause and support recommendations.\n",
      "Code:\n",
      "```python\n",
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "# Build event window relative to subscription/user start\n",
      "base_cols = [\"user_id\", \"signup_date\", \"acquisition_channel\", \"plan\", \"mrr\", \"status\", \"is_churned\", \"is_retained\", \"cancellation_reason\"]\n",
      "ue = base[base_cols].merge(events, on=\"user_id\", how=\"left\")\n",
      "\n",
      "ue[\"days_from_signup\"] = (ue[\"timestamp\"] - ue[\"signup_date\"]).dt.days\n",
      "early = ue[(ue[\"days_from_signup\"] >= 0) & (ue[\"days_from_signup\"] <= 14)].copy()\n",
      "\n",
      "# User-level early engagement metrics\n",
      "user_early_metrics = (\n",
      "    early.groupby(\"user_id\")\n",
      "    .agg(\n",
      "        early_events=(\"event_type\", \"size\"),\n",
      "        early_active_days=(\"timestamp\", lambda s: s.dt.date.nunique()),\n",
      "        early_sessions=(\"session_id\", \"nunique\"),\n",
      "        early_total_duration=(\"duration_seconds\", \"sum\"),\n",
      "        early_unique_features=(\"event_type\", \"nunique\"),\n",
      "    )\n",
      "    .reset_index()\n",
      ")\n",
      "\n",
      "# add zeros for users with no events in first 14 days\n",
      "user_early_metrics = base[base_cols].merge(user_early_metrics, on=\"user_id\", how=\"left\")\n",
      "for c in [\"early_events\", \"early_active_days\", \"early_sessions\", \"early_total_duration\", \"early_unique_features\"]:\n",
      "    user_early_metrics[c] = user_early_metrics[c].fillna(0)\n",
      "\n",
      "print(\"Early engagement by retention status:\")\n",
      "print(\n",
      "    user_early_metrics.groupby(\"status\")[[\"early_events\",\"early_active_days\",\"early_sessions\",\"early_total_duration\",\"early_unique_features\"]]\n",
      "    .mean()\n",
      ")\n",
      "\n",
      "print(\"\\nEarly engagement by channel and status:\")\n",
      "print(\n",
      "    user_early_metrics.groupby([\"acquisition_channel\",\"status\"])[[\"early_events\",\"early_active_days\",\"early_sessions\",\"early_unique_features\"]]\n",
      "    .mean()\n",
      ")\n",
      "\n",
      "# Feature adoption rates in first 14 days\n",
      "feature_adoption = (\n",
      "    early.assign(value=1)\n",
      "    .pivot_table(index=\"user_id\", columns=\"event_type\", values=\"value\", aggfunc=\"max\", fill_value=0)\n",
      "    .reset_index()\n",
      ")\n",
      "feature_adoption = base[base_cols].merge(feature_adoption, on=\"user_id\", how=\"left\").fillna(0)\n",
      "\n",
      "event_cols = [c for c in feature_adoption.columns if c not in base_cols]\n",
      "adoption_by_status = feature_adoption.groupby(\"status\")[event_cols].mean().T.sort_values(\"cancelled\")\n",
      "adoption_by_status[\"diff_active_minus_cancelled\"] = adoption_by_status.get(\"active\", 0) - adoption_by_status.get(\"cancelled\", 0)\n",
      "print(\"\\nFeature adoption rate in first 14 days by status:\")\n",
      "print(adoption_by_status.sort_values(\"diff_active_minus_cancelled\", ascending=False))\n",
      "\n",
      "adoption_by_channel_status = feature_adoption.groupby([\"acquisition_channel\",\"status\"])[event_cols].mean()\n",
      "\n",
      "print(\"\\nPaid Campaign X early feature adoption vs Organic (all users):\")\n",
      "compare_channels = feature_adoption.groupby(\"acquisition_channel\")[event_cols].mean().loc[[\"paid_campaign_x\",\"organic\"]].T\n",
      "compare_channels[\"diff_org_minus_pcx\"] = compare_channels[\"organic\"] - compare_channels[\"paid_campaign_x\"]\n",
      "print(compare_channels.sort_values(\"diff_org_minus_pcx\", ascending=False))\n",
      "\n",
      "print(\"\\nCancellation reasons by channel (row-normalized):\")\n",
      "cancel_reasons = (\n",
      "    base[base[\"is_churned\"]]\n",
      "    .groupby([\"acquisition_channel\", \"cancellation_reason\"])\n",
      "    .size()\n",
      "    .unstack(fill_value=0)\n",
      ")\n",
      "cancel_reasons_pct = cancel_reasons.div(cancel_reasons.sum(axis=1), axis=0)\n",
      "print(cancel_reasons_pct)\n",
      "\n",
      "# specifically compare campaign_x churn reasons counts\n",
      "print(\"\\nCancellation reason counts for paid_campaign_x:\")\n",
      "print(base[(base[\"acquisition_channel\"]==\"paid_campaign_x\") & (base[\"is_churned\"])][\"cancellation_reason\"].value_counts())\n",
      "```\n",
      "2026/03/08 15:52:47 INFO dspy.predict.rlm: Output (3,543 chars):\n",
      "Early engagement by retention status:\n",
      "           early_events  ...  early_unique_features\n",
      "status                   ...                       \n",
      "active         4.071232  ...               3.333202\n",
      "cancelled      4.122423  ...               3.350862\n",
      "\n",
      "[2 rows x 5 columns]\n",
      "\n",
      "Early engagement by channel and status:\n",
      "                               early_events  ...  early_unique_features\n",
      "acquisition_channel status                   ...                       \n",
      "organic             active         4.116379  ...               3.358934\n",
      "                    cancelled      4.175732  ...               3.389121\n",
      "paid_campaign_x     active         4.087243  ...               3.332111\n",
      "                    cancelled      4.091403  ...               3.286878\n",
      "paid_campaign_y     active         4.083594  ...               3.342188\n",
      "                    cancelled      4.283105  ...               3.593607\n",
      "referral            active         4.022871  ...               3.331230\n",
      "                    cancelled      3.911894  ...               3.255507\n",
      "social              active         3.992235  ...               3.270060\n",
      "                    cancelled      4.183908  ...               3.410920\n",
      "\n",
      "[10 rows x 4 columns]\n",
      "\n",
      "Feature adoption rate in first 14 days by status:\n",
      "status                active  cancelled  diff_active_minus_cancelled\n",
      "advanced_reports    0.341467   0.188894                     0.152573\n",
      "settings_update     0.337269   0.332352                     0.004917\n",
      "api_call            0.332415   0.341186                    -0.008771\n",
      "team_invite         0.329529   0.342448                    -0.012919\n",
      "integration_setup   0.328348   0.342869                    -0.014521\n",
      "notification_click  0.328348   0.349180                    -0.020831\n",
      "search              0.337269   0.362642                    -0.025373\n",
      "dashboard_view      0.338187   0.366008                    -0.027821\n",
      "report_export       0.332546   0.361380                    -0.028834\n",
      "file_upload         0.327824   0.363904                    -0.036080\n",
      "\n",
      "Paid Campaign X early feature adoption vs Organic (all users):\n",
      "acquisition_channel  paid_campaign_x   organic  diff_org_minus_pcx\n",
      "advanced_reports            0.187120  0.351815            0.164695\n",
      "notification_click          0.333333  0.344554            0.011221\n",
      "api_call                    0.338194  0.338944            0.000750\n",
      "team_invite                 0.335763  0.330033           -0.005730\n",
      "report_export               0.354800  0.343234           -0.011565\n",
      "search                      0.358040  0.342904           -0.015135\n",
      "settings_update             0.343459  0.324422           -0.019036\n",
      "integration_setup           0.346294  0.324422           -0.021872\n",
      "file_upload                 0.354800  0.329373           -0.025427\n",
      "dashboard_view              0.360065  0.333993           -0.026071\n",
      "\n",
      "Cancellation reasons by channel (row-normalized):\n",
      "cancellation_reason  missing_features  ...  too_expensive\n",
      "acquisition_channel                    ...               \n",
      "organic                      0.158996  ...       0.156904\n",
      "paid_campaign_x              0.149321  ...       0.142986\n",
      "paid_campaign_y              0.168950  ...       0.173516\n",
      "referral                     0.140969  ...       0.176211\n",
      "social                       0.163793  ...       0.204023\n",
      "\n",
      "[5 rows x 6 columns]\n",
      "\n",
      "Cancellation reason counts for paid_campaign_x:\n",
      "cancellation_reason\n",
      "switched_competitor    200\n",
      "other                  197\n",
      "no_longer_needed       196\n",
      "poor_support           189\n",
      "missing_features       165\n",
      "too_expensive          158\n",
      "Name: count, dtype: int64\n",
      "\n",
      "2026/03/08 15:53:03 INFO dspy.predict.rlm: RLM iteration 4/15\n",
      "Reasoning: We know overall churn is 0.2377 and worst channel appears to be paid_campaign_x with consistently high churn across cohorts and plans. Early-life engagement volume is not lower for churned users, so the issue is likely not simple inactivity. A striking pattern is that retained users have much higher advanced_reports adoption, and paid_campaign_x has much lower advanced_reports adoption than organic. To strengthen the root-cause claim, I should quantify this relationship: churn by advanced_reports adoption overall and within channel/plan, and possibly a simple multivariate control (e.g., by channel + plan group rates). Also inspect whether cancellation reasons for users without advanced_reports skew toward missing_features / switched_competitor. Then I can form a solid key finding and recommendations.\n",
      "Code:\n",
      "```python\n",
      "# Quantify relationship between advanced_reports adoption and churn\n",
      "fa = feature_adoption.copy()\n",
      "fa[\"used_advanced_reports\"] = fa[\"advanced_reports\"] > 0\n",
      "\n",
      "print(\"Overall churn by advanced_reports adoption:\")\n",
      "print(fa.groupby(\"used_advanced_reports\")[\"is_churned\"].agg([\"mean\", \"count\"]))\n",
      "\n",
      "print(\"\\nChurn by channel and advanced_reports adoption:\")\n",
      "channel_adv = fa.groupby([\"acquisition_channel\", \"used_advanced_reports\"])[\"is_churned\"].agg([\"mean\", \"count\"]).reset_index()\n",
      "print(channel_adv)\n",
      "\n",
      "print(\"\\nChurn by plan and advanced_reports adoption:\")\n",
      "plan_adv = fa.groupby([\"plan\", \"used_advanced_reports\"])[\"is_churned\"].agg([\"mean\", \"count\"]).reset_index()\n",
      "print(plan_adv)\n",
      "\n",
      "print(\"\\nPaid Campaign X churn by plan and advanced_reports adoption:\")\n",
      "pcx_plan_adv = (\n",
      "    fa[fa[\"acquisition_channel\"] == \"paid_campaign_x\"]\n",
      "    .groupby([\"plan\", \"used_advanced_reports\"])[\"is_churned\"]\n",
      "    .agg([\"mean\", \"count\"])\n",
      "    .reset_index()\n",
      ")\n",
      "print(pcx_plan_adv)\n",
      "\n",
      "print(\"\\nAdvanced_reports adoption by channel:\")\n",
      "print(fa.groupby(\"acquisition_channel\")[\"used_advanced_reports\"].mean().sort_values())\n",
      "\n",
      "# Cancellation reasons by advanced_reports usage among churned users\n",
      "churned_fa = fa[fa[\"is_churned\"]].copy()\n",
      "reason_by_adv = pd.crosstab(churned_fa[\"used_advanced_reports\"], churned_fa[\"cancellation_reason\"], normalize=\"index\")\n",
      "print(\"\\nCancellation reasons among churned users by advanced_reports usage:\")\n",
      "print(reason_by_adv)\n",
      "\n",
      "print(\"\\nPaid Campaign X cancellation reasons by advanced_reports usage:\")\n",
      "pcx_reason_by_adv = pd.crosstab(\n",
      "    churned_fa[churned_fa[\"acquisition_channel\"]==\"paid_campaign_x\"][\"used_advanced_reports\"],\n",
      "    churned_fa[churned_fa[\"acquisition_channel\"]==\"paid_campaign_x\"][\"cancellation_reason\"],\n",
      "    normalize=\"index\"\n",
      ")\n",
      "print(pcx_reason_by_adv)\n",
      "```\n",
      "2026/03/08 15:53:03 INFO dspy.predict.rlm: Output (2,852 chars):\n",
      "Overall churn by advanced_reports adoption:\n",
      "                           mean  count\n",
      "used_advanced_reports                 \n",
      "False                  0.277490   6948\n",
      "True                   0.147117   3052\n",
      "\n",
      "Churn by channel and advanced_reports adoption:\n",
      "  acquisition_channel  used_advanced_reports      mean  count\n",
      "0             organic                  False  0.157841   1964\n",
      "1             organic                   True  0.157598   1066\n",
      "2     paid_campaign_x                  False  0.550573   2007\n",
      "3     paid_campaign_x                   True  0.000000    462\n",
      "4     paid_campaign_y                  False  0.138693    995\n",
      "5     paid_campaign_y                   True  0.160714    504\n",
      "6            referral                  False  0.152664    976\n",
      "7            referral                   True  0.150289    519\n",
      "8              social                  False  0.224652   1006\n",
      "9              social                   True  0.243513    501\n",
      "\n",
      "Churn by plan and advanced_reports adoption:\n",
      "         plan  used_advanced_reports      mean  count\n",
      "0  enterprise                  False  0.285118    719\n",
      "1  enterprise                   True  0.136054    294\n",
      "2        free                  False  0.283103   2681\n",
      "3        free                   True  0.144223   1255\n",
      "4         pro                  False  0.268606   1478\n",
      "5         pro                   True  0.142617    596\n",
      "6     starter                  False  0.273913   2070\n",
      "7     starter                   True  0.157663    907\n",
      "\n",
      "Paid Campaign X churn by plan and advanced_reports adoption:\n",
      "         plan  used_advanced_reports      mean  count\n",
      "0  enterprise                  False  0.541463    205\n",
      "1  enterprise                   True  0.000000     43\n",
      "2        free                  False  0.561783    785\n",
      "3        free                   True  0.000000    182\n",
      "4         pro                  False  0.529833    419\n",
      "5         pro                   True  0.000000     89\n",
      "6     starter                  False  0.553512    598\n",
      "7     starter                   True  0.000000    148\n",
      "\n",
      "Advanced_reports adoption by channel:\n",
      "acquisition_channel\n",
      "paid_campaign_x    0.187120\n",
      "social             0.332449\n",
      "paid_campaign_y    0.336224\n",
      "referral           0.347157\n",
      "organic            0.351815\n",
      "Name: used_advanced_reports, dtype: float64\n",
      "\n",
      "Cancellation reasons among churned users by advanced_reports usage:\n",
      "cancellation_reason    missing_features  ...  too_expensive\n",
      "used_advanced_reports                    ...               \n",
      "False                          0.159751  ...       0.154564\n",
      "True                           0.131403  ...       0.187082\n",
      "\n",
      "[2 rows x 6 columns]\n",
      "\n",
      "Paid Campaign X cancellation reasons by advanced_reports usage:\n",
      "cancellation_reason    missing_features  ...  too_expensive\n",
      "used_advanced_reports                    ...               \n",
      "False                          0.149321  ...       0.142986\n",
      "\n",
      "[1 rows x 6 columns]\n",
      "\n",
      "2026/03/08 15:53:15 INFO dspy.predict.rlm: RLM iteration 5/15\n",
      "Reasoning: We have a very strong signal, but the perfect 0% churn among paid_campaign_x users who used advanced_reports could be suspicious or still valid. Before submitting, I should verify counts/consistency and formulate a concise key finding. I also want to see if paid_campaign_x differs in advanced_reports adoption over cohorts and whether no users who churned ever used advanced_reports in that channel. Then I can safely submit with overall churn rate, worst channel, root cause, and recommendations focused on acquisition quality/onboarding to advanced_reports.\n",
      "Code:\n",
      "```python\n",
      "# Final verification and compact summary stats for submission\n",
      "print(\"Overall churn rate check:\", base[\"is_churned\"].mean())\n",
      "\n",
      "worst_channel = channel_stats[\"churn_rate\"].idxmax()\n",
      "print(\"Worst channel:\", worst_channel)\n",
      "print(channel_stats.loc[worst_channel])\n",
      "\n",
      "# Verify the striking advanced_reports pattern\n",
      "pcx = fa[fa[\"acquisition_channel\"] == \"paid_campaign_x\"].copy()\n",
      "print(\"\\nPaid Campaign X advanced_reports usage vs churn crosstab:\")\n",
      "print(pd.crosstab(pcx[\"used_advanced_reports\"], pcx[\"status\"], margins=True))\n",
      "\n",
      "print(\"\\nPaid Campaign X advanced_reports adoption by signup cohort:\")\n",
      "print(\n",
      "    pcx.groupby(\"signup_month\")[\"used_advanced_reports\"].mean()\n",
      ")\n",
      "\n",
      "print(\"\\nPaid Campaign X churn by cohort and advanced_reports usage:\")\n",
      "print(\n",
      "    pcx.groupby([\"signup_month\", \"used_advanced_reports\"])[\"is_churned\"]\n",
      "    .agg([\"mean\", \"count\"])\n",
      ")\n",
      "\n",
      "# Compare campaign_x to others on adoption\n",
      "adv_by_channel = fa.groupby(\"acquisition_channel\")[\"used_advanced_reports\"].mean().sort_values()\n",
      "print(\"\\nAdvanced reports adoption by channel:\")\n",
      "print(adv_by_channel)\n",
      "\n",
      "# Prepare outputs\n",
      "overall_churn_rate_out = float(base[\"is_churned\"].mean())\n",
      "worst_channel_out = str(worst_channel)\n",
      "\n",
      "key_finding_out = (\n",
      "    \"Churn is 23.77% overall, and paid_campaign_x is the clear outlier with 44.76% churn \"\n",
      "    \"across every signup cohort and plan. The strongest behavioral signal is early adoption \"\n",
      "    \"of advanced_reports: users who adopt it churn far less overall (14.7% vs 27.7%), while \"\n",
      "    \"paid_campaign_x has by far the lowest advanced_reports adoption (18.7% vs ~33-35% for other channels). \"\n",
      "    \"Within paid_campaign_x, churn is concentrated entirely among users who never adopt advanced_reports, \"\n",
      "    \"indicating the drop in retention is primarily an acquisition-quality/onboarding problem rather than \"\n",
      "    \"a broad product usage decline.\"\n",
      ")\n",
      "\n",
      "recommendations_out = (\n",
      "    \"1) Audit or pause paid_campaign_x because it is bringing in low-retention users. \"\n",
      "    \"2) Redesign onboarding for campaign_x traffic to drive advanced_reports activation in the first 14 days \"\n",
      "    \"(guided setup, templates, onboarding emails, success prompts). \"\n",
      "    \"3) Make ad messaging and landing pages align with the advanced reporting value proposition so acquired users \"\n",
      "    \"match the product's core use case. \"\n",
      "    \"4) Track activation and retention by channel weekly, using advanced_reports adoption as the primary leading indicator.\"\n",
      ")\n",
      "\n",
      "print(\"\\nPrepared outputs:\")\n",
      "print(overall_churn_rate_out)\n",
      "print(worst_channel_out)\n",
      "print(key_finding_out)\n",
      "print(recommendations_out)\n",
      "```\n",
      "2026/03/08 15:53:24 INFO dspy.predict.rlm: RLM iteration 6/15\n",
      "Reasoning: The error happened because `fa`/`feature_adoption` was built from `base_cols`, which did not include `signup_month`. I already have enough evidence for the conclusion, but I should fix the verification step cleanly by adding `signup_month` back from `base` and reprinting the final sanity checks. Then, if the outputs look consistent, submit using variables computed in code to avoid retyping.\n",
      "Code:\n",
      "```python\n",
      "# Fix missing signup_month for verification\n",
      "fa = feature_adoption.merge(base[[\"user_id\", \"signup_month\"]], on=\"user_id\", how=\"left\")\n",
      "\n",
      "print(\"Overall churn rate check:\", float(base[\"is_churned\"].mean()))\n",
      "\n",
      "worst_channel = channel_stats[\"churn_rate\"].idxmax()\n",
      "print(\"\\nWorst channel:\", worst_channel)\n",
      "print(channel_stats.loc[worst_channel])\n",
      "\n",
      "pcx = fa[fa[\"acquisition_channel\"] == \"paid_campaign_x\"].copy()\n",
      "\n",
      "print(\"\\nPaid Campaign X advanced_reports usage vs churn crosstab:\")\n",
      "print(pd.crosstab(pcx[\"used_advanced_reports\"], pcx[\"status\"], margins=True))\n",
      "\n",
      "print(\"\\nPaid Campaign X advanced_reports adoption by signup cohort:\")\n",
      "print(pcx.groupby(\"signup_month\")[\"used_advanced_reports\"].mean())\n",
      "\n",
      "print(\"\\nPaid Campaign X churn by cohort and advanced_reports usage:\")\n",
      "print(\n",
      "    pcx.groupby([\"signup_month\", \"used_advanced_reports\"])[\"is_churned\"]\n",
      "    .agg([\"mean\", \"count\"])\n",
      ")\n",
      "\n",
      "overall_churn_rate_out = float(base[\"is_churned\"].mean())\n",
      "worst_channel_out = str(worst_channel)\n",
      "\n",
      "key_finding_out = (\n",
      "    \"Churn is 23.77% overall, and paid_campaign_x is the clear outlier with 44.76% churn \"\n",
      "    \"across every signup cohort and plan. The strongest behavioral signal is early adoption \"\n",
      "    \"of advanced_reports: users who adopt it churn far less overall (14.7% vs 27.7%), while \"\n",
      "    \"paid_campaign_x has by far the lowest advanced_reports adoption (18.7% vs ~33-35% for other channels). \"\n",
      "    \"Within paid_campaign_x, churn is concentrated among users who never adopt advanced_reports, \"\n",
      "    \"indicating the retention drop is primarily an acquisition-quality/onboarding problem rather than \"\n",
      "    \"a broad product usage decline.\"\n",
      ")\n",
      "\n",
      "recommendations_out = (\n",
      "    \"Pause or tighten paid_campaign_x targeting; redesign onboarding for campaign_x users to drive \"\n",
      "    \"advanced_reports activation in the first 14 days; align ad/landing-page messaging to the product's \"\n",
      "    \"core reporting use case; and track weekly activation-to-retention funnels by channel with \"\n",
      "    \"advanced_reports adoption as the leading indicator.\"\n",
      ")\n",
      "\n",
      "print(\"\\nPrepared outputs:\")\n",
      "print(\"overall_churn_rate_out =\", overall_churn_rate_out)\n",
      "print(\"worst_channel_out =\", worst_channel_out)\n",
      "print(\"key_finding_out =\", key_finding_out)\n",
      "print(\"recommendations_out =\", recommendations_out)\n",
      "\n",
      "SUBMIT(overall_churn_rate_out, worst_channel_out, key_finding_out, recommendations_out)\n",
      "```\n",
      "2026/03/08 15:53:33 INFO dspy.predict.rlm: RLM iteration 7/15\n",
      "Reasoning: Need fix because fa overwritten without used_advanced_reports. Recreate from feature_adoption/base including signup_month and used_advanced_reports, print sanity checks, then submit.\n",
      "Code:\n",
      "```python\n",
      "# Rebuild final verification table cleanly\n",
      "fa = feature_adoption.merge(\n",
      "    base[[\"user_id\", \"signup_month\", \"is_churned\", \"status\", \"acquisition_channel\", \"plan\"]],\n",
      "    on=[\"user_id\", \"is_churned\", \"status\", \"acquisition_channel\", \"plan\"],\n",
      "    how=\"left\"\n",
      ").copy()\n",
      "\n",
      "fa[\"used_advanced_reports\"] = fa[\"advanced_reports\"] > 0\n",
      "\n",
      "print(\"Overall churn rate check:\", float(base[\"is_churned\"].mean()))\n",
      "\n",
      "worst_channel = channel_stats[\"churn_rate\"].idxmax()\n",
      "print(\"\\nWorst channel:\", worst_channel)\n",
      "print(channel_stats.loc[worst_channel])\n",
      "\n",
      "pcx = fa[fa[\"acquisition_channel\"] == \"paid_campaign_x\"].copy()\n",
      "\n",
      "print(\"\\nPaid Campaign X advanced_reports usage vs churn crosstab:\")\n",
      "print(pd.crosstab(pcx[\"used_advanced_reports\"], pcx[\"status\"], margins=True))\n",
      "\n",
      "print(\"\\nPaid Campaign X advanced_reports adoption by signup cohort:\")\n",
      "print(pcx.groupby(\"signup_month\")[\"used_advanced_reports\"].mean())\n",
      "\n",
      "print(\"\\nPaid Campaign X churn by cohort and advanced_reports usage:\")\n",
      "print(\n",
      "    pcx.groupby([\"signup_month\", \"used_advanced_reports\"])[\"is_churned\"]\n",
      "    .agg([\"mean\", \"count\"])\n",
      ")\n",
      "\n",
      "overall_churn_rate_out = float(base[\"is_churned\"].mean())\n",
      "worst_channel_out = str(worst_channel)\n",
      "\n",
      "key_finding_out = (\n",
      "    \"Churn is 23.77% overall, and paid_campaign_x is the clear outlier with 44.76% churn \"\n",
      "    \"across every signup cohort and plan. The strongest behavioral signal is early adoption \"\n",
      "    \"of advanced_reports: users who adopt it churn far less overall (14.7% vs 27.7%), while \"\n",
      "    \"paid_campaign_x has by far the lowest advanced_reports adoption (18.7% vs ~33-35% for other channels). \"\n",
      "    \"Within paid_campaign_x, churn is concentrated among users who never adopt advanced_reports, \"\n",
      "    \"indicating the retention drop is primarily an acquisition-quality/onboarding problem rather than \"\n",
      "    \"a broad product usage decline.\"\n",
      ")\n",
      "\n",
      "recommendations_out = (\n",
      "    \"Pause or tighten paid_campaign_x targeting; redesign onboarding for campaign_x users to drive \"\n",
      "    \"advanced_reports activation in the first 14 days; align ad and landing-page messaging to the product's \"\n",
      "    \"core reporting use case; and track weekly activation-to-retention funnels by channel with \"\n",
      "    \"advanced_reports adoption as the leading indicator.\"\n",
      ")\n",
      "\n",
      "print(\"\\nPrepared outputs:\")\n",
      "print(\"overall_churn_rate_out =\", overall_churn_rate_out)\n",
      "print(\"worst_channel_out =\", worst_channel_out)\n",
      "print(\"key_finding_out =\", key_finding_out)\n",
      "print(\"recommendations_out =\", recommendations_out)\n",
      "\n",
      "SUBMIT(overall_churn_rate_out, worst_channel_out, key_finding_out, recommendations_out)\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "analyzer = dspy.RLM(\n",
    "    CohortRetentionAnalysis,\n",
    "    max_iters=15,\n",
    "    verbose=True,\n",
    ")\n",
    "\n",
    "result = analyzer(\n",
    "    users=wrapped_users,\n",
    "    events=wrapped_events,\n",
    "    subscriptions=wrapped_subscriptions,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overall Churn Rate: 23.8%\n",
      "Worst Channel:     paid_campaign_x\n",
      "\n",
      "Key Finding:\n",
      "  Churn is 23.77% overall, and paid_campaign_x is the clear outlier with 44.76% churn across every signup cohort and plan. The strongest behavioral signal is early adoption of advanced_reports: users who adopt it churn far less overall (14.7% vs 27.7%), while paid_campaign_x has by far the lowest advanced_reports adoption (18.7% vs ~33-35% for other channels). Within paid_campaign_x, churn is concentrated among users who never adopt advanced_reports, indicating the retention drop is primarily an acquisition-quality/onboarding problem rather than a broad product usage decline.\n",
      "\n",
      "Recommendations:\n",
      "  Pause or tighten paid_campaign_x targeting; redesign onboarding for campaign_x users to drive advanced_reports activation in the first 14 days; align ad and landing-page messaging to the product's core reporting use case; and track weekly activation-to-retention funnels by channel with advanced_reports adoption as the leading indicator.\n",
      "\n",
      "RLM completed in 7 iterations\n"
     ]
    }
   ],
   "source": [
    "print(f\"Overall Churn Rate: {result.overall_churn_rate:.1%}\")\n",
    "print(f\"Worst Channel:     {result.worst_channel}\")\n",
    "print(f\"\\nKey Finding:\\n  {result.key_finding}\")\n",
    "print(f\"\\nRecommendations:\\n  {result.recommendations}\")\n",
    "print(f\"\\nRLM completed in {len(result.trajectory)} iterations\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
