QA_event.mediawiki_edit_attempt.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6c138062-0294-44d2-b198-26bdfd606188",
   "metadata": {},
   "source": [
    "# Data-QA for event.mediawiki_edit_attempt\n",
    "Nov 1, 2022 [task T320281](https://phabricator.wikimedia.org/T320281)  \n",
    "The purpose of this pre-deployment QA is to confirm that events are logging as expected by the Metrics Platform. This instrumentation is deployed on [Test Wiki](https://test.wikipedia.org/wiki/Main_Page)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9560b222-8a3e-43fa-89e2-7907177e61bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "from wmfdata import hive, spark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "563ec1a5-4ea7-4ce0-be35-66c72ace9a01",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Goals\n",
    "Verify that the instrumentation for tracking edit events is working successfully\n",
    "\n",
    "###  Expectations\n",
    "- The metrics should be comparable between event.editattemptstep and event.mediawiki_edit_attempt  \n",
    "- Sampling is 100% which means all edit attempt events should be logged into the table  \n",
    "- edit_attempt is a funnel schema. The number of events should be in this order:  \n",
    " init > ready Or loaded > firstChange or abort > saveIntent > saveAttempt > saveSuccess Or saveFailure\n",
    "\n",
    "### Caveats \n",
    "- Ad blocker turned On\n",
    "- Wikis and namespaces excluded from event logging : tba\n",
    "- To do the QA, I had to use Superset for looking at sample data and HUE for the table structure, because Superset does not display `struct` columns well. Until Datahub provides the option for viewing sample data, I don't think we can use it as an effective tool for referencing datasets frequently.  \n",
    "\n",
    "### Reference\n",
    "- [Datahub](https://datahub.wikimedia.org/dataset/urn:li:dataset:(urn:li:dataPlatform:hive,event.mediawiki_edit_attempt,PROD)/Schema?is_lineage_mode=false&schemaFilter=)\n",
    "\n",
    "### Observations\n",
    "- All events are assigned a browser, os, device family   \n",
    "- All discussiontools init events are assigned a section or page init_type  \n",
    "- All events for a session id are in the correct funnel order  (loaded and ready events have the same count)\n",
    "- All logged in users have a user_id  \n",
    "### Issues\n",
    "- Some logged-in users dont have the following fields populated - \n",
    "    - user_name (performer.name)\n",
    "    - registration_dt (performer.registration_dt)\n",
    "    - edit_count_bucket (performer.edit_count_bucket)\n",
    " \n",
    "\n",
    "### Questions\n",
    "- Detailed Documentation on the field definitions. A: will reside on Datahub\n",
    "- where is the event.action captured? A: in the \"name\" field\n",
    "- this will be updated from [gdoc](https://docs.google.com/document/d/1rJwTbbuOWuAjNyxlBLXWXVuRzO4f4vUjuEwJHw4ihsE/edit)\n",
    "\n",
    "### QA Checks\n",
    "- Measure the # of events for each step of an edit attempt action     \n",
    "- Create new test data and track events created by my user id (pending)  \n",
    "- Compare with editattemptstep (pending)   \n",
    "- Check a few sample individual sessions, ordered by timestamp  \n",
    "- Check overall data\n",
    "    - meta data  \n",
    "    - performer data  \n",
    "    - user agent (map) data  \n",
    "    - custom (bespoke) data - here you will find the properties of an event like timing, actions involved in an attempt to edit a page etc. which are specific to the instrumentation  \n",
    "        - check VE events  \n",
    "        - check discussion tools events  \n",
    "        - check wikitext events  \n",
    "  \n",
    "        \n",
    "        \n",
    "\n",
    "\n",
    "### Notes\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea12fa40-f957-4761-b9b0-c68e11ce85f7",
   "metadata": {},
   "source": [
    "### Check the data in meta, performer, user agent, normalized host "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "7c325a1a-3dd8-4f0b-a746-925da6113820",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "query1=spark.run('''\n",
    "SELECT \n",
    "  date_format(dt, 'yyyy-MM-dd') AS date,\n",
    "  meta.domain AS wiki,\n",
    "  meta.id AS meta_id,\n",
    "  meta.request_id as meta_req_id, \n",
    "  normalized_host.project as wiki_db, \n",
    "  normalized_host.project_family, \n",
    "  IF(performer.is_logged_in, \"logged-in\", \"anonymous\") AS user_log,\n",
    "  performer.id AS user_id,\n",
    "  performer.name AS user_name,\n",
    "  performer.session_id,\n",
    "  performer.registration_dt,\n",
    "  performer.edit_count,\n",
    "  performer.edit_count_bucket,\n",
    "  performer.groups,\n",
    "  user_agent_map['browser_family'] AS browser_family,  \n",
    "  user_agent_map['browser_major'] AS browser_major,\n",
    "  user_agent_map['os_family'] AS os_family,\n",
    "  user_agent_map['os_minor'] AS os_minor ,\n",
    "  user_agent_map['os_major'] AS os_major,\n",
    "  user_agent_map['wmf_app_version'] AS wmf_app_version,\n",
    "  user_agent_map['device_family'] AS device_family\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  ORDER BY date\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "834bf2a5-9941-49d9-a8f0-dd1fa09ae674",
   "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>date</th>\n",
       "      <th>wiki</th>\n",
       "      <th>meta_id</th>\n",
       "      <th>meta_req_id</th>\n",
       "      <th>wiki_db</th>\n",
       "      <th>project_family</th>\n",
       "      <th>user_log</th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_name</th>\n",
       "      <th>session_id</th>\n",
       "      <th>...</th>\n",
       "      <th>edit_count</th>\n",
       "      <th>edit_count_bucket</th>\n",
       "      <th>groups</th>\n",
       "      <th>browser_family</th>\n",
       "      <th>browser_major</th>\n",
       "      <th>os_family</th>\n",
       "      <th>os_minor</th>\n",
       "      <th>os_major</th>\n",
       "      <th>wmf_app_version</th>\n",
       "      <th>device_family</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2729</th>\n",
       "      <td>2022-11-03</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>0d2b29a2-2791-4b97-8a56-db77a6799b0d</td>\n",
       "      <td>3595f8d1-d0fb-4550-bea5-00ad42a19441</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>logged-in</td>\n",
       "      <td>54035</td>\n",
       "      <td>None</td>\n",
       "      <td>11b63f79445c7d450baa</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>105</td>\n",
       "      <td>Mac OS X</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>-</td>\n",
       "      <td>Mac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2730</th>\n",
       "      <td>2022-11-03</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>679b1558-88cc-4545-9c7b-c444b4d1fefc</td>\n",
       "      <td>02dea918-af89-4afc-9959-2a9aa8967781</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>logged-in</td>\n",
       "      <td>54035</td>\n",
       "      <td>None</td>\n",
       "      <td>11b63f79445c7d450baa</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>105</td>\n",
       "      <td>Mac OS X</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>-</td>\n",
       "      <td>Mac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2731</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>fa1350b5-dd8b-4785-a625-e742e08a6585</td>\n",
       "      <td>bb7562e5-002a-4f64-9d2d-fc1de0f5a8d4</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>anonymous</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>92fed36697ed199a4bad</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>81</td>\n",
       "      <td>Android</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>-</td>\n",
       "      <td>BM111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2732</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>9e9723ac-6f07-4f03-8976-0edc2206dbd6</td>\n",
       "      <td>95a88008-5bf7-4e32-8489-9e79b1670c8d</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>anonymous</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>92fed36697ed199a4bad</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>81</td>\n",
       "      <td>Android</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>-</td>\n",
       "      <td>BM111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2733</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>846c0128-af10-4955-ae56-13e5fdf83efa</td>\n",
       "      <td>2877b861-a478-4658-aecf-9fea0b71e996</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>anonymous</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>92fed36697ed199a4bad</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>81</td>\n",
       "      <td>Android</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>-</td>\n",
       "      <td>BM111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2734</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>60062b84-f752-40a9-bead-ae40c793c3f2</td>\n",
       "      <td>c56bb927-697a-4a44-bb30-cd1a5f0e5606</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>anonymous</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>92fed36697ed199a4bad</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>81</td>\n",
       "      <td>Android</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>-</td>\n",
       "      <td>BM111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2735</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>d4004fc4-fa37-4ff2-904a-bad739c74728</td>\n",
       "      <td>95e476c0-0e35-48a1-9527-c450be8319a4</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>logged-in</td>\n",
       "      <td>55665</td>\n",
       "      <td>None</td>\n",
       "      <td>8d87251e2739718fe822</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>107</td>\n",
       "      <td>Windows</td>\n",
       "      <td>-</td>\n",
       "      <td>10</td>\n",
       "      <td>-</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2736</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>36eb3722-08f4-4342-be99-a18f6de57dc3</td>\n",
       "      <td>61aa8f46-8489-4433-970d-ac0e399ecbb7</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>logged-in</td>\n",
       "      <td>55665</td>\n",
       "      <td>None</td>\n",
       "      <td>8d87251e2739718fe822</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>107</td>\n",
       "      <td>Windows</td>\n",
       "      <td>-</td>\n",
       "      <td>10</td>\n",
       "      <td>-</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2737</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>6a5b8d6f-8d93-4395-ae23-e7ab0fc5f5bb</td>\n",
       "      <td>7bdbf292-9f2b-45f2-9b53-12b964fc07b1</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>logged-in</td>\n",
       "      <td>55665</td>\n",
       "      <td>None</td>\n",
       "      <td>8d87251e2739718fe822</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>107</td>\n",
       "      <td>Windows</td>\n",
       "      <td>-</td>\n",
       "      <td>10</td>\n",
       "      <td>-</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2738</th>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>4f24f932-06ee-4d8a-bc4f-b08031cac61d</td>\n",
       "      <td>658b3464-163d-4932-a87b-b0d31c0047f4</td>\n",
       "      <td>test</td>\n",
       "      <td>wikipedia</td>\n",
       "      <td>logged-in</td>\n",
       "      <td>55665</td>\n",
       "      <td>None</td>\n",
       "      <td>8d87251e2739718fe822</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>107</td>\n",
       "      <td>Windows</td>\n",
       "      <td>-</td>\n",
       "      <td>10</td>\n",
       "      <td>-</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date                wiki                               meta_id  \\\n",
       "2729  2022-11-03  test.wikipedia.org  0d2b29a2-2791-4b97-8a56-db77a6799b0d   \n",
       "2730  2022-11-03  test.wikipedia.org  679b1558-88cc-4545-9c7b-c444b4d1fefc   \n",
       "2731  2022-11-04  test.wikipedia.org  fa1350b5-dd8b-4785-a625-e742e08a6585   \n",
       "2732  2022-11-04  test.wikipedia.org  9e9723ac-6f07-4f03-8976-0edc2206dbd6   \n",
       "2733  2022-11-04  test.wikipedia.org  846c0128-af10-4955-ae56-13e5fdf83efa   \n",
       "2734  2022-11-04  test.wikipedia.org  60062b84-f752-40a9-bead-ae40c793c3f2   \n",
       "2735  2022-11-04  test.wikipedia.org  d4004fc4-fa37-4ff2-904a-bad739c74728   \n",
       "2736  2022-11-04  test.wikipedia.org  36eb3722-08f4-4342-be99-a18f6de57dc3   \n",
       "2737  2022-11-04  test.wikipedia.org  6a5b8d6f-8d93-4395-ae23-e7ab0fc5f5bb   \n",
       "2738  2022-11-04  test.wikipedia.org  4f24f932-06ee-4d8a-bc4f-b08031cac61d   \n",
       "\n",
       "                               meta_req_id wiki_db project_family   user_log  \\\n",
       "2729  3595f8d1-d0fb-4550-bea5-00ad42a19441    test      wikipedia  logged-in   \n",
       "2730  02dea918-af89-4afc-9959-2a9aa8967781    test      wikipedia  logged-in   \n",
       "2731  bb7562e5-002a-4f64-9d2d-fc1de0f5a8d4    test      wikipedia  anonymous   \n",
       "2732  95a88008-5bf7-4e32-8489-9e79b1670c8d    test      wikipedia  anonymous   \n",
       "2733  2877b861-a478-4658-aecf-9fea0b71e996    test      wikipedia  anonymous   \n",
       "2734  c56bb927-697a-4a44-bb30-cd1a5f0e5606    test      wikipedia  anonymous   \n",
       "2735  95e476c0-0e35-48a1-9527-c450be8319a4    test      wikipedia  logged-in   \n",
       "2736  61aa8f46-8489-4433-970d-ac0e399ecbb7    test      wikipedia  logged-in   \n",
       "2737  7bdbf292-9f2b-45f2-9b53-12b964fc07b1    test      wikipedia  logged-in   \n",
       "2738  658b3464-163d-4932-a87b-b0d31c0047f4    test      wikipedia  logged-in   \n",
       "\n",
       "      user_id user_name            session_id  ... edit_count  \\\n",
       "2729    54035      None  11b63f79445c7d450baa  ...        1.0   \n",
       "2730    54035      None  11b63f79445c7d450baa  ...        1.0   \n",
       "2731        0      None  92fed36697ed199a4bad  ...        NaN   \n",
       "2732        0      None  92fed36697ed199a4bad  ...        NaN   \n",
       "2733        0      None  92fed36697ed199a4bad  ...        NaN   \n",
       "2734        0      None  92fed36697ed199a4bad  ...        NaN   \n",
       "2735    55665      None  8d87251e2739718fe822  ...        0.0   \n",
       "2736    55665      None  8d87251e2739718fe822  ...        0.0   \n",
       "2737    55665      None  8d87251e2739718fe822  ...        0.0   \n",
       "2738    55665      None  8d87251e2739718fe822  ...        0.0   \n",
       "\n",
       "      edit_count_bucket groups browser_family browser_major os_family  \\\n",
       "2729               None   None         Chrome           105  Mac OS X   \n",
       "2730               None   None         Chrome           105  Mac OS X   \n",
       "2731               None   None         Chrome            81   Android   \n",
       "2732               None   None         Chrome            81   Android   \n",
       "2733               None   None         Chrome            81   Android   \n",
       "2734               None   None         Chrome            81   Android   \n",
       "2735               None   None         Chrome           107   Windows   \n",
       "2736               None   None         Chrome           107   Windows   \n",
       "2737               None   None         Chrome           107   Windows   \n",
       "2738               None   None         Chrome           107   Windows   \n",
       "\n",
       "     os_minor os_major wmf_app_version device_family  \n",
       "2729       15       10               -           Mac  \n",
       "2730       15       10               -           Mac  \n",
       "2731        1        7               -         BM111  \n",
       "2732        1        7               -         BM111  \n",
       "2733        1        7               -         BM111  \n",
       "2734        1        7               -         BM111  \n",
       "2735        -       10               -         Other  \n",
       "2736        -       10               -         Other  \n",
       "2737        -       10               -         Other  \n",
       "2738        -       10               -         Other  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "ac40cec2-2e40-444f-9a54-57b3dbb68e61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['test.wikipedia.org']"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1['wiki'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "c464286a-7b0f-4673-8e8c-8a4e8deacbd0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['wikipedia']"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1['project_family'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "6604bf7f-dfca-4821-8afb-6e3b6dcbff0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1['edit_count_bucket'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "d4afc5c4-5f0c-4528-bfb6-808e1b1d3ca7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1['registration_dt'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "fab76af5-62a4-4aa4-88de-6177ebea30d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "logged-in    1975\n",
       "anonymous     764\n",
       "Name: user_log, dtype: int64"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1['user_log'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1d3f465-dd4b-4885-a281-3af162711338",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e7a92527-c68e-4bd5-892a-102ccca26be1",
   "metadata": {},
   "source": [
    "All logged in users have user_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "3a392c4d-1d17-480a-b7e7-2f19b5b5e491",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "perf_query1=spark.run('''\n",
    "SELECT \n",
    "  date_format(dt, 'yyyy-MM-dd') AS date,\n",
    "  performer.id AS user_id,\n",
    "  performer.name AS user_name\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE performer.is_logged_in\n",
    "  AND performer.id=0\n",
    "  ORDER BY date\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "1992f184-9134-44a1-9af2-5f5d356884f6",
   "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>date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [date, user_id, user_name]\n",
       "Index: []"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "perf_query1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2e1136e-11a3-44e9-85fb-328868f4ebea",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6b422584-96f4-4d59-9bc8-6f7c22f291b2",
   "metadata": {},
   "source": [
    "### All browser families"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "196021e6-2453-40a0-977e-2bbdc62559ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Firefox',\n",
       " 'Chrome',\n",
       " 'Chrome Mobile',\n",
       " 'Mobile Safari',\n",
       " 'Safari',\n",
       " 'Edge Mobile',\n",
       " 'Edge',\n",
       " 'Google',\n",
       " 'Pale Moon',\n",
       " 'Apple Mail',\n",
       " 'Waterfox',\n",
       " 'Opera',\n",
       " 'Samsung Internet',\n",
       " 'Firefox Mobile']"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ua_query1['browser_family'].unique().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf8f29ec-ee05-4f9a-9caa-377606a4381c",
   "metadata": {},
   "source": [
    "### Operating Systems"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "af413162-22af-4e5c-a510-ac9524f610f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Windows', 'Mac OS X', 'Android', 'iOS', 'Linux', 'Chrome OS', 'Ubuntu']"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ua_query1['os_family'].unique().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33553204-6dd4-424a-8662-151b3891ea9f",
   "metadata": {},
   "source": [
    "### All Device families"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "49f40a88-7743-456b-9c49-cc295f4d2648",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Other', 'Mac', 'Oppo CPH2269', 'iPhone', 'vivo 1906', 'MYA-L13',\n",
       "       'Lenovo TB-X306X', 'LM-Q720', 'Samsung SM-G981U',\n",
       "       'Samsung SM-M307F', 'Huawei DRA-L21', 'iPad', '5031G',\n",
       "       'Samsung SM-G532F', 'Samsung SM-G950F', 'Oppo RMX3195', 'KB2005',\n",
       "       'FP3', 'SM-T285', 'XiaoMi Mi 9T', 'Samsung SM-A315F', '22011119TI',\n",
       "       'Lenovo TB-7104F', 'XiaoMi Redmi Note 8T', 'moto e20',\n",
       "       'moto e5 plus', 'Oppo CPH2145', 'Nexus 5', 'Samsung SM-J400F',\n",
       "       'XiaoMi Redmi 7A', 'Z33', 'Samsung SM-A125F', 'Samsung SM-J810Y',\n",
       "       'Generic Smartphone', 'M2003J15SC', 'Samsung SM-G950U1', 'V2134',\n",
       "       'Itel P651W', 'XiaoMi Redmi 8A Dual'], dtype=object)"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ua_query1.device_family.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "307f3012-0b5d-4160-8429-540ac1b1057b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "date_query1=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT date_format(dt, 'yyyy-MM-dd') AS date\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  ORDER BY date\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "fc06eae6-75a9-4612-9d76-476fb00e94cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(date_query1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "405d8054-0974-407f-8d92-7521b580debb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date    2022-09-13\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "date_query1.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9e1ae7ff-d5d6-4ab1-a03b-31f4496b8a7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date    2022-11-01\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "date_query1.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0458a023-f90b-4051-a691-43200a0b23ca",
   "metadata": {},
   "source": [
    "## Check the custom (bespoke) data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "be86f706-22a7-4f6f-a191-f83030a786ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "name_query1=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, count(1)\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  GROUP BY name\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461a27ee-cdfa-47ca-811e-3a6374fa4f0c",
   "metadata": {},
   "source": [
    "There are 26 distinct name types in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "71368b86-a396-4840-9c9d-93611ba9b842",
   "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>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.dt.init</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>eas.dt.ready</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.dt.loaded</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>eas.dt.first_change</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>eas.dt.save_intent</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.dt.save_attempt</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>eas.dt.save_success</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eas.dt.abort</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.ve.save_failure</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>eas.dt.save_failure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name  count(1)\n",
       "19          eas.ve.init       281\n",
       "23         eas.ve.ready       261\n",
       "25        eas.ve.loaded       261\n",
       "9          eas.ve.abort       162\n",
       "4           eas.mf.init       113\n",
       "0          eas.mf.ready       109\n",
       "22        eas.mf.loaded       109\n",
       "3   eas.ve.first_change       108\n",
       "21   eas.ve.save_intent        94\n",
       "7           eas.dt.init        86\n",
       "20         eas.dt.ready        80\n",
       "1         eas.dt.loaded        80\n",
       "24  eas.ve.save_attempt        79\n",
       "16  eas.ve.save_success        76\n",
       "14  eas.dt.first_change        61\n",
       "18         eas.mf.abort        56\n",
       "11  eas.mf.first_change        52\n",
       "5    eas.mf.save_intent        51\n",
       "17   eas.dt.save_intent        49\n",
       "2   eas.dt.save_attempt        49\n",
       "12  eas.dt.save_success        48\n",
       "10         eas.dt.abort        37\n",
       "15  eas.mf.save_attempt        36\n",
       "6   eas.mf.save_success        29\n",
       "8   eas.ve.save_failure         3\n",
       "13  eas.dt.save_failure         1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name_query1.sort_values(by=['count(1)','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "3a1cd06c-73d8-402e-91c1-df59c1d1971d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2371"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_events=name_query1['count(1)'].sum()\n",
    "total_events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "a53b5a00-aa27-4090-aaec-1afd2cb4d350",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "ve_events=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, count(1) AS event_count\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE name like 'eas.ve%'\n",
    "  GROUP BY name\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fdbf91b-8f19-4b13-ac79-1b1bb14f9ddb",
   "metadata": {},
   "source": [
    "### Visual Editor edit events and types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "72170b7e-5fc1-46ef-b929-9edfc35c5354",
   "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>event_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.ve.save_failure</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  event_count\n",
       "4          eas.ve.init          281\n",
       "6         eas.ve.ready          261\n",
       "8        eas.ve.loaded          261\n",
       "2         eas.ve.abort          162\n",
       "0  eas.ve.first_change          108\n",
       "5   eas.ve.save_intent           94\n",
       "7  eas.ve.save_attempt           79\n",
       "3  eas.ve.save_success           76\n",
       "1  eas.ve.save_failure            3"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ve_events.sort_values(by=['event_count','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "ca200c49-9b75-43a5-80ef-f7c666870b36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1325"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ve_events['event_count'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "40e421a5-0b7a-4ef9-a45e-019ffa89cb82",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "wikitext_events =spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, count(1) AS event_count\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE name like 'eas.mf%'\n",
    "  GROUP BY name\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d51d5dfc-34b8-4480-a698-710869db7410",
   "metadata": {},
   "source": [
    "### wikitext events and types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "bd57a50a-5e25-4b43-9b54-82ba64ec5a0b",
   "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>event_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  event_count\n",
       "1          eas.mf.init          113\n",
       "0         eas.mf.ready          109\n",
       "7        eas.mf.loaded          109\n",
       "6         eas.mf.abort           56\n",
       "4  eas.mf.first_change           52\n",
       "2   eas.mf.save_intent           51\n",
       "5  eas.mf.save_attempt           36\n",
       "3  eas.mf.save_success           29"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wikitext_events.sort_values(by=['event_count','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "c5bb2324-0fbc-4818-821e-ce975bd75346",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "555"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wikitext_events['event_count'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "b6da58c8-e3be-428b-af99-1f4ce355b12e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "discussiontools_events =spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, count(1) AS event_count\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE name like 'eas.dt%'\n",
    "  GROUP BY name\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f5eb8e8-25e4-407b-8d14-19416b075c9c",
   "metadata": {},
   "source": [
    "### Reply tool events and types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "3a2345ca-74c7-4755-b94a-475bbcec2054",
   "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>event_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.dt.init</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>eas.dt.ready</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.dt.loaded</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.dt.first_change</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.dt.save_intent</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.dt.save_attempt</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.dt.save_success</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.dt.abort</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.dt.save_failure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  event_count\n",
       "2          eas.dt.init           86\n",
       "8         eas.dt.ready           80\n",
       "0        eas.dt.loaded           80\n",
       "6  eas.dt.first_change           61\n",
       "7   eas.dt.save_intent           49\n",
       "1  eas.dt.save_attempt           49\n",
       "4  eas.dt.save_success           48\n",
       "3         eas.dt.abort           37\n",
       "5  eas.dt.save_failure            1"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "discussiontools_events.sort_values(by=['event_count','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "29cd05dd-f638-479b-aa92-45eb8df59bfd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "491"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "discussiontools_events['event_count'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "1590f4f0-5ec7-429b-b149-7f11b54a82e3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "discussiontools_events1 =spark.run('''\n",
    "SELECT \n",
    "    custom_data['init_type'] as dt_type, count(1) AS event_count\n",
    "  --IF(custom_data['init_type'] = \"section\", \"new discussion tool\", \"reply tool\") as dt_type, count(1) AS event_count\n",
    "  --IF(custom_data.init_type = \"section\", \"new discussion tool\", \"reply tool\") as dt_type, count(1) AS event_count\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE name like 'eas.dt.init'\n",
    "  GROUP BY dt_type\n",
    "  --IF(custom_data.init_type = \"section\", \"new discussion tool\", \"reply tool\")\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7b20787-6f5d-486b-8848-d6b27f2c1d50",
   "metadata": {},
   "source": [
    "Reply Tool (section) vs New Discussion Tool (page) \n",
    "All dt init events are assigned a section or page init_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "313d5760-7a3d-4820-b672-cf84d4067777",
   "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_type</th>\n",
       "      <th>event_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(string, section)</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(string, page)</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             dt_type  event_count\n",
       "0  (string, section)           36\n",
       "1     (string, page)           50"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "discussiontools_events1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e33c709-b5bd-4fbc-940a-dce967453686",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Check sample individual sessions "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "883723d6-0f72-47e6-a024-db91bad33442",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "check_event1 =spark.run('''\n",
    "SELECT \n",
    "    *\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE performer.session_id='8d87251e2739718fe822'\n",
    "  ORDER BY dt\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "edc15fe4-5e2b-46ad-b85a-849fdae357c8",
   "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>_schema</th>\n",
       "      <th>agent</th>\n",
       "      <th>custom_data</th>\n",
       "      <th>dt</th>\n",
       "      <th>http</th>\n",
       "      <th>mediawiki</th>\n",
       "      <th>meta</th>\n",
       "      <th>name</th>\n",
       "      <th>page</th>\n",
       "      <th>performer</th>\n",
       "      <th>user_agent_map</th>\n",
       "      <th>is_wmf_domain</th>\n",
       "      <th>normalized_host</th>\n",
       "      <th>datacenter</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-04T13:39:10.255Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-04T13:39:15.395Z,...</td>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>(SureshKumar0307/sandbox, None, 0, None, 2, No...</td>\n",
       "      <td>(None, 0, None, None, 55665, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Windows', 'browser_major': '107...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-04T13:39:10.584Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-04T13:39:15.398Z,...</td>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>(SureshKumar0307/sandbox, None, 0, None, 2, No...</td>\n",
       "      <td>(None, 0, None, None, 55665, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Windows', 'browser_major': '107...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-04T13:39:10.593Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-04T13:39:15.397Z,...</td>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>(SureshKumar0307/sandbox, None, 0, None, 2, No...</td>\n",
       "      <td>(None, 0, None, None, 55665, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Windows', 'browser_major': '107...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'abort_mechanism': ('string', 'navigate'), 'i...</td>\n",
       "      <td>2022-11-04T13:39:13.817Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-04T13:39:15.399Z,...</td>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>(SureshKumar0307/sandbox, None, 0, None, 2, No...</td>\n",
       "      <td>(None, 0, None, None, 55665, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Windows', 'browser_major': '107...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  _schema                                  agent  \\\n",
       "0    None  (None, mediawiki_js, desktop_browser)   \n",
       "1    None  (None, mediawiki_js, desktop_browser)   \n",
       "2    None  (None, mediawiki_js, desktop_browser)   \n",
       "3    None  (None, mediawiki_js, desktop_browser)   \n",
       "\n",
       "                                         custom_data  \\\n",
       "0  {'integration': ('string', 'page'), 'editing_s...   \n",
       "1  {'integration': ('string', 'page'), 'editing_s...   \n",
       "2  {'integration': ('string', 'page'), 'editing_s...   \n",
       "3  {'abort_mechanism': ('string', 'navigate'), 'i...   \n",
       "\n",
       "                         dt  \\\n",
       "0  2022-11-04T13:39:10.255Z   \n",
       "1  2022-11-04T13:39:10.584Z   \n",
       "2  2022-11-04T13:39:10.593Z   \n",
       "3  2022-11-04T13:39:13.817Z   \n",
       "\n",
       "                                                http  \\\n",
       "0  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "1  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "2  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "3  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "\n",
       "                                           mediawiki  \\\n",
       "0  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "1  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "2  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "3  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "\n",
       "                                                meta           name  \\\n",
       "0  (test.wikipedia.org, 2022-11-04T13:39:15.395Z,...    eas.ve.init   \n",
       "1  (test.wikipedia.org, 2022-11-04T13:39:15.398Z,...   eas.ve.ready   \n",
       "2  (test.wikipedia.org, 2022-11-04T13:39:15.397Z,...  eas.ve.loaded   \n",
       "3  (test.wikipedia.org, 2022-11-04T13:39:15.399Z,...   eas.ve.abort   \n",
       "\n",
       "                                                page  \\\n",
       "0  (SureshKumar0307/sandbox, None, 0, None, 2, No...   \n",
       "1  (SureshKumar0307/sandbox, None, 0, None, 2, No...   \n",
       "2  (SureshKumar0307/sandbox, None, 0, None, 2, No...   \n",
       "3  (SureshKumar0307/sandbox, None, 0, None, 2, No...   \n",
       "\n",
       "                                           performer  \\\n",
       "0  (None, 0, None, None, 55665, None, True, None,...   \n",
       "1  (None, 0, None, None, 55665, None, True, None,...   \n",
       "2  (None, 0, None, None, 55665, None, True, None,...   \n",
       "3  (None, 0, None, None, 55665, None, True, None,...   \n",
       "\n",
       "                                      user_agent_map  is_wmf_domain  \\\n",
       "0  {'os_family': 'Windows', 'browser_major': '107...           True   \n",
       "1  {'os_family': 'Windows', 'browser_major': '107...           True   \n",
       "2  {'os_family': 'Windows', 'browser_major': '107...           True   \n",
       "3  {'os_family': 'Windows', 'browser_major': '107...           True   \n",
       "\n",
       "                         normalized_host datacenter  year  month  day  hour  \n",
       "0  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    4    13  \n",
       "1  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    4    13  \n",
       "2  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    4    13  \n",
       "3  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    4    13  "
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_event"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "6432ee78-0d83-42ea-be0d-8c699d8463ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "check_event2 =spark.run('''\n",
    "SELECT \n",
    "    *\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE performer.session_id='ebbb63d0393cfd51c08a'\n",
    "  ORDER BY dt\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2be2b0e1-bd83-4e6a-92c5-f75fd7536d80",
   "metadata": {},
   "source": [
    "All events for a session id are in the correct funnel order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "9e2c46b6-d4e8-43c8-b7bd-57a3c32f5198",
   "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>_schema</th>\n",
       "      <th>agent</th>\n",
       "      <th>custom_data</th>\n",
       "      <th>dt</th>\n",
       "      <th>http</th>\n",
       "      <th>mediawiki</th>\n",
       "      <th>meta</th>\n",
       "      <th>name</th>\n",
       "      <th>page</th>\n",
       "      <th>performer</th>\n",
       "      <th>user_agent_map</th>\n",
       "      <th>is_wmf_domain</th>\n",
       "      <th>normalized_host</th>\n",
       "      <th>datacenter</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T03:54:56.662Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T03:55:25.338Z,...</td>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 548998, No...</td>\n",
       "      <td>(None, 132, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T03:54:57.231Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T03:55:25.359Z,...</td>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 548998, No...</td>\n",
       "      <td>(None, 132, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T03:54:57.231Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T03:55:25.358Z,...</td>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 548998, No...</td>\n",
       "      <td>(None, 132, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T03:55:04.157Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T03:55:25.361Z,...</td>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 548998, No...</td>\n",
       "      <td>(None, 132, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T03:55:07.524Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T03:55:25.363Z,...</td>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 548998, No...</td>\n",
       "      <td>(None, 132, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T05:00:06.504Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T05:00:22.053Z,...</td>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 549140, No...</td>\n",
       "      <td>(None, 156, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T05:00:11.925Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T05:00:22.056Z,...</td>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 549140, No...</td>\n",
       "      <td>(None, 156, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T05:00:14.354Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T05:00:22.059Z,...</td>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 549140, No...</td>\n",
       "      <td>(None, 156, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T05:00:16.396Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T05:00:22.062Z,...</td>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 549140, No...</td>\n",
       "      <td>(None, 156, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, desktop_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-11-03T05:00:17.356Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.8, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-11-03T05:00:22.064Z,...</td>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>(Rose, None, 146951, None, 0, None, 549140, No...</td>\n",
       "      <td>(None, 156, None, None, 44768, None, True, Non...</td>\n",
       "      <td>{'os_family': 'Ubuntu', 'browser_major': '106'...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>codfw</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>162 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    _schema                                  agent  \\\n",
       "0      None  (None, mediawiki_js, desktop_browser)   \n",
       "1      None  (None, mediawiki_js, desktop_browser)   \n",
       "2      None  (None, mediawiki_js, desktop_browser)   \n",
       "3      None  (None, mediawiki_js, desktop_browser)   \n",
       "4      None  (None, mediawiki_js, desktop_browser)   \n",
       "..      ...                                    ...   \n",
       "157    None  (None, mediawiki_js, desktop_browser)   \n",
       "158    None  (None, mediawiki_js, desktop_browser)   \n",
       "159    None  (None, mediawiki_js, desktop_browser)   \n",
       "160    None  (None, mediawiki_js, desktop_browser)   \n",
       "161    None  (None, mediawiki_js, desktop_browser)   \n",
       "\n",
       "                                           custom_data  \\\n",
       "0    {'integration': ('string', 'page'), 'editing_s...   \n",
       "1    {'integration': ('string', 'page'), 'editing_s...   \n",
       "2    {'integration': ('string', 'page'), 'editing_s...   \n",
       "3    {'integration': ('string', 'page'), 'editing_s...   \n",
       "4    {'integration': ('string', 'page'), 'editing_s...   \n",
       "..                                                 ...   \n",
       "157  {'integration': ('string', 'page'), 'editing_s...   \n",
       "158  {'integration': ('string', 'page'), 'editing_s...   \n",
       "159  {'integration': ('string', 'page'), 'editing_s...   \n",
       "160  {'integration': ('string', 'page'), 'editing_s...   \n",
       "161  {'integration': ('string', 'page'), 'editing_s...   \n",
       "\n",
       "                           dt  \\\n",
       "0    2022-11-03T03:54:56.662Z   \n",
       "1    2022-11-03T03:54:57.231Z   \n",
       "2    2022-11-03T03:54:57.231Z   \n",
       "3    2022-11-03T03:55:04.157Z   \n",
       "4    2022-11-03T03:55:07.524Z   \n",
       "..                        ...   \n",
       "157  2022-11-03T05:00:06.504Z   \n",
       "158  2022-11-03T05:00:11.925Z   \n",
       "159  2022-11-03T05:00:14.354Z   \n",
       "160  2022-11-03T05:00:16.396Z   \n",
       "161  2022-11-03T05:00:17.356Z   \n",
       "\n",
       "                                                  http  \\\n",
       "0    (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "1    (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "2    (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "3    (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "4    (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "..                                                 ...   \n",
       "157  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "158  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "159  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "160  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "161  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "\n",
       "                                             mediawiki  \\\n",
       "0    (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "1    (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "2    (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "3    (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "4    (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "..                                                 ...   \n",
       "157  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "158  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "159  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "160  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "161  (None, None, None, None, None, 1.40.0-wmf.8, N...   \n",
       "\n",
       "                                                  meta                 name  \\\n",
       "0    (test.wikipedia.org, 2022-11-03T03:55:25.338Z,...          eas.ve.init   \n",
       "1    (test.wikipedia.org, 2022-11-03T03:55:25.359Z,...        eas.ve.loaded   \n",
       "2    (test.wikipedia.org, 2022-11-03T03:55:25.358Z,...         eas.ve.ready   \n",
       "3    (test.wikipedia.org, 2022-11-03T03:55:25.361Z,...  eas.ve.first_change   \n",
       "4    (test.wikipedia.org, 2022-11-03T03:55:25.363Z,...   eas.ve.save_intent   \n",
       "..                                                 ...                  ...   \n",
       "157  (test.wikipedia.org, 2022-11-03T05:00:22.053Z,...        eas.ve.loaded   \n",
       "158  (test.wikipedia.org, 2022-11-03T05:00:22.056Z,...  eas.ve.first_change   \n",
       "159  (test.wikipedia.org, 2022-11-03T05:00:22.059Z,...   eas.ve.save_intent   \n",
       "160  (test.wikipedia.org, 2022-11-03T05:00:22.062Z,...  eas.ve.save_attempt   \n",
       "161  (test.wikipedia.org, 2022-11-03T05:00:22.064Z,...  eas.ve.save_success   \n",
       "\n",
       "                                                  page  \\\n",
       "0    (Rose, None, 146951, None, 0, None, 548998, No...   \n",
       "1    (Rose, None, 146951, None, 0, None, 548998, No...   \n",
       "2    (Rose, None, 146951, None, 0, None, 548998, No...   \n",
       "3    (Rose, None, 146951, None, 0, None, 548998, No...   \n",
       "4    (Rose, None, 146951, None, 0, None, 548998, No...   \n",
       "..                                                 ...   \n",
       "157  (Rose, None, 146951, None, 0, None, 549140, No...   \n",
       "158  (Rose, None, 146951, None, 0, None, 549140, No...   \n",
       "159  (Rose, None, 146951, None, 0, None, 549140, No...   \n",
       "160  (Rose, None, 146951, None, 0, None, 549140, No...   \n",
       "161  (Rose, None, 146951, None, 0, None, 549140, No...   \n",
       "\n",
       "                                             performer  \\\n",
       "0    (None, 132, None, None, 44768, None, True, Non...   \n",
       "1    (None, 132, None, None, 44768, None, True, Non...   \n",
       "2    (None, 132, None, None, 44768, None, True, Non...   \n",
       "3    (None, 132, None, None, 44768, None, True, Non...   \n",
       "4    (None, 132, None, None, 44768, None, True, Non...   \n",
       "..                                                 ...   \n",
       "157  (None, 156, None, None, 44768, None, True, Non...   \n",
       "158  (None, 156, None, None, 44768, None, True, Non...   \n",
       "159  (None, 156, None, None, 44768, None, True, Non...   \n",
       "160  (None, 156, None, None, 44768, None, True, Non...   \n",
       "161  (None, 156, None, None, 44768, None, True, Non...   \n",
       "\n",
       "                                        user_agent_map  is_wmf_domain  \\\n",
       "0    {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "1    {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "2    {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "3    {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "4    {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "..                                                 ...            ...   \n",
       "157  {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "158  {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "159  {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "160  {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "161  {'os_family': 'Ubuntu', 'browser_major': '106'...           True   \n",
       "\n",
       "                           normalized_host datacenter  year  month  day  hour  \n",
       "0    (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     3  \n",
       "1    (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     3  \n",
       "2    (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     3  \n",
       "3    (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     3  \n",
       "4    (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     3  \n",
       "..                                     ...        ...   ...    ...  ...   ...  \n",
       "157  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     5  \n",
       "158  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     5  \n",
       "159  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     5  \n",
       "160  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     5  \n",
       "161  (wikipedia, test, [], org, wikipedia)      codfw  2022     11    3     5  \n",
       "\n",
       "[162 rows x 18 columns]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_event2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6587659-88bc-4d11-acfd-346b8de8d2c8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}