{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Opt-out rate for desktop improvements feature on test wikis\n",
    "\n",
    "[Task](https://phabricator.wikimedia.org/T260149)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Questions/Notes for Olga:\n",
    "\n",
    "https://superset.wikimedia.org/r/310\n",
    "\n",
    "-  PrefUpdate captures user preference modifications at registration - not isolated to just user initiated changes. https://phabricator.wikimedia.org/T260867#6403042. Caused by change merged on August 3rd https://phabricator.wikimedia.org/rEWMV30731c2c748a0ea34f798628fe7961b928572a23. This is likely why only the numbers for users with 0 edits are so high. \n",
    "- Is the table https://docs.google.com/spreadsheets/d/1svlRDsYjOhjXkIV1a-DqLYTcLIFuYoc6pHHz8_VmQcc/edit#gid=625518477 correct? Numbers not matching. For example, on frwiki there are more users changing to legacy, which doesn't make sense if legact is default. We would first need them to opt-in to modern before opt-out to legacy.\n",
    "- For thos shown the modern vector as default, we will need another way to find the total number of people that had access prior to opt-out (number that viewed a page?)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Purpose\n",
    "\n",
    "We would like to know the number of logged-in users that have opted out from the desktop improvements projects from the beginning of the deployments on the test wikis (target is less than 40%)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Metrics\n",
    "\n",
    "Report on the percentage of logged-in users that have opted out from the new version of vector:\n",
    "- per test wiki\n",
    "- per user edit bucket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Approach\n",
    "\n",
    "Data comes from the [PrefUpdate](https://meta.wikimedia.org/wiki/Schema:PrefUpdate) table, which records preference changes for registered users. \n",
    "\n",
    "Due to the issue of accidental opt-ins/opt-outs, we will have to only consider the percentage of opt-outs after the date of deployment on each wiki as listed below:\n",
    "\n",
    "* **euwiki**: Jul 22nd (Default Skin Version: 2)\n",
    "* **frwikitionary**: (Default Skin Version: 2)\n",
    "* **officewiki**: (Default Skin Version: 2)\n",
    "* **ptwikiversity**: (Default Skin Version: 2)\n",
    "\n",
    "\n",
    "* **fawiki**: July 28 2020 (Default Skin Version: 1)\n",
    "* **frwiki**: Aug 5 2020(Default Skin Version: 1)\n",
    "* **hewiki**: July 28  2020(Default Skin Version: 1)\n",
    "\n",
    "\n",
    "PrefUpdateBugs:\n",
    "- The event.isDefault field is not recorded correctly for the VectorSkinVersion property. For the PrefUpdate instrumentation, the default value of the VectorSkinVersion preference is always null (not \"1\" or \"2\").See [T258971](https://phabricator.wikimedia.org/T258971). \n",
    "- PrefUpdate captures user preference modifications at registration - not isolated to just user initiated changes. https://phabricator.wikimedia.org/T260867#6403042. Caused by change merged on August 3rd https://phabricator.wikimedia.org/rEWMV30731c2c748a0ea34f798628fe7961b928572a23. This is likely why only the numbers for users with 0 edits are so high. \n",
    "\n",
    "As a result, we will use the event.value field to determine the opt-out rate. event.value = 1 \"legacy vector\", event.value = 2 \"modern vector\". The below table what the default is for each wiki: https://docs.google.com/spreadsheets/d/1svlRDsYjOhjXkIV1a-DqLYTcLIFuYoc6pHHz8_VmQcc/edit#gid=625518477 \n",
    "\n",
    "Note: Data reflects total number of opt-outs so this will include any users that might have opt-out multiple times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Opt-Out rate based on deployment dates on each wiki\n",
    "\n",
    "query<- \"SELECT\n",
    "    wiki,\n",
    "    event.bucketedUserEditCount AS user_edit_bucket,\n",
    "    Count(*) AS n_opt\n",
    "FROM event.prefupdate \n",
    "WHERE \n",
    "    event.property = 'VectorSkinVersion' AND\n",
    "    year = 2020 and month >= 07 AND\n",
    "    ((wiki = 'euwiki' AND event.savetimestamp >= '20200728' ) OR\n",
    "     (wiki = 'frwikitionary' AND event.savetimestamp >= '20200728') OR\n",
    "     (wiki = 'officewiki' AND event.savetimestamp >= '20200728') OR\n",
    "     (wiki = 'ptwikiversity' AND event.savetimestamp >= '20200728' ) OR\n",
    "     (wiki = 'fawiki' AND event.savetimestamp >= '20200728') OR\n",
    "     (wiki = 'frwiki' AND event.savetimestamp >= '20200728') OR\n",
    "     (wiki = 'hewiki' AND event.savetimestamp >='20200728' )) AND\n",
    "    event.value = '\"1\"'\n",
    "GROUP BY wiki, event.bucketedUserEditCount\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# User User Preferences Table Instead to Obtain Total Current Users with Preference Set\n",
    "\n",
    "References: https://github.com/wikimedia-research/Active-Editor-Skin-Statistics/blob/master/Re-run%20active%20editors%20skin%20statistics.ipynb\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pending resolution of PrefUpdate issues, we will use the user properties table to identify the total number of users with each vector skin preference set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query='''\n",
    "SELECT \n",
    "  up_value AS skin, \n",
    "  COUNT(*) AS users\n",
    "FROM user_properties\n",
    "WHERE up_user in ({users})\n",
    "AND up_property = \"skin\"\n",
    "GROUP BY up_value\n",
    "'''"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}