T264831_navigation_clicks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task Navigation and Clicks\n",
    "\n",
    "What percent of newcomers navigate between tasks or click on a given task in the Newcomer Tasks module?\n",
    "\n",
    "In this case, we'll combine our canonical user table with HomepageModule to identify users in the experiment who also visited the Homepage. We'll combine this with information on task navigation and clicks from HomepageModule to aggregate for each user.\n",
    "\n",
    "We use HomepageModule as the source for users visiting the Homepage to account for users who block EventLogging or don't have JavaScript enabled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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\n",
    "from growth import utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Start timestamp of the Variant C/D experiment, which is when the edit tag bug fix went into place\n",
    "exp_start_ts = dt.datetime(2020, 10, 28, 18, 40, 2)\n",
    "\n",
    "## Ordered list of wikis that we'll be gathering data for\n",
    "## Note that we're excluding euwiki due to their small number of registrations\n",
    "wikis = ['cswiki', 'kowiki', 'viwiki', 'arwiki', 'ukwiki', 'huwiki', 'srwiki', 'hywiki',\n",
    "         'frwiki', 'fawiki', 'hewiki', 'ruwiki', 'plwiki', 'ptwiki', 'svwiki', 'trwiki']\n",
    "\n",
    "## The mediawiki_history snapshot we'll be working with\n",
    "wmf_snapshot = '2020-11'\n",
    "\n",
    "## The canonical user table that we'll join mediawiki_history with\n",
    "canonical_user_table = 'nettrom_growth.hp_variant_test2'\n",
    "\n",
    "## Where we write out the resulting dataset for further analysis\n",
    "navigation_click_counts_output_filename = 'datasets/variant-test-2-navigation-click-counts.tsv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Navigation and Click Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "navigation_click_query = '''\n",
    "WITH hp AS ( -- visits, navigation, clicks within 24 hours of registration\n",
    "    SELECT users.wiki_db, users.user_id,\n",
    "    1 AS visited_homepage,\n",
    "    SUM(IF(event.action = \"se-task-navigation\", 1, 0)) AS num_task_navigations,\n",
    "    SUM(IF(event.action = \"se-task-click\", 1, 0)) AS num_task_clicks\n",
    "    FROM {exp_user_table} users\n",
    "    JOIN event_sanitized.homepagemodule hpm\n",
    "    ON (users.wiki_db = hpm.wiki\n",
    "        AND users.user_id = hpm.event.user_id)\n",
    "    WHERE hpm.year = 2020\n",
    "    AND hpm.month IN (10, 11)\n",
    "    AND hpm.dt > \"{start_ts}\"\n",
    "    AND (unix_timestamp(hpm.dt, \"yyyy-MM-dd'T'HH:mm:ss'Z'\") -\n",
    "         unix_timestamp(users.user_registration, \"yyyyMMddHHmmss\") < 86400) -- within 24 hours of registration\n",
    "    GROUP BY users.wiki_db, users.user_id\n",
    ")\n",
    "SELECT u.wiki_db, u.user_id, u.user_registration, u.reg_on_mobile, u.hp_enabled, u.hp_variant,\n",
    "       COALESCE(hp.visited_homepage, 0) AS visited_homepage,\n",
    "       COALESCE(hp.num_task_navigations, 0) AS num_task_navigations,\n",
    "       COALESCE(hp.num_task_clicks, 0) AS num_task_clicks\n",
    "FROM {exp_user_table} AS u\n",
    "LEFT JOIN hp\n",
    "ON u.wiki_db = hp.wiki_db\n",
    "AND u.user_id = hp.user_id\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "navigation_click_counts = spark.run(\n",
    "    navigation_click_query.format(\n",
    "        start_ts = exp_start_ts.strftime(utils.hive_format),\n",
    "        exp_user_table = canonical_user_table\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67599"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(navigation_click_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "navigation_click_counts.loc[navigation_click_counts['visited_homepage'] > 0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "navigation_click_counts.loc[navigation_click_counts['num_task_navigations'] > 0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "navigation_click_counts.loc[navigation_click_counts['num_task_clicks'] > 0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write out the dataset for importing into R for further analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "navigation_click_counts.to_csv(navigation_click_counts_output_filename,\n",
    "                           header = True, index = False, sep = '\\t')"
   ]
  }
 ],
 "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
}