{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3b51e5f-bbfa-47ef-8f58-d2ba616d24c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown as md\n",
    "import pandas as pd\n",
    "from users import load_users, User\n",
    "from datetime import timedelta, date, datetime, timezone\n",
    "\n",
    "os.environ[\"S3_BUCKET\"] = \"pingcap-plg\"\n",
    "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"/home/zhoushuai/PLG/classifier/gcpbigquery.json\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12aee738-08f5-46eb-8b6b-ec86cdfc1bdb",
   "metadata": {},
   "source": [
    "# TiDB Cloud User Experience Metrics Product Report\n",
    "\n",
    "- [Signup](#Signup)\n",
    "  - problem 1: too many fields in registration and users are reluctant to fill\n",
    "    - metrics:\n",
    "      - [signup count](#Signup-count)\n",
    "    - features\n",
    "      - feature 1: \n",
    "        - name: Google SSO signup (5.13)\n",
    "        - metrics: none\n",
    "      - feature 2: \n",
    "        - name: Github SSO signup (5.18)\n",
    "        - metrics: none\n",
    "      - feature 3:\n",
    "        - name: Free trial signup (6.7)\n",
    "        - metrics: none\n",
    "- [Cluster Creation](#Cluster-Creation)\n",
    "  - problem 1: the cluster creation process is complicated, and the user gives up submission midway\n",
    "    - metrics:\n",
    "      - [cluster creation rate](#cluster-creation-rate)\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: simplification on cluster creation (6.16)\n",
    "        - metrics: [cluster creation feature usage](#cluster-creation-feature-usage)\n",
    "      - feature 2:\n",
    "        - name: new cluster creation page (8.12)\n",
    "        - metrics: [cluster creation feature usage](#cluster-creation-feature-usage)\n",
    "  - problem 2: cluster creation process take too long time, users leave before cluster ready\n",
    "    - metrics:\n",
    "      - [cluster creation duration seconds (p90)](#cluster-creation-duration-seconds-(p90))\n",
    "      - [user ratio who leave during cluster creation](#user-ratio-who-leave-during-cluster-creation)\n",
    "    - features:\n",
    "      - none\n",
    "- [Connection](#Connection)\n",
    "  - problem 1: users need define whitelist before connect the cluster, which is complex\n",
    "    - metrics:\n",
    "      - none\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: add \"Allow access from anywhere\" (7.28)\n",
    "        - metrics: [allow access from anywhere usage](#allow-access-from-anywhere-usage)\n",
    "  - problem 2: the overview entrance is not conspicuous, it's hard for users to find\n",
    "    - metrics:\n",
    "      - [overview page entrance rate](#overview-page-entrance-rate)\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: overview page entrance optimization (8.2)\n",
    "        - metrics: [Cluster Name Link Clicked](#Cluster-Name-Link-Clicked)\n",
    "  - problem 3: connection button is hide too deep, users can't find it easy\n",
    "    - metrics:\n",
    "      - [connect rate](#connect-rate)\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: move connect button to a conspicuous position (8.2)\n",
    "        - metrics: [connect button clicked](#connect-button-clicked)\n",
    "  - problem 4: no download guidelines for mysql client and mycli, make it hard to users who don't have client to connect\n",
    "    - metrics: [user's connect rate who copy mysql / mycli connection string](#user's-connect-rate-who-copy-mysql-/-mycli-connection-string)\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [mysql copy usage](#mysql-copy-button-usage)\n",
    "  - problem 5: sample code for connection is not readable, makes it hard for user to try\n",
    "    - metrics:\n",
    "      - [user's connect rate who copy JDBC/Python/Go/Node.js sample code](#user's-connect-rate-who-copy-JDBC/Python/Go/Node.js-sample-code)\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: support syntax highlighting in sample code provided in the Connect to TiDB dialog to improve code readability (7.19)\n",
    "        - metrics: measured by [code copy button usage by user](#code-copy-button-usage-by-user)\n",
    "  - problem 6: user need reset password to connect the cluster\n",
    "    - metrics:\n",
    "      - [generate password button click rate](#generate-password-button-click-rate)\n",
    "    - features:\n",
    "      - none\n",
    "  - problem 7: web shell offen throw errors which block users connecting the cluster\n",
    "    - metrics: \n",
    "      - [web shell error rate](#web-shell-error-rate)\n",
    "    - features:\n",
    "      - none\n",
    "- [Run SQL](#Run-SQL)\n",
    "  - problem 1: no sample query, users should plan the query in advance\n",
    "    - metrics:\n",
    "      - none\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: playground (8.30)\n",
    "        - metrics: [playground usage](#playground-usage)\n",
    "  - problem 2: no sample data, users should import data first, which is painful\n",
    "    - metrics:\n",
    "      - [import rate](#import-rate)\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: playground (8.30)\n",
    "        - metrics: none\n",
    "- [Cluster Status](#Cluster-Status)\n",
    "  - problem 1: user must resume cluster after hibernated\n",
    "    - metrics:\n",
    "      - none\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [resume cluster usage](#resume-cluster-usage)\n",
    "- [Cluster Monitoring](#Cluster-Monitorinng)\n",
    "  - problem 1: dev tier user is unable to try monitoring functions\n",
    "    - metrics:\n",
    "      - [integration page visit rate](#integration-page-visit-rate)\n",
    "      - [integration page stay seconds (p90)](#integration-page-stay-seconds-(p90))\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [integration usage](#integration-usage)\n",
    "- [Cluster Diagnosis](#Cluster-Diagnosis)\n",
    "  - problem 1: it's hard for dev tier user to learn TiDB diagnosis module\n",
    "    - metrics:\n",
    "      - [diagnosis page visit rate](#diagnosis-page-visit-rate)\n",
    "      - [diagnosis page stay seconds (p90)](#diagnosis-page-stay-seconds-(p90))\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [diagnosis usage](#diagnosis-usage)\n",
    "- [Account Management](#Account-Management)\n",
    "  - problem 1: organization/project/member information structure display is not clear\n",
    "    - metrics:\n",
    "      - none\n",
    "    - features:\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [account management usage](#account-management-usage)\n",
    "- [POC](#POC)\n",
    "  - problem 1: the upgrade button is not attracting users\n",
    "    - metrics:\n",
    "      - none\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [Upgrade Link Clicked]()\n",
    "  - problem 2: user want to pause the cluster\n",
    "    - metrics:\n",
    "      - none\n",
    "    - features\n",
    "      - feature 1:\n",
    "        - name: none\n",
    "        - metrics: [pause the cluster usage](#pause-the-cluster-usage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84e8f63b-c684-4372-b9f7-8bbbfe07343a",
   "metadata": {},
   "outputs": [],
   "source": [
    "begin = datetime(2022, 5, 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": "89226525-106d-4a65-8e53-dc76f7327a8a",
   "metadata": {},
   "source": [
    "## Signup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ffebaf1-fae8-4b25-8eb0-079d67e72e2c",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Signup count\n",
    "\n",
    "This chart is used to measure the problem that too many fields in registration page and users are reluctant to fill.\n",
    "\n",
    "It use the number of tenants signup over days to figure out how many users signup success every day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac204d6f-aab7-4404-b446-0e1122492bb7",
   "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",
    "event_df = users[0]._event_df\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": "d18d9740-1cee-4a27-bf38-c4d48fe3d860",
   "metadata": {},
   "source": [
    "## Cluster Creation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d783a9a-bb41-4742-8e44-57b36b1170aa",
   "metadata": {
    "tags": []
   },
   "source": [
    "### cluster creation rate\n",
    "\n",
    "This chart is used to measure the problem that the cluster creation process is complicated, and the user gives up submission midway.\n",
    "\n",
    "We use the indicator, $ CreationRate = \\frac{CreationCount}{SignupCount} $ to draw the cluster creation rate in signup users where:\n",
    "\n",
    "- $CreationCount$: the number of user who signup and has created at least one cluster in the future\n",
    "- $SignupCount$: the number of user who signup\n",
    "\n",
    "The x-axis is the date the user signup."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7164b256-bf67-4930-8eb0-dbd667656a7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'create': len(u.clusters()) > 0,\n",
    "} for u in users])\n",
    "\n",
    "ax = (df[df.create].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='cluster creation rate')\n",
    "ax.axvline(datetime(2022, 6, 16, tzinfo=timezone.utc), linestyle='--', color='r', label='simplification on cluster creation')\n",
    "ax.axvline(datetime(2022, 8, 12, tzinfo=timezone.utc), linestyle='--', color='g', label='new cluster creation page')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3479d0b2-0f51-4aa5-95c0-9d37e546afa7",
   "metadata": {
    "tags": []
   },
   "source": [
    "### cluster creation feature usage\n",
    "\n",
    "This chart is used to measure the usage of the features:\n",
    "\n",
    "- simplification on cluster creation (6.16)\n",
    "- new cluster creation page (8.12)\n",
    "\n",
    "The y-axis is the number of unique users who clicked specific button on the specific day (x-axis shows the day)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c4f7379-c954-4117-ae0d-bf729fddd61a",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = ['Create Cluster Button Clicked', 'Dedicated Tier Tab Clicked', 'Developer Tier Tab Clicked', 'Cancel Created A Cluster', 'Created A Cluster']\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df.loc[(df.event == 'Created A Cluster') & (df['Cluster Type'] == 'Dedicated Cluster'), 'event'] ='Created A Dedicated Cluster'\n",
    "df.loc[(df.event == 'Created A Cluster') & (df['Cluster Type'] == 'Dev Tier'), 'event'] ='Created A Dev Tier Cluster'\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.drop(columns=['Created A Cluster']).plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='create cluster feature usage by unique user')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43a8e97d-ac6d-463f-b80c-130b83e96f21",
   "metadata": {
    "tags": []
   },
   "source": [
    "### cluster creation duration seconds (p90)\n",
    "\n",
    "This chart is used to identity the issue cluster creation process take too long time, some user may not be patient enough to wait the cluster be ready.\n",
    "\n",
    "We use the 90% cluster creation seconds as the y-axis and use two lines to distinguish devtier clusters and dedicated clusters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e5d7444-f98a-4edf-8201-942b7d5c8ad4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def p90(x):\n",
    "    return x.quantile(0.90)\n",
    "\n",
    "devtier_df = pd.concat([u.devtier_clusters() for u in users]).set_index('created_at')\n",
    "dedicate_df = pd.concat([u.dedicate_clusters() for u in users]).set_index('created_at')\n",
    "\n",
    "df = pd.DataFrame()\n",
    "df['devtier'] = (devtier_df.creation_finished_time - devtier_df.index).dt.total_seconds().groupby(pd.Grouper(freq='D')).agg(p90)\n",
    "df['dedicate'] = (dedicate_df.creation_finished_time - dedicate_df.index).dt.total_seconds().groupby(pd.Grouper(freq='D')).agg(p90)\n",
    "\n",
    "df.rolling(15, min_periods=1).mean().plot(figsize=(15, 9)).set_title('create cluster duration seconds (p90)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd4e5036-0d05-4a86-9226-9c6600a37b1a",
   "metadata": {},
   "source": [
    "### user ratio who leave during cluster creation\n",
    "\n",
    "This chart is used to measure the issue that cluster creation process take too long time, some user may not be patient enough to wait the cluster be ready. It figure out how many users leave before cluster ready."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cf576e9-bbda-457e-8889-efdc3148c822",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'leave': u.events().time.iloc[-1] < u.clusters().creation_finished_time.iloc[-1],\n",
    "} for u in users if len(u.clusters()) > 0 and len(u.events()) > 0])\n",
    "\n",
    "ax = (df[df.leave].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='user ratio who leave during cluster creation')\n",
    "\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d96a3734-de51-4331-810f-c0afc216bc8a",
   "metadata": {},
   "source": [
    "## Connection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84af1a76-04a2-430e-a3e2-e3d27cf7bd43",
   "metadata": {
    "tags": []
   },
   "source": [
    "### allow access from anywhere usage\n",
    "\n",
    "This chart measures the flow of the features:\n",
    "\n",
    "- add \"Allow access from anywhere\" (7.28)\n",
    "\n",
    "It's count by unique user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "732a2be5-8ef6-4b01-9dad-fb81ea38b81a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([u.events() for u in users])\n",
    "ax = df[df.event == 'Allow Access from Anywhere Button Clicked'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique().plot(kind='bar', figsize=(16, 9))\n",
    "ax.set(title='allow access from anywhere button clicked by unique user (weekly)')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6df21dbf-3395-4a44-9c3f-d4ce0f91c780",
   "metadata": {},
   "source": [
    "### overview page entrance rate\n",
    "\n",
    "This chart measures how difficult it is to find the overview entrance, it's measured by the formula $EntranceRate = \\frac{EntranceCount}{SignupCount} $, where:\n",
    "\n",
    "- $EntranceCount$: the number of user who signup and enter cluster overview page at least once in the future\n",
    "- $SignupCount$: the number of user who signup\n",
    "\n",
    "The x-axis is the date the user signup."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec69aa86-c63a-4c01-aff5-dcd8c118c45c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'overview': u.count_event('Cluster Overview Page View') > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df.overview].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='overview page entrance rate')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='overview page entrance optimization')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "648d58d7-5372-46cf-9b73-130a654b938b",
   "metadata": {},
   "source": [
    "### Cluster Name Link Clicked\n",
    "\n",
    "This chart is used to measure the usage of the features:\n",
    "\n",
    "- overview page entrance optimization (8.2)\n",
    "\n",
    "It's count by unique user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e0d8109-8c66-4749-bd3e-431203363738",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([u.events() for u in users])\n",
    "\n",
    "ax = df[df.event == 'Cluster Name Link Clicked'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique().rolling(7, min_periods=1).mean().plot(kind='bar', figsize=(16, 9))\n",
    "\n",
    "ax.set(title='Cluster Name Link Clicked')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7180e83-b191-4fe7-bdff-44692d492c61",
   "metadata": {
    "tags": []
   },
   "source": [
    "### connect rate\n",
    "\n",
    "connect rate is used to measure the problem that connection button is hide too deep, users can't find it easy, using the formula $ ConnectRate = \\frac{ConnectUser}{CreateClusterUser} $ where:\n",
    "\n",
    "- $ConnectUser$: the number of user who create cluster and click the 'Connect' button in the future\n",
    "- $CreateClusterCount$: the number of user who create cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a2fecb2-0315-4dfe-876e-5af600d7cda0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'connect': u.max_connection() > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df.connect].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='connect rate')\n",
    "ax.axvline(datetime(2022, 8, 2, tzinfo=timezone.utc), linestyle='--', color='g', label='move connect button to a conspicuous position')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a84e09d-68b4-470c-a2bb-c53adc4b6ce2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### connect button clicked\n",
    "This chart is used to measure the usage of the features:\n",
    "\n",
    "- move connect button to a conspicuous position (8.2)\n",
    "\n",
    "It's count by unique user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98c3fbe6-3e54-4c34-9ef7-f2c90b96dff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([u.events() for u in users])\n",
    "ax = df[df.event == 'Connect Button Clicked'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique().rolling(7, min_periods=1).mean().plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='connect button clicked by unique user')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00234ad0-3f98-4322-a962-380688e3dd1e",
   "metadata": {
    "tags": []
   },
   "source": [
    "### user's connect rate who copy mysql / mycli connection string\n",
    "\n",
    "Measurement for problem \"no download guidelines for mysql client and mycli, make it hard to users who don't have client to connect\".\n",
    "\n",
    "$ConnectRate = \\frac{ConnectUserCount}{CopyClickedUserCount}$ where:\n",
    "\n",
    "- $ConnectUserCount$: the number of users who copy mysql connect string and connect the cluster\n",
    "- $CopyClickedUserCount$: the number of users who copy mysql connect string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b884ee62-b0d2-47a7-ad9b-eb03a6961a55",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'connect': u.max_connection() > 0,\n",
    "} for u in users if u.count_event('MySQL-Copy Button Clicked') + u.count_event('MyCLI-Copy Button Clicked') > 0]).set_index('time')\n",
    "df = df[df.index > '2022-07-18']\n",
    "\n",
    "ax = (df[df.connect].groupby(pd.Grouper(freq='D')).id.nunique()\n",
    "  /\n",
    "df.groupby(pd.Grouper(freq='D')).id.nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title=\"user's connect rate who copy mysql / mycli connection string\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6845fd9-4fae-43b0-b324-f41bad04cae1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### mysql copy button usage\n",
    "\n",
    "This chart is used to measure the mysql copy button usage by unique user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b6a460f-da31-43bc-abf1-1c51b235f04d",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = ['MySQL-Copy Button Clicked', 'MyCLI-Copy Button Clicked']\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.rolling(15, min_periods=1).mean().plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='mysql copy button usage by user')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baec1880-46cf-4cf2-8558-cc9b604a73d0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### user's connect rate who copy JDBC/Python/Go/Node.js sample code\n",
    "\n",
    "Measure the problem: sample code for connection is not readable, makes it hard for user to try.\n",
    "\n",
    "formula: $ConnectRate = \\frac{ConnectUserCount}{CopyUserCount}$, where:\n",
    "\n",
    "- $ConnectUserCount$: the number of user who copy sample code and connect the cluster in the future\n",
    "- $CopyUserCount$: the number of user who copy sample code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8dd555f-b834-4991-a7bf-f0039ffd99f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def f(u):\n",
    "    c = 0\n",
    "    for e in ['JDBC-Copy Button Clicked', 'Python-Copy Button Clicked', 'Go-Copy Button Clicked', 'Node.js-Copy Button Clicked']:\n",
    "        c += u.count_event(e)\n",
    "    return c\n",
    "    \n",
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'connect': u.max_connection() > 0,\n",
    "} for u in users if f(u) > 0])\n",
    "\n",
    "df = df[df.time > '2022-07-01']\n",
    "\n",
    "ax = (df[df.connect].set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()).rolling(15, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title=\"user's connect rate who copy JDBC/Python/Go/Node.js sample code\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "909c26a5-adca-43c1-962b-8fbaa7901ea9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### code copy button usage by user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8a65260-04b2-41e0-a080-d405bc7a849b",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = [\n",
    "    \"JDBC-Copy Button Clicked\", \n",
    "    \"JDBC-Show Example Usage Checkbox Clicked\", \n",
    "    \"Python-Copy Button Clicked\", \n",
    "    \"Python-Show Example Usage Checkbox Clicked\",\n",
    "    \"Go-Copy Button Clicked\",\n",
    "    \"Go-Show Example Usage Checkbox Clicked\",\n",
    "    \"Node.js-Copy Button Clicked\",\n",
    "    \"Node.js-Show Example Usage Checkbox Clicked\"\n",
    "]\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.plot(kind='bar', figsize=(16, 9))\n",
    "ax.set(title='sample code copy button usage by user')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de6fd7ed-3987-4c69-82c2-a0a8277f0735",
   "metadata": {
    "tags": []
   },
   "source": [
    "### generate password button click rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d10c1754-098f-45c3-bcff-d3960f91947a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([u.events() for u in users])\n",
    "\n",
    "reset_df = df[df.event == 'Generate Root Password Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].nunique()\n",
    "create_df = df[df.event == 'Created A Cluster'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].nunique()\n",
    "\n",
    "(reset_df / create_df).rolling(7, min_periods=1).mean().plot(figsize=(16, 9)).set(title='generate password button click rate')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8dbd8df-f520-40e7-99b7-cccf1dd5ebb0",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### web shell error rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3806a4d8-2e63-4932-8c6e-d284e35176f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "sentry_df = pd.concat([u.errors() for u in users])\n",
    "event_df = event_df[event_df.event == 'Open SQL Shell Button Clicked'].set_index('time').groupby(pd.Grouper(freq='D'))['$user_id'].nunique()\n",
    "sentry_df = sentry_df[sentry_df.title == 'load webshell error'].set_index('time').groupby(pd.Grouper(freq='D'))['user_id'].nunique()\n",
    "(sentry_df / event_df).rolling(7, min_periods=1).mean().plot(figsize=(16, 9)).set(title='web shell error rate by unique user')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f0c5c21-be20-4b54-a547-b3696f9fc963",
   "metadata": {},
   "source": [
    "## Run SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f18b3d2-8cc3-4ccd-954a-99ccdf911307",
   "metadata": {
    "tags": []
   },
   "source": [
    "### playground usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f292985-da22-4c6d-8368-1011e0b6f602",
   "metadata": {},
   "outputs": [],
   "source": [
    "# event_df = pd.concat([u.events() for u in users])\n",
    "# feature_events = ['Go To Playground Button Clicked', 'Playground Page View', 'Playground Side Menu Clicked', 'Playground Run Button Clicked']\n",
    "# df = event_df[event_df.event.isin(feature_events)]\n",
    "# df = df.set_index('time').groupby([pd.Grouper(freq='D'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "# df.rolling(15, min_periods=1).mean().plot(figsize=(16, 9)).set(title='playground usage')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3319a9dd-3c78-4bbc-8886-b6ae1a301a1d",
   "metadata": {
    "tags": []
   },
   "source": [
    "### import rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca87d3be-0c32-472b-81a5-4a63de49e29b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'import': u.count_event('Import Form Submission') > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df['import']].set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='import rate')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad21a4ce-cd98-4e8a-ac2b-343aa08f5e6f",
   "metadata": {},
   "source": [
    "## Cluster Status"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5890ec30-7e49-4bfd-bc34-51a6acbc2d9a",
   "metadata": {},
   "source": [
    "### resume cluster usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73af770b-f5dc-4cad-83fb-bfaa93bf13a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = ['Resume Button Clicked', 'Try To Resume The Cluster', 'Successfully Resumed The Cluster', 'Cancel Resume The Cluster']\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='resume cluster usage')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7983428-8aeb-45c0-99ea-f3f30fb13954",
   "metadata": {},
   "source": [
    "## Cluster Monitorinng"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05c726e0-9e3c-46a2-b37e-875192f3d3fc",
   "metadata": {},
   "source": [
    "### integration page visit rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24085aeb-20f2-4044-890c-31033268fece",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'visit': u.count_event('Integrations Page View') > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df.visit].set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='integration page visit rate')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74d5bc11-67d0-4b50-b6fb-ef5abbefd72b",
   "metadata": {},
   "source": [
    "### integration page stay seconds (p90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e62cc32-8e4c-43ed-9ded-7da525b167f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'time': u.signup_time(),\n",
    "    'duration': u.event_duration_sum('Integrations Page View').total_seconds(),\n",
    "} for u in users if u.count_event('Integrations Page View') > 0])\n",
    "\n",
    "ax = df.set_index('time').duration.groupby(pd.Grouper(freq='D')).agg(p90).rolling(15, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.set(title='integration page stay seconds (p90)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e8fd872-c0d6-4eb0-81b1-2e68f03aab6e",
   "metadata": {},
   "source": [
    "### integration usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4c350eb-c8f0-4144-9821-516fd5b948cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = [\n",
    "    'Integrations Page View', \n",
    "    'Integration To Datadog Cancel Button Clicked', \n",
    "    'Integration To Datadog Test Integration Button Clicked', \n",
    "    'Integration to Promethus Add File Button Clicked',\n",
    "    'Integration To Prometheus Delete Button Clicked',\n",
    "    'Integration To Prometheus Documentation Link Clicked',\n",
    "]\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='integration usage')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f99311ce-8d06-41f2-b70f-03440ddeb7b4",
   "metadata": {},
   "source": [
    "## Cluster Diagnosis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53c148a8-ab89-4641-bb75-300dd59a88ef",
   "metadata": {},
   "source": [
    "### diagnosis page visit rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6701e9ed-bb53-451e-9a94-a5582a32ea09",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'id': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'visit': u.count_event('Diagnosis Page View') > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df.visit].set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D')).id.nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='diagnosis page visit rate')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f898f257-4279-4483-855f-f58a6029c009",
   "metadata": {},
   "source": [
    "### diagnosis page stay seconds (p90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e1d17db-c587-4aa1-af24-f4ea43c2b8b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'time': u.signup_time(),\n",
    "    'duration': u.event_duration_sum('Diagnosis Page View').total_seconds(),\n",
    "} for u in users if u.count_event('Diagnosis Page View') > 0])\n",
    "\n",
    "ax = df.set_index('time').duration.groupby(pd.Grouper(freq='D')).agg(p90).rolling(15, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.set(title='diagnosis page stay seconds (p90)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aefaa49-9a71-4fdb-ae8a-765da07686c2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### diagnosis usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d48a34f-6834-43e1-ba76-d7c47f8d0cce",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = [\n",
    "    'Diagnosis Page View', \n",
    "    'keyviz Tab Clicked', \n",
    "    'KeyViz: Change Metric', \n",
    "    'KeyViz: Open Setting',\n",
    "]\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.rolling(7, min_periods=1).mean().plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='diagnosis usage')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83a34863-0300-4369-b958-25b225a191e8",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Account Management"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "536023f1-8156-4855-bca3-0d502e792c6d",
   "metadata": {},
   "source": [
    "### account management usage\n",
    "\n",
    "This chart is used to measure the usage of account management"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51b6d2bd-f2a5-4dd1-b4cf-59abd627ee24",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = ['Project Invite Button Clicked',  'User Management Invite Button Clicked', 'Invite User Completed']\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='account management usage')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee0b1962-d8c1-4775-b216-612c7bc68ade",
   "metadata": {
    "tags": []
   },
   "source": [
    "## POC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93300da7-4ac1-4fdd-a12a-fea3f725ee13",
   "metadata": {},
   "source": [
    "### POC submit rate\n",
    "\n",
    "This chart is used to measure the problem that PoC submit rate is low.\n",
    "\n",
    "formula: $SubmitRate = \\frac{PoCApplication}{PoCPageView}$, where:\n",
    "\n",
    "- $PoCApplication$: the number of user who submit the PoC form\n",
    "- $PoCPageView$: the number of user who visit the PoC application page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "473e0966-88eb-420a-9bfb-96823a822053",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'submit': u.count_event('PoC Form Submission') > 0,\n",
    "} for u in users if u.count_event('Poc Application Page View') > 0])\n",
    "\n",
    "ax = (df[df.submit].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='poc submit rate')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20b32fb9-bdc9-4f5e-9475-592c89e664ab",
   "metadata": {},
   "source": [
    "### POC form usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2004ab63-0508-4169-bb18-a9355109fdd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = ['Open PoC Form',  'Cancel PoC Form Application', 'PoC Form Submission']\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='poc form usage')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0082189-ebdf-4abe-ae18-6e89e27d63b1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### pause the cluster usage\n",
    "\n",
    "This chart is used to measure the usage of the feature \"Pause Cluster\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35e399b7-059c-49df-8e4f-990c568f7bda",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_df = pd.concat([u.events() for u in users])\n",
    "feature_events = ['Try To Pause The Cluster',  'Successfully Paused The Cluster']\n",
    "df = event_df[event_df.event.isin(feature_events)]\n",
    "df = df.set_index('time').groupby([pd.Grouper(freq='W-MON'), 'event'])['$user_id'].nunique().reset_index('event').pivot(columns='event', values='$user_id')\n",
    "ax = df.plot(figsize=(16, 9), kind='bar')\n",
    "ax.set(title='pause cluster usage')\n",
    "_ = ax.set_xticklabels([item.get_text()[:10] for item in ax.get_xticklabels()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fb58bdc-bb61-435e-9b1c-fb45bec4e119",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'entrance': u.count_event('Create Cluster Button Clicked') > 0,\n",
    "} for u in users])\n",
    "\n",
    "ax = (df[df.entrance].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "ax.set(title='Create Cluster Entrance Rate')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64e8bc35-f5ee-422e-a0c4-a0799a824067",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'view': u.count_event('Diagnosis Page View') > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df.view].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='User rate who do Diagnosis Page View')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef0d9ddc-a585-4bd4-b5a6-ee1f0fe95540",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'view': u.count_event('keyviz Tab Clicked') > 0,\n",
    "} for u in users if u.create_cluster_count() > 0])\n",
    "\n",
    "ax = (df[df.view].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n",
    "  /\n",
    "df.set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()).rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n",
    "\n",
    "ax.set(title='User rate who click keyviz Tab')\n",
    "ax.legend()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bd136fa-201c-41de-8b0a-34f7ba67aeca",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "\n",
    "events = [\n",
    "    'keyviz Tab Clicked', \n",
    "    'KeyViz: Change Bright', \n",
    "    'KeyViz: Change Time Duration', \n",
    "    'KeyViz: Reset Zoom', \n",
    "    'KeyViz: Change Metric',\n",
    "    'KeyViz: Click Manual Refresh',\n",
    "    'KeyViz: Open Setting',\n",
    "]\n",
    "df = pd.DataFrame.from_records([{\n",
    "    'unique user': u.id(),\n",
    "    'time': u.signup_time(),\n",
    "    'duration': u.event_duration_sum(*events).total_seconds()\n",
    "} for u in users if u.create_cluster_count() > 0 and u.event_duration_sum('keyviz Tab Clicked').total_seconds() > 0])\n",
    "\n",
    "df['week'] = df.time.dt.isocalendar().week.astype(str)\n",
    "ax = sns.jointplot(data=df.sort_values('time'), x='week', y='duration', kind=\"scatter\").ax_joint\n",
    "ax.set_yscale('log')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07ac679e-e909-477b-b296-ac1964732630",
   "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
}