{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9db67095-91b2-4846-8511-6f73a0391ed8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import boto3\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown as md\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "from users import load_users, User, dbaas_cluster_usedsize, dbaas_paied_cluster_ids\n",
    "from datetime import timedelta, date, datetime, timezone\n",
    "\n",
    "os.environ[\"S3_BUCKET\"] = \"pingcap-plg\" \n",
    "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"/home/ubuntu/gcpbigquery.json\"\n",
    "os.environ[\"CLINIC_API_KEY\"] = os.getenv(\"NOTEBOOK_CLINIC_API_KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f2f7496-3878-4138-b4c5-d8e06bfb7cdd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "begin = datetime.now(timezone.utc) - timedelta(days=30)\n",
    "md(f'''\n",
    "report generate time: **{datetime.now().date()}**\n",
    "\n",
    "only include tenants signup after **{begin.date()}**\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "586e399a-b80a-4ba5-845b-e4f46f77b188",
   "metadata": {},
   "source": [
    "- [Overall Business Metrics](#Overall-Business-Metrics)\n",
    "  - [Weekly Signup / Signin User](#Weekly-Signup-/-Signin-User)\n",
    "  - [Weekly Active User Count](#Weekly-Active-User-Count)\n",
    "  - [Daily Serverless users](#Daily-Serverless-users)\n",
    "  - [Weekly Active Serverless Users](#Weekly-Active-Serverless-Users)\n",
    "  - [Weekly Serverless Clusters' User Storage Size](#Weekly-Serverless-Clusters'-User-Storage-Size)\n",
    "  - [Weekly Dedicated tier Users Count](#Weekly-Dedicated-tier-Users-Count)\n",
    "  - [Weekly Dedicated tier Clusters Count](#Weekly-Dedicated-tier-Clusters-Count)\n",
    "  - [Weekly Dedicated tier users who delete his last dedicated cluster](#Weekly-Dedicated-tier-users-who-delete-his-last-dedicated-cluster)\n",
    "  - [Details of dedicated users this week](#Details-of-dedicated-users-this-week)\n",
    "  - [Weekly PoC submission Numbers](#Weekly-PoC-submission-Numbers)\n",
    "  - [Weekly User numbers of being billing capable](#Weekly-User-numbers-of-being-billing-capable)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f55dad8a-e76c-4564-acff-2456bf839893",
   "metadata": {},
   "source": [
    "## Overall Business Metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fceedef-b979-44c4-bb25-cb11750f09cb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "users = load_users(begin)\n",
    "all_users = [u for u in users if not u.is_pingcap()]\n",
    "\n",
    "users = all_users\n",
    "event_df = pd.concat([u.events() for u in users])\n",
    "event_df = event_df[event_df.time < datetime.now().strftime('%Y-%m-%d')]\n",
    "cluster_df = pd.concat([u.clusters() for u in all_users])\n",
    "gw_access_df = users[0]._gw_access_df\n",
    "gw_access_df = gw_access_df[gw_access_df.cluster_id.isin(cluster_df.id)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec87b9e9",
   "metadata": {},
   "source": [
    "### AI Generated SQL Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3f3ec96-c742-412e-843c-fc75fb793243",
   "metadata": {},
   "outputs": [],
   "source": [
    "TZ = timezone(timedelta(hours=8))\n",
    "def has_asked_question(u):\n",
    "    if u.events().empty:\n",
    "        return False\n",
    "    # After 2023-01-30, the backend uploads oplog which is much preciser \n",
    "    if u.signup_time().astimezone(TZ).date() < datetime.strptime(\"2023-01-30 00:00:00\", \"%Y-%m-%d %H:%M:%S\").astimezone(TZ).date():\n",
    "        return u.count_event(\"SQL Editor Generate AI\") > 0 and (u.first_event(\"SQL Editor Generate AI\")[1] - u.signup_time() < timedelta(days=1))\n",
    "    else:\n",
    "        chat2query_info = u.sql_editor_info().sort_values(\"created_at\")\n",
    "        if chat2query_info.chat2query_sid.nunique():      \n",
    "            return chat2query_info.created_at.iloc[0] - u.signup_time() < timedelta(days=1)\n",
    "\n",
    "    return False\n",
    "\n",
    "def has_accepted_ai_result(u):\n",
    "    if u.events().empty:\n",
    "        return False\n",
    "    # Before 2023-01-30, no way to know if accepted\n",
    "    if u.signup_time().astimezone(TZ).date() < datetime.strptime(\"2023-01-30 00:00:00\", \"%Y-%m-%d %H:%M:%S\").astimezone(TZ).date():\n",
    "        return has_asked_question(u)\n",
    "    else:\n",
    "        chat2query_info = u.sql_editor_info().sort_values(\"created_at\")\n",
    "        for _, info in chat2query_info[[\"is_accept\", \"created_at\"]].iterrows():\n",
    "            if info.is_accept == 1:\n",
    "                if info.created_at - u.signup_time() < timedelta(days=1):\n",
    "                        return True\n",
    "    \n",
    "    return False\n",
    "\n",
    "def has_executed_ai_result(u):\n",
    "    if not has_accepted_ai_result(u):\n",
    "        return False\n",
    "    \n",
    "    # After 2023-01-30, the backend uploads oplog which is much preciser\n",
    "    if u.signup_time().astimezone(TZ).date() < datetime.strptime(\"2023-01-30 00:00:00\", \"%Y-%m-%d %H:%M:%S\").astimezone(TZ).date():\n",
    "        return u.count_event(\"SQL Editor Generate AI\") > 0 and u.count_event(\"SQL Editor Executed\") > 0 and\\\n",
    "              (u.first_event(\"SQL Editor Generate AI\")[1] - u.signup_time() < timedelta(days=1)) and\\\n",
    "              (u.first_event(\"SQL Editor Executed\")[1] - u.signup_time() < timedelta(days=1)) and\\\n",
    "              (u.executed_ai_generated_sql_count() > 0)\n",
    "    else:\n",
    "        chat2query_info = u.sql_editor_info().sort_values(\"created_at\")\n",
    "        for _, info in chat2query_info[[\"event_type\", \"chat2query_sid\", \"created_at\"]].iterrows():\n",
    "            if info.event_type == 'exec_sql' and info.chat2query_sid:\n",
    "                if info.created_at - u.signup_time() < timedelta(days=1):\n",
    "                    return True\n",
    "    return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c78b6f85",
   "metadata": {},
   "outputs": [],
   "source": [
    "display_events = [\"Create Account\", \"Tracing Users\", \"Create Cluster\", \"Enter Chat2query\", \"Ask Question\", \"Accept AI Result\", \"Execute AI Result\"]\n",
    "df = pd.DataFrame()\n",
    "mf = pd.DataFrame.from_records([{\n",
    "    'email': u.email(),\n",
    "    'time': u.signup_time().astimezone(TZ),\n",
    "    'is_create_account': True,\n",
    "    'is_tracing_users': not u.events().empty,\n",
    "    'is_create_cluster': (not u.events().empty) and u.has_create_cluster(),\n",
    "    'is_enter_chat2query': (u.count_event(\"Chat 2 Query Page View\") > 0) and (u.first_event(\"Chat 2 Query Page View\")[1] - u.signup_time() < timedelta(days=1)),\n",
    "    'is_ai_generated_sql': u.count_event(\"SQL Editor Generate AI\") > 0 and (u.first_event(\"SQL Editor Generate AI\")[1] - u.signup_time() < timedelta(days=1)),\n",
    "    'is_execute_sql': u.count_event(\"SQL Editor Generate AI\") > 0 and u.count_event(\"SQL Editor Executed\") > 0 and \n",
    "                     (u.first_event(\"SQL Editor Generate AI\")[1] - u.signup_time() < timedelta(days=1)) and \n",
    "                     (u.first_event(\"SQL Editor Executed\")[1] - u.signup_time() < timedelta(days=1)) and\n",
    "                     (u.executed_ai_generated_sql_count() > 0),\n",
    "    'ask_question': has_asked_question(u),\n",
    "    'accept_ai_result': has_accepted_ai_result(u),\n",
    "    'execute_ai_result': has_executed_ai_result(u),\n",
    "} for u in users if u.signup_time().astimezone(TZ).date() >= begin.date()])\n",
    "\n",
    "df[\"Create Account\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).is_create_account.sum()\n",
    "df[\"Tracing Users\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).is_tracing_users.sum()\n",
    "df[\"Create Cluster\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).is_create_cluster.sum()\n",
    "df[\"Enter Chat2query\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).is_enter_chat2query.sum()\n",
    "df[\"Ask Question\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).ask_question.sum()\n",
    "df[\"Accept AI Result\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).accept_ai_result.sum()\n",
    "df[\"Execute AI Result\"] = mf.set_index('time').groupby(pd.Grouper(freq='1D')).execute_ai_result.sum()\n",
    "df[\"Create Cluster Ratio\"] = (df[\"Create Cluster\"] / df[\"Tracing Users\"]).map(lambda x: '%.0f%%' % (x * 100))\n",
    "df[\"Enter Chat2query Ratio\"] = (df[\"Enter Chat2query\"] / df[\"Create Cluster\"]).map(lambda x: '%.0f%%' % (x * 100))\n",
    "df[\"Ask Question Ratio\"] = (df[\"Ask Question\"] / df[\"Enter Chat2query\"]).map(lambda x: '%.0f%%' % (x * 100))\n",
    "df[\"Accept AI Result Ratio\"] = (df[\"Accept AI Result\"] / df[\"Ask Question\"]).map(lambda x: '%.0f%%' % (x * 100))\n",
    "df[\"Execute AI Result Ratio\"] = (df[\"Execute AI Result\"] / df[\"Accept AI Result\"]).map(lambda x: '%.0f%%' % (x * 100))\n",
    "\n",
    "ax = df[:-1][display_events].plot(figsize=(15, 9))\n",
    "ax.set_ylim(ymin=0)\n",
    "ax.set_ylabel('user count')\n",
    "ax.set(title='AI Generated SQL Analysis')\n",
    "ax.legend()\n",
    "df[\"Create Cluster\"] = df[\"Create Cluster\"].map(str) + \"(\" + df[\"Create Cluster Ratio\"] + \")\"\n",
    "df[\"Enter Chat2query\"] = df[\"Enter Chat2query\"].map(str) + \"(\" + df[\"Enter Chat2query Ratio\"] + \")\"\n",
    "df[\"Ask Question\"] = df[\"Ask Question\"].map(str) + \"(\" + df[\"Ask Question Ratio\"] + \")\"\n",
    "df[\"Accept AI Result\"] = df[\"Accept AI Result\"].map(str) + \"(\" + df[\"Accept AI Result Ratio\"] + \")\"\n",
    "df[\"Execute AI Result\"] = df[\"Execute AI Result\"].map(str) + \"(\" + df[\"Execute AI Result Ratio\"] + \")\"\n",
    "# Before 2023-01-30, no way to know if accepted\n",
    "for i, r in df.iterrows():\n",
    "    if i.astimezone(TZ).date() < datetime.strptime(\"2023-01-30 00:00:00\", \"%Y-%m-%d %H:%M:%S\").astimezone(TZ).date():\n",
    "        df.loc[i, \"Accept AI Result\"] = np.nan\n",
    "\n",
    "df[-14:-1][display_events]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6f03af7-37c7-421f-9ab3-8f6acbe59407",
   "metadata": {},
   "source": [
    "### Weekly Signup / Signin User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15b19a49-359e-4ed8-86a0-c09df7170acc",
   "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 = pd.DataFrame({\n",
    "    'signin': event_df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique(),\n",
    "    'signup': df.set_index('time').groupby(pd.Grouper(freq='W-THU')).id.count(),\n",
    "}).fillna(0)\n",
    "\n",
    "ax = df[df.index > begin + timedelta(days=2)].plot(figsize=(15, 9))\n",
    "ax.set_ylim(ymin=0)\n",
    "ax.set_ylabel('user count')\n",
    "ax.set(title='User SignUp / SignIn Count')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e05d14ca-db09-486c-b80b-2efc8d6751cd",
   "metadata": {},
   "source": [
    "### Weekly Active User Count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f94321bd-ca1c-4f18-8972-c2d18b0e63ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "regions = ['APAC', 'China Outbound', 'EMEA', 'Japan', 'NA']\n",
    "df = pd.DataFrame()\n",
    "for region in regions:\n",
    "    ids = [int(m) for u in users for m in u.members() if u.region() == region]\n",
    "    df[region] = event_df[event_df['$user_id'].isin(ids)].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n",
    "ax = df.plot(figsize=(15, 9))\n",
    "ax.set(title='Weekly Active User Count by Region')\n",
    "ax.set_ylabel('user count')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "350756a3-2ffa-40ef-b532-26669a2fa542",
   "metadata": {},
   "source": [
    "### Daily Serverless users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "614ce590-0d7f-4967-b6b3-1f34c6fafb85",
   "metadata": {},
   "outputs": [],
   "source": [
    "def heartbeat(df_x):\n",
    "    df_x.loc[df_x.deleted_time.isna(), 'deleted_time'] = datetime.now(timezone.utc)\n",
    "    rg = pd.date_range(start=df_x.created_at.iloc[0], end=df_x.deleted_time.iloc[0], freq='D').to_frame(index=False, name='time')\n",
    "    df_y = rg.merge(df_x, how='cross')\n",
    "    return df_y[df_y.time < datetime.now(timezone.utc)]\n",
    "\n",
    "serverless_df = cluster_df[(cluster_df.deploy_type == 'shared')]\n",
    "cf = serverless_df.groupby('id').apply(heartbeat)\n",
    "\n",
    "# Drop the last imcomplete value\n",
    "ax = pd.DataFrame({\n",
    "    'alive users': cf.set_index('time').groupby(pd.Grouper(freq='D')).tenant_id.nunique(),\n",
    "})[:-1].plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='daily serverless users')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4d62109-edb6-4c34-a80b-33b629eb5d8f",
   "metadata": {},
   "source": [
    "### Weekly Active Serverless Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fff59b9-204c-444d-aa66-280712115b24",
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_df.id = cluster_df.id.astype(str)\n",
    "gw_access_df = users[0]._gw_access_df\n",
    "gw_access_df.cluster_id = gw_access_df.cluster_id.astype(str)\n",
    "gw_access_df = gw_access_df[gw_access_df.duration > 1]\n",
    "gw_access_df = gw_access_df[~gw_access_df.gateway_instance.str.contains('-inner-')]\n",
    "\n",
    "af = pd.merge(cluster_df[['id', 'tenant_id']], gw_access_df, left_on='id', right_on='cluster_id')\n",
    "\n",
    "ax = pd.DataFrame({\n",
    "    'active users': af.set_index(pd.to_datetime(af.ts, unit='s', utc=True)).groupby(pd.Grouper(freq='W-THU')).tenant_id.nunique(),\n",
    "}).plot(figsize=(15, 9))\n",
    "\n",
    "ax.set_ylim(ymin=0)\n",
    "ax.set(title='weekly active serverless users')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2408c3c6-7a99-4782-a6a1-018d6028f6c5",
   "metadata": {},
   "source": [
    "### Weekly Serverless tier Active User\n",
    "- Bar 1: weekly MAU who has access serverless tier in the last month\n",
    "- Bar 2: weekly WAU who has access serverless tier in the last week\n",
    "- Line 1: Weekly ratio of WAU to MAU"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b9281dd-6dc5-4d94-b497-5f6617c72fb1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# af = pd.merge(cluster_df, gw_access_df[~gw_access_df.gateway_instance.str.contains('-inner-')], left_on='id', right_on='cluster_id')\n",
    "# af = af.set_index(pd.to_datetime(af.ts, unit='s', utc=True)).sort_index()['tenant_id']\n",
    "cluster_df.id = cluster_df.id.astype(str)\n",
    "gw_access_df = users[0]._gw_access_df\n",
    "gw_access_df.cluster_id = gw_access_df.cluster_id.astype(str)\n",
    "gw_access_df = gw_access_df[gw_access_df.duration > 1]\n",
    "gw_access_df = gw_access_df[~gw_access_df.gateway_instance.str.contains('-inner-')]\n",
    "\n",
    "af = pd.merge(cluster_df[['id', 'tenant_id']], gw_access_df, left_on='id', right_on='cluster_id')\n",
    "af = af.set_index(pd.to_datetime(af.ts, unit='s', utc=True)).sort_index()['tenant_id']\n",
    "# Discretize af to prevent loss of precision when converting int64 to float64 while rolling()\n",
    "dic = {}\n",
    "count = 0\n",
    "for i, id in af.iteritems():\n",
    "    if id not in dic:\n",
    "        dic[id] = count\n",
    "        count += 1\n",
    "    af[i] = dic[id]\n",
    "    \n",
    "WAU = af.groupby(pd.Grouper(freq='W-THU')).nunique()\n",
    "MAU = af.rolling('30D').apply(lambda x: x.nunique()).groupby(pd.Grouper(freq='W-THU')).last()\n",
    "\n",
    "# Because the first three data less than a month, so they need to be dropped, this lineneed to be deleted in April 3 \n",
    "MAU = MAU[3:]\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'WAU': WAU,\n",
    "    'MAU': MAU,\n",
    "})\n",
    "\n",
    "ax = df.plot(kind='bar', width=0.2, figsize=(15, 9))\n",
    "ax.set_xticklabels(df.index.strftime('%m-%d'), rotation=90)\n",
    "ax.set_ylim(ymin=0)\n",
    "ax.set(title='weekly serverless tier active users')\n",
    "ax.legend()\n",
    "\n",
    "rax = pd.DataFrame({\n",
    "    'WAU/MAU': WAU / MAU,\n",
    "}).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n",
    "rax.set_ylabel('ratio')\n",
    "rax.set_ylim(ymin=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ef0e0b2-be59-4d19-afcd-d9ca1529c166",
   "metadata": {},
   "source": [
    "### Weekly Serverless Clusters' User Storage Size"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc7a488b-54dd-4ece-aada-defdb4563e2c",
   "metadata": {},
   "source": [
    "#### Total Storage Size\n",
    "\n",
    "Sum all cluster's storage whose tikv usage >= 800Mb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74230291-93c3-4cd7-80ca-f4ea4a54e6f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# cluster_usedsize_df = pd.concat([u.serverless_cluster_usedsize() for u in users])\n",
    "# df = cluster_usedsize_df[\n",
    "#     cluster_usedsize_df.tikv_size_used_bytes > 800 * 1024 * 1024\n",
    "# ].set_index('created_at').groupby([pd.Grouper(freq='W-THU'), 'cluster_id']).tikv_size_used_bytes.last().reset_index('cluster_id')\n",
    "# s = df.groupby(pd.Grouper(freq='W-THU')).tikv_size_used_bytes.sum()\n",
    "# z = cluster_usedsize_df[\n",
    "#     cluster_usedsize_df.tikv_size_used_bytes > 800 * 1024 * 1024\n",
    "# ].set_index('created_at').groupby(pd.Grouper(freq='W-THU')).cluster_id.nunique()\n",
    "# \n",
    "# ax = z.to_frame('user count').plot(kind='bar', width=0.2, figsize=(15, 9))\n",
    "# s.div(1024 * 1024 * 1024).reset_index(drop=True).to_frame('storage size').plot(secondary_y=True, ax=ax).set_ylabel('Storage (Gb)')\n",
    "# ax.set_xlabel('date')\n",
    "# ax.set_ylabel('User Count')\n",
    "# ax.set(title='TiDB Cloud Serverless Storage Size (total)')\n",
    "# _ = ax.set_xticklabels(z.index.strftime('%m-%d'), rotation=90)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceffb5e3-312d-4fa3-a78f-1c6e81d9af61",
   "metadata": {},
   "source": [
    "#### Avarage Storage Size\n",
    "\n",
    "Total Storage Size dived by cluster number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce348d2a-e615-47c6-a344-3c36c74d91e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ax = s.div(z).div(1024 * 1024 * 1024).plot(figsize=(15, 9))\n",
    "# ax.set_xlabel('date')\n",
    "# ax.set_ylabel('Storage (Gb)')\n",
    "# ax.set_ylim(ymin=0)\n",
    "# ax.set(title='TiDB Cloud Serverless Storage Size (average)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5b4bdd5-856f-461e-aa02-7f4a2609253d",
   "metadata": {},
   "source": [
    "### Weekly Dedicated tier Users Count\n",
    "\n",
    "Users who has dedicated clusters every week, include PoC users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5eb33a92-7b65-4266-b31f-cb06a613fb03",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dedicated_cluster_df = pd.concat([u.dedicate_clusters().assign(\n",
    "#     region=lambda _: u.region(), \n",
    "#     email=lambda _: u.email(),\n",
    "#     signup=lambda _: u.signup_time(),\n",
    "# ) for u in users])\n",
    "# dedicated_cluster_df = dedicated_cluster_df[dedicated_cluster_df.id.astype(str).isin(dbaas_paied_cluster_ids().cluster_id.astype(str))]\n",
    "# \n",
    "# df = dedicated_cluster_df.groupby('id').apply(heartbeat).set_index('time')\n",
    "# f = df.groupby([pd.Grouper(freq='D'), 'region']).tenant_id.nunique().reset_index('region').pivot(columns='region', values='tenant_id')\n",
    "# ax = f[f.index > begin][:-1].plot(stacked=True, kind='area', colormap='Pastel2', figsize=(15, 9))\n",
    "# ax.set_xlabel('date')\n",
    "# ax.set_ylabel('user count')\n",
    "# ax.set(title='users who has dedicated clusters')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bba8d07d-eaea-40f4-aefa-33f5fea2f246",
   "metadata": {},
   "source": [
    "### Weekly Dedicated tier Clusters Count\n",
    "\n",
    "Dedicated clusters count every week, whose status has been normal, paused and scaling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f70290e3-e085-4522-b143-e3b6e0d2ae7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = dedicated_cluster_df.groupby('id').apply(heartbeat).set_index('time')\n",
    "# f = df.groupby([pd.Grouper(freq='D'), 'region']).id.nunique().reset_index('region').pivot(columns='region', values='id')\n",
    "# ax = f[f.index > begin][:-1].plot(kind='area', stacked=True, colormap='Pastel2', figsize=(15, 9))\n",
    "# ax.set_xlabel('date')\n",
    "# ax.set_ylabel('cluster count')\n",
    "# ax.set(title='dedicated clusters')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1897845f-dc87-4803-aa50-9be7fa7d9e3a",
   "metadata": {},
   "source": [
    "### Weekly Dedicated tier users who delete his last dedicated cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6579249-4c92-4160-9085-373e12965008",
   "metadata": {},
   "outputs": [],
   "source": [
    "# def last_delete(df_x):\n",
    "#     if df_x[df_x.deleted_time.isna()].size > 0:\n",
    "#         return None\n",
    "#     return df_x.deleted_time.max()\n",
    "# \n",
    "# df = dedicated_cluster_df.groupby(['tenant_id', 'region']).apply(last_delete).to_frame('last').reset_index().set_index('last')\n",
    "# f = df.groupby([pd.Grouper(freq='W-THU'), 'region']).size().to_frame('lost').reset_index('region').pivot(columns='region', values='lost')\n",
    "# ax = f[f.index > begin].plot(kind='bar', stacked=True, colormap='Pastel2', figsize=(15, 9))\n",
    "# ax.set_xlabel('date')\n",
    "# ax.set_ylabel('user count')\n",
    "# ax.set_xticklabels(f[f.index > begin].index.strftime('%m-%d'), rotation=90)\n",
    "# ax.set(title='users delete his last dedicated cluster')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80ba183a-afcb-4e2b-9950-b5bf8dfac48c",
   "metadata": {},
   "source": [
    "### Details of dedicated users this week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "019e9c08-6321-4c8b-ac4a-f68e4d0c38d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# xs = pd.date_range(start=begin.date(), end=datetime.now(timezone.utc).date(), freq='W-FRI')\n",
    "# cursor = xs[-1].date()\n",
    "# if cursor == datetime.now().date():\n",
    "#     cursor = xs[-2].date()\n",
    "#     \n",
    "# df = dedicated_cluster_df[(\n",
    "#     dedicated_cluster_df.deleted_time > cursor.strftime('%Y-%m-%d')) | dedicated_cluster_df.deleted_time.isna()\n",
    "# ]\n",
    "# \n",
    "# tikv_used = dbaas_cluster_usedsize(cursor-timedelta(days=7)).sort_values('created_at').groupby('cluster_id').tikv_size_used_bytes.last().to_frame('tikv_used')\n",
    "# tiflash_used = dbaas_cluster_usedsize(cursor-timedelta(days=7)).sort_values('created_at').groupby('cluster_id').tiflash_size_used_bytes.last().to_frame('tiflash_used')\n",
    "# df = pd.merge(df, tikv_used, left_on='id', right_on='cluster_id')\n",
    "# df = pd.merge(df, tiflash_used, left_on='id', right_on='cluster_id')\n",
    "# \n",
    "# def make_clickable(tenant_id):\n",
    "#     # target _blank to open new window\n",
    "#     if tenant_id == 0:\n",
    "#         return 'total'\n",
    "#     return '<a target=\"_blank\" href=\"https://clinic.pingcap.com/portal/#/overview?query={}\">{}</a>'.format(tenant_id, tenant_id)\n",
    "# \n",
    "# def mark_alive_cls(number):\n",
    "#     if number == 0:\n",
    "#         return '<text style=\"color: red\">{}</text>'.format(number)\n",
    "#     return number\n",
    "# \n",
    "# df = pd.DataFrame({\n",
    "#     'tenant id': df.groupby('tenant_id').tenant_id.first(),\n",
    "#     'email': df.groupby('tenant_id').email.first(),\n",
    "#     'signup': df.groupby('tenant_id').signup.first().dt.date,\n",
    "#     'first seen': df.groupby('tenant_id').created_at.min().dt.date,\n",
    "#     'region': df.groupby('tenant_id').region.first(),\n",
    "#     'cls paid': df.groupby('tenant_id').id.nunique(),\n",
    "#     'cls alive': df[df.deleted_time.isna()].groupby('tenant_id').id.nunique().astype(str),\n",
    "#     'tikv used': df.groupby('tenant_id').tikv_used.sum() / 1024 / 1024 / 1024,\n",
    "#     'tiflash used': df.groupby('tenant_id').tiflash_used.sum() / 1024 / 1024 / 1024,\n",
    "# }).reset_index(drop=True).sort_values('signup', ascending=False).fillna(0)\n",
    "# \n",
    "# r = df[['cls paid', 'cls alive', 'tikv used', 'tiflash used']].astype(int).sum()\n",
    "# r['tenant id'] = 0\n",
    "# \n",
    "# pd.concat([df, r.to_frame().T], ignore_index=True).style.format({\n",
    "#     'tenant id': make_clickable,\n",
    "#     'cls alive': mark_alive_cls,\n",
    "# })"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9770b9df-66ca-471a-8d0e-b23913b99f8b",
   "metadata": {},
   "source": [
    "### Weekly PoC submission Numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70ca3f1c-1df0-48fb-a506-13072deec627",
   "metadata": {},
   "outputs": [],
   "source": [
    "# s = event_df[\n",
    "#     (event_df.event == 'PoC Form Submission')\n",
    "#     |\n",
    "#     ((event_df.event == 'Submit Contact Us Form') & (event_df.contactType == 'Apply for PoC'))\n",
    "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n",
    "# ax = s.reset_index(drop=True).to_frame('unique user count').plot(kind='bar', figsize=(15, 9))\n",
    "# ax.set_xlabel('date')\n",
    "# ax.set_ylabel('user count')\n",
    "# _ = ax.set_xticklabels([str(item)[:10] for item in s.index])\n",
    "# ax.set(title='User who submit PoC application')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "825acb73-b104-49f3-beb9-ea20284d6394",
   "metadata": {},
   "source": [
    "### Weekly User numbers of being billing capable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d45e26d1-c976-48ec-b2ac-64ade0f30a99",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\n",
    "    'Became A Billed Customer': event_df[event_df.event == 'Became A Billed Customer'].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique(),\n",
    "    'Add A New Card Successfull': event_df[event_df.event == 'Add A New Card Successfull'].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique(),\n",
    "}, index=event_df.set_index('time').groupby(pd.Grouper(freq='W-THU')).size().index)\n",
    "ax = df.plot(kind='bar', figsize=(15, 9))\n",
    "ax.set_xlabel('date')\n",
    "ax.set_ylabel('user count')\n",
    "ax.set_xticklabels(df.index.strftime('%m-%d'), rotation=90)\n",
    "ax.set(title='Weekly Numbers of Add Credit Card Successfully')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fe424a4-b9c1-4669-861a-0f616f2ecc80",
   "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}