{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f9b9285-93d2-4603-859e-2b1677ec1cac",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from IPython.display import Markdown as md\n",
    "# os.environ[\"S3_BUCKET\"] = \"pingcap-plg\"\n",
    "# os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"/home/zhoushuai/PLG/classifier/gcpbigquery.json\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a5adf9e-a6ba-4b87-89f6-d2f613bfee44",
   "metadata": {},
   "source": [
    "# TiDB Cloud User Classification - MP report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b35233f5-d5c8-403a-8e98-4d1c1dcd7a16",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import timedelta, datetime, timezone\n",
    "\n",
    "begin = datetime(2022, 7, 1, tzinfo=timezone.utc)\n",
    "\n",
    "md(f'''\n",
    "report generate time: **{datetime.now().date()}**\n",
    "\n",
    "only include tenants signup after **{begin.date()}**\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "082b18ae-7797-4235-bdf5-a6549916fd72",
   "metadata": {},
   "outputs": [],
   "source": [
    "from users import load_users\n",
    "\n",
    "users = load_users(begin)\n",
    "users = [u for u in users if not u.is_pingcap()]\n",
    "users = [u for u in users if u.third_party_account_provider() is not None and u.signup_time() > begin and not u.has_poc()]\n",
    "\n",
    "md(f'After **{begin.date()}**, there are **{len(users)}** Marketplace users signup')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea6c3fd3-240c-4436-aa4b-b44dc1fb8561",
   "metadata": {},
   "source": [
    "### Class 1\n",
    "\n",
    "conditions (match any):\n",
    "- connect and import\n",
    "- web shell page stay time > 60s\n",
    "- import page stay time > 60s\n",
    "- max qps > 10\n",
    "- max connection > 10\n",
    "- PoC Form Submission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc81590b-71f8-4099-9846-0868043bed06",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from tabulate import tabulate\n",
    "from classify import classify\n",
    "\n",
    "xs = [u for u in users if classify(u) == 'first class']\n",
    "xs = sorted(xs, key=lambda u: -u.event_types())\n",
    "\n",
    "def class_1_1(u):\n",
    "    return u.is_business_email() or u.count_event('PoC Form Submission') > 0\n",
    "\n",
    "def class_1_2(u):\n",
    "    mails = [\n",
    "        'gmail.com',\n",
    "        'msn.com',\n",
    "        'outlook.com',\n",
    "        'hotmail.com',\n",
    "        'yahoo.com',\n",
    "        'foxmail.com',\n",
    "        'yopmail.com',\n",
    "        'icloud.com',\n",
    "        'protonmail.com',\n",
    "    ]\n",
    "    return not class_1_1(u) and u.email().split('@')[1] in mails\n",
    "\n",
    "def class_1_3(u):\n",
    "    return not class_1_1(u) and not class_1_2(u)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12f0a1c0-01f8-48cf-8575-cafc92bf44ec",
   "metadata": {},
   "source": [
    "#### Class 1.1\n",
    "\n",
    "conditions (match any):\n",
    "- use business email\n",
    "- PoC Form Submission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b731c718-1a9d-483c-9e23-e027a60e3654",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(\n",
    "    [[\n",
    "        u.email(),\n",
    "        str(u.id()),\n",
    "        u.owner_id(),\n",
    "        u.signup_time().date(),\n",
    "        u.alive().total_seconds() / 24 / 60 / 60,\n",
    "        u.event_types(),\n",
    "        u.first_cluster_create_time().date() if u.first_cluster_create_time() is not None else '',\n",
    "        u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "        'Y' if u.count_event('PoC Form Submission') > 0 else 'N',\n",
    "        'Y' if u.has_bind_card() else 'N',\n",
    "     ] for u in xs if class_1_1(u)],\n",
    "    columns=[\n",
    "        \"Email\", \n",
    "        \"Tenant ID\", \n",
    "        \"Owner\", \n",
    "        \"Signup Time\", \n",
    "        \"DBaaS Trial (day)\", \n",
    "        \"Event Types\", \n",
    "        \"Cluster Create\", \n",
    "        \"Clusters Trial (day)\", \n",
    "        'PoC Form Submission', \n",
    "        'Has Bind Card',\n",
    "    ],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6b11e0a-fa89-41a1-bf2d-f8e6a87aa27e",
   "metadata": {},
   "source": [
    "#### Class 1.2\n",
    "\n",
    "conditions:\n",
    "- use international personal mail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "017a05a1-f12a-4603-8493-c3a105cb7d14",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(\n",
    "    [[\n",
    "        u.email(),\n",
    "        str(u.id()),\n",
    "        u.owner_id(),\n",
    "        u.signup_time().date(),\n",
    "        u.alive().total_seconds() / 24 / 60 / 60,\n",
    "        u.event_types(),\n",
    "        u.first_cluster_create_time().date() if u.first_cluster_create_time() is not None else '',\n",
    "        u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "        'Y' if u.count_event('PoC Form Submission') > 0 else 'N',\n",
    "        'Y' if u.has_bind_card() else 'N',\n",
    "     ] for u in xs if class_1_2(u)],\n",
    "    columns=[\n",
    "        \"Email\", \n",
    "        \"Tenant ID\", \n",
    "        \"Owner\", \n",
    "        \"Signup Time\", \n",
    "        \"DBaaS Trial (day)\", \n",
    "        \"Event Types\", \n",
    "        \"Cluster Create\", \n",
    "        \"Clusters Trial (day)\", \n",
    "        'PoC Form Submission', \n",
    "        'Has Bind Card',\n",
    "    ],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab9a5f66-ab1f-436d-a348-081370d0fd05",
   "metadata": {},
   "source": [
    "#### Class 1.3\n",
    "\n",
    "conditions:\n",
    "- use chinese personal mail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14818928-7e5f-46a0-93ed-b9d9116d745f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(\n",
    "    [[\n",
    "        u.email(),\n",
    "        str(u.id()),\n",
    "        u.owner_id(),\n",
    "        u.signup_time().date(),\n",
    "        u.alive().total_seconds() / 24 / 60 / 60,\n",
    "        u.event_types(),\n",
    "        u.first_cluster_create_time().date() if u.first_cluster_create_time() is not None else '',\n",
    "        u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "        'Y' if u.count_event('PoC Form Submission') > 0 else 'N',\n",
    "        'Y' if u.has_bind_card() else 'N',\n",
    "     ] for u in xs if class_1_3(u)],\n",
    "    columns=[\n",
    "        \"Email\", \n",
    "        \"Tenant ID\", \n",
    "        \"Owner\", \n",
    "        \"Signup Time\", \n",
    "        \"DBaaS Trial (day)\", \n",
    "        \"Event Types\", \n",
    "        \"Cluster Create\", \n",
    "        \"Clusters Trial (day)\", \n",
    "        'PoC Form Submission', \n",
    "        'Has Bind Card',\n",
    "    ],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23871930-c94b-4345-a44e-e0fb035fb18d",
   "metadata": {},
   "source": [
    "### Class 2\n",
    "\n",
    "conditions:\n",
    "- connect and no import and max qps > 1\n",
    "- web shell page stay time > 60s and event types > 20\n",
    "- import page stay time > 60s and event types > 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "192de3ac-dfd8-4a31-86b5-b639751e6598",
   "metadata": {},
   "outputs": [],
   "source": [
    "xs = [u for u in users if classify(u) == 'second class']\n",
    "xs = sorted(xs, key=lambda u: -u.event_types())\n",
    "\n",
    "pd.DataFrame(\n",
    "    [[\n",
    "        u.email(),\n",
    "        str(u.id()),\n",
    "        u.id(),\n",
    "        u.signup_time().date(),\n",
    "        u.alive().total_seconds() / 24 / 60 / 60,\n",
    "        u.event_types(),\n",
    "        u.first_cluster_create_time().date() if u.first_cluster_create_time() is not None else '',\n",
    "        u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "     ] for u in xs],\n",
    "    columns=[\"Email\", \"Tenant ID\", \"Owner\", \"Signup Time\", \"DBaaS Trial (day)\", \"Event Types\", \"Cluster Create\", \"Clusters Trial (day)\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dee12228-1e92-4062-b895-dabbc79a9847",
   "metadata": {},
   "source": [
    "### Class 3\n",
    "\n",
    "conditions:\n",
    "- has create cluster and event types > 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a940e0d-27dd-40f0-97be-60613cd55b69",
   "metadata": {},
   "outputs": [],
   "source": [
    "xs = [u for u in users if classify(u) == 'third class']\n",
    "xs = sorted(xs, key=lambda u: -u.event_types())\n",
    "\n",
    "pd.DataFrame(\n",
    "    [[\n",
    "        u.email(),\n",
    "        str(u.id()),\n",
    "        u.owner_id(),\n",
    "        u.signup_time().date(),\n",
    "        u.alive().total_seconds() / 24 / 60 / 60,\n",
    "        u.event_types(),\n",
    "        u.first_cluster_create_time().date() if u.first_cluster_create_time() is not None else '',\n",
    "        u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "     ] for u in xs],\n",
    "    columns=[\"Email\", \"Tenant ID\", \"Owner\", \"Signup Time\", \"DBaaS Trial (day)\", \"Event Types\", \"Cluster Create\", \"Clusters Trial (day)\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0dd9d9d-3216-41cf-a8d4-c8bebccfeb82",
   "metadata": {},
   "source": [
    "### Others"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6df23ea5-2151-41e5-9412-fbfed0c63866",
   "metadata": {},
   "outputs": [],
   "source": [
    "xs = [u for u in users if classify(u) == 'others']\n",
    "xs = sorted(xs, key=lambda u: -u.event_types())\n",
    "\n",
    "pd.DataFrame(\n",
    "    [[\n",
    "        u.email(),\n",
    "        str(u.id()),\n",
    "        u.owner_id(),\n",
    "        u.signup_time().date(),\n",
    "        u.alive().total_seconds() / 24 / 60 / 60,\n",
    "        u.event_types(),\n",
    "        u.first_cluster_create_time().date() if u.first_cluster_create_time() is not None else '',\n",
    "        u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "     ] for u in xs],\n",
    "    columns=[\"Email\", \"Tenant ID\", \"Owner\", \"Signup Time\", \"DBaaS Trial (day)\", \"Event Types\", \"Cluster Create\", \"Clusters Trial (day)\"],\n",
    ")"
   ]
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}