unique_devices_by_project_family(Feb-July2024).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a8136a3a-611c-4596-b6f9-f158a235eca1",
   "metadata": {},
   "source": [
    "# European Unique Devices by Project Family"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "165ccf4b-ae0c-4082-8e50-aaa7922c1568",
   "metadata": {},
   "source": [
    "## Task Description:\n",
    "\n",
    "Create the following datasets and export to a csv file. \n",
    "\n",
    "* Unique Devices by Project Family\n",
    "  * Requested fields: Project Family, 6-month average unique devices\n",
    "  * Filters: Only include countries in European Union (should be available in canonical data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10e90e8d-b2c9-421b-bbdc-f861f9718aaa",
   "metadata": {},
   "source": [
    "## Methodology and Data Sources: \n",
    "\n",
    "Unique devices per project family data comes from the [unique_devices_per_project_family_monthly](https://github.com/wikimedia/analytics-refinery/blob/master/oozie/unique_devices/per_project_family/monthly/unique_devices_per_project_family_monthly.hql). \n",
    "\n",
    "**Metric**: The `uniques_estimate` average over the last 6 months **(February thru July 2024)**. Data limited to only countries within European Union.\n",
    "\n",
    "**Important note**: \n",
    "- Unique devices by family metrics has been overcounted by approx ~5% globally between Feb 9, 2021 and June 30, 2022.  \n",
    "For more info, read [2021-02-09 Unique Devices By Family Overcount](https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Data_Issues/2021-02-09_Unique_Devices_By_Family_Overcount)\n",
    "- Unique devices were inflated due to actors showing automated behavior (like Cloud services, VPNs etc.) which were not filtered correctly in our bot labelling. This has affected data for July, Aug and Sep 2024. See [Investigate recent rise in Unique Devices](https://phabricator.wikimedia.org/T373630) \n",
    "\n",
    "\n",
    "EU country data is based on data from [canonical_data](https://github.com/wikimedia-research/canonical-data) dataset. \n",
    "\n",
    "Other notes:\n",
    "* Used the new `is_eu` flag from canonical_data.countries to filter EU member countries.\n",
    "* The breakdown of Wikimedia projects will not be equal to Wikimedia project family which is a known consequence of [T325544](https://phabricator.wikimedia.org/T325544) . However, it is recommended to use unique_devices_per_domain_monthly numbers for the wikimedia project breakdown. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "28feb82c-e305-4b64-a73d-7e7c5016043f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import wmfdata as wmf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed6cd632-d988-4fad-bfaa-2cf3f4409171",
   "metadata": {},
   "source": [
    "## Unique Devices by Project Family"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4c57ad82-e1ee-42c4-9b0f-5a1c4c40170d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "query = '''\n",
    "\n",
    "SELECT\n",
    "    project_family,\n",
    "    countries.name AS country, \n",
    "    ud.year,\n",
    "    ud.month,\n",
    "    SUM(ud.uniques_estimate) as unique_devices\n",
    "  FROM wmf.unique_devices_per_project_family_monthly ud,\n",
    "    canonical_data.countries AS countries  \n",
    "  WHERE \n",
    "    ud.country_code = countries.iso_code \n",
    "  -- only European Union countries\n",
    "    AND countries.is_eu  \n",
    "  -- review most recent 6 months available: Feb - July 2024\n",
    "    AND \n",
    "     YEAR = 2024 AND MONTH >= 02\n",
    "      AND MONTH < 8\n",
    "  GROUP BY \n",
    "    project_family,\n",
    "    name,\n",
    "    year,\n",
    "    month\n",
    "\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f5c13c40-a280-45ed-b58a-a3723f683f2c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "unique_devices_byprojectfamily = wmf.spark.run(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ae14d85d-fdcc-4be3-ad97-a8d0dc9c1c1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_789586/957048093.py:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  unique_devices_byprojectfamily_total = unique_devices_byprojectfamily.groupby(['project_family', 'year','month']).sum()\n"
     ]
    }
   ],
   "source": [
    "unique_devices_byprojectfamily_total = unique_devices_byprojectfamily.groupby(['project_family', 'year','month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f5d50c3e-2084-48d7-a93f-ca5a015ba162",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg = (unique_devices_byprojectfamily_total.groupby(['project_family']).mean().\n",
    "                                          round().sort_values(by=['unique_devices'], ascending = False).reset_index())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b0c5448f-152d-4991-9376-d798ccb0bc11",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg['Monthly Avg Unique Users'] = (unique_devices_byprojectfamily_avg['unique_devices']/2.4).round(-3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "0814a6ad-7883-42b6-a8b5-e79f2ac28fb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg.columns = ['Project Family', 'Monthly Avg Unique Devices', 'Monthly Avg Unique Users']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f790ff2-c0d4-4a2e-9ec8-43a1859d30f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8206394f-d56d-4ddf-ac35-edc2a467dc28",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg.to_csv('unique_devices_byprojectfamily_avg.csv', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4db60991-aa09-437c-bda4-4d4344b2621b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "243adce9-df73-48b0-ace7-ec05d7c033c6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d3116e9e-b64d-4860-94ff-abe5ee8bfc2d",
   "metadata": {},
   "source": [
    "### Roll up language wikimedia domain into 'wikimedia.org'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "eee5c101-efb4-41ce-b12a-acf8fd54d132",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "\n",
    "-- collect unique devices per project in the wikimedia domain and country per month\n",
    "\n",
    "SELECT\n",
    "    --wikis.domain_name AS project,\n",
    "    IF(rlike (wikis.database_code,'^.{2}wikimedia'),'wikimedia.org',wikis.domain_name) AS project,\n",
    "    countries.name AS country,\n",
    "    ud.year,\n",
    "    ud.month,\n",
    "    SUM(ud.uniques_estimate) as unique_devices\n",
    "FROM wmf.unique_devices_per_domain_monthly AS ud\n",
    "JOIN canonical_data.countries AS countries \n",
    "    ON ud.country_code = countries.iso_code\n",
    "-- combine mobile and web domains\n",
    "    \n",
    "-- Strip mobile subdomains so mobile and desktop sites are combined. \n",
    "LEFT JOIN canonical_data.wikis AS wikis ON\n",
    "REGEXP_REPLACE(\n",
    "    REGEXP_REPLACE(\n",
    "        -- The canonical domains for Wikidata and MediaWiki.org start with `www`, which \n",
    "        -- gets _replaced_ by the mobile subdomain. Combine the two possibilities for each site.\n",
    "        REGEXP_REPLACE(\n",
    "            REGEXP_REPLACE(domain, \"^m\\\\\\\\.wikidata\", \"www.wikidata\"),\n",
    "        \"^m\\\\\\\\.mediawiki\", \"www.mediawiki\"),\n",
    "    \"^m\\\\\\\\.\", \"\"),\n",
    "\"\\\\\\\\.m\\\\\\\\.\", \".\")= wikis.domain_name\n",
    "\n",
    "-- only European Union countries\n",
    "    WHERE countries.is_eu = true\n",
    "\n",
    "-- review most recent 6 months available: Feb thru July 2024\n",
    "    AND YEAR = 2024 AND MONTH >= 02\n",
    "      AND MONTH < 8\n",
    "  AND ud.domain LIKE '%wikimedia%'\n",
    "  GROUP BY \n",
    "    project,\n",
    "    name,\n",
    "    year,\n",
    "    month\n",
    "'''\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f62c4c05-3c13-41e2-827d-a11cb586cc8e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "unique_devices_wikimedia_rollup = wmf.spark.run(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05376ff3-2491-4424-b59d-18ee58db16e4",
   "metadata": {},
   "source": [
    "*Note*: The breakdown of Wikimedia projects will not be equal to Wikimedia project family which is a known consequence of [T325544](https://phabricator.wikimedia.org/T325544) . But according to [T301403#8478621](https://phabricator.wikimedia.org/T301403#8478621) we understand that we can trust the `unique_devices_per_domain_monthly` numbers and shouldnt report the `unique_devices_per_project_family_monthly` numbers for **wikimedia**, which is what we're doing here. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2a14ce1d-7968-46af-9493-b4ac5ef44d99",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_789586/207635059.py:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  unique_devices_wikimedia_rollup_eu   = unique_devices_wikimedia_rollup.groupby(['project', 'year', 'month']).sum()\n"
     ]
    }
   ],
   "source": [
    "unique_devices_wikimedia_rollup_eu   = unique_devices_wikimedia_rollup.groupby(['project', 'year', 'month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d1e54e1e-49bc-477c-be95-39d9f80bfe63",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg = (unique_devices_wikimedia_rollup_eu.groupby(['project']).mean().\n",
    "                                        round().sort_values(by=['unique_devices'], ascending = False).reset_index())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "f055eda2-2b7d-4299-a501-bc76254433ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg['Monthly Avg Unique Users'] = (unique_devices_wikimedia_rollup_eu_avg['unique_devices']/2.4).round(-3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43f87898-5b0e-41a1-8e6a-e309d677773b",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "25b780bb-b74b-4ac3-91f9-accd42026022",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg.to_csv('unique_devices_wikimedia_rollup_eu_avg.csv', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d389191a-58ed-4024-9c3e-2ba46be0e73d",
   "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}