{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c348fa71-1b06-4b1c-8fe1-79302d6e6a43",
   "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": "a2bd53f3-daba-43c4-951a-8cf9ebce644a",
   "metadata": {},
   "source": [
    "# TiDB Cloud User Experience Metrics Product Report\n",
    "\n",
    "- [Pain point 1: Not good signup experience cause few signup users](#Pain-point-1:-Not-good-signup-experience-cause-few-signup-users)\n",
    "  - [Daily SignUp Users](#Daily-SignUp-Users)\n",
    "- [Pain Point 2: Cluster creation procedure is complex](#Pain-Point-2:-Cluster-creation-procedure-is-complex)\n",
    "  - [1. Cluster Created Number](#1.-Cluster-Created-Number)\n",
    "  - [2. dev tier cluster creation ratio](#2.-dev-tier-cluster-creation-ratio)\n",
    "  - [3. patience of the user who leave before cluster completely create](#3.-patience-of-the-user-who-leave-before-cluster-completely-create)\n",
    "- [Pain Point 3: Low ratio of user who connnect to dev tier cluster](#Pain-Point-3:-Low-ratio-of-user-who-connnect-to-dev-tier-cluster)\n",
    "  - [1. Cluster Overview Page Entrance Rate](#1.-Cluster-Overview-Page-Entrance-Rate)\n",
    "  - [2. connect button click rate](#2.-connect-button-click-rate)\n",
    "  - [3. DB connect rate](#3.-DB-connect-rate)\n",
    "  - [4. ratio of user who click web shell and connect](#4.-ratio-of-user-who-click-web-shell-and-connect)\n",
    "  - [5. ratio of user who click mysql copy button and connect](#5.-ratio-of-user-who-click-mysql-copy-button-and-connect)\n",
    "  - [6. user need to reset password before connect](#6.-user-need-to-reset-password-before-connect)\n",
    "- [Paint Point 4: Web shell experience is not good](#Paint-Point-4:-Web-shell-experience-is-not-good)\n",
    "  - [1. open web shell importance analysis](#1.-open-web-shell-importance-analysis)\n",
    "  - [2. open web shell error rate](#2.-open-web-shell-error-rate)\n",
    "  - [3. open web shell error message group](#3.-open-web-shell-error-message-group)\n",
    "  - [4. clicks to web shell (p50)](#4.-clicks-to-web-shell-(p50))\n",
    "- [Paint point 5: Few remaining users](#Paint-point-5:-Few-remaining-users)\n",
    "  - [1. event ratio distribution](#1.-event-ratio-distribution)\n",
    "  - [2. error number distribution](#2.-error-number-distribution)\n",
    "  - [3. connection number distribution](#3.-connection-number-distribution)\n",
    "  - [4. DBaaS alive distribution](#4.-DBaaS-alive-distribution)\n",
    "  - [5. Cluster alive distribution](#5.-Cluster-alive-distribution)\n",
    "- [Paint point 6: Dev tier cluster cost](#Paint-point-6:-Dev-tier-cluster-cost)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b812118f-9f62-4669-bad2-d7658848d4b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from users import load_users, User\n",
    "from datetime import timedelta, date, datetime, timezone\n",
    "\n",
    "begin = datetime(2022, 4, 1, tzinfo=timezone.utc)\n",
    "users = load_users(begin)\n",
    "users = [u for u in users if u.signup_time() >= begin and len(u.emails()) > 0 and not u.is_pingcap() and not u.has_poc()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59fbd3dd-0491-4f06-8b17-0d07341e37ae",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Pain point 1: Not good signup experience cause few signup users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa7035a3-a6d8-46d4-b577-44a8e44a1dce",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Daily SignUp Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf74347e-e589-4153-8f02-c26cb6e78694",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import timezone\n",
    "\n",
    "columns = [\n",
    "    'id',\n",
    "    'time',\n",
    "]\n",
    "df = pd.DataFrame([[\n",
    "    u.id(), \n",
    "    u.signup_time(),\n",
    "] for u in users], columns=columns)\n",
    "\n",
    "event_df = users[0]._event_df\n",
    "non_sso = event_df[event_df.event == 'Create Account'].set_index('time').groupby(pd.Grouper(freq='D')).distinct_id.count()\n",
    "\n",
    "df = df.set_index('time').groupby(pd.Grouper(freq='D')).id.count().rolling(7, min_periods=1).mean().to_frame('signup')\n",
    "\n",
    "ax = df.plot(figsize=(15, 9))\n",
    "ax.set(title='Daily SignUp Users')\n",
    "\n",
    "ax.axvline(datetime(2022, 5, 13, tzinfo=timezone.utc), linestyle='--', color='r', label='Google SSO signup launch')\n",
    "ax.axvline(datetime(2022, 5, 18, tzinfo=timezone.utc), linestyle='--', color='g', label='Github SSO signup launch')\n",
    "ax.axvline(datetime(2022, 6, 7, tzinfo=timezone.utc), linestyle='--', color='b', label='Free trial signup launch')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71dd70a3-ba2b-4323-bb80-2900c38f3be0",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Pain Point 2: Cluster creation procedure is complex"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b5dd0a8-f22d-436b-acd8-890bb42fab6a",
   "metadata": {},
   "source": [
    "### 1. Cluster Created Number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdd74e28-092c-44a8-8e2c-1a2eaa04e1ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_df = users[0]._cluster_df\n",
    "cluster_df = cluster_df[cluster_df.tenant_id.isin([u.id() for u in users])]\n",
    "count_df = cluster_df.set_index('created_at').groupby(pd.Grouper(freq='D')).id.count()\n",
    "count_df = count_df.rolling(7, min_periods=1).mean().to_frame('count')\n",
    "ax = count_df.plot(figsize=(15, 9))\n",
    "ax.set(title='Cluster Created Number')\n",
    "\n",
    "ax.axvline(datetime(2022, 6, 16, tzinfo=timezone.utc), linestyle='--', color='r', label='Simplify the cluster creation process')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff99e923-7501-4e26-a2a9-5140904fdd9f",
   "metadata": {},
   "source": [
    "### 2. dev tier cluster creation ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d79e0634-7897-4813-adb5-37bf90d8a7bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\n",
    "    'id',\n",
    "    'time',\n",
    "]\n",
    "df = pd.DataFrame([[\n",
    "    u.id(), \n",
    "    u.signup_time(),\n",
    "] for u in users], columns=columns)\n",
    "\n",
    "df = df.set_index('time').groupby(pd.Grouper(freq='W-MON')).id.count().to_frame('signup')\n",
    "\n",
    "cluster_df = users[0]._cluster_df\n",
    "cluster_df[cluster_df.tenant_id.isin([u.id() for u in users])]\n",
    "cluster_df = cluster_df[cluster_df.tenant_id.isin([u.id() for u in users])]\n",
    "df['cluster created'] = cluster_df.set_index('created_at').groupby(pd.Grouper(freq='W-MON')).tenant_id.nunique()\n",
    "\n",
    "df.plot(kind='bar', figsize=(15, 9)).set(title='user signup vs. cluster created')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d85cc5e-3b1c-4ee0-8c5c-7758e7ed9b53",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ratio'] = df['cluster created'] / df['signup']\n",
    "ax = df.plot(y='ratio', figsize=(15, 9))\n",
    "ax.set(title='dev tier cluster creation ratio')\n",
    "\n",
    "ax.axvline(datetime(2022, 6, 16, tzinfo=timezone.utc), linestyle='--', color='r', label='Simplify the cluster creation process')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "885d467d-9a64-4ef7-9535-9729dd04ada9",
   "metadata": {},
   "source": [
    "### 3. patience of the user who leave before cluster completely create"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "354f8dc4-0013-412b-83ad-b19cb3dab806",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "\n",
    "xs = [u for u in users if u.has_create_cluster() and len(u.events()) > 0 and u.events().time.iloc[-1] < u.first_cluster_ready_time()]\n",
    "\n",
    "df = pd.DataFrame.from_records([{\n",
    "    'wait seconds': (u.events().time.iloc[-1] - u.first_cluster_create_time()).total_seconds()\n",
    "} for u in xs])\n",
    "\n",
    "sns.displot(data=df[df['wait seconds'] > 0], y=\"wait seconds\", kind=\"ecdf\").set(title='wait time of the user who leave before cluster create completely', yscale=\"log\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5274dace-900b-4145-b42a-d4e0914416a9",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Pain Point 3: Low ratio of user who connnect to dev tier cluster"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46460099-1ecb-4c11-abbd-bacfbcc2acda",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 1. Cluster Overview Page Entrance Rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfcd94bf-ebd5-4af6-9d2c-04fce3629cc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "columns = [\n",
    "    'id',\n",
    "    'create_time',\n",
    "    'page_time',\n",
    "]\n",
    "df = pd.DataFrame([[\n",
    "    u.id(), \n",
    "    u.create_first_cluster_time()[1],\n",
    "    u.first_event('Cluster Overview Page View')[1],\n",
    "] for u in users if u.create_cluster_count() > 0], columns=columns)\n",
    "\n",
    "adf = df.set_index('create_time').groupby(pd.Grouper(freq='D')).count().rename(columns={\"id\": \"aid\"})\n",
    "cdf = df.loc[df.page_time - df.create_time < timedelta(days=1)].set_index('create_time').groupby(pd.Grouper(freq='D')).count().rename(columns={\"id\": \"cid\"})\n",
    "df = pd.concat([adf, cdf], axis=1, join='outer')\n",
    "df['ratio'] = (df.cid.fillna(0) / df.aid).rolling(7, min_periods=1).mean()\n",
    "\n",
    "ax = df[['ratio']].plot(figsize=(15, 9))\n",
    "ax.set(title='Cluster Overview Page Entrance Rate')\n",
    "\n",
    "ax.axvline(datetime(2022, 7, 1, tzinfo=timezone.utc), linestyle='--', color='r', label='a couple of features to optimize connection experience')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='highlight the position of connect / import entrance button in the console')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88dd0ce5-710d-420a-8414-8a888c0dbcd0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2. connect button click rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "386fc6cc-b717-42ab-bec5-c15ac8672a9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "columns = [\n",
    "    'id',\n",
    "    'create_time',\n",
    "    'click_time',\n",
    "]\n",
    "df = pd.DataFrame([[\n",
    "    u.id(), \n",
    "    u.create_first_cluster_time()[1], \n",
    "    u.first_event('Connect Button Clicked')[1],\n",
    "] for u in users if u.create_cluster_count() > 0], columns=columns)\n",
    "\n",
    "adf = df.set_index('create_time').groupby(pd.Grouper(freq='D')).count().rename(columns={\"id\": \"aid\"})\n",
    "cdf = df.loc[df.click_time - df.create_time < timedelta(days=1)].set_index('create_time').groupby(pd.Grouper(freq='D')).count().rename(columns={\"id\": \"cid\"})\n",
    "df = pd.concat([adf, cdf], axis=1, join='outer')\n",
    "df['ratio'] = (df.cid.fillna(0) / df.aid).rolling(7, min_periods=1).mean()\n",
    "\n",
    "ax = df[['ratio']].plot(figsize=(15, 9))\n",
    "ax.set(title=\"Ratio of user who click connect button\")\n",
    "\n",
    "ax.axvline(datetime(2022, 7, 1, tzinfo=timezone.utc), linestyle='--', color='r', label='a couple of features to optimize connection experience')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='highlight the position of connect / import entrance button in the console')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f037f45-a213-4154-80a2-c879ef10cce3",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3. DB connect rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3a644a7-0393-481c-9d24-17b477c43b05",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'time': u.signup_time(),\n",
    "    'connect': u.max_connection() > 0,\n",
    "} for u in users if u.signup_time() > datetime(2022, 5, 25, tzinfo=timezone.utc) and u.has_create_cluster()])\n",
    "\n",
    "df = df[df.connect].set_index('time').groupby(pd.Grouper(freq='D')).connect.count() / df.set_index('time').groupby(pd.Grouper(freq='D')).connect.count()\n",
    "ax = df.rename('connect ratio').rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.set_title('ratio of users who connect cluster')\n",
    "\n",
    "ax.axvline(datetime(2022, 7, 1, tzinfo=timezone.utc), linestyle='--', color='r', label='a couple of features to optimize connection experience')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='highlight the position of connect / import entrance button in the console')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6e382d2-d825-44e0-8883-34979b62b7b8",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4. ratio of user who click web shell and connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6db3b44-c2c8-4780-94ee-aa744dcb8574",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'time': u.signup_time(),\n",
    "    'connection': u.max_connection(),\n",
    "} for u in users if u.count_event('Open SQL Shell Button Clicked') > 0]).set_index('time')\n",
    "\n",
    "ax = (df[df.connection > 0].groupby(pd.Grouper(freq='D')).connection.count()\n",
    "    /\n",
    "df.groupby(pd.Grouper(freq='D')).connection.count()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.axvline(datetime(2022, 7, 1, tzinfo=timezone.utc), linestyle='--', color='r', label='a couple of features to optimize connection experience')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='highlight the position of connect / import entrance button in the console')\n",
    "ax.set(title='ratio of user who click web shell and connect')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b612bf26-0174-40a6-8685-a64c6dc101b2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5. ratio of user who click mysql copy button and connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e874e1dc-5946-4d5f-9a1c-48608071feae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'time': u.signup_time(),\n",
    "    'connection': u.max_connection(),\n",
    "} for u in users if u.count_event('MySQL-Copy Button Clicked') > 0]).set_index('time')\n",
    "df = df[df.index > '2022-07-18']\n",
    "\n",
    "ax = (df[df.connection > 0].groupby(pd.Grouper(freq='D')).connection.count()\n",
    "    /\n",
    "df.groupby(pd.Grouper(freq='D')).connection.count()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.axvline(datetime(2022, 7, 1, tzinfo=timezone.utc), linestyle='--', color='r', label='a couple of features to optimize connection experience')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='highlight the position of connect / import entrance button in the console')\n",
    "ax.set(title='ratio of user who click mysql copy button and connect')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81a4a533-30a9-4cdf-8304-a2a4cc61f231",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 6. user need to reset password before connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47be4914-8464-4505-8f2a-d917d022f417",
   "metadata": {},
   "outputs": [],
   "source": [
    "user_df = users[0]._user_df\n",
    "user_df = user_df[user_df.tenant_id.isin([u.id() for u in users])]\n",
    "event_df = users[0]._event_df\n",
    "event_df = event_df[event_df['$user_id'].isin(user_df.id)]\n",
    "ax = (event_df[event_df.event == 'Generate Root Password Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].count()\n",
    "    /\n",
    "event_df[event_df.event == 'Generate Root Password Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].nunique()).plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='password reset count per user who reset password')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e33caf76-66f0-4c4e-959a-e90285468243",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Paint Point 4: Web shell experience is not good"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f8cfa9d-70b5-4007-97a1-f45f793b78e4",
   "metadata": {},
   "source": [
    "### 1. open web shell importance analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5a993a6-7dd8-4be1-863e-b16373364851",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.DataFrame()\n",
    "\n",
    "for u in filter(lambda u: u.signup_time() > datetime(2022, 7, 19, tzinfo=timezone.utc), users):\n",
    "    event_df = pd.concat([event_df, u.events()])\n",
    "\n",
    "df = event_df[event_df.event == 'Open SQL Shell Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].nunique().rolling(7, min_periods=1).mean().to_frame('Open Web Shell')\n",
    "df = df.join(event_df[event_df.event == 'MySQL-Copy Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].nunique().rolling(7, min_periods=1).mean().to_frame('MySQL Copy'), how='outer')\n",
    "df.plot(figsize=(15, 9)).set_title('Open Web Shell vs. String Connection Copy')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b85bd552-815a-48b4-a1b1-05b80fb98f81",
   "metadata": {},
   "source": [
    "### 2. open web shell error rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3eb98c56-dbdd-465e-ac7c-d182afb9fe63",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sentry_df = pd.DataFrame()\n",
    "    \n",
    "for u in filter(lambda u: u.signup_time() > datetime(2022, 7, 19, tzinfo=timezone.utc), users):\n",
    "    sentry_df = pd.concat([sentry_df, u.errors()])\n",
    "    \n",
    "df = sentry_df[sentry_df.title == 'load webshell error'].set_index('time').groupby(pd.Grouper(freq='D')).user_id.count().to_frame('Web Shell Error')\n",
    "df = df.join(event_df[event_df.event == 'Open SQL Shell Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].count().to_frame('Web Shell Opened'), how='outer')\n",
    "\n",
    "ax = df.fillna(0).plot(kind='bar', figsize=(15, 9))\n",
    "ax.set(title='open web shell vs. error')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e02032c-6ae8-4be8-8521-7db8124c8096",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ratio'] = (df['Web Shell Error'] / df['Web Shell Opened']).fillna(0)\n",
    "\n",
    "df.plot(y='ratio', secondary_y=True, figsize=(15, 9)).set(title='open web shell error rate')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f1c0ea1-7187-4f84-ac50-e7f7f154c6f5",
   "metadata": {},
   "source": [
    "### 3. open web shell error message group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c199f393-0838-49b8-b50b-aea47145c17c",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context(\"display.max_colwidth\", None):\n",
    "    display(sentry_df[sentry_df.title.str.contains('load webshell error')].groupby('message').message.count().to_frame('error count').reset_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ad825ee-2922-4cbf-b466-f4885dbc94fc",
   "metadata": {},
   "source": [
    "### 4. clicks to web shell (p50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f240a209-84d1-47b2-8eb4-119b111c6aab",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'time': u.signup_time(),\n",
    "    'clicks': u.first_event('Open SQL Shell Button Clicked')[0],\n",
    "} for u in users if u.count_event('Open SQL Shell Button Clicked') > 0])\n",
    "\n",
    "ax = df.set_index('time').groupby(pd.Grouper(freq='D')).clicks.median().rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='highlight the position of connect / import entrance button in the console')\n",
    "ax.set(title='clicks before open webshell (p50)')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd7834bb-413e-4de8-977b-88278bda96b8",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Paint point 5: Few remaining users "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2309d2f9-d438-4792-ae20-e1346217181f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def active(u):\n",
    "    return len(u.events()) > 0 and u.events().iloc[-1].time > datetime.now() - timedelta(days=7)\n",
    "\n",
    "def hibernated(u):\n",
    "    return len(u._cluster_df[(u._cluster_df.tenant_id == u.id()) & (u._cluster_df.status == 'hibernated') & (u._cluster_df.updated_at < datetime.now(timezone.utc) - timedelta(days=7))]) > 0\n",
    "\n",
    "def classify(u):\n",
    "    if u.has_cluster() and hibernated(u):\n",
    "        return 'hibernated for 7 days'\n",
    "    if not u.has_cluster() and u.count_event('Deleted A Cluster') > 0:\n",
    "        return 'proactively delete cluster'\n",
    "    if u.has_cluster() and not hibernated(u) and u.signup_time() < datetime.now(timezone.utc) - timedelta(days=8):\n",
    "        return 'active in 7 days'\n",
    "\n",
    "start_date = datetime.now(timezone.utc) - timedelta(30)\n",
    "\n",
    "users = list(filter(lambda u: u.signup_time() > start_date, users))\n",
    "\n",
    "md(f'''\n",
    "There are {len(users)} users signup after {start_date.date()}:\n",
    "- {len([u for u in users if classify(u) == 'hibernated for 7 days'])} of them hibernated (cluster with no connection for 7 days)\n",
    "- {len([u for u in users if classify(u) == 'proactively delete cluster'])} of them proactively delete cluster\n",
    "- {len([u for u in users if classify(u) == 'active in 7 days'])} still active (signup 8 days ago and has activity in 7 days)\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86c50882-3aa5-4124-a45a-c1f600f81646",
   "metadata": {},
   "source": [
    "### 1. event ratio distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efa26dd9-ad7e-4100-96f6-41c725eb5bc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "\n",
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'ratio': u.event_ratio(),\n",
    "    'class': classify(u),\n",
    "} for u in users])\n",
    "\n",
    "sns.displot(data=df, y=\"ratio\", hue='class', kind=\"ecdf\").set(title='event ratio distribution')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4df6103-c475-43e1-a984-4d730c2c5ce1",
   "metadata": {},
   "source": [
    "### 2. error number distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d276c35-87ed-41c5-be78-28a5e00d4676",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'error': len(u.errors()),\n",
    "    'class': classify(u),\n",
    "} for u in users])\n",
    "\n",
    "sns.displot(data=df, y=\"error\", hue='class', kind=\"ecdf\").set(title='error number distribution')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfa89b56-096f-45ed-9bae-af2460fd4239",
   "metadata": {},
   "source": [
    "### 3. connection number distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "278b17a0-9e95-41dd-9f33-1cc938fd9a32",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'connection': u.max_connection(),\n",
    "    'class': classify(u),\n",
    "} for u in users])\n",
    "\n",
    "sns.displot(data=df, y=\"connection\", hue='class', kind=\"ecdf\").set(title='connection number distribution', yscale=\"log\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b06e4fa5-2f51-4b43-b1b2-5c1497c4b7a4",
   "metadata": {},
   "source": [
    "### 4. DBaaS alive distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efec8a21-cf82-43f5-ad1f-c7594c64bec2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'alive': u.alive().total_seconds() / 24 / 60 / 60,\n",
    "    'class': classify(u),\n",
    "} for u in users])\n",
    "\n",
    "sns.displot(data=df, y=\"alive\", hue='class', kind=\"ecdf\").set(title='DBaaS alive distribution (days)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "698f4aeb-0884-41dc-a847-578f537b6b41",
   "metadata": {},
   "source": [
    "### 5. Cluster alive distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cce00ec-f8cb-4436-a41b-b43e62ea09b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'alive': u.clusters_alive().total_seconds() / 24 / 60 / 60,\n",
    "    'class': classify(u),\n",
    "} for u in users])\n",
    "\n",
    "sns.displot(data=df, y=\"alive\", hue='class', kind=\"ecdf\").set(title='cluster alive distribution (days)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8aa53aa9-0a11-46cc-b8a6-67a8c8f0b47c",
   "metadata": {},
   "source": [
    "## Paint point 6: Dev tier cluster cost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a037bad6-ac55-490e-b246-27017b7c375d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "\n",
    "s3 = boto3.client('s3')\n",
    "df = pd.read_csv(s3.get_object(Bucket='prod-devtier-billing', Key='prod-devtier-daily-billing-finance.csv')['Body'])\n",
    "ax = df.set_index(pd.to_datetime(df.day, utc=True))[['cost/total_cls']].plot(figsize=(15, 9))\n",
    "ax.set(title='cost per devtier cluster')\n",
    "ax.axvline(datetime(2022, 7, 26, tzinfo=timezone.utc), linestyle='--', color='r', label='hibernate idle clusters')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f565869c-85ba-408c-9c10-8790cf6f0a06",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a392894d-b45f-4f39-b38b-c95325acfecc",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}