T286816-user-dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "827f6502",
   "metadata": {},
   "source": [
    "# Add a Link: User Dataset\n",
    "\n",
    "We're looking to analyze the Add a Link experiment, and therefore start by grabbing a canonical dataset of users who were part of the experiment. The phab task for this work is [T286816](https://phabricator.wikimedia.org/T286816)\n",
    "\n",
    "We use the MediaWiki History dataset as the authoritative source of user registrations, then augment it with information from ServerSideAccountCreation and the MediaWiki databases. This process is similar to how we've done it previously for other experiments such as NEWTEA.\n",
    "\n",
    "The experiment started on 2021-05-27 at 19:12:03 UTC, ref [T277356#7120922](https://phabricator.wikimedia.org/T277356#7120922) At the start, it was deployed to four Wikipedias: Arabic, Bengali, Czech, and Vietnamese\n",
    "\n",
    "It was deployed to additional wikis on 2021-07-21 at 11:11:21 UTC, ref [T284481#7226968](https://phabricator.wikimedia.org/T284481#7226968) This added the following Wikipedias: Russian, French, Polish, Romanian, Persian, and Hungarian.\n",
    "\n",
    "Users that get the Growth Features are split 50/50 between having Add a Link and having the unstructured link task."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7e2e1fa4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import datetime as dt\n",
    "\n",
    "from collections import defaultdict\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from wmfdata import hive, spark, mariadb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "426a4075",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Deployment to the first group of Wikipedias, ref above\n",
    "group_1_ts = dt.datetime(2021, 5, 27, 19, 12, 3)\n",
    "group_1_wikis = ['arwiki', 'bnwiki', 'cswiki', 'viwiki']\n",
    "\n",
    "## Deployment to the second group of Wikipedias, ref above\n",
    "group_2_ts = dt.datetime(2021, 7, 21, 11, 11, 21)\n",
    "group_2_wikis = ['fawiki', 'frwiki', 'huwiki', 'plwiki', 'rowiki', 'ruwiki']\n",
    "\n",
    "## End of data gathering\n",
    "## May 27 was a Thursday\n",
    "## July 21 was a Wednesday\n",
    "## Let's use a whole number of weeks from a Thursday as our end date\n",
    "## We need 15 days of data past the end date. That gives us Oct 14 as the last day\n",
    "end_ts = dt.datetime(2021, 10, 14, 0, 0, 0)\n",
    "\n",
    "## The MediaWiki history snapshot we use for our data gathering\n",
    "mwh_snapshot = '2021-10'\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 group_1_wikis + group_2_wikis:\n",
    "    known_users[wiki] = known_users[wiki] | get_known_users(wiki)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "015f904a",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Filename of where the canonical datasets are stored.\n",
    "tsv_canonical_user_file = '/home/nettrom/src/2021-Growth-structured-tasks/datasets/add-a-link-experiment-canonical-users.tsv'\n",
    "tsv_datalake_user_file = '/home/nettrom/src/2021-Growth-structured-tasks/datasets/add-a-link-experiment-datalake-users.tsv'\n",
    "canonical_user_table = 'nettrom_growth.addalink_exp_users'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4c8b5c3",
   "metadata": {},
   "source": [
    "## Helper Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b4e40385",
   "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": 19,
   "id": "afc26cec",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_partition_statement(start_ts, end_ts, prefix = ''):\n",
    "    '''\n",
    "    This takes the two timestamps and creates a statement that selects\n",
    "    partitions based on `year`, `month`, and `day` in order to make our\n",
    "    data gathering not use excessive amounts of data. It assumes that\n",
    "    `start_ts` and `end_ts` are either in the same year, or if spanning\n",
    "    a year boundary are within a month apart.\n",
    "    This assumption simplifies the code and output a lot.\n",
    "    \n",
    "    An optional prefix can be set to enable selecting partitions for\n",
    "    multiple tables with different aliases.\n",
    "    \n",
    "    :param start_ts: start timestamp\n",
    "    :type start_ts: datetime.datetime\n",
    "    \n",
    "    :param end_ts: end timestamp\n",
    "    :type end_ts: datetime.datetime\n",
    "    \n",
    "    :param prefix: prefix to use in front of partition clauses, \".\" is added automatically\n",
    "    :type prefix: str\n",
    "    '''\n",
    "    \n",
    "    if prefix:\n",
    "        prefix = f'{prefix}.' # adds \".\" after the prefix\n",
    "    \n",
    "    # there are three cases:\n",
    "    # 1: month and year are the same, output a \"BETWEEN\" statement with the days\n",
    "    # 2: the years are the same, and the months differ by 1: output a statement for each month\n",
    "    # 3: the years are the same: create a list of statements from start_ts.month to end_ts.month,\n",
    "    #    return them OR'ed together\n",
    "    # 4: the years differ by 1, start_ts is December and end_ts is January, do the same as #2\n",
    "    # 5: anything else, raise an exception because this isn't implemented yet.\n",
    "    \n",
    "    if start_ts.year == end_ts.year and start_ts.month == end_ts.month:\n",
    "        return(f'''{prefix}year = {start_ts.year}\n",
    "AND {prefix}month = {start_ts.month}\n",
    "AND {prefix}day BETWEEN {start_ts.day} AND {end_ts.day}''')\n",
    "    elif start_ts.year == end_ts.year and (end_ts.month - start_ts.month) == 1:\n",
    "        return(f'''\n",
    "(\n",
    "    ({prefix}year = {start_ts.year}\n",
    "     AND {prefix}month = {start_ts.month}\n",
    "     AND {prefix}day >= {start_ts.day})\n",
    " OR ({prefix}year = {end_ts.year}\n",
    "     AND {prefix}month = {end_ts.month}\n",
    "     AND {prefix}day <= {end_ts.day})\n",
    ")''')\n",
    "    elif start_ts.year == end_ts.year:\n",
    "        # do the start month as a list\n",
    "        parts = [f'''({prefix}year = {start_ts.year}\n",
    "     AND {prefix}month = {start_ts.month}\n",
    "     AND {prefix}day >= {start_ts.day})''']\n",
    "        # for month +1 to end month, add each month\n",
    "        for m in range(start_ts.month+1, end_ts.month):\n",
    "            parts.append(f'''({prefix}year = {start_ts.year}\n",
    "            AND {prefix}month = {m})''')\n",
    "        # then append the end month and return a parenthesis OR'ed together of all of it\n",
    "        parts.append(f'''({prefix}year = {end_ts.year}\n",
    "     AND {prefix}month = {end_ts.month}\n",
    "     AND {prefix}day <= {end_ts.day})''')\n",
    "        return('({})'.format(\n",
    "            '\\nOR\\n'.join(parts)\n",
    "        ))\n",
    "    elif (end_ts.year - start_ts.year) == 1 and start_ts.month == 12 and end_ts.month == 1:\n",
    "        return(f'''\n",
    "(\n",
    "    ({prefix}year = {start_ts.year}\n",
    "     AND {prefix}month = {start_ts.month}\n",
    "     AND {prefix}day >= {start_ts.day})\n",
    " OR ({prefix}year = {end_ts.year}\n",
    "     AND {prefix}month = {end_ts.month}\n",
    "     AND {prefix}day <= {end_ts.day})\n",
    ")''')\n",
    "    else:\n",
    "        raise Exception('Difference between start and end timestamps is not implemented. See code for details.')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c419f2e5",
   "metadata": {},
   "source": [
    "## User Registrations\n",
    "\n",
    "We get user registrations for users registered between the deployment to a given group and the end of data gathering, separately for each group of wikis. Known users are excluded individually for each wiki based on `known_users`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "85976bb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Query to get user registrations on the given wikis for the given dates, ignoring\n",
    "## known users.\n",
    "\n",
    "user_registrations_query = '''\n",
    "SELECT\n",
    "    wiki_db,\n",
    "    user_id,\n",
    "    user_registration_timestamp\n",
    "FROM wmf.mediawiki_user_history\n",
    "WHERE snapshot = \"{snapshot}\"\n",
    "AND caused_by_event_type = \"create\" -- account registration\n",
    "AND created_by_self = true -- no auto-created accounts\n",
    "AND SIZE(is_bot_by) = 0 -- no bots\n",
    "AND SIZE(is_bot_by_historical) = 0 -- definitely no bots\n",
    "AND (\n",
    "        (wiki_db IN ({group_1_wiki_list})\n",
    "         AND user_registration_timestamp > \"{group_1_start_timestamp}\"\n",
    "         AND user_registration_timestamp < \"{end_timestamp}\")\n",
    "    OR\n",
    "        (wiki_db IN ({group_2_wiki_list})\n",
    "         AND user_registration_timestamp > \"{group_2_start_timestamp}\"\n",
    "         AND user_registration_timestamp < \"{end_timestamp}\")\n",
    "    )\n",
    "AND ({known_user_id_expression})\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5b30c6f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "## NOTE: if you use growth.utils.hive_format to format the timestamps, then you miss the first day of data\n",
    "## as the \"T\" in the Hive format isn't in the timestamps in mediawiki_history. And you probably get\n",
    "## an extra day of data at the end.\n",
    "\n",
    "user_registrations = spark.run(\n",
    "    user_registrations_query.format(\n",
    "        snapshot = mwh_snapshot,\n",
    "\n",
    "        group_1_wiki_list = ','.join(['\"{}\"'.format(w) for w in group_1_wikis]),\n",
    "        group_1_start_timestamp = group_1_ts.strftime('%Y-%m-%d %H:%M:%S'),\n",
    "        \n",
    "        group_2_wiki_list = ','.join(['\"{}\"'.format(w) for w in group_2_wikis]),\n",
    "        group_2_start_timestamp = group_2_ts.strftime('%Y-%m-%d %H:%M:%S'),\n",
    "\n",
    "        end_timestamp = end_ts.strftime('%Y-%m-%d %H:%M:%S'),\n",
    "        known_user_id_expression = make_known_users_sql(known_users, 'wiki_db', 'user_id'),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "24ce03ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2021-05-27 19:16:14.0'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## verify first registration in the dataset\n",
    "user_registrations['user_registration_timestamp'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9e77a81c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2021-10-13 23:59:39.0'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## verify last registration in the dataset\n",
    "user_registrations['user_registration_timestamp'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c444ef54",
   "metadata": {},
   "source": [
    "Those timestamps fit our requirements."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68471225",
   "metadata": {},
   "source": [
    "## Get mobile/desktop and API data\n",
    "\n",
    "This enables us to identify differences between mobile/desktop registrations. We also filter out API registrations as those are almost always mobile apps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7f52c7f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Get all self-created registrations from ServerSideAccountCreation\n",
    "\n",
    "ssac_reg_query = '''\n",
    "SELECT\n",
    "    wiki AS wiki_db,\n",
    "    event.userid AS user_id,\n",
    "    CAST(event.displaymobile AS INT) AS reg_on_mobile,\n",
    "    event.isapi\n",
    "FROM event_sanitized.serversideaccountcreation\n",
    "WHERE {partition_statement}\n",
    "AND wiki IN ({wiki_list})\n",
    "AND event.isselfmade = true\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d30f7d2d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "ssac_reg_data = spark.run(ssac_reg_query.format(\n",
    "    wiki_list = ','.join(['\"{}\"'.format(w) for w in group_1_wikis + group_2_wikis]),\n",
    "    partition_statement = make_partition_statement(group_1_ts, end_ts)\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ea9cf5ed",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki_db</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>arwiki</th>\n",
       "      <td>37374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bnwiki</th>\n",
       "      <td>7313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cswiki</th>\n",
       "      <td>4010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fawiki</th>\n",
       "      <td>14704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>frwiki</th>\n",
       "      <td>30668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>huwiki</th>\n",
       "      <td>1711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>plwiki</th>\n",
       "      <td>5247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rowiki</th>\n",
       "      <td>1670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ruwiki</th>\n",
       "      <td>24361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>viwiki</th>\n",
       "      <td>16954</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id\n",
       "wiki_db         \n",
       "arwiki     37374\n",
       "bnwiki      7313\n",
       "cswiki      4010\n",
       "fawiki     14704\n",
       "frwiki     30668\n",
       "huwiki      1711\n",
       "plwiki      5247\n",
       "rowiki      1670\n",
       "ruwiki     24361\n",
       "viwiki     16954"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## What's the number of registrations?\n",
    "\n",
    "user_registrations.groupby('wiki_db').agg({'user_id' : 'size'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "903ffd3f",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki_db</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>arwiki</th>\n",
       "      <td>37997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bnwiki</th>\n",
       "      <td>7418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cswiki</th>\n",
       "      <td>4087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fawiki</th>\n",
       "      <td>23986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>frwiki</th>\n",
       "      <td>50716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>huwiki</th>\n",
       "      <td>2867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>plwiki</th>\n",
       "      <td>8819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rowiki</th>\n",
       "      <td>2916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ruwiki</th>\n",
       "      <td>39611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>viwiki</th>\n",
       "      <td>17214</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id\n",
       "wiki_db         \n",
       "arwiki     37997\n",
       "bnwiki      7418\n",
       "cswiki      4087\n",
       "fawiki     23986\n",
       "frwiki     50716\n",
       "huwiki      2867\n",
       "plwiki      8819\n",
       "rowiki      2916\n",
       "ruwiki     39611\n",
       "viwiki     17214"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ssac_reg_data.groupby('wiki_db').agg({'user_id' : 'size'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5415222a",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Join with SSAC data\n",
    "\n",
    "all_users = user_registrations.merge(ssac_reg_data, how = 'left',\n",
    "                                    on = ['wiki_db', 'user_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "8bde4370",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki_db</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>arwiki</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fawiki</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>frwiki</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rowiki</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>viwiki</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id\n",
       "wiki_db         \n",
       "arwiki         8\n",
       "fawiki         1\n",
       "frwiki         4\n",
       "rowiki         1\n",
       "viwiki         2"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Did we lose anyone?\n",
    "\n",
    "all_users.loc[all_users['reg_on_mobile'].isna()].groupby('wiki_db').agg({'user_id' : 'size'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc599c6b",
   "metadata": {},
   "source": [
    "There's only a handful of users (on the grand scale of things) that we don't appear to have registration data for, so that means that we're good to go. Yay!\n",
    "\n",
    "We remove users that have `isapi = True`, because those are most likely app registrations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3aa89049",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_users = all_users.loc[~(all_users['isapi'] == True)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3afdcd91",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Drop the isapi columns\n",
    "\n",
    "all_users = all_users.drop(columns = ['isapi'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3585b502",
   "metadata": {},
   "source": [
    "## Get treatment/control assignments\n",
    "\n",
    "This involves two operations.\n",
    "\n",
    "1. Get all users who have the Homepage turned on in their preferences.\n",
    "2. Get the variant setting for all users.\n",
    "\n",
    "Users who don't have the Homepage turned on are candidates for the control group, and likewise for the experiment group. Secondary, users are randomly assigned to variants, which we'll use later in the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7600f429",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_prop_settings(wiki, prop, col_name, cast_type, users=None):\n",
    "    '''\n",
    "    Query and return a `pandas.DataFrame` with columns `wiki` and `user_id` of all users who have\n",
    "    the given property turned on in their preferences for that given wiki.\n",
    "    \n",
    "    :param wiki: database code of the wiki we're querying\n",
    "    :type wiki: str\n",
    "    \n",
    "    :param prop: the user preference we're querying for\n",
    "    :type prop: str\n",
    "    \n",
    "    :param col_name: name that the column with preference value should have in the\n",
    "                     resulting DataFrame (e.g. \"is_treatment\")\n",
    "    :type col_name: str\n",
    "    \n",
    "    :param cast_type: SQL type to cast the property to (in the database this is a BLOB)\n",
    "    :type cast_type: str\n",
    "    \n",
    "    :param users: user IDs of the users we are interested in. This is optional.\n",
    "    :type users: list\n",
    "    '''\n",
    "    \n",
    "    prop_query = '''\n",
    "    SELECT \"{wiki}\" AS wiki, up_user AS user_id,\n",
    "           CAST(up_value AS {cast_type}) AS {col_name}\n",
    "    FROM user_properties\n",
    "    WHERE up_property = \"{prop}\"\n",
    "    '''.format(wiki = wiki, prop = prop, cast_type = cast_type, col_name = col_name)\n",
    "    \n",
    "    if users is not None:\n",
    "        prop_query += '''\n",
    "        AND up_user IN ({})\n",
    "        '''.format(','.join([str(uid) for uid in users]))\n",
    "        \n",
    "    return(mariadb.run(prop_query, wiki))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "f7010cb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Get treatment/control assignments from the MW databases\n",
    "\n",
    "hp_prefs = pd.concat(\n",
    "    [get_prop_settings(wiki,\n",
    "                       'growthexperiments-homepage-enable',\n",
    "                       'hp_enabled',\n",
    "                       'UNSIGNED INTEGER') for wiki in group_1_wikis + group_2_wikis]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c6714cb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_users = all_users.merge(hp_prefs, how = 'left',\n",
    "                            left_on = ['wiki_db', 'user_id'],\n",
    "                            right_on = ['wiki', 'user_id']).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "93401ab8",
   "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>n_users</th>\n",
       "      <th>perc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki_db</th>\n",
       "      <th>hp_enabled</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">arwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>7705</td>\n",
       "      <td>22.417154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>26666</td>\n",
       "      <td>77.582846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">bnwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1585</td>\n",
       "      <td>22.845200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>5353</td>\n",
       "      <td>77.154800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">cswiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>790</td>\n",
       "      <td>21.572911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>2872</td>\n",
       "      <td>78.427089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">fawiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>2745</td>\n",
       "      <td>19.965088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>11004</td>\n",
       "      <td>80.034912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">frwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>5602</td>\n",
       "      <td>19.979315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>22437</td>\n",
       "      <td>80.020685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">huwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>320</td>\n",
       "      <td>20.113136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>1271</td>\n",
       "      <td>79.886864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">plwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>953</td>\n",
       "      <td>19.821131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>3855</td>\n",
       "      <td>80.178869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">rowiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>296</td>\n",
       "      <td>20.598469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>1141</td>\n",
       "      <td>79.401531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ruwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>4103</td>\n",
       "      <td>19.991230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>16421</td>\n",
       "      <td>80.008770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">viwiki</th>\n",
       "      <th>0.0</th>\n",
       "      <td>3325</td>\n",
       "      <td>21.760471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>11955</td>\n",
       "      <td>78.239529</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    n_users       perc\n",
       "wiki_db hp_enabled                    \n",
       "arwiki  0.0            7705  22.417154\n",
       "        1.0           26666  77.582846\n",
       "bnwiki  0.0            1585  22.845200\n",
       "        1.0            5353  77.154800\n",
       "cswiki  0.0             790  21.572911\n",
       "        1.0            2872  78.427089\n",
       "fawiki  0.0            2745  19.965088\n",
       "        1.0           11004  80.034912\n",
       "frwiki  0.0            5602  19.979315\n",
       "        1.0           22437  80.020685\n",
       "huwiki  0.0             320  20.113136\n",
       "        1.0            1271  79.886864\n",
       "plwiki  0.0             953  19.821131\n",
       "        1.0            3855  80.178869\n",
       "rowiki  0.0             296  20.598469\n",
       "        1.0            1141  79.401531\n",
       "ruwiki  0.0            4103  19.991230\n",
       "        1.0           16421  80.008770\n",
       "viwiki  0.0            3325  21.760471\n",
       "        1.0           11955  78.239529"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_users_agg = (all_users.groupby(['wiki_db', 'hp_enabled'])\n",
    "                 .agg({'user_id': 'count'})\n",
    "                 .rename(columns = {'user_id' : 'n_users'}))\n",
    "all_users_agg['perc'] = (100.0 * all_users_agg['n_users'] / \n",
    "                        all_users_agg.groupby('wiki_db')['n_users'].transform('sum'))\n",
    "all_users_agg"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fa72a40",
   "metadata": {},
   "source": [
    "This is mainly routine, the random assignment hasn't failed in any of our experiments so far. From the percentages above we see that it clearly works on the Group 2 wikis. The Group 1 wikis are all affected by T285996, where during a few days in June 2021 the proportions were switched so 80% of registrations ended up in the Control group rather than the Growth Features group. This is why the Group 1 wikis are all somewhere in the 21.5%–22.5% range."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "e9653b20",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Get variant settings from the MW database\n",
    "\n",
    "variant_prefs = pd.concat(\n",
    "    [get_prop_settings(wiki,\n",
    "                       'growthexperiments-homepage-variant',\n",
    "                       'hp_variant',\n",
    "                       'CHAR CHARACTER SET utf8') for wiki in group_1_wikis + group_2_wikis]\n",
    ")"
   ]
  },
  {
   "cell_type": "raw",
   "id": "4543e48d",
   "metadata": {},
   "source": [
    "## Dropping the user name column, it's no longer needed.\n",
    "all_users.drop('hp_variant', axis = 'columns', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c4999079",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_users = all_users.merge(variant_prefs, how = 'left',\n",
    "                           left_on = ['wiki_db', 'user_id'],\n",
    "                           right_on = ['wiki', 'user_id']).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "bc027d6d",
   "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>user_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hp_enabled</th>\n",
       "      <th>hp_variant</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">0.0</th>\n",
       "      <th>0</th>\n",
       "      <td>27336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>control</th>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>linkrecommendation</th>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">1.0</th>\n",
       "      <th>0</th>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>control</th>\n",
       "      <td>51479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>linkrecommendation</th>\n",
       "      <td>51364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               user_id\n",
       "hp_enabled hp_variant                 \n",
       "0.0        0                     27336\n",
       "           control                  45\n",
       "           linkrecommendation       43\n",
       "1.0        0                       132\n",
       "           control               51479\n",
       "           linkrecommendation    51364"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_users.groupby(['hp_enabled', 'hp_variant']).agg({'user_id': 'count'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1516214c",
   "metadata": {},
   "source": [
    "## Users who turned the Homepage on/off in their preferences\n",
    "\n",
    "In NEWTEA as well as other experiments we run, we exclude users who turned the Homepage on/off in their preferences because these self-selected in/out of our randomly assigned groups. Our traditional way of doing this was through the PrefUpdate schema, which only has this data for 90 days. In late August 2020, the Growth team deployed a patch that allows us to correlate the user preference setting (`hp_enabled` in the table above) with the variant they're assigned (`hp_variant` above) and thereby exclude users who aren't in a group they should be.\n",
    "\n",
    "In this case, we can exclude all users in the Control group that have variant set to \"control\" or \"linkrecommendation\", and similarly exclude all users in the Growth Features group that do *not* have variant set to \"control\" or \"linkrecommendation\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f3c10994",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27336"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Control group users cannot have \"control\" or \"linkrecommendation\"\n",
    "len(all_users.loc[(all_users['hp_enabled'] == 0) &\n",
    "                  (~all_users['hp_variant'].isin([\"control\", \"linkrecommendation\"]))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "37c0d618",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "102843"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Homepage group users have to have variant \"control\" or \"linkrecommendation\"\n",
    "len(all_users.loc[(all_users['hp_enabled'] == 1) &\n",
    "                  (all_users['hp_variant'].isin([\"control\", \"linkrecommendation\"]))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "258bc90b",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_users_valid = all_users.loc[\n",
    "    ((all_users['hp_enabled'] == 0) &\n",
    "     (~all_users['hp_variant'].isin([\"control\", \"linkrecommendation\"]))) |\n",
    "    ((all_users['hp_enabled'] == 1) &\n",
    "     (all_users['hp_variant'].isin([\"control\", \"linkrecommendation\"])))\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f412f253",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "130179"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_users_valid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "5e0e7f41",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_users = all_users_valid"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b8c4a6a",
   "metadata": {},
   "source": [
    "# Dataset Export\n",
    "\n",
    "Export the dataset to a TSV, both with and without the header for reading into R and the Data Lake."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "ea030bbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Export users and usage data to TSVs for reading into R for analysis\n",
    "\n",
    "all_users.to_csv(tsv_canonical_user_file, sep = '\\t', header = True, index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f64eba72",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "7db71df0",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Write the dataset out into a TSV so it can be imported into the Data Lake.\n",
    "## NOTE: this file cannot contain a header line.\n",
    "\n",
    "all_users.to_csv(tsv_datalake_user_file, sep = '\\t',\n",
    "                 columns = ['wiki_db', 'user_id', 'user_registration_timestamp',\n",
    "                            'reg_on_mobile', 'hp_enabled', 'hp_variant'],\n",
    "                 header = False, index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d649f196",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Query to create the canonical user dataset table in Hive\n",
    "\n",
    "create_table_query = '''\n",
    "CREATE TABLE {table_name}\n",
    "(wiki_db STRING COMMENT \"wiki this user registered on\",\n",
    " user_id BIGINT COMMENT \"user_id of this user on the given wiki\",\n",
    " user_registration_timestamp STRING COMMENT \"UTC timestamp of user registration\",\n",
    " reg_on_mobile INT COMMENT \"whether the user registered on the mobile site\",\n",
    " hp_enabled INT COMMENT \"whether the user has the Homepage enabled or not\",\n",
    " hp_variant STRING COMMENT \"which variant the user was in\"\n",
    ")\n",
    "ROW FORMAT DELIMITED\n",
    "FIELDS TERMINATED BY \"\\t\"\n",
    "LINES TERMINATED BY \"\\n\"\n",
    "STORED AS TEXTFILE\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "48438b45",
   "metadata": {},
   "outputs": [],
   "source": [
    "hive.run(create_table_query.format(\n",
    "    table_name = canonical_user_table\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12b7f9c6",
   "metadata": {},
   "source": [
    "Importing the data was done manually through the command line in order to have a meaningful reference to the actual local file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "f787d2ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "import_data_query = '''\n",
    "LOAD DATA LOCAL INPATH \"{local_path}\"\n",
    "OVERWRITE INTO TABLE {table_name}\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "0bb9deb2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "LOAD DATA LOCAL INPATH \"/home/nettrom/src/2021-Growth-structured-tasks/datasets/add-a-link-experiment-datalake-users.tsv\"\n",
      "OVERWRITE INTO TABLE nettrom_growth.addalink_exp_users\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(import_data_query.format(\n",
    "    local_path = tsv_datalake_user_file,\n",
    "    table_name = canonical_user_table\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f125711",
   "metadata": {},
   "source": [
    "How many users should we have in the table?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "da5dab14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "130179"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_users)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}