{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Time To Event"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"# basic defaults, including study dates, common SQL exclusions and parquet files for anonymized data\n",
"%run -i 'data-defaults.py'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Select first event by action type"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"first_event_query = \"\"\"\n",
"SELECT page_token, page_id, action, MIN(event_offset_time) AS first_event_offset_time\n",
"FROM\n",
" citationusage\n",
"WHERE wiki = 'enwiki'\n",
" {}\n",
" AND to_date(event_time) >= '{}'\n",
" AND to_date(event_time) <= '{}'\n",
" AND useragent_is_bot = FALSE\n",
"GROUP BY page_token, page_id, action\n",
"\"\"\"\n",
"\n",
"citationusage_first_event = spark.sql(\n",
" first_event_query.format(event_exclusion_sql, start_date_string, end_date_string\n",
" ))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Register as table:"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"citationusage_first_event.registerTempTable(\"citationusage_first_event\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Time to event for W pages with external links - First event by type"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------+---------------------------+--------------------------+--------------------+--------+\n",
"| action|average_time_to_event_in_ms|median_time_to_event_in_ms| stddev| iqr|\n",
"+--------+---------------------------+--------------------------+--------------------+--------+\n",
"| fnHover| 1133349.50| 67672.0| 3.36349580336148E8|189559.0|\n",
"| fnClick| 551408.55| 56581.0| 8973764.21151973|132473.0|\n",
"| upClick| 1677620.66| 69882.0|1.7689820370919287E7|249820.0|\n",
"|extClick| 391666.78| 32758.0| 5.647982637670883E7| 86424.0|\n",
"+--------+---------------------------+--------------------------+--------------------+--------+\n",
"\n"
]
}
],
"source": [
"w_event_time_query = \"\"\"\n",
"SELECT action, \n",
" CAST(AVG(first_event_offset_time) AS DECIMAL(10,2)) AS average_time_to_event_in_ms, \n",
" PERCENTILE(first_event_offset_time,0.5) AS median_time_to_event_in_ms,\n",
" STDDEV(first_event_offset_time) as stddev,\n",
" (PERCENTILE(first_event_offset_time,0.75) - PERCENTILE(first_event_offset_time,0.25)) as iqr\n",
"\n",
"FROM \n",
" citationusage_first_event \n",
"WHERE page_id IN \n",
" (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)\n",
"GROUP BY action\n",
"\"\"\"\n",
"\n",
"\n",
"\n",
"spark.sql(\n",
" w_event_time_query.format(\n",
" event_exclusion_sql, start_date_string, end_date_string\n",
" )).show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Time to event for WP:M pages with external links - First event by type"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------+---------------------------+--------------------------+--------------------+---------+\n",
"| action|average_time_to_event_in_ms|median_time_to_event_in_ms| stddev| iqr|\n",
"+--------+---------------------------+--------------------------+--------------------+---------+\n",
"| fnHover| 4515701.82| 76874.0|2.1958250545759377E9|232157.25|\n",
"| fnClick| 881732.14| 72859.5|1.1722656796857577E7|180704.75|\n",
"| upClick| 2010296.18| 93005.0|1.8052394892862096E7| 367071.0|\n",
"|extClick| 825780.67| 47532.0|1.1779610983504394E7|147306.25|\n",
"+--------+---------------------------+--------------------------+--------------------+---------+\n",
"\n"
]
}
],
"source": [
"wpm_event_time_query = \"\"\"\n",
"SELECT action, \n",
" CAST(AVG(first_event_offset_time) AS DECIMAL(10,2)) AS average_time_to_event_in_ms, \n",
" PERCENTILE(first_event_offset_time,0.5) AS median_time_to_event_in_ms,\n",
" STDDEV(first_event_offset_time) as stddev,\n",
" (PERCENTILE(first_event_offset_time,0.75) - PERCENTILE(first_event_offset_time,0.25)) as iqr\n",
"\n",
"FROM \n",
" citationusage_first_event \n",
"WHERE page_id IN \n",
" (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)\n",
"GROUP BY action\n",
"\"\"\"\n",
"\n",
"spark.sql(\n",
" wpm_event_time_query.format(\n",
" event_exclusion_sql, start_date_string, end_date_string\n",
" )).show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Statistics\n",
"\n",
"Time to click:"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"# Plots inline\n",
"%matplotlib inline"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" action | \n",
" event_offset_time | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" extClick | \n",
" 78857 | \n",
"
\n",
" \n",
" | 1 | \n",
" extClick | \n",
" 315215 | \n",
"
\n",
" \n",
" | 2 | \n",
" extClick | \n",
" 16107 | \n",
"
\n",
" \n",
" | 3 | \n",
" extClick | \n",
" 255079 | \n",
"
\n",
" \n",
" | 4 | \n",
" extClick | \n",
" 784937 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" action | \n",
" event_offset_time | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" extClick | \n",
" 4192783 | \n",
"
\n",
" \n",
" | 1 | \n",
" extClick | \n",
" 172940 | \n",
"
\n",
" \n",
" | 2 | \n",
" extClick | \n",
" 27298 | \n",
"
\n",
" \n",
" | 3 | \n",
" extClick | \n",
" 25466 | \n",
"
\n",
" \n",
" | 4 | \n",
" extClick | \n",
" 21528 | \n",
"
\n",
" \n",
"
\n",
"