{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c8214f9c",
   "metadata": {},
   "source": [
    "# DeepBrain relationship analysis\n",
    "Synthetic practice only. Read-only database; no network, paid calls or source writes. Change controls in the next cell. All personal answers remain blank."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a7e07f31",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "import sqlite3\n",
    "ROOT=Path.cwd()\n",
    "if not (ROOT/'deepbrain_practice.sqlite').exists(): ROOT=ROOT/'lab'\n",
    "con=sqlite3.connect('file:'+str((ROOT/'deepbrain_practice.sqlite').resolve())+'?mode=ro',uri=True)\n",
    "controls={'definition':'quality','persona':'all','workflow':'all'}\n",
    "def render(sql):\n",
    "    for key,value in controls.items(): sql=sql.replace('{{'+key+'}}', \"'\"+value.replace(\"'\",\"''\")+\"'\")\n",
    "    return sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee874941",
   "metadata": {},
   "source": [
    "## N01 · Compare weekly account-value definitions\n",
    "Weekly distinct accounts, anchored to each milestone time. Full UTC weeks only. Ready/export alternatives do not redefine DB01 or prove audience value. Missing confirmations undercount."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "adfc98b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['week_start', 'series', 'accounts']\n",
      "rows: 70\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,points AS(SELECT account_id,date(chosen_at,'weekday 0','-6 days') week_start,'Selected definition' series FROM selected WHERE chosen=1 UNION ALL SELECT account_id,date(confirmed_use_at,'weekday 0','-6 days'),'Confirmed quality use' FROM selected WHERE confirmed_quality_use=1 UNION ALL SELECT account_id,date(exported_at,'weekday 0','-6 days'),'Export recorded' FROM selected WHERE exported_at IS NOT NULL) SELECT week_start,series,COUNT(DISTINCT account_id) accounts FROM points WHERE week_start>=date((SELECT MIN(signup_at) FROM sim_users)) AND date(week_start,'+7 days')<=date((SELECT value FROM manifest WHERE key='cutoff_utc')) GROUP BY 1,2 ORDER BY 1,2\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f09f3ccf",
   "metadata": {},
   "source": [
    "## N02 · Task drivers and review coverage\n",
    "Task fractions use stated denominators. These are candidate drivers, not additive contributions to distinct-account North Star value. Missing reviews are unknown."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1ecae008",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['driver', 'fraction']\n",
      "rows: 4\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) SELECT 'Render completion / all tasks' driver,AVG(render_success*1.0) fraction FROM selected UNION ALL SELECT 'Selected value / all tasks',AVG(chosen*1.0) FROM selected UNION ALL SELECT 'Reviewed previews / previews',SUM(preview_at IS NOT NULL AND reviewed_at IS NOT NULL)*1.0/NULLIF(SUM(preview_at IS NOT NULL),0) FROM selected UNION ALL SELECT 'Confirmed quality use / all tasks',AVG(confirmed_quality_use*1.0) FROM selected\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71095e3b",
   "metadata": {},
   "source": [
    "## N03 · Explain metric relationships and guardrails\n",
    "Links are labeled identities, descriptive associations or hypotheses. Arrows are not causal weights; selected definition is a practice alternative."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bf72b70f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['link', 'relationship', 'boundary']\n",
      "rows: 5\n"
     ]
    }
   ],
   "source": [
    "sql=\"SELECT 'Arithmetic identity' link,'Eligible account-weeks × confirmed quality-use account share' relationship,'Only within exactly the same eligible account-week population; not task conversion multiplication' boundary UNION ALL SELECT 'Selected alternative',{{definition}},'Changes reach definition only; DB01 remains quality use' UNION ALL SELECT 'Untested causal hypothesis','Recovery and approval clarity → repeat quality use → renewal','Requires intervention and account-level randomized assignment' UNION ALL SELECT 'Observed synthetic association','First-week value ↔ days 28–41 quality return','Confounding and selection remain; not company performance' UNION ALL SELECT 'Independent guardrails','Review coverage, critical errors, privacy, full cost and human work','Do not hide harm inside one composite score'\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "588683d9",
   "metadata": {},
   "source": [
    "## J01 · Recorded task paths and recovery endpoints\n",
    "Widths are tasks. Events ordered by recorded time; review and export are parallel, not forced into a funnel. Missing events are skipped. Terminal state is not proven abandonment. Before-creation timestamps excluded in J02."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "10423178",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['source', 'target', 'tasks']\n",
      "rows: 14\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,events AS(SELECT task_id,created_at stamp,0 ord,'Created' node FROM selected UNION ALL SELECT task_id,ready_at,1,'Ready' FROM selected WHERE ready_at IS NOT NULL UNION ALL SELECT task_id,preview_at,2,'Preview' FROM selected WHERE preview_at IS NOT NULL UNION ALL SELECT task_id,reviewed_at,3,'Review' FROM selected WHERE reviewed_at IS NOT NULL UNION ALL SELECT task_id,exported_at,4,'Export' FROM selected WHERE exported_at IS NOT NULL UNION ALL SELECT task_id,distributed_at,5,'Distribution' FROM selected WHERE distributed_at IS NOT NULL UNION ALL SELECT task_id,confirmed_use_at,6,'Confirmed use' FROM selected WHERE confirmed_use_at IS NOT NULL),bad AS(SELECT DISTINCT e.task_id FROM events e JOIN selected t USING(task_id) WHERE julianday(e.stamp)<julianday(t.created_at)),ordered AS(SELECT *,LEAD(node) OVER(PARTITION BY task_id ORDER BY julianday(stamp),ord) next_node FROM events WHERE task_id NOT IN(SELECT task_id FROM bad)) SELECT node source,COALESCE(next_node,CASE WHEN chosen=1 THEN 'Meets selected definition' WHEN ready_at IS NOT NULL AND reviewed_at IS NULL THEN 'Ready; review unknown' ELSE 'Last recorded checkpoint' END) target,COUNT(*) tasks FROM ordered JOIN selected USING(task_id) GROUP BY 1,2 ORDER BY 1,2\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e58be91c",
   "metadata": {},
   "source": [
    "## J02 · Chronology, missing review and recovery audit\n",
    "Inspect audit counts before interpreting flow. Failed attempts can recover within one task. Unreviewed ready output is unknown quality, not rejected output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b1f7421a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['tasks', 'chronology_excluded', 'ready_review_unknown', 'exports_without_preview', 'tasks_with_failed_attempt', 'recovered_to_quality_value']\n",
      "rows: 1\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,events AS(SELECT task_id,created_at stamp,0 ord,'Created' node FROM selected UNION ALL SELECT task_id,ready_at,1,'Ready' FROM selected WHERE ready_at IS NOT NULL UNION ALL SELECT task_id,preview_at,2,'Preview' FROM selected WHERE preview_at IS NOT NULL UNION ALL SELECT task_id,reviewed_at,3,'Review' FROM selected WHERE reviewed_at IS NOT NULL UNION ALL SELECT task_id,exported_at,4,'Export' FROM selected WHERE exported_at IS NOT NULL UNION ALL SELECT task_id,distributed_at,5,'Distribution' FROM selected WHERE distributed_at IS NOT NULL UNION ALL SELECT task_id,confirmed_use_at,6,'Confirmed use' FROM selected WHERE confirmed_use_at IS NOT NULL),bad AS(SELECT DISTINCT e.task_id FROM events e JOIN selected t USING(task_id) WHERE julianday(e.stamp)<julianday(t.created_at)),ordered AS(SELECT *,LEAD(node) OVER(PARTITION BY task_id ORDER BY julianday(stamp),ord) next_node FROM events WHERE task_id NOT IN(SELECT task_id FROM bad)) SELECT COUNT(*) tasks,SUM(task_id IN(SELECT task_id FROM bad)) chronology_excluded,SUM(ready_at IS NOT NULL AND reviewed_at IS NULL) ready_review_unknown,SUM(exported_at IS NOT NULL AND preview_at IS NULL) exports_without_preview,SUM(failed_attempts>0) tasks_with_failed_attempt,SUM(failed_attempts>0 AND confirmed_quality_use=1) recovered_to_quality_value FROM selected\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fb70790",
   "metadata": {},
   "source": [
    "## G01 · First-week exposure to mature quality return\n",
    "All mature signups including zero activity. Workflow filters first-week task exposure only; later quality return covers any workflow. Quality early exposure requires review and use before day seven. Association is not causal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "97fb0e6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['source', 'target', 'users']\n",
      "rows: 9\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) SELECT 'Mature signups' source,CASE WHEN early_value>0 THEN 'Early selected value' WHEN early_tasks>0 THEN 'Early task; no selected value' ELSE 'No early filtered task' END target,COUNT(*) users FROM cohort GROUP BY 2 UNION ALL SELECT CASE WHEN early_value>0 THEN 'Early selected value' WHEN early_tasks>0 THEN 'Early task; no selected value' ELSE 'No early filtered task' END,CASE WHEN return28_41=1 THEN 'Days 28–41 quality return' ELSE 'No later quality return recorded' END,COUNT(*) FROM cohort GROUP BY 1,2\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ecf57e3",
   "metadata": {},
   "source": [
    "## G02 · Signup cohorts and later quality return\n",
    "Same mature signup-month denominator and fixed day28–41 outcome. Inspect G03 counts. Latest partially mature signup month is not equivalent to a full-month cohort; prediction excludes it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fd38a4d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['cohort_month', 'signal', 'return_fraction', 'eligible_users']\n",
      "rows: 10\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) SELECT cohort_month,CASE WHEN early_value>0 THEN 'Early selected value' ELSE 'No early selected value' END signal,AVG(return28_41*1.0) return_fraction,COUNT(*) eligible_users FROM cohort GROUP BY 1,2 ORDER BY 1,2\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b39e0c28",
   "metadata": {},
   "source": [
    "## G03 · Cohort counts and uncertainty\n",
    "Counts precede rates. Standard error is a descriptive binomial approximation; small groups and clustered team accounts weaken independence. No treatment effect or company baseline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8241befd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['cohort_month', 'signal', 'eligible_users', 'returned_users', 'return_fraction', 'approximate_standard_error']\n",
      "rows: 10\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) SELECT cohort_month,CASE WHEN early_value>0 THEN 'Early selected value' ELSE 'No early selected value' END signal,COUNT(*) eligible_users,SUM(return28_41) returned_users,AVG(return28_41*1.0) return_fraction,sqrt(AVG(return28_41*1.0)*(1-AVG(return28_41*1.0))/COUNT(*)) approximate_standard_error FROM cohort GROUP BY 1,2 ORDER BY 1,2\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fd65933",
   "metadata": {},
   "source": [
    "## G04 · Early task bands versus later return\n",
    "Each point is an activity band, not a person. Aggregation can conceal user differences. Zero-task signups remain. Use counts and persona slices before proposing a test."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "390d8670",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['task_band', 'mean_early_value_tasks', 'return_fraction', 'eligible_users']\n",
      "rows: 4\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) SELECT CASE WHEN early_tasks=0 THEN '0' WHEN early_tasks=1 THEN '1' WHEN early_tasks<=3 THEN '2–3' ELSE '4+' END task_band,AVG(early_value*1.0) mean_early_value_tasks,AVG(return28_41*1.0) return_fraction,COUNT(*) eligible_users FROM cohort GROUP BY 1\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51378220",
   "metadata": {},
   "source": [
    "## G05 · Weekly early signal and later outcome\n",
    "Both lines use the same mature signup-week users. Shared trends can reflect acquisition mix or seasonality; they do not prove leading-signal causality. Edge signup weeks can be partial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c09eea82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['signup_week', 'early_value_fraction', 'return_fraction', 'eligible_users']\n",
      "rows: 20\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) SELECT date(signup_at,'weekday 0','-6 days') signup_week,AVG(early_value>0) early_value_fraction,AVG(return28_41*1.0) return_fraction,COUNT(*) eligible_users FROM cohort GROUP BY 1 ORDER BY 1\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c53d1560",
   "metadata": {},
   "source": [
    "## G06 · Held-out signal calibration\n",
    "Earlier complete mature signup months train group probabilities; latest complete mature month is held out. Missing train group produces null, not zero. Compare actual held-out outcomes; predictions alone prove no improvement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4ba6f70e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['signal', 'heldout_users', 'training_users', 'predicted_return', 'actual_return']\n",
      "rows: 2\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) ,complete AS(SELECT * FROM cohort WHERE julianday(cohort_month,'+1 month','+42 days')<=julianday((SELECT value FROM manifest WHERE key='cutoff_utc'))),split AS(SELECT *,CASE WHEN cohort_month=(SELECT MAX(cohort_month) FROM complete) THEN 'heldout' ELSE 'train' END period,CASE WHEN early_value>0 THEN 'Early selected value' ELSE 'No early selected value' END signal FROM complete),train AS(SELECT signal,AVG(return28_41*1.0) probability,COUNT(*) training_users FROM split WHERE period='train' GROUP BY signal),scored AS(SELECT s.*,t.probability,t.training_users,(SELECT AVG(return28_41*1.0) FROM split WHERE period='train') baseline FROM split s LEFT JOIN train t USING(signal)) SELECT signal,COUNT(*) heldout_users,MAX(training_users) training_users,MAX(probability) predicted_return,AVG(return28_41*1.0) actual_return FROM scored WHERE period='heldout' GROUP BY signal\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48e371e0",
   "metadata": {},
   "source": [
    "## G07 · Held-out prediction versus training base rate\n",
    "Brier error uses actual held-out outcomes. Lower is better. Baseline and signal score exactly the same covered users; positive difference favors the signal. Synthetic exercise, no deployment or causal improvement claim."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "71d78f0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['heldout_users', 'scored_users', 'signal_brier', 'baseline_brier', 'brier_improvement']\n",
      "rows: 1\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) ,complete AS(SELECT * FROM cohort WHERE julianday(cohort_month,'+1 month','+42 days')<=julianday((SELECT value FROM manifest WHERE key='cutoff_utc'))),split AS(SELECT *,CASE WHEN cohort_month=(SELECT MAX(cohort_month) FROM complete) THEN 'heldout' ELSE 'train' END period,CASE WHEN early_value>0 THEN 'Early selected value' ELSE 'No early selected value' END signal FROM complete),train AS(SELECT signal,AVG(return28_41*1.0) probability,COUNT(*) training_users FROM split WHERE period='train' GROUP BY signal),scored AS(SELECT s.*,t.probability,t.training_users,(SELECT AVG(return28_41*1.0) FROM split WHERE period='train') baseline FROM split s LEFT JOIN train t USING(signal)) SELECT COUNT(*) heldout_users,COUNT(probability) scored_users,AVG(CASE WHEN probability IS NOT NULL THEN (probability-return28_41)*(probability-return28_41) END) signal_brier,AVG(CASE WHEN probability IS NOT NULL THEN (baseline-return28_41)*(baseline-return28_41) END) baseline_brier,AVG(CASE WHEN probability IS NOT NULL THEN (baseline-return28_41)*(baseline-return28_41)-(probability-return28_41)*(probability-return28_41) END) brier_improvement FROM scored WHERE period='heldout'\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f77239d1",
   "metadata": {},
   "source": [
    "## G08 · Inspect mature user evidence rows\n",
    "One row per mature signup; joins retain zero early activity. Workflow filters early exposure, not user eligibility or later any-workflow return. Inspect row evidence before claiming activation predicts renewal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "48715555",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['user_id', 'account_id', 'persona', 'primary_workflow', 'cohort_month', 'early_tasks', 'early_value', 'later_quality_return']\n",
      "rows: 224\n"
     ]
    }
   ],
   "source": [
    "sql=\"WITH selected AS(SELECT *,CASE {{definition}} WHEN 'quality' THEN confirmed_quality_use WHEN 'ready' THEN ready_at IS NOT NULL WHEN 'export' THEN exported_at IS NOT NULL END chosen,CASE {{definition}} WHEN 'quality' THEN confirmed_use_at WHEN 'ready' THEN ready_at WHEN 'export' THEN exported_at END chosen_at FROM v_task WHERE ({{persona}}='all' OR persona={{persona}}) AND ({{workflow}}='all' OR workflow={{workflow}})) ,early AS(SELECT user_id,COUNT(*) early_tasks,SUM(CASE WHEN chosen=1 AND julianday(chosen_at)<julianday(signup_at)+7 AND ({{definition}}<>'quality' OR julianday(reviewed_at)<julianday(signup_at)+7) THEN 1 ELSE 0 END) early_value FROM selected WHERE julianday(created_at)>=julianday(signup_at) AND julianday(created_at)<julianday(signup_at)+7 GROUP BY user_id),cohort AS(SELECT c.*,COALESCE(e.early_tasks,0) early_tasks,COALESCE(e.early_value,0) early_value FROM v_user_cohort c LEFT JOIN early e USING(user_id) WHERE mature42=1 AND ({{persona}}='all' OR c.persona={{persona}})) SELECT user_id,account_id,persona,primary_workflow,cohort_month,early_tasks,early_value,return28_41 later_quality_return FROM cohort ORDER BY user_id\"\n",
    "cur=con.execute(render(sql))\n",
    "print([c[0] for c in cur.description])\n",
    "rows=cur.fetchall()\n",
    "print(\"rows:\",len(rows))\n",
    "rows[:20]"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}