Retest_QA2_event.mediawiki_edit_attempt.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1b2fbb41-0589-4a5d-9646-20d910ae680f",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Re test mediawiki_edit_attempt\n",
    "Following up on the task Instrumentation Data-QA for event.mediawiki_edit_attempt [T320281](https://phabricator.wikimedia.org/T320281).   \n",
    "- The first phase of this testing was conducted in Nov 2022. Several issues were highlighted and discussed. I re tested the data in January 2023 and again in June 2023.  \n",
    "\n",
    "Status Update: \n",
    "On Desktop    \n",
    "- server side wikitext editor (including Save and Init) events are now being logged!!   \n",
    "- All client side (ready, loaded, firstchange, abort) events are logged \n",
    "\n",
    "On Mobile\n",
    "- some wiki editor Mobile events were being logged using `wt` and `mf` mediawiki extensions i.e. the same events were being logged twice and double counted. We had reported this in January 2023 to @DLynch/@phuedx  and this [patch](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikiEditor/+/895337/) was merged in March 2023, and later reverted in Apr (4/14/23) when the Editing team did big centralizations of editattemptstep logging. \n",
    "From @DLynch: the reason we removed that patch was that we thought we’d stopped WikiEditor and the MobileFrontend editor from appearing on the page at the same time with this [patch](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/MobileFrontend/+/904565) for [T334263](https://phabricator.wikimedia.org/T334263). This suggests that we didn’t catch every case that could cause that.\n",
    "\n",
    "\n",
    "After discussing with @DLynch and @cjming, @DLynch made https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikiEditor/+/928926 to reapply the original patch which was merged and deployed Jun 15, 2023. As of Jun 20, we have not retested to see if the revert was successful in removing duplicate events. \n",
    "\n",
    "## Action items from QA test\n",
    "- retest Mobile events on wikitext editor and check any discrepancies between mobile and desktop events on MEA and EAS tables. If they don't match, dig deeper and talk with Metrics Platform team and make aware to the implementation team in Product. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7a170a3-347f-478f-93bf-010a8f43406b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c95a9e12-6ac0-4d83-849e-25c3f952a8c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import wmfdata as wmf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da21da35-0889-4eef-955f-b4a47c76b876",
   "metadata": {},
   "source": [
    "### Compare between editattemptstep and mediawiki_edit_attempt\n",
    "#### 1. wikitext events  \n",
    "We will start with wikitext as all previous attempts to QA wikitext editor events failed since Metrics Platform was not programmed for it.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "64884188-fa53-4fd8-b2d7-bec005278d58",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SPARK_HOME: /usr/lib/spark3\n",
      "Using Hadoop client lib jars at 3.2.0, provided by Spark.\n",
      "PYSPARK_PYTHON=/opt/conda-analytics/bin/python3\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "23/06/07 18:12:32 WARN SparkConf: Note that spark.local.dir will be overridden by the value set by the cluster manager (via SPARK_LOCAL_DIRS in mesos/standalone/kubernetes and LOCAL_DIRS in YARN).\n",
      "23/06/07 18:12:32 WARN Utils: Service 'sparkDriver' could not bind on port 12000. Attempting port 12001.\n",
      "23/06/07 18:12:32 WARN Utils: Service 'sparkDriver' could not bind on port 12001. Attempting port 12002.\n",
      "23/06/07 18:12:32 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.\n",
      "23/06/07 18:12:32 WARN Utils: Service 'SparkUI' could not bind on port 4041. Attempting port 4042.\n",
      "23/06/07 18:12:42 WARN Utils: Service 'org.apache.spark.network.netty.NettyBlockTransferService' could not bind on port 13000. Attempting port 13001.\n",
      "23/06/07 18:12:42 WARN Utils: Service 'org.apache.spark.network.netty.NettyBlockTransferService' could not bind on port 13001. Attempting port 13002.\n",
      "23/06/07 18:12:43 WARN YarnSchedulerBackend$YarnSchedulerEndpoint: Attempted to request executors before the AM has registered!\n",
      "23/06/07 18:12:47 WARN SessionState: METASTORE_FILTER_HOOK will be ignored, since hive.security.authorization.manager is set to instance of HiveAuthorizerFactory.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "wt_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  event.platform AS platform,\n",
    "  count(1) AS wikitext_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.editor_interface = 'wikitext'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action, platform\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8630ab5e-3c5b-4df2-a2ee-9246e427abd7",
   "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>action</th>\n",
       "      <th>platform</th>\n",
       "      <th>wikitext_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ready</td>\n",
       "      <td>desktop</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>loaded</td>\n",
       "      <td>desktop</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>desktop</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>abort</td>\n",
       "      <td>desktop</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ready</td>\n",
       "      <td>phone</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>loaded</td>\n",
       "      <td>phone</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>init</td>\n",
       "      <td>phone</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>phone</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>abort</td>\n",
       "      <td>phone</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>phone</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>phone</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>phone</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveFailure</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         action platform  wikitext_events\n",
       "11        ready  desktop               92\n",
       "5        loaded  desktop               91\n",
       "10  firstChange  desktop               43\n",
       "6         abort  desktop               31\n",
       "8         ready    phone               13\n",
       "4        loaded    phone               13\n",
       "7          init    phone               13\n",
       "0   firstChange    phone                7\n",
       "12        abort    phone                6\n",
       "9    saveIntent    phone                3\n",
       "3   saveAttempt    phone                3\n",
       "1   saveSuccess    phone                2\n",
       "2   saveFailure    phone                1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wt_eas_query.sort_values(by=['wikitext_events','action'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d7774b21-10d6-4e01-bf5c-1be40df4fc79",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "wt_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS wikitext_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE custom_data[\"editor_interface\"].value = \"wikitext\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5d9fefe8-56cc-4406-a902-544a91cbe8c2",
   "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>name</th>\n",
       "      <th>wikitext_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.wt.ready</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.wt.loaded</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.wt.first_change</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.wt.abort</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.mf.save_failure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name  wikitext_events\n",
       "3          eas.wt.ready               93\n",
       "0         eas.wt.loaded               92\n",
       "6   eas.wt.first_change               43\n",
       "7          eas.wt.abort               33\n",
       "2          eas.mf.ready               14\n",
       "12        eas.mf.loaded               14\n",
       "4           eas.mf.init               13\n",
       "9   eas.mf.first_change                7\n",
       "11         eas.mf.abort                6\n",
       "5    eas.mf.save_intent                3\n",
       "10  eas.mf.save_attempt                3\n",
       "8   eas.mf.save_success                2\n",
       "1   eas.mf.save_failure                1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wt_mea_query.sort_values(by=['wikitext_events','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1164fe75-27da-421e-af95-28d07ba61ac6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "324"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Events in mediawiki_edit_attempt\n",
    "wt_mea_query.wikitext_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "2394b492-83d6-4105-b58e-99784c074541",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "318"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Events in editattemptstep\n",
    "wt_eas_query.wikitext_events.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4fe4157-6df8-466b-b68d-a604d0d212c5",
   "metadata": {},
   "source": [
    "The total number of wikitext events logged in editattemptstep and mediawiki_edit_attempt are close. This means the [patch](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikiEditor/+/902441/) submitted for WikiEditor extensions is working correctly and events are logging successfully in Metrics Platform.  \n",
    "\n",
    "In the Metrics Platform world, 'mf' is mobile frontend which means events from mobile device. And the events in mediawiki_edit_attempt are nearly the same as the number of events for each action in editattemptstep for Mobile.  \n",
    "We observe this for desktop i.e. 'wt' events in mediawiki_edit_attempt. \n",
    "\n",
    "There is a slight variation in ready and loaded action on mediawiki_edit_attempt and this could be related to wikieditor JS and the mobile JS both running at the same time (comment https://phabricator.wikimedia.org/T320281#8664796 ) \n",
    "A mobile event is getting logged as desktop event or both. However the proportion of such events is miniscule"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "203ad8fe-88b6-4880-ad75-a1fbe47a741d",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e068c1e4-c754-4267-a3bc-b86506dea6fd",
   "metadata": {},
   "source": [
    "### 2. VE events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b6606244-709d-4c7c-85cf-3e1c61dd5750",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "ve_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS ve_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.editor_interface = 'visualeditor'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "8bc919bf-a26b-4554-baf4-f0027e47da60",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "309"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ve_eas_query.ve_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e6d97530-7df3-421e-b947-f9a15437d952",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "ve_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS ve_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE custom_data[\"editor_interface\"].value = \"visualeditor\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "12b5edbd-11ff-49df-923e-9edbef4f412e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "309"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ve_mea_query.ve_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b3012996-3f39-4b8f-88aa-23f6f24f404c",
   "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>action</th>\n",
       "      <th>ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>init</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ready</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>loaded</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  ve_events\n",
       "6         init         59\n",
       "7        ready         54\n",
       "5       loaded         54\n",
       "4  firstChange         33\n",
       "2   saveIntent         27\n",
       "3  saveSuccess         26\n",
       "0  saveAttempt         26\n",
       "1        abort         25"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ve_eas_query.sort_values(by=['ve_events','action'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "adfa3407-2540-4940-8024-4228ccd220e3",
   "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>name</th>\n",
       "      <th>ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>eas.dt.ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.dt.loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.dt.init</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eas.dt.save_success</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>eas.dt.save_intent</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.dt.save_attempt</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>eas.dt.first_change</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>eas.dt.abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name  ve_events\n",
       "17          eas.ve.init         48\n",
       "21         eas.ve.ready         44\n",
       "23        eas.ve.loaded         44\n",
       "3   eas.ve.first_change         25\n",
       "19   eas.ve.save_intent         21\n",
       "8          eas.ve.abort         21\n",
       "14  eas.ve.save_success         20\n",
       "22  eas.ve.save_attempt         20\n",
       "4           eas.mf.init          6\n",
       "10  eas.mf.first_change          6\n",
       "0          eas.mf.ready          5\n",
       "20        eas.mf.loaded          5\n",
       "18         eas.dt.ready          5\n",
       "1         eas.dt.loaded          5\n",
       "7           eas.dt.init          5\n",
       "6   eas.mf.save_success          4\n",
       "5    eas.mf.save_intent          4\n",
       "13  eas.mf.save_attempt          4\n",
       "16         eas.mf.abort          2\n",
       "11  eas.dt.save_success          2\n",
       "15   eas.dt.save_intent          2\n",
       "2   eas.dt.save_attempt          2\n",
       "12  eas.dt.first_change          2\n",
       "9          eas.dt.abort          2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ve_mea_query.sort_values(by=['ve_events','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14eb87d6-e680-4286-bb08-a2090c4d805e",
   "metadata": {},
   "source": [
    "The Visual Editor interface includes VE on mobile and desktop, as well as discussion tools events. In mediawiki_edit_attempt these are split into 'mf' for mobile frontend, 've' for desktop and 'dt' for discussion tools based on the mediawiki extension. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f81bafe6-4575-4ed3-bc57-7525a37f0c69",
   "metadata": {},
   "source": [
    "All Visual Editor events are being logged successfully"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6be88041-2510-47ab-9853-e73885cf2004",
   "metadata": {},
   "source": [
    "### 3. Discussion Tools events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c0111dfa-8da0-4dc4-b7b9-24175156ea73",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "dt_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS dt_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.editor_interface = 'visualeditor'\n",
    "  AND event.integration='discussiontools'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "750fea92-102d-48ae-9f8e-19ef44f14076",
   "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>action</th>\n",
       "      <th>dt_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>init</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  dt_events\n",
       "7        ready          5\n",
       "5       loaded          5\n",
       "6         init          5\n",
       "3  saveSuccess          2\n",
       "2   saveIntent          2\n",
       "0  saveAttempt          2\n",
       "4  firstChange          2\n",
       "1        abort          2"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dt_eas_query.sort_values(by=['dt_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "228d504b-8922-425f-a4db-7e3434345e65",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "dt_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS dt_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE custom_data[\"editor_interface\"].value = \"visualeditor\"\n",
    "  AND name like 'eas.dt%'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "dfa6bdd7-5610-43a9-9573-1b8696f703f1",
   "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>name</th>\n",
       "      <th>dt_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.dt.ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.dt.loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.dt.init</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.dt.save_success</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.dt.save_intent</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.dt.save_attempt</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.dt.first_change</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.dt.abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  dt_events\n",
       "7         eas.dt.ready          5\n",
       "0        eas.dt.loaded          5\n",
       "2          eas.dt.init          5\n",
       "4  eas.dt.save_success          2\n",
       "6   eas.dt.save_intent          2\n",
       "1  eas.dt.save_attempt          2\n",
       "5  eas.dt.first_change          2\n",
       "3         eas.dt.abort          2"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dt_mea_query.sort_values(by=['dt_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76b8bdc4-18f4-46f2-b29d-2285168f4425",
   "metadata": {},
   "source": [
    "Discussion Tools events are logged successfully "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "623bea86-e99e-46ce-9ba3-e859fb3a079e",
   "metadata": {},
   "source": [
    "### 4. Mobile events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f4896afe-8b62-4992-97bb-4343d4f92dbb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS mobile_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'phone'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "daf6e1e0-e5f6-4677-bd94-49a29d962ac0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS mobile_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='mobile_browser'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "bf1820c0-715f-456e-9d7a-77e5914b8366",
   "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>name</th>\n",
       "      <th>mobile_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.save_failure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  mobile_events\n",
       "1         eas.mf.ready             19\n",
       "8        eas.mf.loaded             19\n",
       "2          eas.mf.init             19\n",
       "5  eas.mf.first_change             13\n",
       "7         eas.mf.abort              8\n",
       "3   eas.mf.save_intent              7\n",
       "6  eas.mf.save_attempt              7\n",
       "4  eas.mf.save_success              6\n",
       "0  eas.mf.save_failure              1"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_mea_query.sort_values(by=['mobile_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6300a3a5-81f1-4121-bfe5-c3a0ba5e6685",
   "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>action</th>\n",
       "      <th>mobile_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>init</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ready</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>loaded</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveFailure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  mobile_events\n",
       "7         init             19\n",
       "8        ready             18\n",
       "6       loaded             18\n",
       "5  firstChange             13\n",
       "1        abort              8\n",
       "2   saveIntent              7\n",
       "0  saveAttempt              7\n",
       "4  saveSuccess              6\n",
       "3  saveFailure              1"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_eas_query.sort_values(by=['mobile_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "11773650-0318-440e-9793-64bdc9b0d68a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_eas_query.mobile_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "52cd695c-4546-4d41-9910-83a21acec842",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_mea_query.mobile_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccf34c79-8915-4d01-8a53-b7c4cd3f1fcf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9e6527cf-0964-4c1f-9311-607605a672bd",
   "metadata": {},
   "source": [
    "Mobile events on `mediawiki_edit_attempt` are slightly higher. This is due to wiki editor events that are logged twice - once from `wt` extension and once from `mf` extension. This is an issue on both instrument platforms and will need to be fixed.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7573907c-0ab2-4ded-ac18-a77eb2dc7605",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "458f8867-4fff-4aa2-a40c-17a27c4b53e1",
   "metadata": {},
   "source": [
    "### 4.1 Mobile wiki editor events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "aafe8bb1-0aec-42cb-8b87-b9a951a93403",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_wt_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS mobile_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'phone'\n",
    "  AND event.editor_interface='wikitext'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "aa19ce91-a365-4515-a47a-555720c4598a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_wt_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS mobile_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='mobile_browser'\n",
    "  AND custom_data[\"editor_interface\"].value = \"wikitext\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "6f126a1b-830b-4f8d-8d08-16994128a7c2",
   "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>name</th>\n",
       "      <th>mobile_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.save_failure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  mobile_events\n",
       "1         eas.mf.ready             14\n",
       "8        eas.mf.loaded             14\n",
       "2          eas.mf.init             13\n",
       "5  eas.mf.first_change              7\n",
       "7         eas.mf.abort              6\n",
       "3   eas.mf.save_intent              3\n",
       "6  eas.mf.save_attempt              3\n",
       "4  eas.mf.save_success              2\n",
       "0  eas.mf.save_failure              1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_wt_mea_query.sort_values(by=['mobile_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "e9a6cd85-841d-449a-b0e5-d7bbfa294093",
   "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>action</th>\n",
       "      <th>mobile_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ready</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>loaded</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>init</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveFailure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  mobile_events\n",
       "8        ready             13\n",
       "6       loaded             13\n",
       "7         init             13\n",
       "5  firstChange              7\n",
       "1        abort              6\n",
       "2   saveIntent              3\n",
       "0  saveAttempt              3\n",
       "4  saveSuccess              2\n",
       "3  saveFailure              1"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_wt_eas_query.sort_values(by=['mobile_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "b919f2e6-41da-4790-a207-9708db3f3d8a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "63"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_wt_mea_query.mobile_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "fcf93450-1df8-4b51-9add-908a114efdc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "61"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_wt_eas_query.mobile_events.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0f3ec27-35bd-40a5-bc2f-a9eca2179b08",
   "metadata": {},
   "source": [
    "We can see that on Mobile, wikitext editor events are slightly higher on Metrics platform table `mediawiki_edit_attempt` than on legacy `editattemptstep`.   \n",
    "\n",
    "Unlike the previous round of QA where we saw the same Mobile events being logged twice using `wt` and `mf` mediawiki extensions, this time we see them being logged only using the 'mf' extension. However Metrics platform is logging an additional event with a different timestamp (and sometimes a different timing value) for ready and loaded actions in a small proportion of editing sessions. \n",
    "We are not entirely sure if Metrics Platform is recording an extra event that shouldnt be logged or if legacy is not recording an event. In either case, if one editing session is programmed to have only have one ready or loaded event then this is a discrepancy to that design."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "280669f2-bb32-4294-8626-58ebb487c10b",
   "metadata": {},
   "source": [
    "#### Further investigation into this "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "deab7b07-9fae-4210-b963-f1aae20e8795",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_wt_mea_query1= wmf.spark.run('''\n",
    "SELECT \n",
    "  dt, name, month, day, hour, \n",
    "  custom_data.editing_session_id, \n",
    "  custom_data.ready_timing, \n",
    "  custom_data.editor_interface,\n",
    "  meta.request_id,\n",
    "  performer.id\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='mobile_browser'\n",
    "  AND custom_data[\"editor_interface\"].value = \"wikitext\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND day=4\n",
    "  AND hour=2\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  AND name='eas.mf.ready'\n",
    "  ORDER BY dt\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e685140f-a349-4f65-b16f-d7115f2ddb75",
   "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>dt</th>\n",
       "      <th>name</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>editing_session_id</th>\n",
       "      <th>ready_timing</th>\n",
       "      <th>editor_interface</th>\n",
       "      <th>request_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-04T02:05:14.959Z</td>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>(number, 3856)</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>aa57564e-faa8-47a3-ad3c-c297a4411569</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-04T02:05:15.645Z</td>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>(number, 700)</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>988640c3-504e-4d4e-be2d-c726688d70ed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dt          name  month  day  hour  \\\n",
       "0  2023-06-04T02:05:14.959Z  eas.mf.ready      6    4     2   \n",
       "1  2023-06-04T02:05:15.645Z  eas.mf.ready      6    4     2   \n",
       "\n",
       "                           editing_session_id    ready_timing  \\\n",
       "0  (string, 147542f4c8ffd4302236e3b4ddc65220)  (number, 3856)   \n",
       "1  (string, 147542f4c8ffd4302236e3b4ddc65220)   (number, 700)   \n",
       "\n",
       "     editor_interface                            request_id  id  \n",
       "0  (string, wikitext)  aa57564e-faa8-47a3-ad3c-c297a4411569   0  \n",
       "1  (string, wikitext)  988640c3-504e-4d4e-be2d-c726688d70ed   0  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_wt_mea_query1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "e811b2f8-f63f-43a2-bbe9-913156782b5e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_wt_eas_query1=wmf.spark.run('''\n",
    "SELECT \n",
    "  dt, event.action, month, day, hour, \n",
    "  event.editing_session_id, \n",
    "  event.ready_timing,\n",
    "  event.user_id\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'phone'\n",
    "  AND event.editor_interface='wikitext'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND day=4\n",
    "  AND hour=2\n",
    "  AND wiki = 'testwiki' \n",
    "  AND event.action='ready'\n",
    "  ORDER BY dt\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "98ee1439-bc38-4c90-9f3c-736bacd22f0d",
   "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>dt</th>\n",
       "      <th>action</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>editing_session_id</th>\n",
       "      <th>ready_timing</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-04T02:05:23.339Z</td>\n",
       "      <td>ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>700</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dt action  month  day  hour  \\\n",
       "0  2023-06-04T02:05:23.339Z  ready      6    4     2   \n",
       "\n",
       "                 editing_session_id  ready_timing  user_id  \n",
       "0  147542f4c8ffd4302236e3b4ddc65220           700        0  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_wt_eas_query1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "c8c07a1e-fcb1-4aa5-a189-ba08df1534a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# On mediawiki_edit_attempt\n",
    "investigate_mf_wt_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  dt, name, month, day, hour, \n",
    "  custom_data.editing_session_id, \n",
    "  custom_data.ready_timing, \n",
    "  custom_data.editor_interface,\n",
    "  agent.client_platform_family,\n",
    "  meta.request_id,\n",
    "  performer.id\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE custom_data[\"editing_session_id\"].value = \"147542f4c8ffd4302236e3b4ddc65220\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  ORDER BY dt\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "67a1e93a-affd-451f-aeaa-5950ecdcedce",
   "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>dt</th>\n",
       "      <th>name</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>editing_session_id</th>\n",
       "      <th>ready_timing</th>\n",
       "      <th>editor_interface</th>\n",
       "      <th>client_platform_family</th>\n",
       "      <th>request_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-04T02:05:14.945Z</td>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>None</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>mobile_browser</td>\n",
       "      <td>14ad5837-2578-4446-81d4-5e93ab824065</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-04T02:05:14.959Z</td>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>(number, 3856)</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>mobile_browser</td>\n",
       "      <td>aa57564e-faa8-47a3-ad3c-c297a4411569</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-04T02:05:14.978Z</td>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>None</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>mobile_browser</td>\n",
       "      <td>83fc7f1b-ad28-430d-815d-1c1378b3ba2d</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-06-04T02:05:15.645Z</td>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>None</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>mobile_browser</td>\n",
       "      <td>07b9974f-3568-47d7-963b-36f4d1b02b84</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-06-04T02:05:15.645Z</td>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>(string, 147542f4c8ffd4302236e3b4ddc65220)</td>\n",
       "      <td>(number, 700)</td>\n",
       "      <td>(string, wikitext)</td>\n",
       "      <td>mobile_browser</td>\n",
       "      <td>988640c3-504e-4d4e-be2d-c726688d70ed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dt           name  month  day  hour  \\\n",
       "0  2023-06-04T02:05:14.945Z    eas.mf.init      6    4     2   \n",
       "1  2023-06-04T02:05:14.959Z   eas.mf.ready      6    4     2   \n",
       "2  2023-06-04T02:05:14.978Z  eas.mf.loaded      6    4     2   \n",
       "3  2023-06-04T02:05:15.645Z  eas.mf.loaded      6    4     2   \n",
       "4  2023-06-04T02:05:15.645Z   eas.mf.ready      6    4     2   \n",
       "\n",
       "                           editing_session_id    ready_timing  \\\n",
       "0  (string, 147542f4c8ffd4302236e3b4ddc65220)            None   \n",
       "1  (string, 147542f4c8ffd4302236e3b4ddc65220)  (number, 3856)   \n",
       "2  (string, 147542f4c8ffd4302236e3b4ddc65220)            None   \n",
       "3  (string, 147542f4c8ffd4302236e3b4ddc65220)            None   \n",
       "4  (string, 147542f4c8ffd4302236e3b4ddc65220)   (number, 700)   \n",
       "\n",
       "     editor_interface client_platform_family  \\\n",
       "0  (string, wikitext)         mobile_browser   \n",
       "1  (string, wikitext)         mobile_browser   \n",
       "2  (string, wikitext)         mobile_browser   \n",
       "3  (string, wikitext)         mobile_browser   \n",
       "4  (string, wikitext)         mobile_browser   \n",
       "\n",
       "                             request_id  id  \n",
       "0  14ad5837-2578-4446-81d4-5e93ab824065   0  \n",
       "1  aa57564e-faa8-47a3-ad3c-c297a4411569   0  \n",
       "2  83fc7f1b-ad28-430d-815d-1c1378b3ba2d   0  \n",
       "3  07b9974f-3568-47d7-963b-36f4d1b02b84   0  \n",
       "4  988640c3-504e-4d4e-be2d-c726688d70ed   0  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "investigate_mf_wt_mea_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "db559a61-7e17-4090-8e89-d1d5511f72ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# On editattemptstep\n",
    "\n",
    "investigate_mf_wt_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  dt, event.action, month, day, hour, \n",
    "  event.editing_session_id, \n",
    "  event.ready_timing,\n",
    "  event.platform,\n",
    "  event.editor_interface,\n",
    "  event.user_id\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.editing_session_id = \"147542f4c8ffd4302236e3b4ddc65220\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  ORDER BY dt\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "eb5e42d1-c2c9-4ffd-bdfc-d297c29f0bb5",
   "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>dt</th>\n",
       "      <th>action</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>editing_session_id</th>\n",
       "      <th>ready_timing</th>\n",
       "      <th>platform</th>\n",
       "      <th>editor_interface</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-04T02:05:12Z</td>\n",
       "      <td>init</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>wikitext</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-04T02:05:23.337Z</td>\n",
       "      <td>init</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>phone</td>\n",
       "      <td>wikitext</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-04T02:05:23.338Z</td>\n",
       "      <td>ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>3856.0</td>\n",
       "      <td>desktop</td>\n",
       "      <td>wikitext</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-06-04T02:05:23.338Z</td>\n",
       "      <td>loaded</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>wikitext</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-06-04T02:05:23.339Z</td>\n",
       "      <td>ready</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>700.0</td>\n",
       "      <td>phone</td>\n",
       "      <td>wikitext</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2023-06-04T02:05:23.342Z</td>\n",
       "      <td>loaded</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>147542f4c8ffd4302236e3b4ddc65220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>phone</td>\n",
       "      <td>wikitext</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dt  action  month  day  hour  \\\n",
       "0      2023-06-04T02:05:12Z    init      6    4     2   \n",
       "1  2023-06-04T02:05:23.337Z    init      6    4     2   \n",
       "2  2023-06-04T02:05:23.338Z   ready      6    4     2   \n",
       "3  2023-06-04T02:05:23.338Z  loaded      6    4     2   \n",
       "4  2023-06-04T02:05:23.339Z   ready      6    4     2   \n",
       "5  2023-06-04T02:05:23.342Z  loaded      6    4     2   \n",
       "\n",
       "                 editing_session_id  ready_timing platform editor_interface  \\\n",
       "0  147542f4c8ffd4302236e3b4ddc65220           NaN  desktop         wikitext   \n",
       "1  147542f4c8ffd4302236e3b4ddc65220           NaN    phone         wikitext   \n",
       "2  147542f4c8ffd4302236e3b4ddc65220        3856.0  desktop         wikitext   \n",
       "3  147542f4c8ffd4302236e3b4ddc65220           NaN  desktop         wikitext   \n",
       "4  147542f4c8ffd4302236e3b4ddc65220         700.0    phone         wikitext   \n",
       "5  147542f4c8ffd4302236e3b4ddc65220           NaN    phone         wikitext   \n",
       "\n",
       "   user_id  \n",
       "0        0  \n",
       "1        0  \n",
       "2        0  \n",
       "3        0  \n",
       "4        0  \n",
       "5        0  "
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "investigate_mf_wt_eas_query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea6120d8-2283-4a21-9e2c-6dda15e7692e",
   "metadata": {},
   "source": [
    "**Conclusion:** A small proportion of mobile wikitext editing sessions log an additional (ready and loaded) event. This behavior is different than legacy editattemptstep and should be kept in mind during the implementation of Metrics platform schemas. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "404a7b96-27c2-4a7a-8889-09a4e86bd8e0",
   "metadata": {},
   "source": [
    "Update: From Summary above, After discussing with @DLynch and @cjming, @DLynch made https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikiEditor/+/928926 to reapply the original patch which was merged and deployed Jun 15, 2023. As of Jun 20, we have not retested to see if the revert was successful in removing duplicate events."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bae3a599-c0c5-4c94-bc91-d6507b97c9a8",
   "metadata": {},
   "source": [
    "### 4.2 Mobile VE events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "8e4b796b-04d9-4f1f-8750-b9a0f960a771",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_ve_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS mobile_ve_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'phone'\n",
    "  AND event.editor_interface='visualeditor'\n",
    "  AND event.integration='page' -- restricting to non discussion tools events only\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "b078a12f-78a4-45a8-b459-e8a599412ccb",
   "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>action</th>\n",
       "      <th>mobile_ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>init</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  mobile_ve_events\n",
       "6         init                 6\n",
       "4  firstChange                 6\n",
       "7        ready                 5\n",
       "5       loaded                 5\n",
       "3  saveSuccess                 4\n",
       "2   saveIntent                 4\n",
       "0  saveAttempt                 4\n",
       "1        abort                 2"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_ve_eas_query.sort_values(by=['mobile_ve_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "032a0743-62e6-473e-86b5-155efba71be3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mf_ve_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS mobile_ve_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='mobile_browser'\n",
    "  AND custom_data[\"editor_interface\"].value = \"visualeditor\"\n",
    "  --AND name like 'eas.ve%'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "21b5a668-c094-41b1-ab70-cb55c5a9e5d0",
   "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>name</th>\n",
       "      <th>mobile_ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  mobile_ve_events\n",
       "1          eas.mf.init                 6\n",
       "4  eas.mf.first_change                 6\n",
       "0         eas.mf.ready                 5\n",
       "7        eas.mf.loaded                 5\n",
       "3  eas.mf.save_success                 4\n",
       "2   eas.mf.save_intent                 4\n",
       "5  eas.mf.save_attempt                 4\n",
       "6         eas.mf.abort                 2"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_ve_mea_query.sort_values(by=['mobile_ve_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "c07f2054-6556-4186-b8f5-07f7170482e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_ve_eas_query.mobile_ve_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "f33d291a-937c-4649-87a8-d1abe7997eda",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf_ve_mea_query.mobile_ve_events.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e232595c-996f-4dae-9845-74cff43bf117",
   "metadata": {},
   "source": [
    "On Mobile, Visual Editor events between the two tables are consistent (this comparison does not include discussion tool events)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32e755fe-902f-4f62-8c63-4fe0e9618c46",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "51f9b0d2-d6d5-4948-a6f8-023f04306d78",
   "metadata": {},
   "source": [
    "### 5. Desktop events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "228cb789-74f8-4577-ac53-4e6ca23c76a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "desktop_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS desktop_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'desktop'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "73461c38-c952-4211-b0ab-8b5ed6813e3e",
   "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>action</th>\n",
       "      <th>desktop_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ready</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>loaded</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>init</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  desktop_events\n",
       "7        ready             165\n",
       "5       loaded             164\n",
       "4  firstChange              91\n",
       "6         init              78\n",
       "1        abort              57\n",
       "2   saveIntent              43\n",
       "3  saveSuccess              39\n",
       "0  saveAttempt              39"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_eas_query.sort_values(by=['desktop_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "819ee647-15da-4390-b501-ce179a29c6ca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "3a82c78b-9f9a-4421-9b28-082ca68984ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "desktop_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS desktop_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='desktop_browser'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "4c187787-13ff-4973-bba9-7181aeeb7b3f",
   "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>name</th>\n",
       "      <th>desktop_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.wt.ready</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.wt.loaded</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.wt.first_change</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.wt.abort</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.dt.init</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>eas.dt.ready</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.dt.loaded</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eas.dt.save_success</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>eas.dt.save_intent</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.dt.save_attempt</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eas.dt.first_change</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>eas.dt.abort</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name  desktop_events\n",
       "3          eas.wt.ready              93\n",
       "0         eas.wt.loaded              92\n",
       "14          eas.ve.init              56\n",
       "17         eas.ve.ready              52\n",
       "19        eas.ve.loaded              52\n",
       "5   eas.wt.first_change              43\n",
       "6          eas.wt.abort              33\n",
       "4   eas.ve.first_change              31\n",
       "16   eas.ve.save_intent              26\n",
       "8          eas.ve.abort              23\n",
       "12  eas.ve.save_success              22\n",
       "18  eas.ve.save_attempt              22\n",
       "7           eas.dt.init              22\n",
       "15         eas.dt.ready              21\n",
       "1         eas.dt.loaded              21\n",
       "10  eas.dt.save_success              17\n",
       "13   eas.dt.save_intent              17\n",
       "2   eas.dt.save_attempt              17\n",
       "11  eas.dt.first_change              17\n",
       "9          eas.dt.abort               3"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_mea_query.sort_values(by=['desktop_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "ee2d31a6-885d-44ed-b53a-8a64d5e23668",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "680"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_mea_query.desktop_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "a5870a84-7cb5-4eb9-be29-df71190df27b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "676"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_eas_query.desktop_events.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c32545d-0a69-466a-a53c-f9d3fa0336d1",
   "metadata": {},
   "source": [
    "Once again, similar to Mobile, the number of Desktop events on mediawiki_edit_attempt is slightly higher than editattempstep. Lets investigate further..  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a62938f1-0e26-405d-af49-25389cb50ba3",
   "metadata": {},
   "source": [
    "### 5.1 Desktop VE events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "1e1c9d0a-f956-434e-b593-a755b1043f04",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "desktop_ve_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS desktop_ve_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'desktop'\n",
    "  AND event.editor_interface='visualeditor'\n",
    "  AND event.integration='page' -- restricting to non discussion tools events only\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "6e45f7f1-1034-493f-a30d-66f4760eb50a",
   "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>action</th>\n",
       "      <th>desktop_ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>init</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ready</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>loaded</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abort</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  desktop_ve_events\n",
       "6         init                 49\n",
       "7        ready                 45\n",
       "5       loaded                 45\n",
       "4  firstChange                 26\n",
       "1        abort                 22\n",
       "2   saveIntent                 21\n",
       "3  saveSuccess                 20\n",
       "0  saveAttempt                 20"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_ve_eas_query.sort_values(by=['desktop_ve_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "bce775f6-2544-40a7-9681-a789b1fb33d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "248"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_ve_eas_query.desktop_ve_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "c4056f0c-4163-4f0f-835b-ffc786df1efc",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "desktop_ve_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS desktop_ve_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='desktop_browser'\n",
    "  AND custom_data[\"editor_interface\"].value = \"visualeditor\"\n",
    "  AND name like 'eas.ve%'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "3c0acce7-ba0f-4048-86ba-d47d0969242f",
   "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>name</th>\n",
       "      <th>desktop_ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  desktop_ve_events\n",
       "3          eas.ve.init                 49\n",
       "5         eas.ve.ready                 45\n",
       "7        eas.ve.loaded                 45\n",
       "0  eas.ve.first_change                 26\n",
       "1         eas.ve.abort                 22\n",
       "4   eas.ve.save_intent                 21\n",
       "2  eas.ve.save_success                 20\n",
       "6  eas.ve.save_attempt                 20"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_ve_mea_query.sort_values(by=['desktop_ve_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "505a0928-ca6c-4389-b3a1-0cefa60fefc5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "248"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_ve_mea_query.desktop_ve_events.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de1193ce-f78f-48af-9d5f-5c8400d9e78c",
   "metadata": {},
   "source": [
    "Visual Editor events on desktop are the same in both instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a233b586-129e-4ca6-9051-5ef26e8d9c1b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "cbe3dec4-5e15-483b-936b-6b659be093dd",
   "metadata": {},
   "source": [
    "### 5.2 Desktop Wikitext events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "ae28fbb0-2fef-430f-917b-658ea327de41",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "desktop_wt_eas_query=wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, \n",
    "  count(1) AS desktop_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.platform = 'desktop'\n",
    "  AND event.editor_interface='wikitext'\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "0dffcb47-e728-4fd1-9a33-b047678fb1a1",
   "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>action</th>\n",
       "      <th>desktop_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ready</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>loaded</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>abort</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  desktop_events\n",
       "3        ready              92\n",
       "2       loaded              91\n",
       "1  firstChange              43\n",
       "0        abort              31"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_wt_eas_query.sort_values(by=['desktop_events','action'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "cd9854e7-8f54-4948-9289-e836780ac056",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "257"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_wt_eas_query.desktop_events.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "3bb9aa41-79fb-4449-8eab-40ec1ccaca45",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "desktop_wt_mea_query= wmf.spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, \n",
    "  count(1) AS desktop_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE agent.client_platform_family='desktop_browser'\n",
    "  AND custom_data[\"editor_interface\"].value = \"wikitext\"\n",
    "  AND year = 2023\n",
    "  AND month= 6\n",
    "  AND mediawiki.database = \"testwiki\"\n",
    "  GROUP BY name\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "e6af2da6-703e-41e3-a2e4-7eec1d613479",
   "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>name</th>\n",
       "      <th>desktop_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.wt.ready</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.wt.loaded</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.wt.first_change</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.wt.abort</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  desktop_events\n",
       "1         eas.wt.ready              93\n",
       "0        eas.wt.loaded              92\n",
       "2  eas.wt.first_change              43\n",
       "3         eas.wt.abort              33"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_wt_mea_query.sort_values(by=['desktop_events','name'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "e9189ac3-d95a-4403-885f-4a8236b37add",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "261"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desktop_wt_mea_query.desktop_events.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "638267cf-14df-42ab-9e61-8695d1c9a800",
   "metadata": {},
   "source": [
    "This observation is similar to the one for mobile, where the number of wikitext events is slightly higher in mediawiki_edit_attempt.\n",
    "On Desktop we see that a small proportion of wikitext editing sessions log an additional ready, loaded and abort event. This is different than legacy editattemptstep, and should be kept in mind during the implementation of Metrics Platform. "
   ]
  }
 ],
 "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}