Q2 FY 2021 OKR baselines.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q2 2020 OKR baselines\n",
    "\n",
    "The team defined a set of Q2 OKRs that require us to estimate a couple of baselines. They are:\n",
    "\n",
    "1. Of the users who make edits to suggested articles, what percentage edits more than one article?\n",
    "2. Of the users who select (click on) a suggested article, what percentage save an edit to that article?\n",
    "\n",
    "We'll grab data from MediaWiki on known test accounts to exclude those from the analysis. We'll most likely also want to segment or restrict the data by registration date or user tenure in order to filter out experienced users. It might be most straightforward to just work on users registered after Guidance was launched (on 2020-06-15).\n",
    "\n",
    "Edits made to a suggested article are tagged if they're made within a week after the user clicks to select a task. In order to have the same set of users across the whole analysis, we limit registrations to those prior to 2020-09-01. That allows for two weeks of click events, and a week for the edit to happen. Since we're using the HomepageModule data as our basis for this analysis, we lose users without client-side EventLogging. That means users without JavaScript, or users with ad-blockers enabled that block our EL beacon.\n",
    "\n",
    "For the baseline of edits to multiple articles, we count edits within two weeks after registration, because it's easy to restrict that in the query. Because this is based on edit data, it includes all users and therefore includes a larger group of users than that for the click-edit funnel.\n",
    "\n",
    "We include all wikis with the Growth features up to and including Hebrew Wikipedia, where they were deployed on 2020-08-11. Deployment to Russian Wikipedia happened on 2020-08-27, so it's not included due to there being only a few days between deployment and our registration cutoff."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import datetime as dt\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from collections import defaultdict\n",
    "\n",
    "from wmfdata import hive, spark, mariadb\n",
    "from growth import utils"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Configuration variables\n",
    "\n",
    "wmf_snapshot = '2020-04'\n",
    "\n",
    "## list of Wikipedias where the Homepage is deployed that we'll use in this analysis\n",
    "wikis = ['cswiki', 'kowiki', 'viwiki', 'arwiki', 'huwiki', 'ukwiki', 'hywiki', 'euwiki',\n",
    "         'srwiki', 'frwiki', 'fawiki', 'hewiki']\n",
    "\n",
    "## Start and end timestamps of data gathering, per notes above\n",
    "start_ts = dt.datetime(2020, 6, 16, 0, 0, 0)\n",
    "end_ts = dt.datetime(2020, 9, 1, 0, 0, 0)\n",
    "\n",
    "## User IDs of known users to exclude\n",
    "known_users = defaultdict(set)\n",
    "\n",
    "known_users['cswiki'].update([322106, 339583, 341191, 341611, 433381, 433382, 433511, 404765, 421667,\n",
    "                      427625, 437386, 181724, 272273, 339583, 437386, 439783, 439792, 138342,\n",
    "                      392634, 404765, 275298, 458487, 458049])\n",
    "known_users['kowiki'].update([384066, 539296, 539299, 539302, 539303, 539304, 539305, 539306, 539307,\n",
    "                      539298, 416361, 416360, 413162, 495265, 518393, 518394, 518396, 530285,\n",
    "                      531579, 531785, 536786, 536787, 542720, 542721, 542722, 543192, 543193,\n",
    "                      544145, 544283, 470932, 38759, 555673])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get known test accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 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",
   "metadata": {},
   "source": [
    "## Get all registrations\n",
    "\n",
    "We'll grab registrations from ServerSideAccountCreation, and use that as a basis for our analysis to exclude users who didn't register during our given timeframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_registrations_query = '''\n",
    "SELECT wiki, event.userid AS user_id,\n",
    "       dt AS reg_timestamp,\n",
    "       CAST(event.displaymobile AS INT) AS reg_on_mobile\n",
    "FROM event_sanitized.serversideaccountcreation\n",
    "WHERE year = 2020\n",
    "AND month BETWEEN 6 AND 9\n",
    "AND wiki IN ({wiki_list})\n",
    "AND event.isselfmade = true\n",
    "AND event.isapi = false\n",
    "AND dt BETWEEN \"{start_time}\" AND \"{end_time}\"\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_registrations = spark.run(\n",
    "    user_registrations_query.format(\n",
    "        wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "        start_time = start_ts.strftime(utils.hive_format),\n",
    "        end_time = end_ts.strftime(utils.hive_format)\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-06-16T00:03:05Z'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## verify first registration in the dataset\n",
    "user_registrations['reg_timestamp'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-08-31T23:58:30Z'"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## verify last registration in the dataset\n",
    "user_registrations['reg_timestamp'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Proportion of edits to multiple articles\n",
    "\n",
    "Of the users who make edits to suggested articles, what percentage edits more than one article? This needs to be calculated on a per-wiki basis, and then averaged across all wikis.\n",
    "\n",
    "We'll use the `mediawiki_revision_tags_change` event table to measure this. We'll use all edits tagged with \"newcomer task\" made by users within 14 days of registration, so all users get an even time span to make edits. Because this table has bot-information as well, we'll filter out bot edits. It's unlikely that we have bot users making tagged edits, but filtering that out is easy to do. We aggregate on wiki and user ID, and count the number of distinct articles they've edited."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_articles_query = '''\n",
    "SELECT `database` AS wiki, performer.user_id, count(DISTINCT page_id) AS num_articles_edited\n",
    "FROM event.mediawiki_revision_tags_change\n",
    "WHERE datacenter = \"eqiad\"\n",
    "AND year = 2020\n",
    "AND month BETWEEN 6 AND 9\n",
    "AND `database` IN ({wiki_list})\n",
    "AND performer.user_registration_dt >= \"{start_timestamp}\"\n",
    "AND performer.user_registration_dt < \"{end_timestamp}\"\n",
    "AND performer.user_is_bot = false -- not a bot edit\n",
    "AND array_contains(performer.user_groups, 'bot') = false -- not in the bot group either\n",
    "AND array_contains(tags, \"newcomer task\")\n",
    "AND unix_timestamp(rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "    unix_timestamp(performer.user_registration_dt, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") < 60*60*24*14\n",
    "GROUP BY `database`, performer.user_id\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_articles_per_user = spark.run(num_articles_query.format(\n",
    "    wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "    start_timestamp = start_ts.strftime(utils.hive_format),\n",
    "    end_timestamp = end_ts.strftime(utils.hive_format)\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "## join users and edits\n",
    "articles_per_user_data = user_registrations.merge(num_articles_per_user, on = ['wiki', 'user_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "articles_per_user_data['edited_many'] = (articles_per_user_data['num_articles_edited'] > 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "articles_per_user_agg = (articles_per_user_data.groupby(['wiki', 'edited_many'])\n",
    "                         .agg({'user_id' : 'count'})\n",
    "                         .reset_index()\n",
    "                         .rename(columns = {'user_id' : 'n'}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "articles_per_user_agg['perc'] = (100 * articles_per_user_agg['n'] /\n",
    "                                 articles_per_user_agg.groupby('wiki')['n'].transform('sum'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "articles_per_user_agg.round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39.5"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## average across all wikis except Basque\n",
    "\n",
    "articles_per_user_agg.loc[\n",
    "    (articles_per_user_agg['wiki'] != 'euwiki') &\n",
    "    (articles_per_user_agg['edited_many'] == True), 'perc'].mean().round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44.6"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## average across all wikis\n",
    "\n",
    "articles_per_user_agg.loc[(articles_per_user_agg['edited_many'] == True), 'perc'].mean().round(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Proportion of users who save an edit on a suggested article\n",
    "\n",
    "Of the users who select (click on) a suggested article, what percentage save an edit to that article?\n",
    "\n",
    "We'll reuse the query from the background analysis for \"Add a link\" tasks, and use it to grab clicks from `event_sanitized.homepagemodule` and page IDs from `event_sanitized.newcomertask`. We'll add edits from `event.mediawiki_revision_tags_change` as before, and limit them to one week after the click happened."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_click_edit_query = '''\n",
    "WITH gc AS ( -- guidance click\n",
    "    SELECT wiki, event.user_id, dt, event.homepage_pageview_token,\n",
    "           regexp_extract(event.action_data, 'newcomerTaskToken=([\\\\\\\\w\\\\\\\\d]+)', 1) AS newcomer_task_token\n",
    "    FROM event_sanitized.homepagemodule\n",
    "    WHERE year = 2020\n",
    "    AND month BETWEEN 6 AND 9\n",
    "    AND dt BETWEEN \"{event_start_timestamp}\" AND \"{event_end_timestamp}\"\n",
    "    AND wiki IN ({wiki_list})\n",
    "    AND event.action = \"se-task-click\"\n",
    "),\n",
    "tok AS ( -- task info for guidance impressions and clicks\n",
    "    SELECT event.newcomer_task_token, event.task_type, event.ordinal_position, event.page_id,\n",
    "           ROW_NUMBER() OVER (PARTITION BY wiki, event.newcomer_task_token ORDER BY dt) AS row_number\n",
    "    FROM event_sanitized.newcomertask\n",
    "    WHERE year = 2020\n",
    "    AND month BETWEEN 6 AND 9\n",
    "    AND dt BETWEEN \"{event_start_timestamp}\" AND \"{event_end_timestamp}\"\n",
    "    AND wiki IN ({wiki_list})\n",
    "),\n",
    "te AS ( -- edits tagged with \"newcomer task\" within 2 weeks of registration\n",
    "    -- NOTE: edits can have multiple tag events, so we use DISTINCT to de-duplicate\n",
    "    SELECT DISTINCT `database` AS wiki, performer.user_id, rev_id, rev_timestamp, page_id\n",
    "    FROM event.mediawiki_revision_tags_change\n",
    "    WHERE datacenter = \"eqiad\"\n",
    "    AND year = 2020\n",
    "    AND month BETWEEN 6 AND 9\n",
    "    AND `database` IN ({wiki_list})\n",
    "    AND performer.user_registration_dt >= \"{reg_start_timestamp}\"\n",
    "    AND performer.user_registration_dt < \"{reg_end_timestamp}\"\n",
    "    AND performer.user_is_bot = false -- not a bot edit\n",
    "    AND array_contains(performer.user_groups, 'bot') = false -- not in the bot group either\n",
    "    AND array_contains(tags, \"newcomer task\")\n",
    "    -- edits within 21 days of registration, per the requirements\n",
    "    AND unix_timestamp(rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "        unix_timestamp(performer.user_registration_dt, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") < 60*60*24*21\n",
    ")\n",
    "SELECT gc.wiki, gc.user_id, gc.dt AS click_timestamp, te.rev_id, te.page_id, te.rev_timestamp\n",
    "FROM gc\n",
    "JOIN tok\n",
    "ON gc.newcomer_task_token = tok.newcomer_task_token\n",
    "LEFT JOIN te\n",
    "ON gc.wiki = te.wiki\n",
    "AND gc.user_id = te.user_id\n",
    "AND tok.page_id = te.page_id\n",
    "WHERE te.rev_timestamp IS NULL\n",
    "   OR (unix_timestamp(te.rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "       unix_timestamp(gc.dt, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") < 60*60*24*7)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "## registration start and end timestamps are the same as before\n",
    "## event start timestamp is start_ts, event end timestamp is end_ts + two weeks\n",
    "## We'll do further limitations on when clicks occurred after joining with registrations.\n",
    "\n",
    "event_start_ts = start_ts\n",
    "event_end_ts = end_ts + dt.timedelta(days = 14)\n",
    "\n",
    "reg_start_ts = start_ts\n",
    "reg_end_ts = end_ts\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_clicks_edits_data = spark.run(task_click_edit_query.format(\n",
    "    wiki_list = ','.join(['\"{}\"'.format(w) for w in wikis]),\n",
    "    event_start_timestamp = event_start_ts.strftime(utils.hive_format),\n",
    "    event_end_timestamp = event_end_ts.strftime(utils.hive_format),\n",
    "    reg_start_timestamp = reg_start_ts.strftime(utils.hive_format),\n",
    "    reg_end_timestamp = reg_end_ts.strftime(utils.hive_format)\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge with user registrations, keeping only those who clicked on something because that's a requirement in this case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_clicks_edits = user_registrations.merge(task_clicks_edits_data, on = ['wiki', 'user_id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert all timestamp strings to datetime objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_clicks_edits['click_ts'] = pd.to_datetime(user_clicks_edits['click_timestamp'],\n",
    "                                               format = '%Y-%m-%dT%H:%M:%SZ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_clicks_edits['rev_ts'] = pd.to_datetime(user_clicks_edits['rev_timestamp'],\n",
    "                                             format = '%Y-%m-%dT%H:%M:%SZ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_clicks_edits['reg_ts'] = pd.to_datetime(user_clicks_edits['reg_timestamp'],\n",
    "                                             format = '%Y-%m-%dT%H:%M:%SZ')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we need to limit all users who made clicks/edits to clicks made within 14 days of registration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_clicks_edits = user_clicks_edits.loc[\n",
    "    (user_clicks_edits['click_ts'] - user_clicks_edits['reg_ts'] < dt.timedelta(days = 14))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "## make a 'did_edit' column, set it to 0\n",
    "user_clicks_edits['did_edit'] = 0\n",
    "\n",
    "## set 'did_edit' to 1 if rev_timestamp is not None\n",
    "user_clicks_edits.loc[~user_clicks_edits['rev_id'].isna(), 'did_edit'] = 1\n",
    "\n",
    "## group by wiki and user, sum 'did_edit'\n",
    "user_clicks_edits_user_agg = (user_clicks_edits.groupby(['wiki', 'user_id'])\n",
    "                             .agg({'did_edit' : 'sum'})\n",
    "                             .reset_index()\n",
    "                             .rename(columns = {'did_edit' : 'num_clicks_edited'}))\n",
    "\n",
    "## set a flag for user whose sum is > 0\n",
    "user_clicks_edits_user_agg['did_edit'] = (user_clicks_edits_user_agg['num_clicks_edited'] > 0)\n",
    "                         \n",
    "## group by wiki and that flag\n",
    "user_clicks_edits_agg = (user_clicks_edits_user_agg.groupby(['wiki', 'did_edit'])\n",
    "                         .agg({'user_id' : 'count'})\n",
    "                         .reset_index()\n",
    "                         .rename(columns = {'user_id' : 'n'}))\n",
    "\n",
    "## calculate per-wiki proportions\n",
    "user_clicks_edits_agg['perc'] = (100 * user_clicks_edits_agg['n'] /\n",
    "                                 user_clicks_edits_agg.groupby('wiki')['n'].transform('sum'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_clicks_edits_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31.8"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_clicks_edits_agg.loc[user_clicks_edits_agg['did_edit'] == True, 'perc'].mean().round(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "Of the users who make edits to suggested articles, what percentage edits more than one article? This needs to be calculated on a per-wiki basis, and then averaged across all wikis. We count all edits made by users within 14 days of registration.\n",
    "\n",
    "The answer is 39.5% if we exclude Basque Wikipedia, 44.6% if we include it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of the users who select (click on) a suggested article, what percentage save an edit to that article? Again, we calculate this on a per-wiki basis and average across all wikis. We count clicks on articles made within 14 days of registration, and edits to those articles made within 7 days of those clicks.\n",
    "\n",
    "The answer is 31.8%"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}