{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Baseline Metrics of Legacy Search on Commons\n", "\n", "In order to understand the effects of tests of search on Commons, we need to establish baselines. This notebook does that for legacy search on Commons. The phab task for this is [T258723](https://phabricator.wikimedia.org/T258723). The metrics are listed in its parent task [T258229](https://phabricator.wikimedia.org/T258229) because we want to measure these for both legacy search and Media Search.\n", "\n", "The metrics are:\n", "\n", "1. Number of searches made.\n", "2. Number of search sessions.\n", "3. Number of searches per session.\n", "4. Search session length.\n", "5. Click-through rate.\n", "6. Average position of clicked result in successful searches.\n", "\n", "I think we'd like to grab data for this on either a daily or weekly basis, and store aggregates somewhere, then build dashboards on top of it. It would be great to be able to update these datasets regularly, e.g. daily with a cron job." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "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, "metadata": {}, "outputs": [], "source": [ "## Load the RPython library so we can use R for graphs\n", "\n", "%load_ext rpy2.ipython" ] }, { "cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [], "source": [ "%%R\n", "library(ggplot2)\n", "library(hrbrthemes)\n", "library(tidyr)\n", "library(lubridate)\n", "library(zoo)\n", "library(dplyr)\n", "import::from(polloi, compress)" ] }, { "cell_type": "code", "execution_count": 119, "metadata": {}, "outputs": [], "source": [ "%%R\n", "\n", "## Options\n", "options(mc.cores = 4, digits = 3, scipen = 500)\n", "\n", "## Defining a custom theme for all plots\n", "commons_theme = function() {\n", " theme_ipsum_rc(\n", " base_size = 14, axis_title_size = 12, subtitle_size = 16,\n", " axis_title_just = 'cm'\n", " )\n", "}\n", "\n" ] }, { "cell_type": "code", "execution_count": 213, "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'))" ] }, { "cell_type": "code", "execution_count": 208, "metadata": {}, "outputs": [], "source": [ "today = dt.datetime.now(dt.timezone.utc).date()\n", "last_week = today - dt.timedelta(days = 7)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## SearchSatisfaction schema notes\n", "\n", "The SearchSatisfaction schema is as far as I know the first schema to be ported to the [Event Platform](https://wikitech.wikimedia.org/wiki/Event_Platform). At the time of this analysis (early August 2020), the database for this schema contains data captured through two event infrastructures: EventLogging (EL) and Event Platform (EP). This means that we need to deal with some inconsistencies in that data.\n", "\n", "### Timestamps\n", "\n", "The data contains three timestamp columns: `dt`, `meta.dt`, and `client_ts`. The first one of those is an EL column, the other two are EP columns. `meta.dt` and `dt` are set server-side, except when `client_ts` is set and `dt` is not, then it equals `client_ts`.\n", "\n", "Once we've accumulated enough data to only have EP data, we can most likely simplify our analysis and focus on `client_dt`. In the meantime, we'll combine all three timestamps in priority order: `client_ts`, `meta.dt`, then `dt`. Later, we might focus on client timestamps to understand more." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Number of Searches Per Day on Commons\n", "\n", "We make these measurements similarly to how we did this back in March 2020, when grabbing these numbers for the SDAW grant:\n", "\n", "1. For fulltext searches, we count every \"searchResultPage\".\n", "2. For autocomplete searches, we count every distinct `searchSessionId` + `pageViewId` combination. An autocomplete search session can consist of multiple searches as the user types out their query, and this collapses them into a single unit.\n", "3. A user can hit Enter in their autocomplete search and get to a fulltext search if their autocomplete search did not find any pages. In this case, we count it as two separate searches. Partly because we expect this to be somewhat rare compared to autocomplete searches in general. Secondly because identifying these can be tricky.\n", "4. Users who have Do Not Track enabled are not part of the dataset.\n", "\n", "Regarding No. 3, one way to go about this as Mikhail points out is: \"for each searchSessionId that have autocomplete and fulltext events, grab the query from the last autocomplete search preceeding (or that happened temporally near) a fulltext search and do a string comparison\" However, that's outside the scope of this analysis due to the tight deadline." ] }, { "cell_type": "code", "execution_count": 231, "metadata": {}, "outputs": [], "source": [ "# Query to count fulltext and autocomplete searches on Commons\n", "\n", "search_count_query = '''\n", "WITH ac AS (\n", " SELECT TO_DATE(coalesce(meta.dt, client_dt, dt)) AS log_date,\n", " COUNT(DISTINCT event.searchsessionid, event.pageviewid) AS n_autocomp\n", " FROM event.searchsatisfaction\n", " WHERE year = 2020\n", " AND month = 8\n", " AND wiki = \"commonswiki\"\n", " AND useragent.is_bot = false\n", " AND event.subTest IS NULL\n", " AND event.action = \"searchResultPage\"\n", " AND event.isforced IS NULL -- only include non-test users\n", " AND event.source = \"autocomplete\"\n", " GROUP BY TO_DATE(coalesce(meta.dt, client_dt, dt))\n", "), ft AS (\n", " SELECT TO_DATE(coalesce(meta.dt, client_dt, dt)) AS log_date,\n", " SUM(IF(event.hitsReturned > 0 , 1, 0)) AS n_fulltext_successful,\n", " SUM(IF(event.hitsReturned IS NULL , 1, 0)) AS n_fulltext_zeroresults\n", " FROM event.searchsatisfaction\n", " WHERE year = 2020\n", " AND month = 8\n", " AND wiki = \"commonswiki\"\n", " AND useragent.is_bot = false\n", " AND event.subTest IS NULL\n", " AND event.action = \"searchResultPage\"\n", " AND event.isforced IS NULL -- only include non-test users\n", " AND event.source = \"fulltext\"\n", " GROUP BY TO_DATE(coalesce(meta.dt, client_dt, dt))\n", ")\n", "SELECT ac.log_date, n_autocomp, n_fulltext_successful, n_fulltext_zeroresults\n", "FROM ac\n", "LEFT JOIN ft\n", "ON ac.log_date = ft.log_date\n", "'''" ] }, { "cell_type": "code", "execution_count": 232, "metadata": {}, "outputs": [], "source": [ "commons_searches_daily = spark.run(search_count_query)" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "## rpy2 doesn't seem to handle datetime.date objects very well, so we make it a string\n", "commons_searches_daily['log_date_str'] = commons_searches_daily['log_date'].apply(str)" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "%%R\n", "\n", "## Moving average function with right-alignment and zero-fill\n", "mavg = function(x, ndays) {\n", " rollapply(x, ndays, mean, align = 'right', fill = 0)\n", "}\n", "\n", "## Color-blind-friendly palette with black\n", "## http://www.cookbook-r.com/Graphs/Colors_(ggplot2)/\n", "cbbPalette <- c(\"#000000\", \"#E69F00\", \"#56B4E9\", \"#009E73\", \"#F0E442\", \"#0072B2\", \"#D55E00\", \"#CC79A7\")" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "%%R -i commons_searches_daily\n", "\n", "commons_searches_daily %>% mutate(log_date = ymd(log_date_str)) %>%\n", " filter(log_date < today(tzone = 'UTC')) %>% ## skip today because it's partial data\n", " arrange(log_date) %>%\n", " mutate(n_autocomp_m7 = mavg(n_autocomp, 7)) %>%\n", " ggplot(aes(x = log_date)) +\n", " scale_y_continuous(labels = compress) +\n", " scale_x_date(date_breaks = \"1 month\", minor_breaks = NULL, date_labels = \"%b\\n%Y\") +\n", " labs(x = \"Date\", y = \"Number of searches\",\n", " title = \"Autocomplete searches per day with 7-day MA\") +\n", " geom_line(aes(y = n_autocomp)) +\n", " geom_line(aes(y = n_autocomp_m7), color = cbbPalette[2]) +\n", " commons_theme()" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "%%R -i commons_searches_daily\n", "\n", "commons_searches_daily %>% mutate(log_date = ymd(log_date_str)) %>%\n", " filter(log_date < today(tzone = 'UTC')) %>% ## skip today because it's partial data\n", " arrange(log_date) %>%\n", " mutate(n_fulltext_successful_m7 = mavg(n_fulltext_successful, 7)) %>%\n", " ggplot(aes(x = log_date)) +\n", " scale_y_continuous(labels = compress) +\n", " scale_x_date(date_breaks = \"1 month\", minor_breaks = NULL, date_labels = \"%b\\n%Y\") +\n", " labs(x = \"Date\", y = \"Number of searches\",\n", " title = \"Successful Fulltext searches per day with 7-day MA\") +\n", " geom_line(aes(y = n_fulltext_successful)) +\n", " geom_line(aes(y = n_fulltext_successful_m7), color = cbbPalette[2]) +\n", " commons_theme()" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "%%R -i commons_searches_daily\n", "\n", "commons_searches_daily %>% mutate(log_date = ymd(log_date_str)) %>%\n", " filter(log_date < today(tzone = 'UTC')) %>% ## skip today because it's partial data\n", " arrange(log_date) %>%\n", " mutate(n_fulltext_zeroresults_m7 = mavg(n_fulltext_zeroresults, 7)) %>%\n", " ggplot(aes(x = log_date)) +\n", " scale_y_continuous(labels = compress) +\n", " scale_x_date(date_breaks = \"1 month\", minor_breaks = NULL, date_labels = \"%b\\n%Y\") +\n", " labs(x = \"Date\", y = \"Number of searches\",\n", " title = \"Zero-results Fulltext searches per day with 7-day MA\") +\n", " geom_line(aes(y = n_fulltext_zeroresults)) +\n", " geom_line(aes(y = n_fulltext_zeroresults_m7), color = cbbPalette[2]) +\n", " commons_theme()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Baselines for number of searches\n", "\n", "Let's calculate some baselines using the most recent 7-day average." ] }, { "cell_type": "code", "execution_count": 233, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "n_autocomp 52473.57\n", "n_fulltext_successful 97826.00\n", "n_fulltext_zeroresults 3646.00\n", "dtype: float64" ] }, "execution_count": 233, "metadata": {}, "output_type": "execute_result" } ], "source": [ "round(commons_searches_daily.loc[(commons_searches_daily['log_date'] < today) &\n", " (commons_searches_daily['log_date'] >= last_week)][\n", " ['n_autocomp', 'n_fulltext_successful', 'n_fulltext_zeroresults']\n", "].mean(), 2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Number of Search Sessions and Number of Searches per Session\n", "\n", "Here, we're interested in the number of search sessions that originate on a given day. This means, for each session, get the timestamp of the first SERP in that session. Also, count the number of SERPs in that session.\n", "\n", "Because autocomplete searches generate multiple searches while the user types, we'll count these separately." ] }, { "cell_type": "code", "execution_count": 234, "metadata": {}, "outputs": [], "source": [ "autocomp_session_query = '''\n", "SELECT event.searchsessionid,\n", " MIN(TO_DATE(coalesce(meta.dt, client_dt, dt))) AS session_start_date,\n", " SUM(1) AS num_searches\n", "FROM event.searchsatisfaction\n", "WHERE year = 2020\n", "AND month = 8\n", "AND wiki = \"commonswiki\"\n", "AND useragent.is_bot = false\n", "AND event.subTest IS NULL\n", "AND event.action = \"searchResultPage\"\n", "AND event.isforced IS NULL -- only include non-test users\n", "AND event.source = \"autocomplete\"\n", "GROUP BY event.searchsessionid\n", "'''" ] }, { "cell_type": "code", "execution_count": 235, "metadata": {}, "outputs": [], "source": [ "autocomp_session_metrics = spark.run(autocomp_session_query)" ] }, { "cell_type": "code", "execution_count": 236, "metadata": {}, "outputs": [], "source": [ "fulltext_session_query = '''\n", "SELECT event.searchsessionid,\n", " MIN(TO_DATE(coalesce(meta.dt, client_dt, dt))) AS session_start_date,\n", " SUM(1) AS num_searches\n", "FROM event.searchsatisfaction\n", "WHERE year = 2020\n", "AND month = 8\n", "AND wiki = \"commonswiki\"\n", "AND useragent.is_bot = false\n", "AND event.subTest IS NULL\n", "AND event.action = \"searchResultPage\"\n", "AND event.isforced IS NULL -- only include non-test users\n", "AND event.source = \"fulltext\"\n", "GROUP BY event.searchsessionid\n", "'''" ] }, { "cell_type": "code", "execution_count": 237, "metadata": {}, "outputs": [], "source": [ "fulltext_session_metrics = spark.run(fulltext_session_query)" ] }, { "cell_type": "code", "execution_count": 238, "metadata": {}, "outputs": [], "source": [ "## Drop the session ID columns, we don't really need those\n", "autocomp_session_metrics.drop(columns = 'searchsessionid', inplace = True)\n", "fulltext_session_metrics.drop(columns = 'searchsessionid', inplace = True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We aggregate by day, filter out today because it's partial data, and remove sessions with more than 50 searches because those tend to be non-human. I got the 50 cutoff from Chelsy and Mikhail's work.\n", "\n", "Daily average number of autocomplete sessions:" ] }, { "cell_type": "code", "execution_count": 239, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "17438.6" ] }, "execution_count": 239, "metadata": {}, "output_type": "execute_result" } ], "source": [ "round(autocomp_session_metrics.loc[(autocomp_session_metrics['session_start_date'] < today) &\n", " (autocomp_session_metrics['session_start_date'] >= last_week) &\n", " (autocomp_session_metrics['num_searches'] < 50)]\n", " .groupby('session_start_date')\n", " .agg({'session_start_date' : 'count'})\n", " ['session_start_date'].mean(), 1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Daily average number of fulltext sessions:" ] }, { "cell_type": "code", "execution_count": 240, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "21099.6" ] }, "execution_count": 240, "metadata": {}, "output_type": "execute_result" } ], "source": [ "round(fulltext_session_metrics.loc[(fulltext_session_metrics['session_start_date'] < today) &\n", " (fulltext_session_metrics['session_start_date'] >= last_week) &\n", " (fulltext_session_metrics['num_searches'] < 50)]\n", " .groupby('session_start_date')\n", " .agg({'session_start_date' : 'count'})\n", " ['session_start_date'].mean(), 1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Number of searches per session" ] }, { "cell_type": "code", "execution_count": 241, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " session_start_date num_searches session_start_date_str\n", " Min. :737638 Min. : 1 Length:221229 \n", " 1st Qu.:737641 1st Qu.: 2 Class :character \n", " Median :737644 Median : 5 Mode :character \n", " Mean :737644 Mean : 9 \n", " 3rd Qu.:737647 3rd Qu.:12 \n", " Max. :737650 Max. :49 \n" ] } ], "source": [ "%%R\n", "\n", "autocomp_session_metrics %>% filter(num_searches < 50) %>%\n", " summary()" ] }, { "cell_type": "code", "execution_count": 242, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " session_start_date num_searches session_start_date_str\n", " Min. :737638 Min. : 1.0 Length:263699 \n", " 1st Qu.:737641 1st Qu.: 1.0 Class :character \n", " Median :737644 Median : 2.0 Mode :character \n", " Mean :737644 Mean : 4.3 \n", " 3rd Qu.:737647 3rd Qu.: 5.0 \n", " Max. :737650 Max. :49.0 \n" ] } ], "source": [ "%%R\n", "\n", "fulltext_session_metrics %>% filter(num_searches < 50) %>%\n", " summary()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's take out today's date, filter to the last week (because activity moves by week), and then calculate the median." ] }, { "cell_type": "code", "execution_count": 243, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "num_searches 5.0\n", "dtype: float64" ] }, "execution_count": 243, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autocomp_session_metrics.loc[(autocomp_session_metrics['session_start_date'] < today) &\n", " (autocomp_session_metrics['session_start_date'] >= last_week) &\n", " (autocomp_session_metrics['num_searches'] < 50)].median()" ] }, { "cell_type": "code", "execution_count": 244, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "num_searches 2.0\n", "dtype: float64" ] }, "execution_count": 244, "metadata": {}, "output_type": "execute_result" } ], "source": [ "fulltext_session_metrics.loc[(fulltext_session_metrics['session_start_date'] < today) &\n", " (fulltext_session_metrics['session_start_date'] >= last_week) &\n", " (fulltext_session_metrics['num_searches'] < 50)].median()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Search session length\n", "\n", "We define it as the time difference beween the first search event and last event in a session, for non-bot sessions with less than 50 searches. Since it's convenient to do more things at the same time, we also gather information about click-through, positions, and dwell time in the same query." ] }, { "cell_type": "code", "execution_count": 249, "metadata": {}, "outputs": [], "source": [ "session_info_query = '''\n", "WITH cs AS (\n", " SELECT event.searchsessionid,\n", " MIN(coalesce(meta.dt, client_dt, dt)) AS session_start_ts\n", " FROM event.searchsatisfaction\n", " WHERE year = 2020\n", " AND month = 8\n", " AND wiki = \"commonswiki\"\n", " AND useragent.is_bot = false\n", " AND event.subTest IS NULL\n", " AND event.action = \"searchResultPage\"\n", " AND event.isforced IS NULL -- only include non-test users\n", " GROUP BY event.searchsessionid\n", " HAVING SUM(1) < 50\n", "),\n", "se AS (\n", " SELECT event.searchsessionid,\n", " MAX(coalesce(meta.dt, client_dt, dt)) AS session_end_ts\n", " FROM event.searchsatisfaction\n", " WHERE year = 2020\n", " AND month = 8\n", " AND wiki = \"commonswiki\"\n", " AND useragent.is_bot = false\n", " AND event.subTest IS NULL\n", " AND event.isforced IS NULL -- only include non-test users\n", " GROUP BY event.searchsessionid\n", "),\n", "ct AS (\n", " SELECT event.searchsessionid, event.position\n", " FROM event.searchsatisfaction\n", " WHERE year = 2020\n", " AND month = 8\n", " AND wiki = \"commonswiki\"\n", " AND useragent.is_bot = false\n", " AND event.subTest IS NULL\n", " AND event.action = \"visitPage\"\n", " AND event.isforced IS NULL -- only include non-test users\n", "),\n", "dw AS (\n", " SELECT event.searchsessionid, max(event.checkin) AS last_checkin\n", " FROM event.searchsatisfaction\n", " WHERE year = 2020\n", " AND month = 8\n", " AND wiki = \"commonswiki\"\n", " AND useragent.is_bot = false\n", " AND event.subTest IS NULL\n", " AND event.action = \"checkin\"\n", " AND event.isforced IS NULL -- only include non-test users\n", " GROUP BY event.searchsessionid\n", ")\n", "SELECT cs.searchsessionid, session_start_ts, se.session_end_ts,\n", " IF(ct.searchsessionid IS NOT NULL, 1, 0) AS clicked_through,\n", " coalesce(ct.position, -1) AS position,\n", " IF(dw.last_checkin IS NOT NULL, dw.last_checkin, -1) AS last_checkin\n", "FROM cs\n", "JOIN se\n", "ON cs.searchsessionid = se.searchsessionid\n", "LEFT JOIN ct\n", "ON cs.searchsessionid = ct.searchsessionid\n", "LEFT JOIN dw\n", "ON cs.searchsessionid = dw.searchsessionid\n", "'''" ] }, { "cell_type": "code", "execution_count": 250, "metadata": {}, "outputs": [], "source": [ "session_info = spark.run(session_info_query)" ] }, { "cell_type": "code", "execution_count": 251, "metadata": {}, "outputs": [], "source": [ "session_info.drop(columns = 'searchsessionid', inplace = True)" ] }, { "cell_type": "code", "execution_count": 261, "metadata": {}, "outputs": [], "source": [ "r_session_info = session_info[['session_start_ts', 'session_end_ts']]" ] }, { "cell_type": "code", "execution_count": 264, "metadata": {}, "outputs": [ { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAMAAABKCk6nAAAC9FBMVEUAAAABAQECAgIDAwMEBAQFBQUGBgYHBwcICAgJCQkKCgoLCwsMDAwNDQ0ODg4PDw8QEBARERESEhITExMUFBQVFRUWFhYXFxcYGBgZGRkaGhobGxscHBwdHR0eHh4fHx8gICAhISEiIiIjIyMkJCQlJSUmJiYnJycoKCgpKSkqKiorKyssLCwuLi4vLy8wMDAxMTEyMjIzMzM0NDQ1NTU2NjY3Nzc4ODg5OTk6Ojo7Ozs8PDw9PT0+Pj4/Pz9AQEBBQUFCQkJDQ0NERERFRUVGRkZISEhJSUlKSkpMTExNTU1OTk5PT09QUFBRUVFSUlJTU1NUVFRVVVVWVlZXV1dYWFhZWVlaWlpbW1tcXFxdXV1eXl5fX19gYGBhYWFiYmJjY2NkZGRlZWVmZmZnZ2doaGhpaWlqampra2tsbGxtbW1ubm5vb29wcHBxcXFycnJzc3N0dHR1dXV2dnZ3d3d4eHh5eXl6enp7e3t8fHx9fX1+fn5/f3+AgICBgYGCgoKDg4OEhISFhYWGhoaHh4eIiIiJiYmKioqLi4uMjIyNjY2Ojo6Pj4+QkJCRkZGSkpKTk5OUlJSVlZWWlpaXl5eYmJiZmZmampqbm5ucnJydnZ2enp6fn5+goKChoaGioqKjo6OkpKSlpaWmpqanp6eoqKipqamqqqqrq6usrKytra2urq6vr6+wsLCxsbGysrKzs7O0tLS1tbW2tra4uLi5ubm6urq7u7u8vLy9vb2+vr6/v7/AwMDBwcHCwsLDw8PExMTFxcXGxsbHx8fIyMjJycnKysrLy8vMzMzNzc3Ozs7Pz8/Q0NDR0dHS0tLT09PU1NTV1dXW1tbX19fY2NjZ2dna2trb29vc3Nzd3d3e3t7f39/g4ODh4eHi4uLj4+Pk5OTl5eXm5ubn5+fo6Ojp6enq6urr6+vs7Ozt7e3u7u7v7+/w8PDx8fHy8vLz8/P09PT19fX29vb39/f4+Pj5+fn6+vr7+/v8/Pz9/f3+/v7////RJsHrAAAU1UlEQVR4nO3da2BU5Z3H8VG0LGIBL62tiNR2t7rV1lXXitX1VrfWSUIyMSEJEeQaCgVMUS4jQkOtFxANgpByiYIsaAVFRatZECsupCJWsSBNTAi5B0LChMmQMM+bfZ7nzBxOZs6cOUlmMmf+8/u+mOeZk3MzH3IyiQzHxhDpbLE+ARTdAEw8ABMPwMQDMPEATDwAEw/AxAMw8QBMPAATD8DEAzDxAEw8ABMPwMQDMPEATDwAEw/AxAMw8QBMPAATD8DEAzDxAEw8ABMPwMRLLOAzJpd5za7YeXYTr87C8DsQi70Ga2nOxMQRdEos4GadZSe8wctOHze5cb06az+lszD8DngtboO13C3q1HVanTaZFwYwgAkFYOIBmHgAJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJl6kgLNsSm8A2FpFCnjUU02iEVsBbK0iBrxMDrcD2GIBmHgAJh6AiQdg4gGYeAAmHoDDtcnOyxCzkkkjf7MnzMRyAThcm6aWlZVV8MmnSX+uKE45bDixXr0Grt4n+xVd4HzfxLmQP8xYYjixXr0GnjXoSlE/usCOUbkF/CvYm/4mf1Y8wWhiwXoP/KicDicLXFpyaO+jGY2szf4hf7bVYTQRdVqrYzrLjncEL3M3hdj4d12BX69VP9rmUqe1OhuHOjqv2WWwlqtZnba61WmDR7NKRIFF7pwNfsV0o4nwbbRWlTrLjjQEL6vTW1Esm9oV+KVy9aO1Neq0XGfjUEcXJ1BjsFbNEXV6tE6dVtRrVgnxvvKeA7O5T+MSLQeCl2h5zJzVifsiizrwc7sO7nk0rSphf0wiD1w4LjW3oFzMSib6f60RemK5AEw8ABMPwMQDMPEATDwAEw/AxAMw8QBMPAATD8DEAzDxAEw8ABMPwMQDMPEATDwAEw/AxOs58I68vIfy8vKuB7Cl6znw8z8rEF0BYEvXC+BsOdwMYEsHYOIBmHgAJh6AiQdg4gGYeACOcN52a9Wgs6zRHbzMVR+4ZIku8KvV6gqtJ9RpdeDGBkfnNbUYrNXSpE6bXeq0rk2zis4fUABrAjChcIkmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6AidcD4Pr3ZHkAjod6APz6hTeKhgA4HuoJ8B1ySAVwPARg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6AiQdg4gGYeAA20Rv2P4qhZJL/RnahJ5YLwOErGztTACfMzSkTDdg9ec9CAZwwt5dNNODnVzABnDg3iE4w4I8mt0tg333cHUYTRuIW7+uMgMnd4r0xh39r1QCnG02ksLU6prPseEfwMneTOn3NAPi2TRUukdhDm0vdpLYbR+c1uwzWcjWr01a3Om3waFYxJuse8Cf25OTkpKTkelyiRYNtSqWMyiX6VAVv9mMVnXiRJRq8uU30AzrAsoWJ8WPS0XmyNCPgrXK4iiIwK5no/7VG6Inl6g7wniEzRTclHnAc1y3gH8npLADHUQAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6AiWcKuDk3NyczN/fXAI6/TAFXn/esKBPA8Zc54P5yWAvg+AvAxANwhDvTYq2qdZbVnAhYcLDbwDv5Vk0N6g4qu3F0Xm2jwVqNteq0/pg6rWrWrBLJd/jHe/gKJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJl4o4FfXKrWLBQCO30IBD7szSWRrFAsAHL+FBP5MTs8BcJwHYOIBmHgAJh6AiQdg4oUBtk3J500CcNwWDnjGHN5DAI7bwgHLS/Q+AMdtACYegIkHYOIBOFx/mZ6RPmOXmJG6xTuA/e0sOXCgKOlzavcuBHCXxm6kdot3AGs6/VHK59Ru8Q5gtcpke9oO07d4t1oADtvpin+sS/+H2Vu8d9Zaq3KdZd/U1NZe3jvgd/luqo6oezzcjaPzKqoM1qqqUKeVR9VpWbVmFWPsnnwPXliAS7QcKH4Fi5yP40WWHAgCv/DRwf0r7DvxY5IcCAKvnJiWPfsTMSN1i3cAEwnAxAMw8QBMPAATD8DBvSQfi0zv0NIBOLjh8nGI6R1aOgAHVlr6/VLe/wwzvUNLB+DAhg7tN3To0GE3vm56h5YOwMHlmd5VHARg4gE4uI4N+ZN4pndo6aID/N3Hi4uLVxcV+6HiCzjn5rkFPNM7tHTRAT7v9vtF59T69hhfwD86HW6NOCpKwKVyOD8+gW/uML0v6wfg4J507PyCZ3qHlg7AwV2mZHqHlg7AxAMw8QAc3Dwl0zu0dAAObjovZ3CW6R1aOgCH6J8jTe/Q0gE4VD8zvUNLB+Dg5LtnVv2H6R1aOgAH14836I79pndo6QBMPADr9Gl+Zv4+0/uzdgAObuOF4wrGXbjZ9A4tHYCDu1r8mzq7rjG9Q0sH4OAGe/iDp4d/bfZMiLvZx6pqnWU1J1pahkYE+GvfHiu7cXRebaPBWo216rT+mDqtatascsaQICzw3Qs6WMf8X3aH1brhKzi4Q9dcdN1F/27Jt3N3PwDr1Ln3jb3m92ftAEw8AAc3Vr6KHmd6h5YOwMFdJO7n136J6R1aOgAH951W/tAKYLLA6XntzJNnyX+YsPsBOLjqmwZfO/jmGtM7tHQA1unMvjf3Bd5DOV4DMPEATDwAEw/AxAMw8QBMPAATD8DEAzDxAEw8ABMPwMQDMPEATDwAEy/oU3zHt2TnAJhGQZ/iES/XiPoBmEbBwG/JAcBEAnC43n4kc5TzkJiVTPLfyC70xHIBOFyzNn+235nRQObmlADWqS35fTK3lwWwTq1Je8jcIBrAOi3J6/Dfx91hNBF1WqtjgQtuiSTwUd9Oa80eXanZZbCWq1mdtrrVaYNHs0qkgdflVjO/YrrRRPg2WqvKwAX/GUngA76dlps9utKRGoO1ao6o06N16rSiXrNKL9/hH1jRg0f5Iy7RIoKXaO8LY5V3seBFFiMJvDR9d1lZ2XH8mCQHgsCpdtFGPiuZ6P+1RuiJ5QIw8QBMPAATD8DEAzDxAEw8ABMPwMSLKrDt24NEFwM4dkUXeG85r7QfgGNXdIHlJboewDEMwMQDMPEATDwAEw/AxAMw8QBMPAAT7+wnb9/bsmsATKqzn7z0q24R9QMwqTTAL8rh2wAmFYCJB2DiAZh4ACYegIkHYOIBOMJ526xVnTpLjR5wW1XYo3epodlgreYGddrUok5rTmpWMb5rWZSB261VgzpLix5we3XYo3epqcVgrZYmddrsUqd1bZpVYglstXCJJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACZeHwDXnPvlri95XxsdvUsAjlx9AHzA9j3RpcONjt4lAEeuvgDuJ598CuBYBGDiAZh4ACYe/+QdeEt2G4B1278w175Dzkom+W9kF3piufgnb+YVI0TfArBue9bvVoDj9uaUM+fJ2RUADpUCHLe3lwVwuCRw/N4gGsDhksC++7g7jCbMkrd4n9xHwMN0j66XxW7x3gU43Wgiha3Vsc7OGX31Fax7dL2aXQZruZrVaatbnTZ4NKsYc+ESLcIlWhNeZFEGdpeV2V8ta8SPSXIgCLzfLirks5KJ/l9rhJ5YLgATD8DEAzDxAEw8ABMPwMQDMPEATDwAEw/AxAMw8QBMPAATD8DEAzDx+g54S3/5169HTO56dL0AHLn6DviV78o3UDz2i65H1wvAkasPgZVL9JsA7tMATDwAEw/AxAMw8QBMPAATD8CE+2zZsmVPL1t2F4CJtuSqUaKLAUy0JQ/K4QYAEw3AUejMceu0KBbASy6W79az/0GcQZX+iVXXd3nada36anVa26hOK5s0q0T6Hf7xWky+guf96ypRVoZ4QvAr2ErFBli5RC8HcPQDMPEATDwAEw/AxAMw8QBMPAATD8DEAzDxYgk88dJbeTff+oLeiQE4MsUSOOP2baL0fL0TA3BkiimwvESzOQCOYgAmHoD7oA/ylT7o06MqAbgPWnTDHNENi+Sz58YofdUnx7YA8PQxX8qOdTkxUsDK+6EnK8D3Zy4WXR7lr+ejW2VjYw88YqC89eyApV3OjzBwsRyujzLwpiG3iy6xALByiR4NYH/e00rm/3t02vRLOditBrz5GaVDCQy806b0q94c1KrAd901VjTog0QErrhcdulPWkXTBv5Y5uzRQS0HfNO194sueU0+6z9wsKzad7rEgTuyZEmD/i6afZ1cmHfLbtGU8dp9LRur9GW4g1oO+IdZxaILFODzXy8T/UuV73TpAQ/LfVzWJJ602xYX8qYNkR963gesXKKzh90ne0U+S0mX38GueDfcQa0HrFyiL/IBfyKHAXSBB4ycJhpwUDxpt7nE8L96wPfduV50wz1PiH6yWi68CcCGWQFYuUQPWrqZt8EIWLlE//TGiaILAGwm6wCfO+Ie3p0mgBfI4fsK8JUZ8pdjc+pDHhTAkS70Ld4NgeUluqW7wAPT5G+3+zuXyzyaox1TfoWVHxfA/X5xtywOgA3uXRgNYOUSfe59GbwHbCc0R/vrAPkrrMvjAti28lXeWlscABvcfTSKwPKnpQ7bU+I1+OLctaI518gPTY8PYHmJrrEtfWIpb7X/M2ZBYKP7B0cb2GNLFz9F33tekuin8Qf8T9to0b0XKP+fbYGnpdHj8XTINawCrL3Fe2CLxrtE4+fL4b4iOQx4Ww7nfiYea20NYtg+RC575lo5TLhXDv89Rg7XzZPD91bI4YI3la1LxWOzrUYMJRfKZc9dLYcpd8vh19lyuP4ROQx9Xg4X/lkO/T6Rg61SPO7uL5+s/KEcZt4uh+RMOdz4sByuXCKHQZvkcN5Hytbl4vFv/eSTtVfK4ZERcnA45PDz6XL4wVNyGLJBDufvULb+Wjx+YZNPNgxaKLrL95va294Tvb5H/lqv9bQ1gOUt3jtruzbbpm2gDfWg+bW1ZdWaT6oxdt9eojvbldrk4ynlyUm3HFqVoUVZ2Cof3SeVFZWhzSUHl7K161T3t67jQ9etT7XXu9WtW/1bn6zlW7cGbF3XHrx1lXrslmZ1a7Gw69an5JnX+bdu8x9bDvUnNMdukYc5u/WJBv/W7Q0nlFU6LfwiK9bp/dXzE97gZaePm9z47E/f7ad0FobfAYvD/x9s7Vu8BwXgbmfpW7wHBWBCAZh4ACYegIkHYOIBmHgAJh6AiQdg4gGYeAAmHoCJB2DiAZh4erc30FvmNbvi2U+016uzMPwOxGKvwVqaMzFxBJ0SCzgBAzDxAKxp0cqebecs7tnGSwp7cJxufQDAXdq2Q2/pwiK2f2GuXX5sk7gJUkbgGvLzq7+xYZEA5mcH4N7FP4V71u/2AU8tKyurCFzD4PMr6gj1AQBHPeeaoszst0+/mDFWvL1CXGWdRctzRr/EX7D+dgtf8nQhWyq+aKu89tFpU95nm/L1Nk5dsC5r9KQXGZs3w2FPeUazMXOuXJo1V+fAnmUPZL+8mK+w+3cZWQV1jL2fI14az07uZNX2FYy97GTeLRP4IcVB0rPTRqXxM3QWd6wdZR//OTt7msrZOdfyE3hJ5+c9AGduqHgj6fHNFZtTGnzAaTvOVGZ8rDGSXyOv2NfWfZz5102OUbkFFYEbT7Cv+ObDpAWMjUtaXbnYvl0LnPKXDrfOgf+Uu+/okgf4Cjv+r6b8yd96mTtjN2OtqckH2bvZkxmbtZm9MqGUH3Jq5oYVWUlznnng6ZQGZ/G6jLTtq1LrNKepfAVnFn/z4ciP9f4LEx1YfHWN5zTenB0+4N/zJU+tCAD2OMQleuP80pJDex/NaAzY2JnCPzh6KvMkTeN7ypyqBdb/h2M8aSX8Icd3iW5P5n9mli9k7K0x+a+xxRvTjrenfOVx7OMf2pg1l0/4QTbOz9nhXJOS9iXzTi3SnKYCPJs/FKzQO1KiA6/hD7Ne5g/TtvqA1/InK58IAC5T7jEp367hztkQsLEzm288fgI7Yhdiv8nRAhfpHrfSLv5RnQV8hcqCnCS7vZSxw8nH2fT16+azMYdmf/iZo9N3SMca/7GnbXXm2sU71ZY/pjlNBVi8Ee/FJ/SOlOjA4tXJIxuZQiKBxZKVixibLoye9AEftisvsmRznw7Y2JnD1x0/ngOLdx0KYHXjEC9/Ku3i95mLCpn3oWX1nd4U4Tb9tbKk2r+lH8k488oL/FvwYXuFchA+8Z2h02l/lk+Wz9ec5tkXWSsX6R0JwKGAHxeTadzoCb60Pe0ssDtndcDGfmBPEr/MerMmaDYOAexJ3cvXnFDIGu01jH0jvzDfmbxyHnOnLF3I/j6RfwtuT3tVOQif+IHXjMwo4pfoP2lOU5wdgENmALxlwgnvthRutObh+obDy+2//2Tf9m3P7Tq459G0qoCN/cDsIceeqlXJ/Ar6lMO3cagfYIpmtHi3JBeyzlHbWOvcJAHc5hi5k7H85C2cP/kr/u3X8dbR8u2Ti/lk/Kry7ds4cPG6rMxnV6XWa06Tn13LGQCHygDYU5g9dp24ytbPdijfBVOdnxaOS80tKA/cWAWe9/DokTP/wDdeP9K3cShgz/MZD70gVtg/5cEpu9Llm8CfHeVhrNh+mLE5DvEz0ztTRmY7p/MdvONIznZ+KoA71mTZR3/ONKcpzq4KwHHR3B7+ptQwAFul1h1JR6OwWwBbpTEZb0RjtwAmHoCJB2B/w0tjfQZRKeGB3b5/u2b9Sw2xPpWolPDAzO12X7Ld7Q7xN+LiPgDzLnmfyUv0Zcv/beDDR/5r4P0uxurSvzP82VifWAQCMNMA39NYNmTE5ydvfY55R+S7K378dqzPrPcBmGmA+WifJ2+Stn9QJ2OFD8b6zHofgJkG+AvGMpYx9mIae7P/1VdffdXIWJ9Z7wMw0wXed5nu33CKvwDMdIHP3DLrZOcBa/5zbt0KwEwXmNVlXzbk5+/F+sx6H4CJB2DiAZh4ACYegIkHYOIBmHgAJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6AiQdg4gGYeAAmHoCJB2DiAZh4ACYegIkHYOIBmHgAJh6Aiff/GCNw5g1NwSgAAAAASUVORK5CYII=\n" }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%R -i r_session_info\n", "\n", "r_session_info %>% mutate(\n", " session_start = ymd_hms(session_start_ts),\n", " session_end = ymd_hms(session_end_ts),\n", " session_length = session_end - session_start\n", " ) %>% filter(session_length > 0) %>%\n", " ggplot(aes(x = as.numeric(session_length))) + \n", " geom_histogram(binwidth = 0.2, colour=\"black\", fill='white') +\n", " scale_x_log10(\n", " \"Time\",\n", " breaks=c(60, 15*60, 60*60, 24*60*60, 7*24*60*60, 30*24*60*60, 365*24*60*60),\n", " labels=c(\"minute\", \"15 min.\", \"hour\", \"day\", \"week\", \"month\", \"year\")) +\n", " commons_theme()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This looks fairly well-distributed on a log-scale, so let's use the median." ] }, { "cell_type": "code", "execution_count": 253, "metadata": {}, "outputs": [], "source": [ "session_info['session_start'] = session_lengths['session_start_ts'].apply(parse_dt)\n", "session_info['session_end'] = session_lengths['session_end_ts'].apply(parse_dt)\n", "session_info['session_length'] = session_lengths['session_end'] - session_lengths['session_start']\n", "session_info['session_start_date'] = session_lengths['session_start'].apply(lambda x: x.date())" ] }, { "cell_type": "code", "execution_count": 254, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Timedelta('0 days 00:00:48.148500')" ] }, "execution_count": 254, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session_lengths.loc[(session_lengths['session_start_date'] < today) &\n", " (session_lengths['session_start_date'] >= last_week)]['session_length'].median()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Click-through rate\n", "\n", "Per the [data scientist takehome task](https://github.com/nettrom/Contributors-Hiring-DataScientist-2018), the Search Team defines the click-through rate as the \"proportion of search sessions where the user clicked on one of the results displayed.\"\n", "\n", "We're again limiting this to non-bot sessions with less than 50 searches made." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Calculate the average click-through rate:" ] }, { "cell_type": "code", "execution_count": 255, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "71.55" ] }, "execution_count": 255, "metadata": {}, "output_type": "execute_result" } ], "source": [ "round(100 * session_info['clicked_through'].mean(), 2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Average position of clicked result in successful searches" ] }, { "cell_type": "code", "execution_count": 265, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "count 252434.000000\n", "mean 19.342549\n", "std 52.663988\n", "min 0.000000\n", "25% 0.000000\n", "50% 3.000000\n", "75% 13.000000\n", "max 499.000000\n", "Name: position, dtype: float64" ] }, "execution_count": 265, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Position can't be -1 (that means it's missing), and the maximum number\n", "## of results is 500, so it can't be above that either.\n", "\n", "session_info.loc[(session_info['clicked_through'] == 1) &\n", " (session_info['position'] < 500) &\n", " (session_info['position'] != -1)]['position'].describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The large difference between the mean and median isn't surprising, the bigger values pulls the mean up. In other words, we'll use the median." ] }, { "cell_type": "code", "execution_count": 258, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3.0" ] }, "execution_count": 258, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Position can't be -1 (that means it's missing), and the maximum number\n", "## of results is 500, so it can't be above that either.\n", "\n", "session_info.loc[(session_info['clicked_through'] == 1) &\n", " (session_info['position'] < 500) &\n", " (session_info['position'] != -1)]['position'].median()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Success rate\n", "\n", "Defined as a click-through with a dwell time of at least 10 seconds." ] }, { "cell_type": "code", "execution_count": 257, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "81.35" ] }, "execution_count": 257, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Same assumptions as before,\n", "## plus removing all sessions with no checkin\n", "\n", "round(100 *\n", " session_info.loc[(session_info['clicked_through'] == 1) &\n", " (session_info['position'] < 500) &\n", " (session_info['position'] != -1) &\n", " (session_info['last_checkin'] >= 10)]['last_checkin'].count() /\n", " session_info.loc[(session_info['clicked_through'] == 1) &\n", " (session_info['position'] < 500) &\n", " (session_info['position'] != -1)]['last_checkin'].count(),\n", " 2)" ] } ], "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 }