T264831_duration_spike.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Experiment Duration\n",
    "\n",
    "How long do we need to run this experiment?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time since registration for first tagged edit?\n",
    "\n",
    "Let's use `event.mediawiki_revision_tags_change` to get an estimate of time since registration for users who registered between 2020-07-01 and 2020-09-01 (when the \"newcomer task\" edit tag bug wasn't in effect). We'll limit it to edits within a week after registration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from wmfdata import spark, mariadb\n",
    "from growth import utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_edit_query = '''\n",
    "SELECT `database` AS wiki_db, performer.user_id,\n",
    "  MIN(unix_timestamp(rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ssX\") -\n",
    "      unix_timestamp(performer.user_registration_dt, \"yyyy-MM-dd'T'HH:mm:ssX\"))\n",
    "      AS seconds_to_first_tagged_edit\n",
    "FROM event.mediawiki_revision_tags_change\n",
    "WHERE year = 2020\n",
    "AND month BETWEEN 7 AND 9\n",
    "AND `database` IN ('cswiki', 'kowiki', 'viwiki', 'arwiki' 'ukwiki', 'hywiki', 'srwiki',\n",
    "                   'frwiki', 'fawiki', 'hewiki', 'ruwiki')\n",
    "AND TO_DATE(performer.user_registration_dt) >= \"2020-07-01\"\n",
    "AND TO_DATE(performer.user_registration_dt) < \"2020-09-01\"\n",
    "AND performer.user_is_bot = false\n",
    "-- edit within 7 days of registration\n",
    "AND (unix_timestamp(rev_timestamp, \"yyyy-MM-dd'T'HH:mm:ssX\") -\n",
    "     unix_timestamp(performer.user_registration_dt, \"yyyy-MM-dd'T'HH:mm:ssX\") < 60*60*24*7)\n",
    "AND array_contains(tags, \"newcomer task\")\n",
    "GROUP BY `database`, performer.user_id\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_edit_data = spark.run(first_edit_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_edit_data['minutes_to_first_tagged_edit'] = first_edit_data['seconds_to_first_tagged_edit'] / 60"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      545.000000\n",
       "mean       505.431315\n",
       "std       1389.147376\n",
       "min          2.083333\n",
       "25%         10.050000\n",
       "50%         20.483333\n",
       "75%         97.083333\n",
       "max      10039.900000\n",
       "Name: minutes_to_first_tagged_edit, dtype: float64"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_edit_data['minutes_to_first_tagged_edit'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Median is 20.5mins, 75th percentile is 1hr 37mins."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_edit_data['hours_to_first_tagged_edit'] = first_edit_data['minutes_to_first_tagged_edit'] / 60"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    545.000000\n",
       "mean       8.423855\n",
       "std       23.152456\n",
       "min        0.034722\n",
       "25%        0.167500\n",
       "50%        0.341389\n",
       "75%        1.618056\n",
       "max      167.331667\n",
       "Name: hours_to_first_tagged_edit, dtype: float64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_edit_data['hours_to_first_tagged_edit'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24.39238888888889"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_edit_data['hours_to_first_tagged_edit'].quantile(0.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "53.82977777777749"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_edit_data['hours_to_first_tagged_edit'].quantile(0.95)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "118.00958888888886"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_edit_data['hours_to_first_tagged_edit'].quantile(0.99)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "90th percentile is slightly more than 24 hours, 95th percentile is 53.8 hours, 99th is 118 hours, or just below 5 days."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Number of registrations\n",
    "\n",
    "How many users register on those wikis in four weeks? (We're ignoring test accounts for now, that's fine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_registrations_query = '''\n",
    "SELECT wiki, count(*) AS num_registrations, count(*) * 0.8 AS est_homepage_group_size\n",
    "FROM event.serversideaccountcreation\n",
    "WHERE year = 2020\n",
    "AND month = 10\n",
    "AND TO_DATE(dt) >= '2020-10-01'\n",
    "AND TO_DATE(dt) < '2020-10-29' -- four weeks\n",
    "AND wiki IN ('cswiki', 'kowiki', 'viwiki', 'arwiki' 'ukwiki', 'hywiki', 'srwiki',\n",
    "             'frwiki', 'fawiki', 'hewiki', 'ruwiki')\n",
    "AND event.isselfmade = true\n",
    "AND event.isapi = false\n",
    "GROUP BY wiki\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "registrations_by_wiki = spark.run(num_registrations_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>wiki</th>\n",
       "      <th>num_registrations</th>\n",
       "      <th>est_homepage_group_size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>viwiki</td>\n",
       "      <td>2884</td>\n",
       "      <td>2307.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hewiki</td>\n",
       "      <td>1519</td>\n",
       "      <td>1215.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hywiki</td>\n",
       "      <td>201</td>\n",
       "      <td>160.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>frwiki</td>\n",
       "      <td>12766</td>\n",
       "      <td>10212.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kowiki</td>\n",
       "      <td>1473</td>\n",
       "      <td>1178.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>cswiki</td>\n",
       "      <td>1110</td>\n",
       "      <td>888.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>srwiki</td>\n",
       "      <td>388</td>\n",
       "      <td>310.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>fawiki</td>\n",
       "      <td>5891</td>\n",
       "      <td>4712.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ruwiki</td>\n",
       "      <td>9494</td>\n",
       "      <td>7595.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     wiki  num_registrations est_homepage_group_size\n",
       "0  viwiki               2884                  2307.2\n",
       "1  hewiki               1519                  1215.2\n",
       "2  hywiki                201                   160.8\n",
       "3  frwiki              12766                 10212.8\n",
       "4  kowiki               1473                  1178.4\n",
       "5  cswiki               1110                   888.0\n",
       "6  srwiki                388                   310.4\n",
       "7  fawiki               5891                  4712.8\n",
       "8  ruwiki               9494                  7595.2"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "registrations_by_wiki"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Decimal('28580.8')"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "registrations_by_wiki['est_homepage_group_size'].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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}