T311165-baselines-CTR-sister-projects.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f0fab5b5-ab97-4ede-a959-507f5f4864d2",
   "metadata": {},
   "source": [
    "# Baselines for Sister Projects on special:search "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47ab3479-1b1f-4482-a037-dc99d0f4f569",
   "metadata": {},
   "source": [
    "[T311165](https://phabricator.wikimedia.org/T311165)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4d11c170-94f5-4ebb-a039-4ba47cd4e1cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "You are using wmfdata v1.3.1, but v1.3.3 is available.\n",
      "\n",
      "To update, run `pip install --upgrade git+https://github.com/wikimedia/wmfdata-python.git@release --ignore-installed`.\n",
      "\n",
      "To see the changes, refer to https://github.com/wikimedia/wmfdata-python/blob/release/CHANGELOG.md\n"
     ]
    }
   ],
   "source": [
    "import datetime as dt\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from wmfdata import spark, mariadb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ec1ab90b-06f9-4944-9ffe-1f08d5cf664c",
   "metadata": {},
   "outputs": [],
   "source": [
    "## We're operating with different format for timestamps, so we'll have to be able\n",
    "## to parse them both with and without milliseconds.\n",
    "\n",
    "def parse_dt(ts):\n",
    "    try:\n",
    "        return(dt.datetime.strptime(ts, '%Y-%m-%dT%H:%M:%S.%fZ'))\n",
    "    except ValueError: ## no microseconds\n",
    "        return(dt.datetime.strptime(ts, '%Y-%m-%dT%H:%M:%SZ'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ae97a0ea-ab50-4c9b-9c7d-f35eab059d22",
   "metadata": {},
   "outputs": [],
   "source": [
    "today = dt.datetime.now(dt.timezone.utc).date()\n",
    "yesterday = today - dt.timedelta(days = 1)\n",
    "last_week = today - dt.timedelta(days = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "a61e7080-e27a-44e4-9fd3-062ee2704bcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "today = dt.date(2022, 7, 7)\n",
    "yesterday = today - dt.timedelta(days = 1)\n",
    "last_week = today - dt.timedelta(days = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c9aa52ac-5459-461b-8b4e-e10d508a71a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "wikis = \"'idwiki', 'ptwiki', 'ruwiki'\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c7d56d3-8e10-4e6b-b391-32205f8c94a3",
   "metadata": {},
   "source": [
    "## Timestamp Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2996fe92-8b4c-4d59-9f4d-5deba7b60b98",
   "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, which should\n",
    "    be a reasonable expectation for this notebook.\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 are 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",
    ")''')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c85fd44e-4293-4514-b1c1-6f7971938a7a",
   "metadata": {},
   "source": [
    "## Click-Through Rate for Sister Projects"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c05502-c849-4711-b77d-6ec49af063df",
   "metadata": {},
   "source": [
    "We define the click through rate the proportion of search sessions where the user clicked on one of the results displayed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f4a10b89-36f8-4666-b991-74eac97c686d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ctr_query = \"\"\"\n",
    "\n",
    "WITH \n",
    "ft AS (\n",
    "    SELECT TO_DATE(coalesce(meta.dt, client_dt, dt)) AS log_date, \n",
    "           wiki, \n",
    "           COUNT(DISTINCT event.searchsessionid, event.pageviewid) AS n_ft\n",
    "    FROM event.searchsatisfaction ess\n",
    "    WHERE {ess_partition_statement}\n",
    "      AND wiki in ({wiki_db})\n",
    "      AND useragent.is_bot = false\n",
    "      AND event.action = \"searchResultPage\"\n",
    "      AND event.source = \"fulltext\"\n",
    "      AND event.hitsReturned > 0 -- only include successful searches\n",
    "      AND event.subTest IS NULL\n",
    "      AND event.isforced IS NULL -- only include non-test users\n",
    "    GROUP BY TO_DATE(coalesce(meta.dt, client_dt, dt)), wiki\n",
    "), \n",
    "ss AS (\n",
    "    SELECT TO_DATE(coalesce(meta.dt, client_dt, dt)) AS log_date, \n",
    "           wiki, \n",
    "           COUNT(DISTINCT event.searchsessionid, event.pageviewid) AS n_ss\n",
    "    FROM event.searchsatisfaction ess\n",
    "    WHERE {ess_partition_statement}\n",
    "      AND wiki in ({wiki_db})\n",
    "      AND useragent.is_bot = false\n",
    "      AND event.action = \"ssclick\"\n",
    "      AND event.source = \"fulltext\"\n",
    "      AND event.subTest IS NULL\n",
    "      AND event.isforced IS NULL -- only include non-test users\n",
    "    GROUP BY TO_DATE(coalesce(meta.dt, client_dt, dt)), wiki\n",
    ")\n",
    "\n",
    "SELECT ft.log_date,\n",
    "      ft.wiki, \n",
    "      n_ft,\n",
    "      n_ss\n",
    "FROM ft JOIN ss ON (ft.log_date = ss.log_date AND ft.wiki = ss.wiki)\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f039129-a2e7-4ebf-a308-313f93af9a37",
   "metadata": {},
   "outputs": [],
   "source": [
    "search_count = spark.run(ctr_query.format(\n",
    "            ess_partition_statement = make_partition_statement(last_week, today, prefix = 'ess'),\n",
    "            wiki_db = wikis))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8d695d8d-fba3-4860-9643-eee3eaf21107",
   "metadata": {},
   "outputs": [],
   "source": [
    "search_count = search_count.loc[(search_count['log_date'] >= last_week) &  \n",
    "                                (search_count['log_date'] <= yesterday)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e36574fe-5ac8-42f8-bb05-9a5fb9d7d434",
   "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>n_ft</th>\n",
       "      <th>n_ss</th>\n",
       "      <th>ctr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>idwiki</td>\n",
       "      <td>24984</td>\n",
       "      <td>173</td>\n",
       "      <td>0.692443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ptwiki</td>\n",
       "      <td>82832</td>\n",
       "      <td>695</td>\n",
       "      <td>0.839048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ruwiki</td>\n",
       "      <td>164415</td>\n",
       "      <td>1776</td>\n",
       "      <td>1.080193</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     wiki    n_ft  n_ss       ctr\n",
       "0  idwiki   24984   173  0.692443\n",
       "1  ptwiki   82832   695  0.839048\n",
       "2  ruwiki  164415  1776  1.080193"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ss_ctr = search_count.groupby(['wiki']).sum().reset_index()\n",
    "ss_ctr['ctr'] = (ss_ctr['n_ss'] / ss_ctr['n_ft']) * 100\n",
    "ss_ctr"
   ]
  }
 ],
 "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
}