{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7da5b215-6a82-421e-94a0-b1f7172a4d98",
   "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": "f7fd0ade-11e4-484c-9251-bcbc634219ee",
   "metadata": {},
   "source": [
    "# TiDB Cloud Growth Report for Marketing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99281150-c740-4ec3-8844-1634a1b5a14b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from users import load_users, User\n",
    "from datetime import datetime, timedelta, timezone\n",
    "from users import User\n",
    "from classify import flow, classify\n",
    "import pandas as pd\n",
    "\n",
    "begin = datetime.now(timezone.utc) - timedelta(days=60)\n",
    "users = load_users(begin)\n",
    "users = [u for u in users if not u.is_pingcap() and u.signup_time() > begin]\n",
    "\n",
    "md(f'After **{begin.date()}**, there are **{len(users)}** users signup')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e63c377d-6372-48fa-86b5-576d848e1cc3",
   "metadata": {},
   "source": [
    "## Overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2056b6be-352c-407b-a77b-5a83e22c5f1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records({\n",
    "    'class': classify(u),\n",
    "    'region': u.region(),\n",
    "} for u in users)\n",
    "s = df.groupby(['class', 'region']).size().to_frame('ratio').reset_index().pivot(index='region', columns='class').rename(columns={\n",
    "    'first class': '1.first class', \n",
    "    'second class': '2.second class', \n",
    "    'third class': '3.third class', \n",
    "    'others': '4.others',\n",
    "})\n",
    "z = df.groupby('region').size()\n",
    "df = s.T.div(z).sort_index().T.copy()\n",
    "df['sum'] = s.T.sum()\n",
    "df.style.format(dict([(c, \"{:.2%}\") for c in df.columns if c[0] != 'sum']))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bbce7cb-cf4d-4823-aa08-125b8f0d7cd3",
   "metadata": {},
   "source": [
    "## NA Region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "086eb663-6efc-4d4b-89e2-709775a41480",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_users = [u for u in users if u.region() == \"NA\"]\n",
    "\n",
    "md(f'''\n",
    "Of all users, **{len(us_users)}** of them are from NA\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e7f016a-ee4d-47af-bcac-851fd52567ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['first class', 'second class', 'third class', 'others']\n",
    "df = flow(us_users, begin, datetime.now(timezone.utc), 7)\n",
    "ax = df[columns].groupby(pd.Grouper(freq='W-MON')).sum().plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='four class traffic for NA users')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff855805-8435-4e88-aaad-ea947fb1d0ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "md(f'''\n",
    "- The positive number of each class means users of that class increased that week\n",
    "- The negative number of each class means users of that class decreased that week\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f07af5da-e3be-43f7-82f5-0079a37130c6",
   "metadata": {},
   "source": [
    "## Japan Region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "232752b8-ab2d-4d73-9037-c0d1d8f23c51",
   "metadata": {},
   "outputs": [],
   "source": [
    "jp_users = [u for u in users if u.region() == \"Japan\"]\n",
    "\n",
    "md(f'Of all users, **{len(jp_users)}** of them are from Japan')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cf644d0-ab97-44f4-9c39-af88c1f60ccc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = flow(jp_users, begin, datetime.now(timezone.utc), 7)\n",
    "ax = df[columns].groupby(pd.Grouper(freq='W-MON')).sum().plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='four class traffic for Japan users')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1af165b2-5731-40da-b431-78d95fae43c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "md(f'''\n",
    "- The positive number of each class means users of that class increased that week\n",
    "- The negative number of each class means users of that class decreased that week\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f57eb8e6-faf3-4b12-ba71-d83249e8c8b8",
   "metadata": {},
   "source": [
    "## APAC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30109ff4-64f5-4e3e-b2af-f9d2e9e9a9c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "apac_users = [u for u in users if u.region() == \"APAC\"]\n",
    "\n",
    "md(f'''\n",
    "Of all users, **{len(apac_users)}** of them are from APAC\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ea15d5a-2d5f-491b-8366-6200385f80e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = flow(apac_users, begin, datetime.now(timezone.utc), 7)\n",
    "ax = df[columns].groupby(pd.Grouper(freq='W-MON')).sum().plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='four class traffic for APAC users')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35bbd6b3-4cfd-49c7-8e3a-58da6aff04cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "md(f'''\n",
    "- The positive number of each class means users of that class increased that week\n",
    "- The negative number of each class means users of that class decreased that week\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b735d4b-eacb-4230-8787-ce8034ea3cd7",
   "metadata": {},
   "source": [
    "## EMEA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d50bff55-e194-4f7a-bb23-97166a84a070",
   "metadata": {},
   "outputs": [],
   "source": [
    "emea_users = [u for u in users if u.region() == \"EMEA\"]\n",
    "\n",
    "md(f'''\n",
    "Of all users, **{len(emea_users)}** of them are from EMEA\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0925f60f-b5a0-40e5-b172-a67ca864c1e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = flow(emea_users, begin, datetime.now(timezone.utc), 7)\n",
    "ax = df[columns].groupby(pd.Grouper(freq='W-MON')).sum().plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='four class traffic for APAC users')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67952b2d-ab23-419b-b11e-15d17d3d5e1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "md(f'''\n",
    "- The positive number of each class means users of that class increased that week\n",
    "- The negative number of each class means users of that class decreased that week\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03b9a117-90b5-4609-ac34-adc4421d5026",
   "metadata": {},
   "source": [
    "## China Outbound"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd7adc99-a1cb-41d9-9872-718f91397703",
   "metadata": {},
   "outputs": [],
   "source": [
    "co_users = [u for u in users if u.region() == \"China Outbound\"]\n",
    "\n",
    "md(f'''\n",
    "Of all users, **{len(co_users)}** of them are from CO\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13fa65ff-9232-44e7-b751-16ae6df5ed31",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = flow(co_users, begin, datetime.now(timezone.utc), 7)\n",
    "ax = df[columns].groupby(pd.Grouper(freq='W-MON')).sum().plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='four class traffic for APAC users')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70844297-8b40-443d-80e6-fc22ea2708bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "md(f'''\n",
    "- The positive number of each class means users of that class increased that week\n",
    "- The negative number of each class means users of that class decreased that week\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
}