T300683-03-ontask-funnel-split.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f68a7909",
   "metadata": {},
   "source": [
    "# Add an Image Funnel Analysis Part III\n",
    "\n",
    "This is the third part of the funnel analysis. This is the same as the second part in that we're examining the \"On task\" to \"Task completion\" part of the funnel. In this case, we do it twice: first for users who skipped onboarding, then for users who completed onboarding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "100ad282",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from collections import defaultdict\n",
    "\n",
    "from wmfdata import spark, mariadb\n",
    "\n",
    "from scipy import stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "30f3f816",
   "metadata": {},
   "outputs": [],
   "source": [
    "## We'll gather data from December 2021 and January 2022, as we per the data gathering\n",
    "## time have complete data for both months.\n",
    "\n",
    "start_date = dt.date(2021, 12, 1)\n",
    "end_date = dt.date(2022, 1, 31)\n",
    "\n",
    "## List of wikis that we're gathering data from:\n",
    "wikis = ['arwiki', 'bnwiki', 'cswiki']\n",
    "\n",
    "## Name of the temporary table we use to only gather data\n",
    "## for users who got \"on task\"\n",
    "on_task_temp_table = 'on_task_newcomers'\n",
    "\n",
    "## Lists of known users to ignore (e.g. test accounts and experienced users)\n",
    "known_users = defaultdict(set)\n",
    "known_users['cswiki'].update([14, 127629, 303170, 342147, 349875, 44133, 100304, 307410, 439792, 444907,\n",
    "                              454862, 456272, 454003, 454846, 92295, 387915, 398470, 416764, 44751, 132801,\n",
    "                              137787, 138342, 268033, 275298, 317739, 320225, 328302, 339583, 341191,\n",
    "                              357559, 392634, 398626, 404765, 420805, 429109, 443890, 448195, 448438,\n",
    "                              453220, 453628, 453645, 453662, 453663, 453664, 440694, 427497, 272273,\n",
    "                              458025, 458487, 458049, 59563, 118067, 188859, 191908, 314640, 390445,\n",
    "                              451069, 459434, 460802, 460885, 79895, 448735, 453176, 467557, 467745,\n",
    "                              468502, 468583, 468603, 474052, 475184, 475185, 475187, 475188, 294174,\n",
    "                              402906, 298011])\n",
    "\n",
    "known_users['kowiki'].update([303170, 342147, 349875, 189097, 362732, 384066, 416362, 38759, 495265,\n",
    "                              515553, 537326, 566963, 567409, 416360, 414929, 470932, 472019, 485036,\n",
    "                              532123, 558423, 571587, 575553, 576758, 360703, 561281, 595100, 595105,\n",
    "                              595610, 596025, 596651, 596652, 596653, 596654, 596655, 596993, 942,\n",
    "                              13810, 536529])\n",
    "\n",
    "known_users['viwiki'].update([451842, 628512, 628513, 680081, 680083, 680084, 680085, 680086, 355424,\n",
    "                              387563, 443216, 682713, 659235, 700934, 705406, 707272, 707303, 707681, 585762])\n",
    "\n",
    "known_users['arwiki'].update([237660, 272774, 775023, 1175449, 1186377, 1506091, 1515147, 1538902,\n",
    "                              1568858, 1681813, 1683215, 1699418, 1699419, 1699425, 1740419, 1759328, 1763990])\n",
    "\n",
    "## Grab the user IDs of known test accounts so they can be added to the exclusion list\n",
    "\n",
    "def get_known_users(wiki):\n",
    "    '''\n",
    "    Get user IDs of known test accounts and return a set of them.\n",
    "    '''\n",
    "    \n",
    "    username_patterns = [\"MMiller\", \"Zilant\", \"Roan\", \"KHarlan\", \"MWang\", \"SBtest\",\n",
    "                         \"Cloud\", \"Rho2019\", \"Test\"]\n",
    "\n",
    "    known_user_query = '''\n",
    "SELECT user_id\n",
    "FROM user\n",
    "WHERE user_name LIKE \"{name_pattern}%\"\n",
    "    '''\n",
    "    \n",
    "    known_users = set()\n",
    "    \n",
    "    for u_pattern in username_patterns:\n",
    "        new_known = mariadb.run(known_user_query.format(\n",
    "            name_pattern = u_pattern), wiki)\n",
    "        known_users = known_users | set(new_known['user_id'])\n",
    "\n",
    "    return(known_users)\n",
    "        \n",
    "for wiki in wikis:\n",
    "    known_users[wiki] = known_users[wiki] | get_known_users(wiki)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeae34c8",
   "metadata": {},
   "source": [
    "## Helper Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "87a527dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_known_users_sql(kd, wiki_column, user_column):\n",
    "    '''\n",
    "    Based on the dictionary `kd` mapping wiki names to sets of user IDs of known users,\n",
    "    create a SQL expression to exclude users based on the name of the wiki matching `wiki_column`\n",
    "    and the user ID not matching `user_column`\n",
    "    '''\n",
    "    \n",
    "    wiki_exp = '''({w_column} = '{wiki}' AND {u_column} NOT IN ({id_list}))'''\n",
    "    \n",
    "    expressions = list()\n",
    "\n",
    "    ## Iteratively build the expression for each wiki\n",
    "    for wiki_name, wiki_users in kd.items():\n",
    "        expressions.append(wiki_exp.format(\n",
    "            w_column = wiki_column,\n",
    "            wiki = wiki_name,\n",
    "            u_column = user_column,\n",
    "            id_list = ','.join([str(u) for u in wiki_users])\n",
    "        ))\n",
    "    \n",
    "    ## We then join all the expressions with an OR, and we're done.\n",
    "    return(' OR '.join(expressions))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cdd8891b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_partition_statement(start_ts, end_ts, prefix = ''):\n",
    "    '''\n",
    "    This takes the two timestamps and creates a statement that selects\n",
    "    partitions based on `year`, `month`, and `day` in order to make our\n",
    "    data gathering not use excessive amounts of data. It assumes that\n",
    "    `start_ts` and `end_ts` are either in the same year, or if spanning\n",
    "    a year boundary are within a month apart.\n",
    "    This assumption simplifies the code and output a lot.\n",
    "    \n",
    "    An optional prefix can be set to enable selecting partitions for\n",
    "    multiple tables with different aliases.\n",
    "    \n",
    "    :param start_ts: start timestamp\n",
    "    :type start_ts: datetime.datetime\n",
    "    \n",
    "    :param end_ts: end timestamp\n",
    "    :type end_ts: datetime.datetime\n",
    "    \n",
    "    :param prefix: prefix to use in front of partition clauses, \".\" is added automatically\n",
    "    :type prefix: str\n",
    "    '''\n",
    "    \n",
    "    if prefix:\n",
    "        prefix = f'{prefix}.' # adds \".\" after the prefix\n",
    "    \n",
    "    # there are three cases:\n",
    "    # 1: month and year are the same, output a \"BETWEEN\" statement with the days\n",
    "    # 2: the years are the same, and the months differ by 1: output a statement for each month\n",
    "    # 3: the years are the same: create a list of statements from start_ts.month to end_ts.month,\n",
    "    #    return them OR'ed together\n",
    "    # 4: the years differ by 1, start_ts is December and end_ts is January, do the same as #2\n",
    "    # 5: anything else, raise an exception because this isn't implemented yet.\n",
    "    \n",
    "    if start_ts.year == end_ts.year and start_ts.month == end_ts.month:\n",
    "        return(f'''{prefix}year = {start_ts.year}\n",
    "AND {prefix}month = {start_ts.month}\n",
    "AND {prefix}day BETWEEN {start_ts.day} AND {end_ts.day}''')\n",
    "    elif start_ts.year == end_ts.year and (end_ts.month - start_ts.month) == 1:\n",
    "        return(f'''\n",
    "(\n",
    "    ({prefix}year = {start_ts.year}\n",
    "     AND {prefix}month = {start_ts.month}\n",
    "     AND {prefix}day >= {start_ts.day})\n",
    " OR ({prefix}year = {end_ts.year}\n",
    "     AND {prefix}month = {end_ts.month}\n",
    "     AND {prefix}day <= {end_ts.day})\n",
    ")''')\n",
    "    elif start_ts.year == end_ts.year:\n",
    "        # do the start month as a list\n",
    "        parts = [f'''({prefix}year = {start_ts.year}\n",
    "     AND {prefix}month = {start_ts.month}\n",
    "     AND {prefix}day >= {start_ts.day})''']\n",
    "        # for month +1 to end month, add each month\n",
    "        for m in range(start_ts.month+1, end_ts.month):\n",
    "            parts.append(f'''({prefix}year = {start_ts.year}\n",
    "            AND {prefix}month = {m})''')\n",
    "        # then append the end month and return a parenthesis OR'ed together of all of it\n",
    "        parts.append(f'''({prefix}year = {end_ts.year}\n",
    "     AND {prefix}month = {end_ts.month}\n",
    "     AND {prefix}day <= {end_ts.day})''')\n",
    "        return('({})'.format(\n",
    "            '\\nOR\\n'.join(parts)\n",
    "        ))\n",
    "    elif (end_ts.year - start_ts.year) == 1 and start_ts.month == 12 and end_ts.month == 1:\n",
    "        return(f'''\n",
    "(\n",
    "    ({prefix}year = {start_ts.year}\n",
    "     AND {prefix}month = {start_ts.month}\n",
    "     AND {prefix}day >= {start_ts.day})\n",
    " OR ({prefix}year = {end_ts.year}\n",
    "     AND {prefix}month = {end_ts.month}\n",
    "     AND {prefix}day <= {end_ts.day})\n",
    ")''')\n",
    "    else:\n",
    "        raise Exception('Difference between start and end timestamps is not implemented. See code for details.')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5c8afac9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_variant_data(wikis, variant_property = 'growthexperiments-homepage-variant'):\n",
    "    '''\n",
    "    Connects to the given wikis and queries for the value of the user property that\n",
    "    defines what experiment variant the users are in. This can later be used to\n",
    "    filter out all users who are not in a specific variant\n",
    "    (e.g. \"imagerecommendation\" for Add an Image)\n",
    "    '''\n",
    "    \n",
    "    variant_query = f'''\n",
    "    SELECT\n",
    "      DATABASE() AS wiki,\n",
    "      up_user AS user_id,\n",
    "      up_value AS variant_name\n",
    "    FROM user_properties\n",
    "    WHERE up_property = \"{variant_property}\"\n",
    "    '''\n",
    "    \n",
    "    return(mariadb.run(variant_query, wikis))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "85b25d8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def round_perc(x, y, prec = 1):\n",
    "    return(round(100.0 * x / y, prec))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "80ca51db",
   "metadata": {},
   "outputs": [],
   "source": [
    "def round_perc_df(df_x, df_y, prec = 1):\n",
    "    return(round(100.0 * len(df_x) / len(df_y), prec))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "664016e5",
   "metadata": {},
   "source": [
    "## Onboarding to On Task Funnel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8d7da7ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "onboarding_funnel_query = '''\n",
    "WITH hp_visits AS (\n",
    "    SELECT\n",
    "        -- HomepageVisit is the authoritative source here, and we're grouping by\n",
    "        -- homepage_pageview_token to deduplicate the multiple module impressions\n",
    "        hpv.event.homepage_pageview_token,\n",
    "        FIRST_VALUE(hpv.wiki) AS wiki,\n",
    "        FIRST_VALUE(hpv.event.user_id) AS user_id,\n",
    "        FIRST_VALUE(hpv.event.is_mobile) AS is_mobile,\n",
    "        FIRST_VALUE(hpv.dt) AS visit_dt,\n",
    "        FIRST_VALUE(1) AS homepage_visit,\n",
    "        FIRST_VALUE(IF(ssac.event.userid IS NOT NULL, 1, 0)) AS is_newcomer,\n",
    "        FIRST_VALUE(IF(unix_timestamp(hpv.dt, \"yyyy-MM-dd'T'HH:mm:ss.SSSS'Z'\") -\n",
    "                        unix_timestamp(ssac.dt, \"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'\") < 60*60*24, 1, 0))\n",
    "            AS is_24hr_visit\n",
    "    FROM event.homepagevisit AS hpv\n",
    "    JOIN event.homepagemodule AS hpm\n",
    "    ON hpv.event.homepage_pageview_token = hpm.event.homepage_pageview_token\n",
    "    LEFT JOIN event.serversideaccountcreation AS ssac\n",
    "    ON hpv.wiki = ssac.wiki\n",
    "    AND hpv.event.user_id = ssac.event.userid\n",
    "    WHERE {hpv_partition_statement}\n",
    "    AND hpv.wiki IN ({wiki_list})\n",
    "    AND {hpv_known_user_id_expression}\n",
    "    AND {hpm_partition_statement}\n",
    "    AND {ssac_partition_statement}\n",
    "    AND ssac.wiki IN ({wiki_list})\n",
    "    AND hpv.event.is_mobile = true\n",
    "    AND hpm.event.action = \"impression\"\n",
    "    GROUP BY hpv.event.homepage_pageview_token\n",
    "),\n",
    "newcomer_tasks AS (\n",
    "    -- grab unique task token/task type data from newcomer tasks\n",
    "    SELECT\n",
    "        DISTINCT event.newcomer_task_token, event.task_type, event.page_id\n",
    "    FROM event.newcomertask\n",
    "    WHERE {partition_statement}\n",
    "),\n",
    "addimage_task_clicks AS (\n",
    "    -- clicks to Add an Image tasks\n",
    "    SELECT\n",
    "        event.homepage_pageview_token,\n",
    "        dt AS click_dt,\n",
    "        nt.page_id,\n",
    "        row_number() OVER (PARTITION BY hpm.wiki, hpm.event.user_id ORDER BY hpm.dt) AS click_number\n",
    "    FROM hp_visits AS hpv\n",
    "    JOIN event.homepagemodule AS hpm\n",
    "    ON hpv.homepage_pageview_token = hpm.event.homepage_pageview_token\n",
    "    JOIN newcomer_tasks AS nt\n",
    "    ON str_to_map(hpm.event.action_data, \";\", \"=\")[\"newcomerTaskToken\"] = nt.newcomer_task_token\n",
    "    WHERE {partition_statement}\n",
    "    AND hpm.wiki IN ({wiki_list})\n",
    "    AND event.action IN (\"se-task-click\", \"se-edit-button-click\")\n",
    "    AND nt.task_type = \"image-recommendation\"\n",
    "    AND dt > hpv.visit_dt\n",
    "),\n",
    "first_task_click AS (\n",
    "    SELECT\n",
    "        *\n",
    "    FROM addimage_task_clicks\n",
    "    WHERE click_number = 1\n",
    "),\n",
    "nosuggestion_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN first_task_click AS ftc\n",
    "    ON stimg.homepage_pageview_token = ftc.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"nosuggestions_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND stimg.dt > ftc.click_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "addimage_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN first_task_click AS ftc\n",
    "    ON stimg.homepage_pageview_token = ftc.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface IN (\"machinesuggestions_mode\", \"recommendedimagetoolbar_dialog\")\n",
    "    AND action = \"impression\"\n",
    "    AND stimg.dt > ftc.click_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step1_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN addimage_impression AS ai\n",
    "    ON stimg.homepage_pageview_token = ai.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_1_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    -- not limiting by timestamp because I'm unsure if it always occurs after\n",
    "    -- the first impression of the interface\n",
    "    GROUP BY stimg.homepage_pageview_token \n",
    "),\n",
    "onb_step1_skip AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step1_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_1_dialog\"\n",
    "    AND action = \"skip_all\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step2_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step1_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_2_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step2_skip AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step2_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_2_dialog\"\n",
    "    AND action = \"skip_all\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step3_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step2_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_3_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step3_skip AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step3_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_3_dialog\"\n",
    "    AND action = \"skip_all\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step4_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step3_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_4_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "onb_step4_getstarted AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN onb_step4_impression AS onsi\n",
    "    ON stimg.homepage_pageview_token = onsi.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"onboarding_step_4_dialog\"\n",
    "    AND action = \"get_started\"\n",
    "    AND stimg.dt > onsi.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "on_task AS (\n",
    "    SELECT\n",
    "        homepage_pageview_token,\n",
    "        MIN(event_dt) AS event_dt\n",
    "    FROM (\n",
    "        SELECT homepage_pageview_token, event_dt\n",
    "        FROM onb_step1_skip\n",
    "        UNION ALL\n",
    "        SELECT homepage_pageview_token, event_dt\n",
    "        FROM onb_step2_skip\n",
    "        UNION ALL\n",
    "        SELECT homepage_pageview_token, event_dt\n",
    "        FROM onb_step3_skip\n",
    "        UNION ALL\n",
    "        SELECT homepage_pageview_token, event_dt\n",
    "        FROM onb_step4_getstarted\n",
    "    ) AS ontask_events\n",
    "    GROUP BY homepage_pageview_token\n",
    ")\n",
    "SELECT\n",
    "    hpv.*,\n",
    "    ftc.click_dt,\n",
    "    ftc.click_number,\n",
    "    ftc.page_id,\n",
    "    IF(nosuggestion_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS nosuggestion_impression,\n",
    "    nosuggestion_impression.event_dt AS nosuggestion_impression_dt,\n",
    "    IF(addimage_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS addimage_impression,\n",
    "    addimage_impression.event_dt AS addimage_impression_dt,\n",
    "    IF(onb_step1_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step1_impression,\n",
    "    onb_step1_impression.event_dt AS onboarding_step1_impression_dt,\n",
    "    IF(onb_step1_skip.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step1_skipall,\n",
    "    onb_step1_skip.event_dt AS onboarding_step1_skipall_dt,\n",
    "    IF(onb_step2_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step2_impression,\n",
    "    onb_step2_impression.event_dt AS onboarding_step2_impression_dt,\n",
    "    IF(onb_step2_skip.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step2_skipall,\n",
    "    onb_step2_skip.event_dt AS onboarding_step2_skipall_dt,\n",
    "    IF(onb_step3_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step3_impression,\n",
    "    onb_step3_impression.event_dt AS onboarding_step3_impression_dt,\n",
    "    IF(onb_step3_skip.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step3_skipall,\n",
    "    onb_step3_skip.event_dt AS onboarding_step3_skipall_dt,\n",
    "    IF(onb_step4_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step4_impression,\n",
    "    onb_step4_impression.event_dt AS onboarding_step4_impression_dt,\n",
    "    IF(onb_step4_getstarted.homepage_pageview_token IS NOT NULL, 1, 0) AS onboarding_step4_getstarted,\n",
    "    onb_step4_getstarted.event_dt AS onboarding_step4_getstarted_dt,\n",
    "    IF(on_task.homepage_pageview_token IS NOT NULL, 1, 0) AS on_task,\n",
    "    on_task.event_dt AS on_task_dt\n",
    "FROM hp_visits AS hpv\n",
    "LEFT JOIN first_task_click AS ftc\n",
    "ON hpv.homepage_pageview_token = ftc.homepage_pageview_token\n",
    "LEFT JOIN nosuggestion_impression\n",
    "ON hpv.homepage_pageview_token = nosuggestion_impression.homepage_pageview_token\n",
    "LEFT JOIN addimage_impression\n",
    "ON hpv.homepage_pageview_token = addimage_impression.homepage_pageview_token\n",
    "LEFT JOIN onb_step1_impression\n",
    "ON hpv.homepage_pageview_token = onb_step1_impression.homepage_pageview_token\n",
    "LEFT JOIN onb_step1_skip\n",
    "ON hpv.homepage_pageview_token = onb_step1_skip.homepage_pageview_token\n",
    "LEFT JOIN onb_step2_impression\n",
    "ON hpv.homepage_pageview_token = onb_step2_impression.homepage_pageview_token\n",
    "LEFT JOIN onb_step2_skip\n",
    "ON hpv.homepage_pageview_token = onb_step2_skip.homepage_pageview_token\n",
    "LEFT JOIN onb_step3_impression\n",
    "ON hpv.homepage_pageview_token = onb_step3_impression.homepage_pageview_token\n",
    "LEFT JOIN onb_step3_skip\n",
    "ON hpv.homepage_pageview_token = onb_step3_skip.homepage_pageview_token\n",
    "LEFT JOIN onb_step4_impression\n",
    "ON hpv.homepage_pageview_token = onb_step4_impression.homepage_pageview_token\n",
    "LEFT JOIN onb_step4_getstarted\n",
    "ON hpv.homepage_pageview_token = onb_step4_getstarted.homepage_pageview_token\n",
    "LEFT JOIN on_task\n",
    "ON hpv.homepage_pageview_token = on_task.homepage_pageview_token\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "402389ba",
   "metadata": {},
   "source": [
    "## TODO\n",
    "\n",
    "1. [x] Run the mobile onboarding funnel query.\n",
    "2. [x] Join with variant data as before.\n",
    "3. [x] Select mobile Add an Image newcomers first visit as before.\n",
    "4. [x] Select those who got on task from the previous step.\n",
    "5. [x] Create a PySpark dataframe from that.\n",
    "6. [x] Register the dataframe as a temporary table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "69d74557",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "mobile_onb_funnel_data = spark.run(\n",
    "    onboarding_funnel_query.format(\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        hpv_known_user_id_expression = make_known_users_sql(known_users, 'hpv.wiki', 'hpv.event.user_id'),\n",
    "        hpv_partition_statement = make_partition_statement(start_date, end_date, 'hpv'),\n",
    "        hpm_partition_statement = make_partition_statement(start_date, end_date, 'hpm'),\n",
    "        ssac_partition_statement = make_partition_statement(start_date, end_date, 'ssac'),\n",
    "        partition_statement = make_partition_statement(start_date, end_date),\n",
    "    ), session_type = 'yarn-large'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1ab9d199",
   "metadata": {},
   "outputs": [],
   "source": [
    "variant_data = get_variant_data(wikis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8543e363",
   "metadata": {},
   "outputs": [],
   "source": [
    "variant_data['variant_name'] = variant_data['variant_name'].apply(lambda v: v.decode('utf-8'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b87cf748",
   "metadata": {},
   "outputs": [],
   "source": [
    "variant_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "fb1efcc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "mobile_onb_funnel_data = mobile_onb_funnel_data.merge(variant_data,\n",
    "                                                     on = ['wiki', 'user_id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb0375f4",
   "metadata": {},
   "source": [
    "## Discarding Invalid Sessions\n",
    "\n",
    "Later investigation of task completion based on whether users skipped or completed onboarding revealed that we have some invalid sessions where users did both. We'll discard those sessions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f64269b",
   "metadata": {},
   "outputs": [],
   "source": [
    "mobile_onb_funnel_data.loc[\n",
    "    ((mobile_onb_funnel_data['onboarding_step1_skipall'] == 1) |\n",
    "     (mobile_onb_funnel_data['onboarding_step2_skipall'] == 1) |\n",
    "     (mobile_onb_funnel_data['onboarding_step3_skipall'] == 1)\n",
    "    ) &\n",
    "    (mobile_onb_funnel_data['onboarding_step4_getstarted'] == 1),\n",
    "    'homepage_pageview_token'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "ecb0a18b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def addimage_mobile_newcomers(df):\n",
    "    return(df.loc[\n",
    "        (df['variant_name'] == 'imagerecommendation') &\n",
    "        (df['is_newcomer'] == 1) &\n",
    "        (df['is_24hr_visit'] == 1) &\n",
    "        (df['is_mobile'] == True)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "40192d25",
   "metadata": {},
   "outputs": [],
   "source": [
    "mob_newcomers = addimage_mobile_newcomers(mobile_onb_funnel_data.loc[\n",
    "    ~mobile_onb_funnel_data['homepage_pageview_token'].isin(\n",
    "        mobile_onb_funnel_data.loc[\n",
    "            ((mobile_onb_funnel_data['onboarding_step1_skipall'] == 1) |\n",
    "             (mobile_onb_funnel_data['onboarding_step2_skipall'] == 1) |\n",
    "             (mobile_onb_funnel_data['onboarding_step3_skipall'] == 1)\n",
    "            ) &\n",
    "            (mobile_onb_funnel_data['onboarding_step4_getstarted'] == 1),\n",
    "            'homepage_pageview_token'\n",
    "        ]\n",
    "    )\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52f494b7",
   "metadata": {},
   "source": [
    "We limit the columns to the ones we need in the subsequent query, select for users who got \"on task\", make a copy, then enforce the `page_id` to `int` so that it has the right data type for the later join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "95ffacce",
   "metadata": {},
   "outputs": [],
   "source": [
    "on_task_colnames = [\n",
    "    'homepage_pageview_token', 'wiki', 'user_id', 'is_mobile',\n",
    "    'click_dt', 'page_id', 'on_task', 'on_task_dt'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "a376a3fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "on_task_newcomers = mob_newcomers.loc[mob_newcomers['on_task'] == 1, on_task_colnames].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "a7aed8aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "on_task_newcomers['page_id'] = on_task_newcomers['page_id'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "44d63f74",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "spark_session = spark.get_session()\n",
    "on_task_sdf = spark_session.createDataFrame(on_task_newcomers)\n",
    "on_task_sdf.createOrReplaceGlobalTempView(on_task_temp_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3656127a",
   "metadata": {},
   "source": [
    "## Funnel Definition\n",
    "\n",
    "We have a complex definition of \"on task\", and a query that gives us that. What I want to do here is to rerun the onboarding funnel query but store the resulting dataframe on the cluster. Then I'll use that dataframe to query only the sessions of newcomers on mobile who got \"on task\", and from that identify what they did throughout the remainder of the session.\n",
    "\n",
    "Let's define that funnel more specfiically:\n",
    "\n",
    "1. User is on task.\n",
    "2. Makes a decision (yes/no/not sure).\n",
    "    1. User chose \"not sure\" (aka \"skip\"), confirms , and is returned to the Homepage/Suggested Edits.\n",
    "    2. User chose \"no\", and confirms the rejection. Post-edit dialogue is shown with \"Thanks for reviewing…\" message.\n",
    "    3. User chose \"yes\", and is taken to the caption help.\n",
    "3. User writes a caption, clicks \"Publish changes…\"\n",
    "4. User writes an edit summary and clicks \"Publish changes\" to complete the task.\n",
    "5. Post-edit dialogue is shown with \"You've published an edit\" message.\n",
    "    1. Edit is rejected within 48 hours.\n",
    "    2. User sees post-edit dialogue, makes choices.\n",
    "\n",
    "Similarly as for the onboarding funnel, we have multiple ways to task completion, and so they'll come together in a `UNION` step to make aggregation of those straigthforward if we want to do so.\n",
    "\n",
    "There are two ways into the post-edit dialogue, one is by rejecting the suggestion, the other is by completing the task. Options at the post-edit dialogue step are the same. We'll most likely want to make this into two separate funnels so we can identify differences in behaviour at that step based on how users got there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d7206201",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_funnel_query = '''\n",
    "WITH on_task_sessions AS (\n",
    "    SELECT\n",
    "        wiki,\n",
    "        user_id,\n",
    "        page_id,\n",
    "        homepage_pageview_token,\n",
    "        on_task_dt AS event_dt\n",
    "    FROM global_temp.{on_task_table}\n",
    "),\n",
    "choose_unsure AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN on_task_sessions\n",
    "    ON stimg.homepage_pageview_token = on_task_sessions.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"recommendedimagetoolbar_dialog\"\n",
    "    AND action = \"suggestion_skip\"\n",
    "    AND dt > on_task_sessions.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "confirm_skip AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN choose_unsure\n",
    "    ON stimg.homepage_pageview_token = choose_unsure.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"skip_dialog\"\n",
    "    AND action = \"confirm_skip_suggestion\"\n",
    "    AND dt > choose_unsure.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "choose_reject AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN on_task_sessions\n",
    "    ON stimg.homepage_pageview_token = on_task_sessions.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"recommendedimagetoolbar_dialog\"\n",
    "    AND action = \"suggestion_reject\"\n",
    "    AND dt > on_task_sessions.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "rejection_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN choose_reject\n",
    "    ON stimg.homepage_pageview_token = choose_reject.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"rejection_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND dt > choose_reject.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "confirm_reject AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN rejection_impression\n",
    "    ON stimg.homepage_pageview_token = rejection_impression.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"rejection_dialog\"\n",
    "    AND action = \"close\"\n",
    "    AND str_to_map(action_data, \";\", \"=\")[\"acceptance_state\"] = \"rejected\"\n",
    "    AND dt > rejection_impression.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "choose_accept AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN on_task_sessions\n",
    "    ON stimg.homepage_pageview_token = on_task_sessions.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"recommendedimagetoolbar_dialog\"\n",
    "    AND action = \"suggestion_accept\"\n",
    "    AND dt > on_task_sessions.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "captioninfo_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN choose_accept\n",
    "    ON stimg.homepage_pageview_token = choose_accept.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"captioninfo_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND dt > choose_accept.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "captioninfo_close AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN captioninfo_impression\n",
    "    ON stimg.homepage_pageview_token = captioninfo_impression.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"captioninfo_dialog\"\n",
    "    AND action = \"close\"\n",
    "    AND dt > captioninfo_impression.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "caption_entry_focus AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN captioninfo_close\n",
    "    ON stimg.homepage_pageview_token = captioninfo_close.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"caption_entry\"\n",
    "    AND action = \"focus\"\n",
    "    AND dt > captioninfo_close.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "editsummary_impression AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN caption_entry_focus\n",
    "    ON stimg.homepage_pageview_token = caption_entry_focus.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"editsummary_dialog\"\n",
    "    AND action = \"impression\"\n",
    "    AND dt > caption_entry_focus.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "editsummary_save AS (\n",
    "    SELECT\n",
    "        stimg.homepage_pageview_token,\n",
    "        MIN(dt) AS event_dt\n",
    "    FROM event.mediawiki_structured_task_article_image_suggestion_interaction AS stimg\n",
    "    JOIN editsummary_impression\n",
    "    ON stimg.homepage_pageview_token = editsummary_impression.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND active_interface = \"editsummary_dialog\"\n",
    "    AND action = \"editsummary_save\"\n",
    "    AND dt > editsummary_impression.event_dt\n",
    "    GROUP BY stimg.homepage_pageview_token\n",
    "),\n",
    "edit_revert AS (\n",
    "    -- edits tagged with add image (wiki, user_id, page_id, timestamp)\n",
    "    -- whether the edit was reverted within 48 hours\n",
    "    -- should be able to match editsummary_save event within say 30 seconds?\n",
    "    SELECT\n",
    "        `database` AS wiki,\n",
    "        rev_id,\n",
    "        FIRST_VALUE(page_id) AS page_id,\n",
    "        FIRST_VALUE(performer.user_id) AS user_id,\n",
    "        FIRST_VALUE(rev_timestamp) AS rev_timestamp,\n",
    "        MAX(IF(array_contains(tags, 'mw-reverted') AND\n",
    "               (unix_timestamp(meta.dt, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "                unix_timestamp(rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") < 60*60*48), 1, 0)) AS was_reverted\n",
    "    FROM event_sanitized.mediawiki_revision_tags_change\n",
    "    WHERE {partition_statement}\n",
    "    AND `database` IN ({wiki_list})\n",
    "    AND ({known_user_database_expression})\n",
    "    AND array_contains(tags, \"newcomer task image suggestion\")\n",
    "    GROUP BY wiki, rev_id\n",
    "),\n",
    "completed_task AS (\n",
    "    -- union of confirm_reject and editsummary_save\n",
    "    -- as both of those paths leads to the post-edit dialogue\n",
    "    SELECT\n",
    "        homepage_pageview_token,\n",
    "        MIN(event_dt) AS event_dt\n",
    "    FROM (\n",
    "        SELECT homepage_pageview_token, event_dt\n",
    "        FROM confirm_reject\n",
    "        UNION ALL\n",
    "        SELECT homepage_pageview_token, event_dt\n",
    "        FROM editsummary_save\n",
    "    ) AS comptask_events\n",
    "    GROUP BY homepage_pageview_token\n",
    ")\n",
    "SELECT\n",
    "    ots.*,\n",
    "    IF(choose_unsure.homepage_pageview_token IS NOT NULL, 1, 0) AS chose_unsure,\n",
    "    choose_unsure.event_dt AS chose_unsure_dt,\n",
    "    IF(confirm_skip.homepage_pageview_token IS NOT NULL, 1, 0) AS confirmed_skip,\n",
    "    confirm_skip.event_dt AS confirmed_skip_dt,\n",
    "    IF(choose_reject.homepage_pageview_token IS NOT NULL, 1, 0) AS chose_reject,\n",
    "    choose_reject.event_dt AS chose_reject_dt,\n",
    "    IF(rejection_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS rejection_dialog_impression,\n",
    "    rejection_impression.event_dt AS rejection_dialog_impression_dt,\n",
    "    IF(confirm_reject.homepage_pageview_token IS NOT NULL, 1, 0) AS confirmed_reject,\n",
    "    confirm_reject.event_dt AS confirmed_reject_dt,\n",
    "    IF(choose_accept.homepage_pageview_token IS NOT NULL, 1, 0) AS choose_accept,\n",
    "    choose_accept.event_dt AS choose_accept_dt,\n",
    "    IF(captioninfo_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS captioninfo_dialog_impression,\n",
    "    captioninfo_impression.event_dt AS captioninfo_dialog_impression_dt,\n",
    "    IF(captioninfo_close.homepage_pageview_token IS NOT NULL, 1, 0) AS closed_captioninfo_dialog,\n",
    "    captioninfo_close.event_dt AS closed_captioninfo_dialog_dt,\n",
    "    IF(caption_entry_focus.homepage_pageview_token IS NOT NULL, 1, 0) AS caption_entry_focused,\n",
    "    caption_entry_focus.event_dt AS caption_entry_focused_dt,\n",
    "    IF(editsummary_impression.homepage_pageview_token IS NOT NULL, 1, 0) AS editsummary_dialog_impression,\n",
    "    editsummary_impression.event_dt AS editsummary_dialog_impression_dt,\n",
    "    IF(editsummary_save.homepage_pageview_token IS NOT NULL, 1, 0) AS editsummary_chose_save,\n",
    "    editsummary_save.event_dt AS editsummary_chose_save_dt,\n",
    "    IF(edit_revert.page_id IS NOT NULL, 1, 0) AS tagged_edit_saved,\n",
    "    edit_revert.rev_timestamp AS tagged_edit_saved_dt,\n",
    "    COALESCE(edit_revert.was_reverted, 0) AS tagged_edit_reverted,\n",
    "    IF(completed_task.homepage_pageview_token IS NOT NULL, 1, 0) AS task_completed,\n",
    "    completed_task.event_dt AS task_completed_dt\n",
    "FROM on_task_sessions AS ots\n",
    "LEFT JOIN choose_unsure\n",
    "ON ots.homepage_pageview_token = choose_unsure.homepage_pageview_token\n",
    "LEFT JOIN confirm_skip\n",
    "ON ots.homepage_pageview_token = confirm_skip.homepage_pageview_token\n",
    "LEFT JOIN choose_reject\n",
    "ON ots.homepage_pageview_token = choose_reject.homepage_pageview_token\n",
    "LEFT JOIN rejection_impression\n",
    "ON ots.homepage_pageview_token = rejection_impression.homepage_pageview_token\n",
    "LEFT JOIN confirm_reject\n",
    "ON ots.homepage_pageview_token = confirm_reject.homepage_pageview_token\n",
    "LEFT JOIN choose_accept\n",
    "ON ots.homepage_pageview_token = choose_accept.homepage_pageview_token\n",
    "LEFT JOIN captioninfo_impression\n",
    "ON ots.homepage_pageview_token = captioninfo_impression.homepage_pageview_token\n",
    "LEFT JOIN captioninfo_close\n",
    "ON ots.homepage_pageview_token = captioninfo_close.homepage_pageview_token\n",
    "LEFT JOIN caption_entry_focus\n",
    "ON ots.homepage_pageview_token = caption_entry_focus.homepage_pageview_token\n",
    "LEFT JOIN editsummary_impression\n",
    "ON ots.homepage_pageview_token = editsummary_impression.homepage_pageview_token\n",
    "LEFT JOIN editsummary_save\n",
    "ON ots.homepage_pageview_token = editsummary_save.homepage_pageview_token\n",
    "LEFT JOIN edit_revert\n",
    "ON ots.wiki = edit_revert.wiki\n",
    "AND ots.user_id = edit_revert.user_id\n",
    "AND ots.page_id = edit_revert.page_id\n",
    "LEFT JOIN completed_task\n",
    "ON ots.homepage_pageview_token = completed_task.homepage_pageview_token\n",
    "WHERE (edit_revert.page_id IS NULL\n",
    "       OR editsummary_save.homepage_pageview_token IS NULL\n",
    "       -- within 1 minute\n",
    "       OR abs(unix_timestamp(edit_revert.rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "              unix_timestamp(editsummary_save.event_dt, \"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'\")) < 60)             \n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "9bf3aadd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "task_completion_data = spark.run(\n",
    "    task_completion_funnel_query.format(\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        hpv_known_user_id_expression = make_known_users_sql(known_users, 'hpv.wiki', 'hpv.event.user_id'),\n",
    "        hpv_partition_statement = make_partition_statement(start_date, end_date, 'hpv'),\n",
    "        hpm_partition_statement = make_partition_statement(start_date, end_date, 'hpm'),\n",
    "        ssac_partition_statement = make_partition_statement(start_date, end_date, 'ssac'),\n",
    "        partition_statement = make_partition_statement(start_date, end_date),\n",
    "        on_task_table = on_task_temp_table,\n",
    "        known_user_database_expression = make_known_users_sql(known_users,\n",
    "                                                              '`database`', 'performer.user_id'),\n",
    "    ), session_type = 'yarn-large'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a970e1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09187596",
   "metadata": {},
   "source": [
    "## Inspecting Erroneous Conditions\n",
    "\n",
    "Confirming to skip the suggestion should end the session and take the user back to Suggested Edits. Confirming to reject the suggestion should end the session and show the post-edit dialogue. Saving the edit works similarly as rejecting the suggestion. This gives us three combinations of pairs of conditions that should not occur in the same session."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0640cb5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data.loc[\n",
    "    (task_completion_data['confirmed_skip'] == 1) &\n",
    "    (task_completion_data['confirmed_reject'] == 1),\n",
    "    ['homepage_pageview_token',\n",
    "     'chose_unsure', 'chose_unsure_dt', 'confirmed_skip', 'confirmed_skip_dt',\n",
    "     'chose_reject', 'confirmed_reject_dt', 'editsummary_chose_save', 'editsummary_chose_save_dt']\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d56303b",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data.loc[\n",
    "    (task_completion_data['confirmed_skip'] == 1) &\n",
    "    (task_completion_data['editsummary_chose_save'] == 1),\n",
    "    ['chose_unsure', 'chose_unsure_dt', 'confirmed_skip', 'confirmed_skip_dt',\n",
    "     'chose_reject', 'confirmed_reject_dt', 'editsummary_chose_save', 'editsummary_chose_save_dt']\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e3db779",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data.loc[\n",
    "    (task_completion_data['confirmed_reject'] == 1) &\n",
    "    (task_completion_data['editsummary_chose_save'] == 1),\n",
    "    ['chose_unsure', 'chose_unsure_dt', 'confirmed_skip', 'confirmed_skip_dt',\n",
    "     'chose_reject', 'confirmed_reject_dt', 'editsummary_chose_save', 'editsummary_chose_save_dt']\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9eaf8ac4",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data.loc[\n",
    "    (task_completion_data['confirmed_reject'] == 1) &\n",
    "    (task_completion_data['editsummary_chose_save'] == 1) &\n",
    "    (task_completion_data['confirmed_skip'] == 1),\n",
    "    ['chose_unsure', 'chose_unsure_dt', 'confirmed_skip', 'confirmed_skip_dt',\n",
    "     'chose_reject', 'confirmed_reject_dt', 'editsummary_chose_save', 'editsummary_chose_save_dt']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b41c8f21",
   "metadata": {},
   "source": [
    "We find no session has all three, but there are sessions that have combinations of these. This indicates that we have some data that appears to be combinations of multiple sessions. One approach to dealing with these could be to identify the first of these events and discard anything after it. Another approach would be to discard everything from these sessions.\n",
    "\n",
    "In this case I choose to do the latter, even though that means that we should also discard them from the analysis of the onboarding funnel in order to keep the numbers consistent. We'll live with this for now, and in future analyses perhaps have a look at whether a Homepage session (as defined by its token) has multiple task clicks. That would allow us to discard them earlier."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3291e5dc",
   "metadata": {},
   "source": [
    "## Valid On Task Sessions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "91f0e2c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_ontask_sessions = task_completion_data.loc[\n",
    "    ~(\n",
    "        ((task_completion_data['confirmed_skip'] == 1) &\n",
    "         (task_completion_data['confirmed_reject'] == 1))\n",
    "        |\n",
    "        ((task_completion_data['confirmed_reject'] == 1) &\n",
    "         (task_completion_data['editsummary_chose_save'] == 1))\n",
    "        |\n",
    "        ((task_completion_data['confirmed_skip'] == 1) &\n",
    "         (task_completion_data['editsummary_chose_save'] == 1))\n",
    "    )\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "5fb26448",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1092"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(valid_ontask_sessions)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9deb417b",
   "metadata": {},
   "source": [
    "## Filtering on Skipping or Completing Onboarding "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "ed33c5db",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_onboarding(ontask_df, onboard_df, skipped = True):\n",
    "    if skipped is True:\n",
    "        return(ontask_df.loc[\n",
    "            ontask_df['homepage_pageview_token'].isin(\n",
    "                onboard_df.loc[\n",
    "                    (onboard_df['onboarding_step1_skipall'] == 1) |\n",
    "                    (onboard_df['onboarding_step2_skipall'] == 1) |\n",
    "                    (onboard_df['onboarding_step3_skipall'] == 1), 'homepage_pageview_token'\n",
    "                ]\n",
    "            ) &\n",
    "            ~ontask_df['homepage_pageview_token'].isin(\n",
    "                onboard_df.loc[\n",
    "                    (onboard_df['onboarding_step4_getstarted'] == 1), 'homepage_pageview_token'\n",
    "                ]\n",
    "            )\n",
    "        ])\n",
    "    elif skipped is False:\n",
    "        return(ontask_df.loc[\n",
    "            ontask_df['homepage_pageview_token'].isin(\n",
    "                onboard_df.loc[\n",
    "                    (onboard_df['onboarding_step4_getstarted'] == 1), 'homepage_pageview_token'\n",
    "                ]\n",
    "            ) &\n",
    "            ~ontask_df['homepage_pageview_token'].isin(\n",
    "                onboard_df.loc[\n",
    "                    (onboard_df['onboarding_step1_skipall'] == 1) |\n",
    "                    (onboard_df['onboarding_step2_skipall'] == 1) |\n",
    "                    (onboard_df['onboarding_step3_skipall'] == 1), 'homepage_pageview_token'\n",
    "                ]\n",
    "            )\n",
    "        ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "9aeec55f",
   "metadata": {},
   "outputs": [],
   "source": [
    "skipped_onboarding_sessions = filter_onboarding(valid_ontask_sessions, mob_newcomers, skipped = True)\n",
    "completed_onboarding_sessions = filter_onboarding(valid_ontask_sessions, mob_newcomers, skipped = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "0ede26a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "465"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(skipped_onboarding_sessions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "181a0aba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "627"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(completed_onboarding_sessions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f992bb81",
   "metadata": {},
   "outputs": [],
   "source": [
    "skipped_onboarding_sessions.loc[skipped_onboarding_sessions['homepage_pageview_token'].isin(\n",
    "    completed_onboarding_sessions['homepage_pageview_token']\n",
    ")]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6452e2ee",
   "metadata": {},
   "source": [
    "## Link Decision"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83b471cc",
   "metadata": {},
   "source": [
    "Number of users who clicked \"not sure\", and proportion out of all On Task users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "aa7e06c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "46"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(skipped_onboarding_sessions.loc[skipped_onboarding_sessions['chose_unsure'] == 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "78efbd01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.9"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[skipped_onboarding_sessions['chose_unsure'] == 1],\n",
    "    skipped_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "ac817c8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "92"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(completed_onboarding_sessions.loc[completed_onboarding_sessions['chose_unsure'] == 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "01f482ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14.7"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[completed_onboarding_sessions['chose_unsure'] == 1],\n",
    "    completed_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c28d3b5e",
   "metadata": {},
   "source": [
    "Number of users who clicked to reject the suggestion, and proportion out of all On Task users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "ee66b8e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "119"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(skipped_onboarding_sessions.loc[skipped_onboarding_sessions['chose_reject'] == 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "aa3e9228",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25.6"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[skipped_onboarding_sessions['chose_reject'] == 1],\n",
    "    skipped_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "2e4d80be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "187"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(completed_onboarding_sessions.loc[completed_onboarding_sessions['chose_reject'] == 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "9ff1a409",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.8"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[completed_onboarding_sessions['chose_reject'] == 1],\n",
    "    completed_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e369d768",
   "metadata": {},
   "source": [
    "Number of users who accepted the suggestion, and proportion out of all On Task users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "18340fc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "139"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(skipped_onboarding_sessions.loc[skipped_onboarding_sessions['choose_accept'] == 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "265016a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.9"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[skipped_onboarding_sessions['choose_accept'] == 1],\n",
    "    skipped_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "9b9c6cdb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "283"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(completed_onboarding_sessions.loc[completed_onboarding_sessions['choose_accept'] == 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "0646f759",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45.1"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[completed_onboarding_sessions['choose_accept'] == 1],\n",
    "    completed_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5268809",
   "metadata": {},
   "source": [
    "Tota number of users making a link decision, and proportion out of On task sessions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "ecac0d25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "259"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "8f84fe94",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55.7"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "d40047fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "458"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "153f8d46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73.0"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39792b86",
   "metadata": {},
   "source": [
    "The bounce rate is then the users who didn't do any of these things:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "6d315bf5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "206"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    len(skipped_onboarding_sessions) -\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "d3e703b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44.3"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc(\n",
    "    len(skipped_onboarding_sessions) -\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]\n",
    "    ),\n",
    "    len(skipped_onboarding_sessions)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "5558b924",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "169"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    len(completed_onboarding_sessions) -\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "680f88ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27.0"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc(\n",
    "    len(completed_onboarding_sessions) -\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]\n",
    "    ),\n",
    "    len(completed_onboarding_sessions)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8942fa53",
   "metadata": {},
   "source": [
    "## Task Review for Skip and Reject Sessions\n",
    "\n",
    "We'll treat these separately from other sessions and exclude them from further analysis. In other words, while users in these sessions might've accepted the suggestion, gone to the caption stage, then gone back to reject it and confirm the rejection, we'll ignore \"accept then caption\" part of their session when we later look at that part of the funnel. Getting a deeper understanding of this kind of behaviour would be possible if we switch from a funnel perspective to a flow perspective, meaning that we only look at pairwise transitions between states in the workflow. We'll leave that for a future analysis.\n",
    "\n",
    "We'll give \"not sure\" precedence over rejecting the suggestion, because the former takes the user back to Suggested Edits on the Homepage, whereas the latter workflow has a confirmation step and the post-edit dialogue. Later on, we'll analyze the post-edit dialogue separately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "343724c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_unsure'] == 1) &\n",
    "        (skipped_onboarding_sessions['confirmed_skip'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "1808653a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "80.4"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_unsure'] == 1) &\n",
    "        (skipped_onboarding_sessions['confirmed_skip'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[skipped_onboarding_sessions['chose_unsure'] == 1]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "7b0b41ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "66"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_unsure'] == 1) &\n",
    "        (completed_onboarding_sessions['confirmed_skip'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "d9d0bd8c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "71.7"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_unsure'] == 1) &\n",
    "        (completed_onboarding_sessions['confirmed_skip'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[completed_onboarding_sessions['chose_unsure'] == 1]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82a5aa70",
   "metadata": {},
   "source": [
    "The rejection path means they see the rejection dialog, then confirms it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "aacc7127",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "119"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "37e00212",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "187"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f2e4681",
   "metadata": {},
   "source": [
    "In this case, everyone who clicked \"reject\" also saw the dialog, so that makes proportion calculations easy. \n",
    "\n",
    "*Note:* After doing the reject path I noticed that I did not have the `skip_dialog` impression in the skip path. Since we saw that all the users who clicked also saw the rejection dialogue, I've similarly forwarded all the users who clicked \"not sure\" to also have seen the skip dialogue.\n",
    "\n",
    "Let's look at how many confirmed once they saw the rejection dialog:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "541d8a1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (skipped_onboarding_sessions['rejection_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['confirmed_reject'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "3a8b2e50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "111"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (completed_onboarding_sessions['rejection_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['confirmed_reject'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c68f988",
   "metadata": {},
   "source": [
    "Proportion out of all users who saw the rejection dialogue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "531ed45b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "58.8"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (skipped_onboarding_sessions['rejection_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['confirmed_reject'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "a1077e9e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59.4"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (completed_onboarding_sessions['rejection_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['confirmed_reject'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "        (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bf962ff",
   "metadata": {},
   "source": [
    "The acceptance path is the main path with several steps. First is seeing the caption dialog:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "50482c6d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "136"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "4b990ea1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "281"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "985cdb07",
   "metadata": {},
   "source": [
    "Unlike the rejection dialogue, not all users saw the caption info dialogue. This is somewhat concerning, although we note that it's about 2% of the users.\n",
    "\n",
    "Proportion out of all users who clicked \"yes\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "ca4b974e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97.8"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "b93e15cb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99.3"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37c5afef",
   "metadata": {},
   "source": [
    "After seeing the caption dialogue, the user must close it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "ed3ffc50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "136"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "76817459",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "277"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89062c96",
   "metadata": {},
   "source": [
    "Proportion out of users who saw the dialogue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "f2360d4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100.0"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "b9e36c22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "98.6"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36f111b0",
   "metadata": {},
   "source": [
    "To enter the caption, the caption entry has to get focus:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "43074b7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "b68e33a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "112"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d052feb",
   "metadata": {},
   "source": [
    "Proportion out of users who closed the caption dialogue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "2c6df63e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33.1"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "c3a8d92d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40.4"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77e95e11",
   "metadata": {},
   "source": [
    "After entering a caption, the user can click \"Publish…\" to bring up the edit summary. In the funnel query above, getting to the edit summary dialogue *requires* having focused on the caption entry box. I'm verifying with the team whether that's reasonable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "f03c4456",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "0d69937b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "76"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d838e5e",
   "metadata": {},
   "source": [
    "Proportion out of users who focused on the caption entry box:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "1e5559e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73.3"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "d2e898e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67.9"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a34ac14c",
   "metadata": {},
   "source": [
    "Total number of users who go to the \"Task review\" stage, meaning they either saw the Skip or Reject dialogues, or reached the Edit summary after entering a caption. Hence it's a union of these three:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "80592be0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "183"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "661bc0b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "327"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0746a795",
   "metadata": {},
   "source": [
    "Proportion out of all users who made some kind of link decision:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "8fa216ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70.7"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "        (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "58bb7631",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "71.4"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "        (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c11855a5",
   "metadata": {},
   "source": [
    "Number of users who bounced after a link decision is then the difference between users who made one and users who saw the task review dialogues:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "f993bab2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "76"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]    \n",
    "    ) -\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "4b2b9e7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "131"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]    \n",
    "    ) -\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e67239b",
   "metadata": {},
   "source": [
    "Bounce proportion out of users who made a link decision:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "71a35743",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.3"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc(\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]    \n",
    "    ) -\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    ),\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]    \n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "42ee4ccd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28.6"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc(\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]    \n",
    "    ) -\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    ),\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1) |\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) |\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1)\n",
    "        ]    \n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95c3cfdf",
   "metadata": {},
   "source": [
    "The last step in the \"Yes\" path is to save the edit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "c083b528",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "17e3f28b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6ee596b",
   "metadata": {},
   "source": [
    "Proportion out of users who saw the edit summary dialogue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "049d465e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90.9"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "9d5a634c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90.8"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9768445",
   "metadata": {},
   "source": [
    "Total number of users who completed the task is the union of users who confirmed skip, reject, or saved the edit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "9e7a4d2f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "137"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (skipped_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "21f5d3a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "246"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (completed_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10aa31a2",
   "metadata": {},
   "source": [
    "Proportion out of all users who got to the task review stage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "584e4c4d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74.9"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (skipped_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "6c9d5350",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "75.2"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (completed_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf52bb81",
   "metadata": {},
   "source": [
    "The bounce rate for task completion then becomes the difference:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "7e16442d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "46"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    len(skipped_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ]) -\n",
    "    len(skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (skipped_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "d1c9715e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "81"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    len(completed_onboarding_sessions.loc[\n",
    "        ( # got to the Edit Summary\n",
    "            (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "            (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "            (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "            (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "            (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Rejection dialogue\n",
    "            (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "            (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "        ) |\n",
    "        ( # Saw the Skip dialogue\n",
    "            (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "        )\n",
    "    ]) -\n",
    "    len(completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (completed_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ])\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d881183",
   "metadata": {},
   "source": [
    "And the proportion:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "8f97cc8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25.1"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc(\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    ) -\n",
    "    len(skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (skipped_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]),\n",
    "    len(\n",
    "        skipped_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (skipped_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (skipped_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (skipped_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "7ea831d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24.8"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc(\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    ) -\n",
    "    len(completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['confirmed_skip'] == 1) |\n",
    "        (completed_onboarding_sessions['confirmed_reject'] == 1) |\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]),\n",
    "    len(\n",
    "        completed_onboarding_sessions.loc[\n",
    "            ( # got to the Edit Summary\n",
    "                (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "                (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "                (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "                (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "                (completed_onboarding_sessions['editsummary_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Rejection dialogue\n",
    "                (completed_onboarding_sessions['chose_reject'] == 1) &\n",
    "                (completed_onboarding_sessions['rejection_dialog_impression'] == 1)\n",
    "            ) |\n",
    "            ( # Saw the Skip dialogue\n",
    "                (completed_onboarding_sessions['chose_unsure'] == 1)\n",
    "            )\n",
    "        ]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b298e71",
   "metadata": {},
   "source": [
    "Lastly, the number of reverts and the revert rate for saved edits. In hindsight, we might be able to get the exact revision ID we should look for through EditAttemptStep, as it's found in that schema's `revision_id` field. For now, we're joining on wiki, user ID, page ID, and restricting it by time. As seen below, we appear to have lost 3 edits that way, or about 3% of our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "f7901baa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "404fffbd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe3248a9",
   "metadata": {},
   "source": [
    "Number of saved edits that we could identify using wiki, user ID, and page ID:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "6a499025",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (skipped_onboarding_sessions['tagged_edit_saved'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "7dcc0795",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "68"
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (completed_onboarding_sessions['tagged_edit_saved'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f41d79da",
   "metadata": {},
   "source": [
    "Number of those edits that got reverted:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "id": "c1eafb0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (skipped_onboarding_sessions['tagged_edit_saved'] == 1) &\n",
    "        (skipped_onboarding_sessions['tagged_edit_reverted'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "3c332b53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (completed_onboarding_sessions['tagged_edit_saved'] == 1) &\n",
    "        (completed_onboarding_sessions['tagged_edit_reverted'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e1a3f22",
   "metadata": {},
   "source": [
    "And then we get the revert rates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "d40b2045",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21.4"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (skipped_onboarding_sessions['tagged_edit_saved'] == 1) &\n",
    "        (skipped_onboarding_sessions['tagged_edit_reverted'] == 1)\n",
    "    ],\n",
    "    skipped_onboarding_sessions.loc[\n",
    "        (skipped_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (skipped_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (skipped_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (skipped_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (skipped_onboarding_sessions['tagged_edit_saved'] == 1)\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "id": "e185ab7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10.3"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round_perc_df(\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (completed_onboarding_sessions['tagged_edit_saved'] == 1) &\n",
    "        (completed_onboarding_sessions['tagged_edit_reverted'] == 1)\n",
    "    ],\n",
    "    completed_onboarding_sessions.loc[\n",
    "        (completed_onboarding_sessions['choose_accept'] == 1) &\n",
    "        (completed_onboarding_sessions['captioninfo_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['closed_captioninfo_dialog'] == 1) &\n",
    "        (completed_onboarding_sessions['caption_entry_focused'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_dialog_impression'] == 1) &\n",
    "        (completed_onboarding_sessions['editsummary_chose_save'] == 1) &\n",
    "        (completed_onboarding_sessions['tagged_edit_saved'] == 1)\n",
    "    ]\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}