T277355-leading-indicators.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "51987c13",
   "metadata": {},
   "source": [
    "# Add a Link: Leading Indicators\n",
    "\n",
    "The phab task for this is [T277355](https://phabricator.wikimedia.org/T277355)\n",
    "\n",
    "We've got the following three leading indicators, and a fourth noted below it:\n",
    "\n",
    "1. Revert rate: compare Add a Link edits to that of unstructured link tasks.\n",
    "2. User rejection rate: do users reject more than 30% of links?\n",
    "3. Task completion rate: what is the proportion of users who start the Add a Link task and complete it? If it is below 75%, we investigate.\n",
    "\n",
    "With regards to rejection rate, we also want to calculate \"proportion of users who accept all links\". We then want to compare the rejection rate when exluding these users from the dataset.\n",
    "\n",
    "We distinguish between users who registered before and after the feature was deployed. This is done in order to not create confusion. We decided prior to deployment that all existing users would get the unstructured task replaced by Add a Link so that we could show it to a wider user base. As a result, we put it in front of a large group of retained users who are likely prolific contributors.\n",
    "\n",
    "Users who registered after deployment are randomly assigned (with 50% chance either way) to Add a Link or the unstructured task, and do not have an opportunity to switch. This is part of our experiment plan. As a result, a limited number of users are getting the unstructured link task. Splitting the analysis based on when users registered allows comparing the two types of tasks directly for the group who signed up after deployment where it makes sense (e.g. revert rate)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb24a4ca",
   "metadata": {},
   "source": [
    "# Libraries and Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b0420079",
   "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": "a94aca54",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Start timestamp of the experiment (https://phabricator.wikimedia.org/T277356#7120922)\n",
    "exp_start = '2021-05-27T19:12:03'\n",
    "\n",
    "exp_start_ts = dt.datetime.strptime(exp_start, '%Y-%m-%dT%H:%M:%S')\n",
    "\n",
    "## We'll limit data gathering to midnight June 14, the day we're gathering data\n",
    "exp_end_ts = dt.datetime(2021, 6, 14, 0, 0, 0)\n",
    "\n",
    "## List of wikis that we deployed to:\n",
    "wikis = ['arwiki','bnwiki','cswiki', 'viwiki']\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": "code",
   "execution_count": 3,
   "id": "5a9e9277",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Controlling the maximum number of rows, columns, and output width used\n",
    "## by Pandas. Update it with larger values if tables start getting truncated.\n",
    "\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 150)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8edbfec",
   "metadata": {},
   "source": [
    "## Helper functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b2f003cd",
   "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": 5,
   "id": "00b54d60",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_when_then(wiki_list, wiki_column):\n",
    "    '''\n",
    "    Take the ordered list of wiki names and turn it into a string\n",
    "    of \"WHEN wiki_column = '{wiki}' THEN '{k}'\" where `k` is the index\n",
    "    of the wiki in the list, so it can be used for ordering results.\n",
    "    '''\n",
    "\n",
    "    whens = list()\n",
    "    \n",
    "    for k, wiki in enumerate(wiki_list):\n",
    "        whens.append(f'WHEN {wiki_column} = \"{wiki}\" THEN \"{k:02}\"')\n",
    "    \n",
    "    ## Join them with line breaks to create the list\n",
    "    return('\\n'.join(whens))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cc52d1df",
   "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 not more than 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: months differ, but the years the same.\n",
    "    # 3: years differ too.\n",
    "    # Case #2 and #3 can be combined, because it doesn't really matter\n",
    "    # if the years are the same in the month-selection or not.\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",
    "    else:\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",
    ")''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fce233d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def round_cell(x, num_decimals = 1):\n",
    "    '''\n",
    "    Try converting the value of `x` into a float, then rounding to the specified\n",
    "    number of decimal places. Used when outputting `pandas.DataFrame` that contain\n",
    "    columns full of `object` data types. If the value cannot be parsed as a float,\n",
    "    the value is returned as-is.\n",
    "    \n",
    "    :param x: whatever we want to try to round\n",
    "    :type x: obj\n",
    "    \n",
    "    :param num_decimals: the number of decimal places to round to\n",
    "    :type num_decimals: int\n",
    "    '''\n",
    "    try:\n",
    "        return(round(float(x), num_decimals))\n",
    "    except ValueError:\n",
    "        return(x)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "df3bc1c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_and_sort_wiki_df(wiki_list, df, name_column = 'wiki', value_column = None):\n",
    "    '''\n",
    "    Takes the given list of wikis and compares them with the named column\n",
    "    in the dataframe.\n",
    "    \n",
    "    If no value column is defined, it adds one empty row to the dataframe\n",
    "    for each missing wiki.\n",
    "    \n",
    "    If a value column is defined, it identifies the unique values in that column\n",
    "    and adds them for each wiki.\n",
    "    \n",
    "    Once the full new dataframe is completed, all NAs are replaced with 0,\n",
    "    and the dataframe is sorted by the name and value columns.\n",
    "    \n",
    "    :param wiki_list: list of all the wikis we're expecting to have data for\n",
    "    :type wiki_list: list\n",
    "    \n",
    "    :param df: dataframe with data, possibly missing some wikis.\n",
    "    :type df: pandas.DataFrame\n",
    "    \n",
    "    :param name_column: column in the dataframe that contains wiki names\n",
    "    :type name_column: str\n",
    "    \n",
    "    :param value_column: name of a column that holds values we should generate rows for.\n",
    "    :type value_column: str\n",
    "    '''\n",
    "\n",
    "    ## We name a series out of the list of wikis, then use that to create a dataframe\n",
    "    ## containing the name of any wiki not already in the named column. Then we set\n",
    "    ## all the values to 0, and sort the resulting dataframe by the named column.\n",
    "\n",
    "    wikis_s = pd.Series(wiki_list)\n",
    "    \n",
    "    if value_column is None:\n",
    "        return(\n",
    "            pd.concat([\n",
    "                df,\n",
    "                pd.DataFrame({name_column : wikis_s.loc[~wikis_s.isin(df[name_column])]})\n",
    "            ]).fillna(0).sort_values(name_column))\n",
    "    else:\n",
    "        # Identify what wikis we're missing, and if we're not missing any just return\n",
    "        # the existing dataframe\n",
    "        missing_wikis = wikis_s.loc[~wikis_s.isin(df[name_column])]\n",
    "        if missing_wikis.empty:\n",
    "            return(df)\n",
    "        \n",
    "        ## From https://stackoverflow.com/a/26977495\n",
    "        unique_values = pd.unique(df[value_column])\n",
    "\n",
    "        new_df = pd.concat([\n",
    "            df,\n",
    "            pd.concat( # combine the results of the list comprehension\n",
    "                [\n",
    "                    pd.DataFrame( # for each element in the list, create a pandas.DataFrame\n",
    "                        {name_column : [w] * len(unique_values), # repeat the wiki name to match the values\n",
    "                         value_column : unique_values}) # add all the unique values of the value column\n",
    "                    for w in wikis_s.loc[~wikis_s.isin(df[name_column])] # do this for every missing wiki\n",
    "                ]\n",
    "            )\n",
    "        ])\n",
    "        return(new_df.fillna(0).sort_values([name_column, value_column]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "82667d3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_user_registrations(wiki, user_ids, slice_size = 1000):\n",
    "    '''\n",
    "    Query and return a `pandas.DataFrame` with columns `wiki`, `user_id`, and `user_registration`\n",
    "    for all `user_ids` on the given `wiki`\n",
    "\n",
    "    :param wiki: database code of the wiki we're querying\n",
    "    :type wiki: str\n",
    "    \n",
    "    :param user_ids: the user IDs we're getting registration timestamps for\n",
    "    :type prop: list\n",
    "    \n",
    "    :param slice_size: the number of users we'll query for on each iteration\n",
    "    :type slice_size: int\n",
    "    '''\n",
    "\n",
    "    user_id_query = '''\n",
    "    SELECT\n",
    "        \"{wiki}\" AS wiki,\n",
    "        user_id,\n",
    "        user_registration\n",
    "    FROM user\n",
    "    WHERE user_id IN ({id_list})\n",
    "    '''\n",
    "\n",
    "    reg_df = pd.DataFrame()\n",
    "    \n",
    "    i = 0\n",
    "    while i < len(user_ids):\n",
    "        user_registrations = mariadb.run(user_id_query.format(\n",
    "            wiki = wiki,\n",
    "            id_list = ','.join([str(uid) for uid in user_ids[i:i+slice_size]])\n",
    "        ), wiki)\n",
    "        \n",
    "        reg_df = pd.concat([reg_df, user_registrations])\n",
    "        \n",
    "        i += slice_size\n",
    "    \n",
    "    return(reg_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e13a738d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_user_page_list(df):\n",
    "    '''\n",
    "    Take a data frame with wikis, user IDs, and page IDs, and create a suitable SQL expression\n",
    "    to filter on all three.\n",
    "    '''\n",
    "    \n",
    "    df_subset = df.loc[(df['task_type'] == 'links') &\n",
    "                       (df['num_edits'] == 0)]\n",
    "    \n",
    "    page_expressions = list()\n",
    "    \n",
    "    for wiki, user_id, page_id in zip(df_subset['wiki'], df_subset['user_id'], df_subset['page_id']):\n",
    "        page_expressions.append(f'(wiki = \"{wiki}\" AND user_id = {user_id} AND page_id = {page_id})\\n')\n",
    "        \n",
    "    return('OR\\n'.join(page_expressions))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eacc853",
   "metadata": {},
   "source": [
    "# Revert Rate\n",
    "\n",
    "The way we've done this previously is to only look at user activity within 24 hours of registration, because that's when most of the visits to the Newcomer Homepage take place. In this case, we want to identify all users who visited the Homepage, clicked on either an Add a Link or unstructured link task, and saved an edit to that page. We'll then want to look at the revert rate overall for each type of task. As mentioned in the introduction, we split users based on when they registered."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "919f7435",
   "metadata": {},
   "source": [
    "Update on July 22: I removed the restriction on task type so it enables us to gather data across all task types. We can later filter on only Add a Link and the unstructured link task if needed. I also modified it to create a user registration category (pre/post) based on whether the user registered before or after deployment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8628c7ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "revert_query = '''\n",
    "WITH hp_visits AS (\n",
    "    SELECT\n",
    "        hpv.wiki,\n",
    "        hpv.event.user_id,\n",
    "        hpv.event.homepage_pageview_token,\n",
    "        hpv.dt AS event_dt\n",
    "    FROM event.homepagevisit AS hpv\n",
    "    WHERE {partition_statement}\n",
    "    AND wiki IN ({wiki_list})\n",
    "    AND ({known_user_id_expression})\n",
    "    AND dt >= \"{start_ts}\" AND dt < \"{end_ts}\"\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",
    "homepage_task_clicks AS (\n",
    "    -- clicks to tasks in sessions found in hp_visits\n",
    "    SELECT\n",
    "        hpm.wiki,\n",
    "        hpm.event.user_id,\n",
    "        hpm.dt AS event_dt,\n",
    "        str_to_map(hpm.event.action_data, \";\", \"=\") AS action_data\n",
    "    FROM event.homepagemodule AS hpm\n",
    "    JOIN hp_visits\n",
    "    ON hpm.event.homepage_pageview_token = hp_visits.homepage_pageview_token\n",
    "    WHERE {partition_statement}\n",
    "    AND event.action = \"se-task-click\"\n",
    "    AND dt >= \"{start_ts}\" AND dt < \"{end_ts}\"\n",
    "    AND dt > hp_visits.event_dt\n",
    "),\n",
    "postedit_task_clicks AS (\n",
    "    -- clicks to tasks done after saving an edit\n",
    "    SELECT\n",
    "        hp.wiki,\n",
    "        hp.event.user_id,\n",
    "        hp.dt AS event_dt,\n",
    "        str_to_map(hp.event.action_data, \";\", \"=\") AS action_data\n",
    "    FROM event.helppanel AS hp\n",
    "    WHERE {partition_statement}\n",
    "    AND wiki IN ({wiki_list})\n",
    "    AND ({known_user_id_expression})\n",
    "    AND event.action = \"postedit-task-click\"\n",
    "    AND dt >= \"{start_ts}\" AND dt < \"{end_ts}\"\n",
    "\n",
    "),\n",
    "link_task_clicks AS (\n",
    "-- filter task_clicks down to those on links and link recommendations\n",
    "    SELECT\n",
    "        task_clicks.wiki,\n",
    "        task_clicks.user_id,\n",
    "        task_clicks.event_dt,\n",
    "        newcomer_tasks.page_id,\n",
    "        newcomer_tasks.task_type,\n",
    "        LEAD(task_clicks.event_dt, 1) OVER\n",
    "            (PARTITION BY task_clicks.wiki, task_clicks.user_id, newcomer_tasks.page_id\n",
    "             ORDER BY task_clicks.event_dt) AS next_click_dt\n",
    "    FROM (\n",
    "        SELECT\n",
    "            *\n",
    "        FROM homepage_task_clicks\n",
    "        UNION ALL\n",
    "        SELECT\n",
    "            *\n",
    "        FROM postedit_task_clicks) AS task_clicks\n",
    "    JOIN newcomer_tasks\n",
    "    ON task_clicks.action_data[\"newcomerTaskToken\"] = newcomer_tasks.newcomer_task_token\n",
    "),\n",
    "edits AS (\n",
    "-- edits and reverts (within 48 hours) of newcomer tasks\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",
    "        IF(FIRST_VALUE(performer.user_registration_dt) > \"{start_ts}\", \"post\", \"pre\")\n",
    "            AS user_registration_cat,\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\")\n",
    "    GROUP BY wiki, rev_id\n",
    ")\n",
    "SELECT\n",
    "    link_task_clicks.wiki,\n",
    "    link_task_clicks.user_id,\n",
    "    edits.user_registration_cat,\n",
    "    link_task_clicks.task_type,\n",
    "    COUNT(1) AS num_edits,\n",
    "    SUM(edits.was_reverted) AS num_reverts\n",
    "FROM link_task_clicks\n",
    "JOIN edits\n",
    "ON link_task_clicks.wiki = edits.wiki\n",
    "AND link_task_clicks.user_id = edits.user_id\n",
    "AND link_task_clicks.page_id = edits.page_id\n",
    "WHERE (link_task_clicks.next_click_dt IS NULL\n",
    "       OR link_task_clicks.event_dt != link_task_clicks.next_click_dt) -- removing duplicates\n",
    "AND edits.rev_timestamp > link_task_clicks.event_dt\n",
    "AND (\n",
    "        (link_task_clicks.next_click_dt IS NOT NULL\n",
    "         AND unix_timestamp(edits.rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") <\n",
    "             unix_timestamp(link_task_clicks.next_click_dt, \"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'\"))\n",
    "    OR\n",
    "        (unix_timestamp(edits.rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "         unix_timestamp(link_task_clicks.event_dt, \"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'\") < 60*60*24*7)\n",
    "    )\n",
    "GROUP BY link_task_clicks.wiki, link_task_clicks.user_id,\n",
    "         edits.user_registration_cat, link_task_clicks.task_type\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "17f0fae4",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "link_task_edits_data = spark.run(\n",
    "    revert_query.format(\n",
    "        start_ts = exp_start_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        end_ts = exp_end_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        known_user_id_expression = make_known_users_sql(known_users, 'wiki', 'event.user_id'),\n",
    "        known_userid_expression = make_known_users_sql(known_users, 'wiki', 'event.userid'),\n",
    "        known_user_database_expression = make_known_users_sql(known_users,\n",
    "                                                              '`database`', 'performer.user_id'),\n",
    "        partition_statement = make_partition_statement(exp_start_ts, exp_end_ts)\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdbe0390",
   "metadata": {},
   "outputs": [],
   "source": [
    "link_task_edits_data.head(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1d45a45",
   "metadata": {},
   "source": [
    "Number of task edits, reverts, and revert rate in our dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a15c1f6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>num_edits</th>\n",
       "      <th>num_reverts</th>\n",
       "      <th>revert_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_registration_cat</th>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">post</th>\n",
       "      <th>copyedit</th>\n",
       "      <td>160</td>\n",
       "      <td>45</td>\n",
       "      <td>28.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>expand</th>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>23.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link-recommendation</th>\n",
       "      <td>290</td>\n",
       "      <td>28</td>\n",
       "      <td>9.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>links</th>\n",
       "      <td>63</td>\n",
       "      <td>22</td>\n",
       "      <td>34.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>references</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">pre</th>\n",
       "      <th>copyedit</th>\n",
       "      <td>115</td>\n",
       "      <td>11</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>expand</th>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link-recommendation</th>\n",
       "      <td>958</td>\n",
       "      <td>49</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>links</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>references</th>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update</th>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           num_edits  num_reverts  revert_rate\n",
       "user_registration_cat task_type                                               \n",
       "post                  copyedit                   160           45         28.1\n",
       "                      expand                      13            3         23.1\n",
       "                      link-recommendation        290           28          9.7\n",
       "                      links                       63           22         34.9\n",
       "                      references                   5            2         40.0\n",
       "                      update                       6            0          0.0\n",
       "pre                   copyedit                   115           11          9.6\n",
       "                      expand                      15            0          0.0\n",
       "                      link-recommendation        958           49          5.1\n",
       "                      links                       25            1          4.0\n",
       "                      references                  25            0          0.0\n",
       "                      update                      48            0          0.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Exception in thread Thread-5:\n",
      "Traceback (most recent call last):\n",
      "  File \"/home/nettrom/.conda/envs/2021-05-03T16.30.23_nettrom/lib/python3.7/threading.py\", line 926, in _bootstrap_inner\n",
      "    self.run()\n",
      "  File \"/home/nettrom/.conda/envs/2021-05-03T16.30.23_nettrom/lib/python3.7/threading.py\", line 1177, in run\n",
      "    self.function(*self.args, **self.kwargs)\n",
      "TypeError: stop_session() missing 1 required positional argument: 'session'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "link_tasks_agg = (link_task_edits_data.groupby(['user_registration_cat', 'task_type'])\n",
    "                  .agg({'num_edits' : 'sum', 'num_reverts' : 'sum'}))\n",
    "link_tasks_agg['revert_rate'] = 100 * link_tasks_agg['num_reverts'] / link_tasks_agg['num_edits']\n",
    "link_tasks_agg.round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25634b14",
   "metadata": {},
   "outputs": [],
   "source": [
    "link_task_edits_data.sort_values('num_edits', ascending = False).head(24)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "3a22f5e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16.461800456213275,\n",
       " 4.964022930903756e-05,\n",
       " 1,\n",
       " array([[278.54590571,  39.45409429],\n",
       "        [ 74.45409429,  10.54590571]]))"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For future reference: .loc[] operates on the index if there is one, meaning there's no need\n",
    "# to define what we're matching on.\n",
    "stats.chi2_contingency(\n",
    "    link_tasks_agg.loc['post'].loc[['link-recommendation', 'links'], ['num_edits', 'num_reverts']]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee753e04",
   "metadata": {},
   "source": [
    "## Overall statistics\n",
    "\n",
    "For the slide deck, for each wiki, aggregate the number of edits and editors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "cdda4541",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>num_edits</th>\n",
       "      <th>num_reverts</th>\n",
       "      <th>num_editors</th>\n",
       "      <th>revert_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki</th>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">arwiki</th>\n",
       "      <th>copyedit</th>\n",
       "      <td>178</td>\n",
       "      <td>35</td>\n",
       "      <td>60</td>\n",
       "      <td>19.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>expand</th>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>18.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link-recommendation</th>\n",
       "      <td>884</td>\n",
       "      <td>71</td>\n",
       "      <td>94</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>links</th>\n",
       "      <td>51</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>25.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>references</th>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update</th>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">bnwiki</th>\n",
       "      <th>copyedit</th>\n",
       "      <td>27</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>18.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>expand</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link-recommendation</th>\n",
       "      <td>83</td>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>links</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>references</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">cswiki</th>\n",
       "      <th>copyedit</th>\n",
       "      <td>33</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>18.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>expand</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link-recommendation</th>\n",
       "      <td>206</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>links</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>14.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>references</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>33.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">viwiki</th>\n",
       "      <th>copyedit</th>\n",
       "      <td>37</td>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>expand</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link-recommendation</th>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>links</th>\n",
       "      <td>20</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>references</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            num_edits  num_reverts  num_editors  revert_rate\n",
       "wiki   task_type                                                            \n",
       "arwiki copyedit                   178           35           60         19.7\n",
       "       expand                      16            3            8         18.8\n",
       "       link-recommendation        884           71           94          8.0\n",
       "       links                       51           13           17         25.5\n",
       "       references                  23            0            5          0.0\n",
       "       update                      47            0            6          0.0\n",
       "bnwiki copyedit                    27            5           13         18.5\n",
       "       expand                       1            0            1          0.0\n",
       "       link-recommendation         83            2           18          2.4\n",
       "       links                       10            1            6         10.0\n",
       "       references                   3            0            1          0.0\n",
       "       update                       3            0            2          0.0\n",
       "cswiki copyedit                    33            6           19         18.2\n",
       "       expand                       8            0            2          0.0\n",
       "       link-recommendation        206            3           22          1.5\n",
       "       links                        7            1            5         14.3\n",
       "       references                   3            1            2         33.3\n",
       "       update                       3            0            3          0.0\n",
       "viwiki copyedit                    37           10           14         27.0\n",
       "       expand                       3            0            2          0.0\n",
       "       link-recommendation         75            1           24          1.3\n",
       "       links                       20            8            8         40.0\n",
       "       references                   1            1            1        100.0\n",
       "       update                       1            0            1          0.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_agg = (link_task_edits_data.groupby(['wiki', 'task_type'])\n",
    "               .agg({'num_edits' : 'sum', 'num_reverts' : 'sum', 'user_id' : 'count'})\n",
    "               .rename(columns = {'user_id' : 'num_editors'}))\n",
    "overall_agg['revert_rate'] = 100 * overall_agg['num_reverts'] / overall_agg['num_edits']\n",
    "overall_agg.round(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "311b6798",
   "metadata": {},
   "source": [
    "# Rejection Rate\n",
    "\n",
    "Q: Do we count all rejections, or only those that are part of saved edits? Do we count all edit sessions, or only those made by new accounts?\n",
    "\n",
    "A: We'll count edit sessions from all users, because in this case experienced users are going to be better able to determine if a suggested link is appropriate. We will, however, only count saved edits, because we want to ignore users loading up the editor and testing out Add a Link.\n",
    "\n",
    "To make things easier, we'll use the structured task schema as our source of data because it has an `editsummary_save` event. Since we're relying on instrumented events, that's going to make it easier.\n",
    "\n",
    "Also, the number of accepted, rejected, and skipped links is also saved in the edit summary of each edit. That data is, however, localized to each wiki so I'm not going to spend time digging those numbers out.\n",
    "\n",
    "### Notes\n",
    "\n",
    "There is the possibility that a user gets to the edit summary screen and then choses to go back and make changes. We'll treat this as two separate end states and count both. We do this because we expect the number of times this happens to be relatively low, and secondly that there isn't anything wrong with going back and changing your mind.\n",
    "\n",
    "For the `skipall_dialog`, we'll need to go fetch their initial impression of the interface, because that's where the number of suggestions is stored.\n",
    "\n",
    "To get registration categories into this and subsequent datasets, I grab `user_registration` from the `user` table in MariaDB. There's only 4 wikis and a limited number of users in the dataset, so that's not a costly operation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "9c1d16da",
   "metadata": {},
   "outputs": [],
   "source": [
    "rejection_rate_query = '''\n",
    "WITH saved_edits AS (\n",
    "    SELECT\n",
    "        coalesce(lsi.dt, lsi.meta.dt) AS event_dt,\n",
    "        lsi.homepage_pageview_token,\n",
    "        hpv.wiki,\n",
    "        hpv.event.user_id\n",
    "    FROM event.mediawiki_structured_task_article_link_suggestion_interaction AS lsi\n",
    "    JOIN event.homepagevisit AS hpv\n",
    "    ON lsi.homepage_pageview_token = hpv.event.homepage_pageview_token\n",
    "    WHERE {lsi_partition_statement}\n",
    "    AND {hpv_partition_statement}\n",
    "    AND hpv.wiki IN ({wiki_list})\n",
    "    AND ({known_user_id_expression})\n",
    "    AND lsi.action = \"editsummary_save\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) >= \"{start_ts}\" AND coalesce(lsi.dt, lsi.meta.dt) < \"{end_ts}\"\n",
    "),\n",
    "saved_rates AS ( -- grab accept/reject/skips for saved edits\n",
    "    -- str_to_map() first splits on \";\" (the pair delimiter),\n",
    "    -- then splits each key/value pair on \"=\" (the key/value delimiter)\n",
    "    SELECT\n",
    "        lsi.homepage_pageview_token,\n",
    "        saved_edits.user_id,\n",
    "        saved_edits.wiki,\n",
    "        coalesce(lsi.dt, lsi.meta.dt) as event_dt,\n",
    "        str_to_map(lsi.action_data, \";\", \"=\") AS rate_map\n",
    "    FROM saved_edits\n",
    "    JOIN event.mediawiki_structured_task_article_link_suggestion_interaction AS lsi\n",
    "    ON saved_edits.homepage_pageview_token = lsi.homepage_pageview_token\n",
    "    WHERE {lsi_partition_statement}\n",
    "    AND lsi.action = \"impression\"\n",
    "    AND lsi.active_interface = \"editsummary_dialog\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) >= \"{start_ts}\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) < \"{end_ts}\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) < saved_edits.event_dt\n",
    "),\n",
    "skip_alls AS (\n",
    "    SELECT\n",
    "        coalesce(lsi.dt, lsi.meta.dt) AS event_dt,\n",
    "        lsi.homepage_pageview_token,\n",
    "        hpv.wiki,\n",
    "        hpv.event.user_id\n",
    "    FROM event.mediawiki_structured_task_article_link_suggestion_interaction AS lsi\n",
    "    JOIN event.homepagevisit AS hpv\n",
    "    ON lsi.homepage_pageview_token = hpv.event.homepage_pageview_token\n",
    "    WHERE {lsi_partition_statement}\n",
    "    AND {hpv_partition_statement}\n",
    "    AND hpv.wiki IN ({wiki_list})\n",
    "    AND ({known_user_id_expression})\n",
    "    AND lsi.active_interface = \"skipall_dialog\"\n",
    "    AND lsi.action = \"confirm_skip_all_suggestions\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) >= \"{start_ts}\" AND coalesce(lsi.dt, lsi.meta.dt) < \"{end_ts}\"\n",
    "),\n",
    "skip_all_rates AS ( -- grab the suggestion count from start of the sessions\n",
    "    SELECT\n",
    "        lsi.homepage_pageview_token,\n",
    "        skip_alls.user_id,\n",
    "        skip_alls.wiki,\n",
    "        coalesce(lsi.dt, lsi.meta.dt) as event_dt,\n",
    "        -- building a map similar to what we have for saved edits\n",
    "        map(\"accepted_count\", \"0\", \"rejected_count\", \"0\",\n",
    "            \"skipped_count\",\n",
    "            str_to_map(lsi.action_data, \";\", \"=\")[\"number_phrases_found\"]) AS rate_map\n",
    "    FROM skip_alls\n",
    "    JOIN event.mediawiki_structured_task_article_link_suggestion_interaction AS lsi\n",
    "    ON skip_alls.homepage_pageview_token = lsi.homepage_pageview_token\n",
    "    WHERE {lsi_partition_statement}\n",
    "    AND lsi.action = \"impression\"\n",
    "    AND lsi.active_interface = \"machinesuggestions_mode\"\n",
    "    AND lsi.action = \"impression\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) >= \"{start_ts}\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) < \"{end_ts}\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) < skip_alls.event_dt\n",
    "),\n",
    "rates_cast AS (\n",
    "    SELECT\n",
    "        wiki,\n",
    "        user_id,\n",
    "        homepage_pageview_token,\n",
    "        CAST(rate_map['accepted_count'] AS INT) AS accepted_count,\n",
    "        CAST(rate_map['rejected_count'] AS INT) AS rejected_count,\n",
    "        CAST(rate_map['skipped_count'] AS INT) AS skipped_count\n",
    "    FROM\n",
    "        (SELECT\n",
    "             *\n",
    "         FROM saved_rates\n",
    "         UNION ALL\n",
    "         SELECT\n",
    "             *\n",
    "         FROM skip_all_rates) AS r\n",
    ")\n",
    "SELECT\n",
    "    wiki,\n",
    "    user_id,\n",
    "    SUM(accepted_count) AS num_links_accepted,\n",
    "    SUM(rejected_count) AS num_links_rejected,\n",
    "    SUM(skipped_count) AS num_links_skipped,\n",
    "    SUM(accepted_count + rejected_count + skipped_count) AS num_links_recommended,\n",
    "    COUNT(1) AS num_edit_sessions,\n",
    "    COUNT(IF(accepted_count = accepted_count + rejected_count + skipped_count, 1, NULL)) AS num_all_accepted,\n",
    "    COUNT(IF(rejected_count = accepted_count + rejected_count + skipped_count, 1, NULL)) AS num_all_rejected,\n",
    "    COUNT(IF(skipped_count = accepted_count + rejected_count + skipped_count, 1, NULL)) AS num_all_skipped\n",
    "FROM rates_cast\n",
    "GROUP BY wiki, user_id\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "78ba77c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "rate_data = spark.run(\n",
    "    rejection_rate_query.format(\n",
    "        start_ts = exp_start_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        end_ts = exp_end_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        known_user_id_expression = make_known_users_sql(known_users, 'wiki', 'hpv.event.user_id'),\n",
    "        lsi_partition_statement = make_partition_statement(exp_start_ts, exp_end_ts, prefix = 'lsi'),\n",
    "        hpv_partition_statement = make_partition_statement(exp_start_ts, exp_end_ts, prefix = 'hpv')\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "126229ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_data.loc[rate_data['wiki'] == 'arwiki'].sort_values('num_edit_sessions')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9870d33",
   "metadata": {},
   "source": [
    "Get registration timestamps for these users and join with the rate data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "eada74a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_registrations = pd.concat(\n",
    "    [get_user_registrations(wiki, rate_data.loc[rate_data['wiki'] == wiki, 'user_id']) for wiki in wikis]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "83700b0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_registrations['user_registration_ts'] = pd.to_datetime(rate_registrations['user_registration'],\n",
    "                                                            format = '%Y%m%d%H%M%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "91d5fd86",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_registrations['user_registration_cat'] = rate_registrations['user_registration_ts'].apply(\n",
    "    lambda x: 'post' if x > exp_start_ts else 'pre')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "7d23bb20",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "160"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "a2a91255",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_data = rate_data.merge(rate_registrations, on = ['wiki', 'user_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "e002497c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "160"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "350c6dfd",
   "metadata": {},
   "source": [
    "Now, let's aggregate to get rates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ece2dd27",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "dec2a5ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>num_links_accepted</th>\n",
       "      <th>num_links_rejected</th>\n",
       "      <th>num_links_skipped</th>\n",
       "      <th>num_links_recommended</th>\n",
       "      <th>perc_links_accepted</th>\n",
       "      <th>perc_links_rejected</th>\n",
       "      <th>perc_links_skipped</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_registration_cat</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>post</th>\n",
       "      <td>96</td>\n",
       "      <td>597</td>\n",
       "      <td>125</td>\n",
       "      <td>103</td>\n",
       "      <td>825</td>\n",
       "      <td>72.4</td>\n",
       "      <td>15.2</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pre</th>\n",
       "      <td>64</td>\n",
       "      <td>1464</td>\n",
       "      <td>595</td>\n",
       "      <td>189</td>\n",
       "      <td>2248</td>\n",
       "      <td>65.1</td>\n",
       "      <td>26.5</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       user_id  num_links_accepted  num_links_rejected  num_links_skipped  num_links_recommended  perc_links_accepted  \\\n",
       "user_registration_cat                                                                                                                   \n",
       "post                        96                 597                 125                103                    825                 72.4   \n",
       "pre                         64                1464                 595                189                   2248                 65.1   \n",
       "\n",
       "                       perc_links_rejected  perc_links_skipped  \n",
       "user_registration_cat                                           \n",
       "post                                  15.2                12.5  \n",
       "pre                                   26.5                 8.4  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Exception in thread Thread-7:\n",
      "Traceback (most recent call last):\n",
      "  File \"/home/nettrom/.conda/envs/2021-05-03T16.30.23_nettrom/lib/python3.7/threading.py\", line 926, in _bootstrap_inner\n",
      "    self.run()\n",
      "  File \"/home/nettrom/.conda/envs/2021-05-03T16.30.23_nettrom/lib/python3.7/threading.py\", line 1177, in run\n",
      "    self.function(*self.args, **self.kwargs)\n",
      "TypeError: stop_session() missing 1 required positional argument: 'session'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rate_data_agg = (rate_data.groupby('user_registration_cat')\n",
    "                 .agg({'user_id' : 'count', 'num_links_accepted' : 'sum', 'num_links_rejected' : 'sum',\n",
    "                       'num_links_skipped' : 'sum', 'num_links_recommended' : 'sum'}))\n",
    "rate_data_agg['perc_links_accepted'] = (100.0 * rate_data_agg['num_links_accepted'] /\n",
    "                                        rate_data_agg['num_links_recommended'])\n",
    "rate_data_agg['perc_links_rejected'] = (100.0 * rate_data_agg['num_links_rejected'] /\n",
    "                                        rate_data_agg['num_links_recommended'])\n",
    "rate_data_agg['perc_links_skipped'] = (100.0 * rate_data_agg['num_links_skipped'] /\n",
    "                                        rate_data_agg['num_links_recommended'])\n",
    "rate_data_agg.round(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60819670",
   "metadata": {},
   "source": [
    "We also want to calculate how often users accept all links or not:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "48eb2392",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                  (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "62217660",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "a5f1f6b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15.6"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                       (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])]) /\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre')]), 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4805edd6",
   "metadata": {},
   "source": [
    "So 15.6% of users who registered prior to the experiment have only sessions where they've accepted all the recommended links. We can check how many edit sessions these users have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "a92facd8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "9cb0fd96",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "477"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'pre'), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "b3528975",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.6"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum() /\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'pre'), 'num_edit_sessions'].sum(), 1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aca723ff",
   "metadata": {},
   "source": [
    "They only account for 22 out of 477 edit sessions, or 4.6% of this data.\n",
    "\n",
    "We then do a similar analysis for users registered after deployment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "0da402a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                  (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "5a61daf1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "96"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'post')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "9071e6f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32.3"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                       (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])]) /\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'post')]), 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "249fcf55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "49181c09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "199"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'post'), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "e10f76d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18.1"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum() /\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'post'), 'num_edit_sessions'].sum(), 1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9421fdbd",
   "metadata": {},
   "source": [
    "So 31 out of 96 users registered after deployment (32.3%) have only edit sessions where they've accepted all suggested links. These do not edit as much as the other users, though, since they only account for 36 out of 199 edit sessions (18.1%)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9ac1632",
   "metadata": {},
   "outputs": [],
   "source": [
    "round(100 *\n",
    "     len(rate_data.loc[rate_data['num_edit_sessions'] == rate_data['num_all_accepted']]) /\n",
    "     len(rate_data), 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5760271",
   "metadata": {},
   "source": [
    "So about 27% of users have only edit sessions where they've accepted all the recommended links."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "828d6cc8",
   "metadata": {},
   "source": [
    "## Restricting it to >= 5 Edit Sessions\n",
    "\n",
    "Because most of the users who only accepted links had only a few sessions, what happens when it restrict it to those who had a least 5 sessions?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "32ab30b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                  (rate_data['num_edit_sessions'] >= 5) &\n",
    "                  (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "20319e04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                 (rate_data['num_edit_sessions'] >= 5)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c907d201",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                       (rate_data['num_edit_sessions'] >= 5) &\n",
    "                       (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])]) /\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                      (rate_data['num_edit_sessions'] >= 5)]), 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "8c44502a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "              (rate_data['num_edit_sessions'] >= 5) &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "046896f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "76"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "              (rate_data['num_edit_sessions'] >= 5), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "63aaebc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                   (rate_data['num_edit_sessions'] >= 5) &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum() /\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'post') &\n",
    "                   (rate_data['num_edit_sessions'] >= 5), 'num_edit_sessions'].sum(), 1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6641a411",
   "metadata": {},
   "source": [
    "For users registered after deployment, none of those with more than 5 or more edit sessions have sessions where they accepted all links.\n",
    "\n",
    "How does this play out for pre-deployment?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "49b13099",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                  (rate_data['num_edit_sessions'] >= 5) &\n",
    "                  (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "9a54e885",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                 (rate_data['num_edit_sessions'] >= 5)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "5a80ab41",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5.3"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                       (rate_data['num_edit_sessions'] >= 5) &\n",
    "                       (rate_data['num_edit_sessions'] == rate_data['num_all_accepted'])]) /\n",
    "     len(rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                      (rate_data['num_edit_sessions'] >= 5)]), 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "0826c7f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "              (rate_data['num_edit_sessions'] >= 5) &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "9ca95444",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "382"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "              (rate_data['num_edit_sessions'] >= 5), 'num_edit_sessions'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "7f5028c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.8"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(100 *\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                   (rate_data['num_edit_sessions'] >= 5) &\n",
    "              (rate_data['num_edit_sessions'] == rate_data['num_all_accepted']), 'num_edit_sessions'].sum() /\n",
    "     rate_data.loc[(rate_data['user_registration_cat'] == 'pre') &\n",
    "                   (rate_data['num_edit_sessions'] >= 5), 'num_edit_sessions'].sum(), 1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe71b7a4",
   "metadata": {},
   "source": [
    "On the pre-deployment side, 1 of 19 users (5.3%) have 5 or more edit sessions and accepted all links. This user had 7 edit sessions, which is 1.8% of the 382 edit sessions overall."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ccc54b6",
   "metadata": {},
   "source": [
    "# Task Completion Rate\n",
    "\n",
    "Out of all users who start an Add a Link task, what proportion completes it?\n",
    "\n",
    "We define the start of a Add a Link task session as the impression of the \"machine suggestions\" mode in Visual Editors. Completion is defined as either clicking to save the edit at the edit summary dialog, as before, or confirming that they've skipped all suggestions. We've confirmed that if a user rejects all suggestions and clicks \"Submit\" on the edit summary, the event logged is the same as if they accepted any recommended links."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "fbce735b",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_query = '''\n",
    "WITH task_init AS (\n",
    "    -- select sessions where the user started\n",
    "    SELECT\n",
    "        coalesce(lsi.dt, lsi.meta.dt) AS event_dt,\n",
    "        lsi.homepage_pageview_token,\n",
    "        hpv.wiki,\n",
    "        hpv.event.user_id\n",
    "    FROM event.mediawiki_structured_task_article_link_suggestion_interaction AS lsi\n",
    "    JOIN event.homepagevisit AS hpv\n",
    "    ON lsi.homepage_pageview_token = hpv.event.homepage_pageview_token\n",
    "    WHERE {lsi_partition_statement}\n",
    "    AND {hpv_partition_statement}\n",
    "    AND hpv.wiki IN ({wiki_list})\n",
    "    AND ({known_user_id_expression})\n",
    "    AND lsi.active_interface = \"machinesuggestions_mode\"\n",
    "    AND lsi.action = \"impression\"\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) >= \"{start_ts}\" AND coalesce(lsi.dt, lsi.meta.dt) < \"{end_ts}\"\n",
    "),\n",
    "task_completion AS (\n",
    "    -- session where the user clicked \"save\"\n",
    "    SELECT\n",
    "        coalesce(lsi.dt, lsi.meta.dt) AS event_dt,\n",
    "        lsi.homepage_pageview_token,\n",
    "        1 AS saved_edit\n",
    "    FROM event.mediawiki_structured_task_article_link_suggestion_interaction AS lsi\n",
    "    JOIN task_init\n",
    "    ON lsi.homepage_pageview_token = task_init.homepage_pageview_token\n",
    "    WHERE {lsi_partition_statement}\n",
    "    AND (\n",
    "            (lsi.active_interface = \"editsummary_dialog\"\n",
    "             AND lsi.action = \"editsummary_save\")\n",
    "        OR\n",
    "            (lsi.active_interface = \"skipall_dialog\"\n",
    "             AND lsi.action = \"confirm_skip_all_suggestions\")\n",
    "        )\n",
    "    AND coalesce(lsi.dt, lsi.meta.dt) > task_init.event_dt\n",
    ")\n",
    "SELECT\n",
    "    task_init.wiki,\n",
    "    task_init.user_id,\n",
    "    1 AS started_task,\n",
    "    MAX(coalesce(task_completion.saved_edit, 0)) AS completed_task\n",
    "FROM task_init\n",
    "LEFT JOIN task_completion\n",
    "ON task_init.homepage_pageview_token = task_completion.homepage_pageview_token\n",
    "GROUP BY task_init.wiki, task_init.user_id\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "8106e2a7",
   "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_query.format(\n",
    "        start_ts = exp_start_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        end_ts = exp_end_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        known_user_id_expression = make_known_users_sql(known_users, 'wiki', 'hpv.event.user_id'),\n",
    "        lsi_partition_statement = make_partition_statement(exp_start_ts, exp_end_ts, prefix = 'lsi'),\n",
    "        hpv_partition_statement = make_partition_statement(exp_start_ts, exp_end_ts, prefix = 'hpv')\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27c2c637",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8951c88",
   "metadata": {},
   "source": [
    "We'll get registrations for these users in the same way as we did for the rate data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "8bdb8ba0",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_registrations = pd.concat(\n",
    "    [get_user_registrations(\n",
    "        wiki,\n",
    "        task_completion_data.loc[task_completion_data['wiki'] == wiki, 'user_id']) for wiki in wikis]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "093bcb7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_registrations['user_registration_ts'] = pd.to_datetime(task_registrations['user_registration'],\n",
    "                                                            format = '%Y%m%d%H%M%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "a3fcd986",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_registrations['user_registration_cat'] = task_registrations['user_registration_ts'].apply(\n",
    "    lambda x: 'post' if x > exp_start_ts else 'pre')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "4b2353cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "279"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(task_completion_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "e2635efa",
   "metadata": {},
   "outputs": [],
   "source": [
    "task_completion_data = task_completion_data.merge(task_registrations, on = ['wiki', 'user_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "d979c2de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "279"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(task_completion_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa7b85a7",
   "metadata": {},
   "source": [
    "We can now aggregate across registration category and get numbers. Since the dataset is on a per-user basis, we know that overall: 279 users started a task."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "f95a300f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>started_task</th>\n",
       "      <th>completed_task</th>\n",
       "      <th>perc_completed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_registration_cat</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>post</th>\n",
       "      <td>178</td>\n",
       "      <td>96</td>\n",
       "      <td>53.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pre</th>\n",
       "      <td>101</td>\n",
       "      <td>64</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       started_task  completed_task  perc_completed\n",
       "user_registration_cat                                              \n",
       "post                            178              96            53.9\n",
       "pre                             101              64            63.4"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Exception in thread Thread-8:\n",
      "Traceback (most recent call last):\n",
      "  File \"/home/nettrom/.conda/envs/2021-05-03T16.30.23_nettrom/lib/python3.7/threading.py\", line 926, in _bootstrap_inner\n",
      "    self.run()\n",
      "  File \"/home/nettrom/.conda/envs/2021-05-03T16.30.23_nettrom/lib/python3.7/threading.py\", line 1177, in run\n",
      "    self.function(*self.args, **self.kwargs)\n",
      "TypeError: stop_session() missing 1 required positional argument: 'session'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "task_completion_agg = (task_completion_data.groupby('user_registration_cat')\n",
    "                       .agg({'started_task' : 'sum', 'completed_task' : 'sum'}))\n",
    "task_completion_agg['perc_completed'] = (100 * task_completion_agg['completed_task'] /\n",
    "                                         task_completion_agg['started_task'])\n",
    "task_completion_agg.round(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ded6d129",
   "metadata": {},
   "source": [
    "We note that the number of users in both categories is the same as the number of users in the previous step (task acceptance).\n",
    "\n",
    "In both cases, we see that the proportion of tasks completed relative to the number of tasks started is lower than the 75% threshold listed in the measurement plan."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bb75a9f",
   "metadata": {},
   "source": [
    "# Clarifying Questions\n",
    "\n",
    "What's the number of Add a Link edits in total?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7d16542",
   "metadata": {},
   "outputs": [],
   "source": [
    "add_link_tag_query = '''\n",
    "SELECT\n",
    "    `database` AS wiki,\n",
    "    COUNT(DISTINCT rev_id) AS num_edits\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 add link\")\n",
    "    AND rev_timestamp >= \"{start_ts}\" AND rev_timestamp < \"{end_ts}\"\n",
    "    GROUP BY `database`\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f003b70",
   "metadata": {},
   "outputs": [],
   "source": [
    "tagged_edit_counts = spark.run(\n",
    "    add_link_tag_query.format(\n",
    "        start_ts = exp_start_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        end_ts = exp_end_ts.strftime('%Y-%m-%dT%H:%M:%S'),\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        known_user_database_expression = make_known_users_sql(known_users, '`database`', 'performer.user_id'),\n",
    "        partition_statement = make_partition_statement(exp_start_ts, exp_end_ts)\n",
    "    )\n",
    ")        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8698fa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "tagged_edit_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2f17134",
   "metadata": {},
   "outputs": [],
   "source": [
    "tagged_edit_counts['num_edits'].sum()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}