{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "20e5d992-92e9-4d3f-8f41-d0848f28d046", "metadata": {}, "outputs": [], "source": [ "import os\n", "import pandas as pd\n", "from IPython.display import Markdown as md\n", "import seaborn as sns\n", "import numpy as np\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": "0f99b7e5-c3b2-455b-bc5e-f36e06a77eb6", "metadata": {}, "source": [ "# Feature Evaluation Report\n", "\n", "You can comment on [the template](https://pingcap.feishu.cn/wiki/wikcn2CsaHXeq6P1mdXyRC2yNdb)\n", "\n", "- [Overall Metrics related to Conversion / Growth](#Overall-Metrics-related-to-Conversion-/-Growth)\n", " - [M1：Weekly User SignUp / SignIn Count](#M1：Weekly-User-SignUp-/-SignIn-Count)\n", " - [M2：Weekly Active User Count](#M2：Weekly-Active-User-Count)\n", " - [M3：Daily Active Dev tier Cluster Count](#M3：Daily-Active-Dev-tier-Cluster-Count)\n", " - [M4：Weekly User Remaining Ratio](#M4：Weekly-User-Remaining-Ratio)\n", "- [Task 1：Sign up / in](#Task-1：Sign-up-/-in)\n", " - Task metrics\n", " - Usage metrics\n", " - [User SignUp Count](#User-SignUp-Count)\n", " - [User Signin Count](#User-Signin-Count)\n", " - Engagement metrics\n", " - N / A\n", " - Feature\n", " - N / A\n", "- [Task 2：Create cluster](#Task-2：Create-cluster)\n", " - Task metrics\n", " - Usage metrics\n", " - [User rate who create cluster by different cluster type %](#User-rate-who-create-cluster-by-different-cluster-type)\n", " - Engagement metrics\n", " - [Dev tier cluster alive time P90](#Dev-tier-cluster-alive-time-P90)\n", " - [Feature 1：New cluster creation page](#Feature-1：New-cluster-creation-page)\n", " - Usage metrics\n", " - [User rate who submit \"Create Cluster\" %](#User-rate-who-submit-\"Create-Cluster\")\n", " - Engagement metrics\n", " - [User rate who meet failure during cluster creation %](#User-rate-who-meet-failure-during-cluster-creation)\n", " - [User's P90 ratio of create dev tier cluster clicked to dev tier cluster created %](#P90-Developer-Tier-Tab-Clicked-before-a-devtier-cluster-created)\n", " - [Create Dev tier Cluster Configure Duration P90](#Create-Dev-tier-Cluster-Configure-Duration-P90)\n", " - [Feature 2：Dev tier cluster creation time reduce](#Feature-2：Dev-tier-cluster-creation-time-reduce)\n", " - Usage metrics\n", " - N / A\n", " - Engagement metrics\n", " - [Dev tier cluster creation duration P90](#Dev-tier-cluster-creation-duration-P90)\n", "- [Task 3：Connect to a Cluster](#Task-3：Connect-to-a-Cluster)\n", " - Task metrics\n", " - Usage metrics\n", " - [Cluster Connect Rate %](#Cluster-Connect-Rate)\n", " - Engagement metrics\n", " - N / A \n", " - [Feature 1：MySQL/MyCLI client connection string](#Feature-1：MySQL/MyCLI-client-connection-string)\n", " - Usage metrics\n", " - [User rate who copy connection string %](#User-rate-who-copy-connection-string)\n", " - Engagement metrics\n", " - [User Connect Rate who copy connection string %](#User-Connect-Rate-who-copy-connection-string)\n", " - [User's P90 ratio of copy times to connection number %](#User's-P90-ratio-of-copy-times-to-connection-number)\n", " - [Feature 2：Connection's Sample Code](#Feature-2：Connection's-Sample-Code)\n", " - Usage metrics\n", " - [User rate who copy sample code %](#User-rate-who-copy-sample-code)\n", " - Engagement metrics\n", " - [User Connect Rate who copy sample code %](#User-Connect-Rate-who-copy-sample-code)\n", " - [Feature 3: Web Shell](#Feature-3:-Web-Shell)\n", " - Usage metrics\n", " - [User rate who open web shell %](#User-rate-who-open-web-shell)\n", " - Engagement metrics\n", " - [User connect rate who open web shell %](#User-connect-rate-who-open-web-shell)\n", " - [Feature 4：Allow access from anywhere](#Feature-3：Allow-access-from-anywhere)\n", " - Usage metrics\n", " - [User rate who click \" Allow access from anywhere\" button %](#User-rate-who-click-\"-Allow-access-from-anywhere\"-button)\n", " - Engagement metrics\n", " - N / A\n", " - [Feature 5：Generate password](#Feature-4：Generate-password)\n", " - Usage metrics\n", " - [User rate who click \"copy password\" button %](#User-rate-who-click-\"Generate-Root-Password\"-button)\n", " - Engagement metrics\n", " - [User rate of who generate password but not copy %](#User-rate-of-who-generate-password-but-not-copy)\n", "- [Task 4：Playground](#Task-4：Playground)\n", " - Task metrics\n", " - Usage metrics\n", " - [User rate who click \"Run\" button or \"Ctrl/CMD + Enter\" once in playground %](#User-rate-who-click-\"Run\"-button-or-\"Ctrl/CMD-+-Enter\"-once-in--playground)\n", " - Engagement metrics\n", " - [P50 Run Button Clicked](#P50-Run-Button-Clicked)\n", " - [Feature 1：SQL compatibility experience](#Feature-1：SQL-compatibility-experience)\n", " - Usage metrics\n", " - [User rate who experience SQL compatibility capability %](#User-rate-who-experience-SQL-compatibility-capability)\n", " - Engagement metrics\n", " - WIP\n", " - [Feature 2：HTAP experience](#Feature-2：HTAP-experience)\n", " - Usage metrics\n", " - [User rate who experience HTAP capability %](#User-rate-who-experience-HTAP-capability)\n", " - Engagement metrics\n", " - WIP\n", " - [Feature 3：Run your sql](#Feature-3：Run-your-sql)\n", " - Usage metrics\n", " - [User rate who run your sql once %](#User-rate-who-run-your-sql-once)\n", " - Engagement metrics\n", " - N / A\n", "- [Task 5：Import](#Task-5：Import)\n", " - Task metrics\n", " - Usage metrics\n", " - [User rate who enter import page and submit import %](#User-rate-who-enter-import-page-and-submit-import)\n", " - Engagement metrics\n", " - [User rate who failed in submission %](#User-rate-who-failed-in-submission)\n", " - [Import failure rate after submission %](#Import-failure-rate-after-submission)\n", " - [User rate of each step page entrance %](#User-rate-of-each-step-page-entrance)\n", " - [P50 Duration of each step page stay time](#P50-Duration-of-each-step-page-stay-time)\n", " - [Feature 1：Validation](#Feature-1：Validation)\n", " - Usage metrics\n", " - N / A\n", " - Engagement metrics\n", " - [User rate who meet validation error in 3 steps %](#User-rate-who-meet-validation-error-in-3-steps)\n", "- [Task 6：Diagnosis](#Task-6：Diagnosis)\n", " - Task metrics\n", " - Usage metrics\n", " - [User rate who do Diagnosis Page View %](#User-rate-who-do-Diagnosis-Page-View)\n", " - Engagement metrics\n", " - [Weekly distribution of time users spend on Diagnosis page](#Weekly-distribution-of-time-users-spend-on-Diagnosis-page)\n", " - [Feature 1：SQL statement Diagnosis](#Feature-1：SQL-statement-Diagnosis)\n", " - Usage metrics\n", " - [User rate who click one sql statement page %](#User-rate-who-click-one-sql-statement-page)\n", " - Engagement metrics\n", " - N / A\n", " - [Feature 2：Key Viz](#Feature-2：Key-Viz)\n", " - Usage metrics\n", " - [User rate who click keyviz Tab %](#User-rate-who-click-keyviz-Tab)\n", " - Engagement metrics\n", " - [Weekly distribution of stay time user spend on keyviz](#Weekly-distribution-of-stay-time-user-spend-on-keyviz)\n", " - [User rate who change brigetness %](#User-rate-who-change-brigetness)\n", " - [User rate who change metrics %](#User-rate-who-change-metrics)\n", "- [Task 7：Monitor cluster](#Task-7：Monitor-cluster)\n", " - Task metrics\n", " - Usage metrics\n", " - [User rate who do Integrations Page View %](#User-rate-who-do-Integrations-Page-View)\n", " - Engagement metrics\n", " - WIP\n", " - [Feature 1：Alerts](#Feature-1：Alerts)\n", " - Usage metrics\n", " - [User rate who click \"Subscribe\" button %](#User-rate-who-click-\"Subscribe\"-button)\n", " - Engagement metrics\n", " - WIP\n", "- [Task 8：Upgrade](#Task-8：Upgrade)\n", " - Task metrics\n", " - Usage metrics\n", " - N / A\n", " - Engagement metrics\n", " - N / A\n", " - [Feature 1：PoC application](#Feature-1：PoC-application)\n", " - Usage metrics\n", " - [User rate who submit PoC application %](#User-rate-who-submit-PoC-application)\n", " - Engagement metrics\n", " - [User rate who open PoC form but leave %](#User-rate-who-open-PoC-form-but-leave)\n", " - [User rate who meet failure after submission %](#User-rate-who-meet-failure-after-submission)\n", " - [Feature 2：Upgrade to create dedicated cluster](#Feature-2：Upgrade-to-create-dedicated-cluster)\n", " - Usage metrics\n", " - [User rate who click upgrade button %](#User-rate-who-click-upgrade-button)\n", " - Engagement metrics\n", " - [User rate who open upgrade page but leave %](#User-rate-who-open-upgrade-page-but-leave)\n", " - [Feature 3：Bind credit card](#Feature-3：Bind-credit-card)\n", " - Usage metrics\n", " - [User rate who Became A Billed Customer %](#User-rate-who-Became-A-Billed-Customer)\n", " - [User rate who click \"add new card\" %](#User-rate-who-click-\"add-new-card\")\n", " - Engagement metrics\n", " - [User rate who click \"add new card\" but leave %](#User-rate-who-click-\"add-new-card\"-but-leave)\n", " - [User rate who meet failure after submission %](#User-rate-who-meet-failure-after-submission)" ] }, { "cell_type": "markdown", "id": "a3c8b462-f3c2-4903-b9bb-40028a53d599", "metadata": {}, "source": [ "## Overall Metrics related to Conversion / Growth" ] }, { "cell_type": "code", "execution_count": null, "id": "d2446f31-0462-4e65-9582-e45d22ca56ef", "metadata": {}, "outputs": [], "source": [ "begin = datetime(2022, 7, 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()]\n", "event_df = pd.concat([u.events() for u in users])\n", "error_df = pd.concat([u.errors() for u in users])\n", "cluster_df = pd.concat([u.clusters() for u in users])\n", "import_df = pd.concat([u.imports() for u in users])\n", "connection_df = pd.concat([u.connections() for u in users])" ] }, { "cell_type": "markdown", "id": "8dd317d6-6e51-473f-934f-b3d425313605", "metadata": {}, "source": [ "### M1：Weekly User SignUp / SignIn Count" ] }, { "cell_type": "code", "execution_count": null, "id": "e0ba9225-93c8-4e88-a26a-689cd6dff9d1", "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", "df['signin'] = event_df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "\n", "ax = df.plot(figsize=(15, 9))\n", "ax.set_ylabel('user count')\n", "ax.set(title='User SignUp / SignIn Count')\n", "\n", "ax.legend()" ] }, { "cell_type": "markdown", "id": "3dca5907-84c3-4a55-8de1-2735b331ac2b", "metadata": {}, "source": [ "### M2：Weekly Active User Count" ] }, { "cell_type": "code", "execution_count": null, "id": "d6d2f793-3bd4-4cae-9409-2000572080f2", "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-MON'))['$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": "19139bfb-b693-4874-9e85-2ca3018c17c9", "metadata": {}, "source": [ "### M3：Daily Active Dev tier Cluster Count" ] }, { "cell_type": "code", "execution_count": null, "id": "fd2deb32-831c-48c5-9114-7054ecc53032", "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-detail.csv')['Body'])\n", "ax = df.set_index(pd.to_datetime(df.day, utc=True))[['alive_cls']].rolling(7, min_periods=1).mean().plot(figsize=(15, 9))\n", "ax.set(title='daily active clusters')\n", "ax.axvline(datetime(2022, 7, 26, tzinfo=timezone.utc), linestyle='--', color='r', label='hibernate idle clusters')\n", "ax.legend()" ] }, { "cell_type": "markdown", "id": "f08a1221-a2e2-4c24-9f7b-c55f57c91cf1", "metadata": {}, "source": [ "### M4：Weekly User Remaining Ratio" ] }, { "cell_type": "markdown", "id": "d27c8365-f993-4d30-8b3f-b03e59ff94ed", "metadata": {}, "source": [ "#### Remaining Number" ] }, { "cell_type": "code", "execution_count": null, "id": "07c0df24-c286-459c-bace-792f0f8e4df1", "metadata": {}, "outputs": [], "source": [ "udf = pd.DataFrame.from_records([{\n", " 'id': u.id(),\n", " 'time': u.signup_time(),\n", " 'alive': u.alive().days / 7,\n", "} for u in users])\n", "z = udf.set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "\n", "df = pd.DataFrame(dict([(f'W{w}', udf[udf.alive > w].set_index('time').groupby(pd.Grouper(freq='W-MON')).id.count()) for w in range(10)])).fillna(0)\n", "df.insert(0, 'New Signup User', z)\n", "df.index = df.index.date\n", "for i in range(df.shape[0]):\n", " for j in range(df.shape[1]):\n", " if i + j > df.shape[0]:\n", " df.iloc[i, j] = np.nan\n", "df" ] }, { "cell_type": "markdown", "id": "ab5acd13-b308-4310-940b-8c3b1b11b96e", "metadata": {}, "source": [ "#### Remaining Ratio" ] }, { "cell_type": "code", "execution_count": null, "id": "9f586560-65ac-4f20-a1c5-e4b5b5f20288", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame(dict([(f'W{w}', udf[udf.alive > w].set_index('time').groupby(pd.Grouper(freq='W-MON')).size() / z) for w in range(10)])).fillna(0)\n", "df.insert(0, 'New Signup User', z)\n", "df.index = df.index.date\n", "\n", "for i in range(df.shape[0]):\n", " for j in range(df.shape[1]):\n", " if i + j > df.shape[0]:\n", " df.iloc[i, j] = np.nan\n", "df.style.format(dict([(f'W{w}', \"{:.2%}\") for w in range(10)]))" ] }, { "cell_type": "markdown", "id": "bf730a57-05ef-4c37-8c6a-97518b2334da", "metadata": { "tags": [] }, "source": [ "## Task 1：Sign up / in" ] }, { "cell_type": "markdown", "id": "320ee6c5-dffe-492a-a666-4d6b5fe28f7e", "metadata": { "tags": [] }, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "a7caf2f7-05b3-473f-8a66-81e6cb1a911a", "metadata": { "tags": [] }, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "00315c3c-8b90-4940-bd6a-40b674eddee2", "metadata": { "tags": [] }, "source": [ "##### User SignUp Count\n", "\n", "User signup every week." ] }, { "cell_type": "code", "execution_count": null, "id": "762db6be-77ec-4cd2-a07b-fe47dc239a99", "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", "ax = df.plot(figsize=(15, 9))\n", "ax.set_ylabel('user count')\n", "ax.set(title='User SignUp Count')\n", "\n", "ax.legend()" ] }, { "cell_type": "markdown", "id": "2879f168-13bb-47b9-a2a8-a907aa418f2a", "metadata": {}, "source": [ "##### User Signin Count\n", "\n", "User signin every week." ] }, { "cell_type": "code", "execution_count": null, "id": "dc1250e5-d7c3-44a8-8c98-bcde08c39331", "metadata": {}, "outputs": [], "source": [ "df = event_df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "ax = df.plot(figsize=(15, 9))\n", "ax.set(title='User Signin Count')\n", "ax.set_ylabel('user count')" ] }, { "cell_type": "markdown", "id": "56950e3f-e2e6-47f3-9624-d8934e71a807", "metadata": {}, "source": [ "#### Engagement metrics\n", "N / A" ] }, { "cell_type": "markdown", "id": "4edcb5d0-06e5-48a4-aaaf-592514822a20", "metadata": {}, "source": [ "### Feature\n", "N / A" ] }, { "cell_type": "markdown", "id": "216287aa-7c6e-434a-91de-0a0ed4b69125", "metadata": {}, "source": [ "## Task 2：Create cluster" ] }, { "cell_type": "markdown", "id": "bc220e18-ca85-4f9d-b164-0f9dbfe519bc", "metadata": { "tags": [] }, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "504fd14e-7f59-4f1d-8b93-50e2163a6e27", "metadata": { "tags": [] }, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "c3070b8b-9eed-4cdc-a00e-e8214f7d2bcc", "metadata": { "tags": [] }, "source": [ "##### User rate who create cluster by different cluster type\n", "\n", "The rate of the user who create cluster within 24 hours after registration\n", "\n", "- the bar shows how many users signup every week\n", "- the line shows the rate of the signup user who create devtier cluster or dedicated cluster every week" ] }, { "cell_type": "code", "execution_count": null, "id": "1fba5db4-fa69-4327-84d7-62685cbc037c", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.signup_time(),\n", " 'devtier': len(u.devtier_clusters()) > 0 and (u.devtier_clusters().iloc[0].created_at - u.signup_time()) < timedelta(days=1),\n", " 'dedicate': len(u.dedicate_clusters()) > 0 and (u.dedicate_clusters().iloc[0].created_at - u.signup_time()) < timedelta(days=1), \n", "} for u in users])\n", "\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "f = pd.DataFrame({\n", " 'devtier': df[df.devtier].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique(),\n", " 'dedicate': df[df.dedicate].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique(),\n", "})\n", "\n", "ax = z.reset_index(drop=True).to_frame('signup user').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "f.div(z, axis=0).reset_index(drop=True).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who create cluster by different cluster type')" ] }, { "cell_type": "markdown", "id": "fc1af779-cc42-478c-9b8d-0b737456ac2f", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "92b1cafe-e219-45e3-88c9-4bca28284a18", "metadata": {}, "source": [ "##### Dev tier cluster alive time P90" ] }, { "cell_type": "code", "execution_count": null, "id": "d9bf7fd6-ac8f-489b-8000-383ef07dd3f5", "metadata": {}, "outputs": [], "source": [ "print('WIP')\n", "# def p90(x):\n", "# return x.quantile(0.90)\n", "\n", "# df = pd.DataFrame.from_records([{\n", "# 'time': u.signup_time(),\n", "# 'duration': u.clusters_alive().total_seconds() / 24 / 60 / 60,\n", "# } for u in users if len(u.devtier_clusters()) > 0 and len(u.dedicate_clusters()) == 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='Dev tier cluster alive time P90')" ] }, { "cell_type": "markdown", "id": "f1c2145f-7eb0-41d2-98f3-ac2d397c8570", "metadata": {}, "source": [ "### Feature 1：New cluster creation page" ] }, { "cell_type": "markdown", "id": "512cff95-1331-45ea-be16-154b580de804", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "a3c73992-2ad8-4878-a588-836a7a0af2a9", "metadata": {}, "source": [ "##### User rate who submit \"Create Cluster\"\n", "\n", "The submit rate of the user who click 'Create Cluster' button the first time.\n", "- the bar shows how many users click 'Create Cluster' button the first time\n", "- the line shows the rate of the user really created a cluster after click 'Create Cluster' button" ] }, { "cell_type": "code", "execution_count": null, "id": "20b78aa0-4c38-4923-aec4-0039497fd21f", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Create Cluster Button Clicked')[1],\n", " 'created': u.create_cluster_count() > 0 and (u.create_first_cluster_time()[1] - u.first_event('Create Cluster Button Clicked')[1]) < timedelta(days=1),\n", "} for u in users if u.count_event('Create Cluster Button Clicked') > 0])\n", "\n", "s = df[df.created].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who click create cluster button').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('submit rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who submit \"Create Cluster\"')" ] }, { "cell_type": "markdown", "id": "d471dd1c-db4f-4234-9c27-49657cbe4a01", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "d078cce6-a3ea-474f-8422-0a0812b59c10", "metadata": {}, "source": [ "##### User rate who meet failure during cluster creation\n", "\n", "The rate of the user who meet error after click submit cluster creation\n", "\n", "- the bar shows how many users submit cluster creation request\n", "- the line shows the rate of the user meet error after submit cluster creation request" ] }, { "cell_type": "code", "execution_count": null, "id": "eb0e81bb-bc72-4dba-8636-fca94e87eac4", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame()\n", "for u in users:\n", " for _, cluster in u.clusters().iterrows():\n", " df = pd.concat([df, pd.DataFrame.from_records([{\n", " 'id': u.id(),\n", " 'time': cluster['created_at'],\n", " 'is_create_error': pd.isna(cluster.creation_finished_time),\n", " }])])\n", "\n", "s = df[df.is_create_error].set_index('time').groupby(pd.Grouper(freq='W-MON')).id.nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON')).id.nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('create error rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='error rate for cluster creation')" ] }, { "cell_type": "markdown", "id": "ce0d97e1-1979-4bd0-9d8b-f88163c0f47d", "metadata": {}, "source": [ "##### P90 Developer Tier Tab Clicked before a devtier cluster created" ] }, { "cell_type": "code", "execution_count": null, "id": "facbfbdd-e3a4-4f53-aa5d-4dbc6f4cd97f", "metadata": {}, "outputs": [], "source": [ "print('WIP')\n", "#df = pd.DataFrame.from_records([{\n", "# 'unique user': u.id(),\n", "# 'member': u.members(),\n", "# 'time': u.create_first_cluster_time()[1],\n", "# 'count': u.count_event_with(lambda e: e['event'] == 'Developer Tier Tab Clicked' and e['time'] < u.create_first_cluster_time()[1]),\n", "#} for u in users if u.create_cluster_count() > 0])\n", "\n", "#ax = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['count'].agg(p90).plot(figsize=(15, 9))\n", "\n", "#ax.set_ylabel('click count)')\n", "#ax.set_xlabel('date')\n", "#ax.set_title('P90 Developer Tier Tab Clicked before a devtier cluster created')" ] }, { "cell_type": "markdown", "id": "52f202d7-36d6-47d8-b968-6c118d50c0b1", "metadata": {}, "source": [ "##### Create Dev tier Cluster Configure Duration P90\n", "\n", "Time spend on cluster creation form of 90% users who click \"Create Cluster Button\"." ] }, { "cell_type": "code", "execution_count": null, "id": "fe1176a2-0552-47e5-a32c-8c983b4e2da7", "metadata": {}, "outputs": [], "source": [ "def p90(x):\n", " return x.quantile(0.90)\n", "\n", "df = pd.DataFrame.from_records([{\n", " 'time': u.first_event('Create Cluster Button Clicked')[1],\n", " 'duration': u.event_duration_first('Create Cluster Button Clicked').total_seconds(),\n", "} for u in users if u.count_event('Create Cluster Button Clicked') > 0 and len(u.dedicate_clusters()) == 0 and len(u.devtier_clusters()) > 0])\n", "\n", "ax = df.set_index('time').duration.groupby(pd.Grouper(freq='W-MON')).agg(p90).plot(figsize=(15, 9))\n", "ax.set_ylabel('duration (seconds)')\n", "ax.set_xlabel('date')\n", "ax.set(title='Create Dev tier Cluster Configure Duration P90')" ] }, { "cell_type": "markdown", "id": "fa81661c-0f5a-44f3-b29b-d9378ea4f4c2", "metadata": {}, "source": [ "### Feature 2：Dev tier cluster creation time reduce" ] }, { "cell_type": "markdown", "id": "5f907b1e-d7a7-434d-9c5c-bd12c612bf37", "metadata": { "tags": [] }, "source": [ "#### Usage metrics\n", "N / A" ] }, { "cell_type": "markdown", "id": "b44abc87-c819-405e-9046-19d3bfd50f24", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "6c3d8b7b-609a-4bd8-b581-b03e913a8a2c", "metadata": {}, "source": [ "##### Dev tier cluster creation duration P90\n", "\n", "Time spend on creation for 90% devtier clusters every week." ] }, { "cell_type": "code", "execution_count": null, "id": "68c6fcaf-b8d4-4b1e-9d2a-1c7662e95b44", "metadata": {}, "outputs": [], "source": [ "devtier_df = pd.concat([u.devtier_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='W-MON')).agg(p90)\n", "\n", "ax = df.plot(figsize=(15, 9))\n", "ax.set_ylabel('duration (seconds)')\n", "ax.set_xlabel('date')\n", "ax.set_title('Dev tier cluster creation duration P90')" ] }, { "cell_type": "markdown", "id": "84f9cd70-c1a2-41ca-9d9d-8fa6fa339b2c", "metadata": {}, "source": [ "## Task 3：Connect to a Cluster" ] }, { "cell_type": "markdown", "id": "16707760-7a6f-442f-9be9-93af68592093", "metadata": {}, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "2c2f5aca-3b1f-45e4-a1bf-2431c04a888e", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "b35b6a7b-038b-4a0d-9c67-11621b09dc83", "metadata": {}, "source": [ "##### Cluster Connect Rate\n", "\n", "The connect rate of the users who create a cluster the first time\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of these users who connect the cluster within 24 hours after cluster created" ] }, { "cell_type": "code", "execution_count": null, "id": "1ef4ad68-a7a6-49f4-9f33-3348529297e4", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'connect': u.max_connection() > 0 and (u.first_connect() - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.connect].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('connect rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='Cluster Connect Rate')" ] }, { "cell_type": "markdown", "id": "3b2b795d-444a-4e59-aa44-0a2fef7d3194", "metadata": {}, "source": [ "#### Engagement metrics\n", "N / A " ] }, { "cell_type": "markdown", "id": "a26c746a-cc08-41ce-b602-cc2656feeefb", "metadata": {}, "source": [ "### Feature 1：MySQL/MyCLI client connection string" ] }, { "cell_type": "markdown", "id": "6a4cab1a-83e4-46d9-9f35-f119edd542eb", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "4b973ccd-a47d-474b-b4fa-b2520c06b4a8", "metadata": {}, "source": [ "##### User rate who copy connection string\n", "\n", "The rate of unique user who copy MySQL/MyCLI client connection string within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who copy MySQL/MyCLI connection string within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "12f72296-df64-4d88-8ee0-f9ba5fcf6dd7", "metadata": {}, "outputs": [], "source": [ "events = ['MySQL-Copy Button Clicked', 'MyCLI-Copy Button Clicked']\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'copy': u.count_event(*events) > 0 and (u.first_event(*events)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df['copy']].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('copy rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who copy connection string')" ] }, { "cell_type": "markdown", "id": "3bb83321-2d22-43c7-8ae3-67098b506724", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "5aa18e56-755f-443d-b0b9-04d45eb3b15c", "metadata": {}, "source": [ "##### User Connect Rate who copy connection string\n", "\n", "The rate of unique user who connect the cluster afeter copy MySQL/MyCLI client connection string\n", "\n", "- the bar shows how many users copy MySQL/MyCLI client connection string the first time\n", "- the line shows the rate of user who connect the cluster after copy MySQL/MyCLI connection string\n", "\n", "**Note**: this chart don't distinguish devtier and dedicated clusters" ] }, { "cell_type": "code", "execution_count": null, "id": "df9d9fba-f518-48bc-9c32-166b34fdb62c", "metadata": {}, "outputs": [], "source": [ "events = ['MySQL-Copy Button Clicked', 'MyCLI-Copy Button Clicked']\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event(*events)[1],\n", " 'connect': u.max_connection() > 0 and (u.first_connect() - u.first_event(*events)[1]) < timedelta(days=1),\n", "} for u in users if u.count_event(*events) > 0])\n", "\n", "s = df[df.connect].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who copy connect string').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('connect rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User Connect Rate who copy connection string')" ] }, { "cell_type": "markdown", "id": "a4c890a8-7764-4a5d-964b-467bd830eb7e", "metadata": {}, "source": [ "##### User's P90 ratio of copy times to connection number" ] }, { "cell_type": "code", "execution_count": null, "id": "a850806f-7f8a-4825-b928-6792dc281ade", "metadata": {}, "outputs": [], "source": [ "print('N / A')\n", "# user_df = users[0]._user_df\n", "# events = ['MySQL-Copy Button Clicked', 'MyCLI-Copy Button Clicked']\n", "# event_count = event_df[event_df.event.isin(events)].merge(user_df, left_on='$user_id', right_on='id').set_index('time').groupby([pd.Grouper(freq='D'), 'tenant_id']).event.count()\n", "# connection_count = connection_df.merge(cluster_df, left_on='cluster_id', right_on='id').set_index('time').groupby([pd.Grouper(freq='D'), 'tenant_id']).value.max()\n", "# df = pd.DataFrame.from_records([{\n", "# 'unique user': u.id(),\n", "# 'time': u.signup_time(),\n", "# 'rate': (u.count_event('MySQL-Copy Button Clicked') + u.count_event('MyCLI-Copy Button Clicked')) / u.max_connection(),\n", "# } for u in users if u.max_connection() > 0])\n", "\n", "# df = df.set_index('time').groupby(pd.Grouper(freq='D')).rate.agg(p90).rolling(15, min_periods=1).mean()\n", "# df.plot(figsize=(15, 9)).set_title(\"User's P90 ratio of copy times to connection number\")" ] }, { "cell_type": "markdown", "id": "e00b36d9-5640-450f-af93-0438ae2d0d58", "metadata": {}, "source": [ "### Feature 2：Connection's Sample Code" ] }, { "cell_type": "markdown", "id": "4abddac6-bc54-4421-9c0e-cae936c3ce71", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "d70d6ec6-0f17-4eee-a072-99c6144f7374", "metadata": {}, "source": [ "##### User rate who copy sample code\n", "\n", "\n", "The rate of unique user who copy sample code within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who copy sample code within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "3b5087f6-a164-4435-9dfc-f642d265c76b", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'JDBC-Copy Button Clicked',\n", " 'Python-Copy Button Clicked',\n", " 'Go-Copy Button Clicked',\n", " 'Node.js-Copy Button Clicked',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'copy': u.count_event(*events) > 0 and u.first_event(*events)[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "\n", "s = df[df['copy']].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('copy rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who copy sample code')" ] }, { "cell_type": "markdown", "id": "125251e3-8c94-4673-ad4b-23be2d947887", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "f7bea759-57bc-4536-a925-28a66f1af871", "metadata": {}, "source": [ "##### User Connect Rate who copy sample code\n", "\n", "The rate of unique user who connect the cluster afeter copy sample code\n", "\n", "- the bar shows how many users copy sample code the first time\n", "- the line shows the rate of user who connect the cluster after copy sample code\n", "\n", "**Note**: this chart don't distinguish devtier and dedicated clusters" ] }, { "cell_type": "code", "execution_count": null, "id": "aea63030-b9a2-4d31-b389-2f6260a13b87", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'JDBC-Copy Button Clicked',\n", " 'Python-Copy Button Clicked',\n", " 'Go-Copy Button Clicked',\n", " 'Node.js-Copy Button Clicked',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event(*events)[1],\n", " 'connect': u.max_connection() > 0 and (u.first_connect() - u.first_event(*events)[1]) < timedelta(days=1),\n", "} for u in users if u.count_event(*events) > 0])\n", "\n", "s = df[df.connect].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who copy sample code').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('connect rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User Connect Rate who copy sample code')" ] }, { "cell_type": "markdown", "id": "4ac53567-133c-430c-b2da-59e604461cad", "metadata": {}, "source": [ "### Feature 3: Web Shell" ] }, { "cell_type": "markdown", "id": "4ebc9954-e0a5-4206-8583-41a7ad67ea7b", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "35b7b170-710b-4fdb-9a73-35f50f2820b2", "metadata": {}, "source": [ "##### User rate who open web shell\n", "\n", "The rate of unique user who open web shell within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who open web shell within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "bca32387-1a4f-4a63-b6e4-3fa2b92c0c85", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'Open SQL Shell Button Clicked',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'open': u.count_event(*events) > 0 and u.first_event(*events)[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.open].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('open rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who open web shell')" ] }, { "cell_type": "markdown", "id": "6f6c7d22-c52d-45f6-81df-1b29e931717e", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "b33364c7-9403-4861-b956-a191003887ea", "metadata": {}, "source": [ "##### User connect rate who open web shell\n", "\n", "The rate of unique user who connect the cluster afeter open web shell\n", "\n", "- the bar shows how many users open web shell the first time\n", "- the line shows the rate of user who connect the cluster after open web shell\n", "\n", "**Note**: this chart don't distinguish devtier and dedicated clusters" ] }, { "cell_type": "code", "execution_count": null, "id": "b32ab5fb-3c8c-41b1-8a2b-e130de061f7b", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'Open SQL Shell Button Clicked',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event(*events)[1],\n", " 'connect': u.max_connection() > 0 and u.first_connect() - u.first_event(*events)[1] < timedelta(days=1),\n", "} for u in users if u.count_event(*events) > 0])\n", "\n", "s = df[df.connect].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who open sql shell').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('connect rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User connect rate who open web shell')" ] }, { "cell_type": "markdown", "id": "dd1f908e-af07-4c15-b45d-71d976cd378d", "metadata": {}, "source": [ "### Feature 4：Allow access from anywhere" ] }, { "cell_type": "markdown", "id": "93a8af07-38ab-4ed5-a5a1-098e255b3b1e", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "7d41bb1f-0dd6-492f-b4fe-a7aa55f1a704", "metadata": {}, "source": [ "##### User rate who click \" Allow access from anywhere\" button\n", "\n", "The rate of unique user who click \"Allow access from anywhere\" button within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click \" Allow access from anywhere\" button within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "31033e74-b9e7-482e-a864-d5eebda1a345", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'Allow Access from Anywhere Button Clicked',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'click': u.count_event(*events) > 0 and u.first_event(*events)[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.click].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('click rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who click \"Allow access from anywhere\" button')" ] }, { "cell_type": "markdown", "id": "e18b0bdc-68f8-462c-9edf-42a4514cca82", "metadata": {}, "source": [ "#### Engagement metrics\n", "N / A" ] }, { "cell_type": "markdown", "id": "888e732a-e1ca-401a-aced-cfb418f9eb0d", "metadata": {}, "source": [ "### Feature 5：Generate password" ] }, { "cell_type": "markdown", "id": "83870a2e-9036-436c-91b5-9532716d13c8", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "2cab0eb0-c9cb-4cf7-8a07-80a075716dd6", "metadata": {}, "source": [ "##### User rate who click \"Generate Root Password\" button\n", "\n", "The rate of unique user who click \"Generate Root Password\" button within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click \"Generate Root Password\" button within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "9282af69-c694-4497-a411-5c11de885128", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'Generate Root Password Button Clicked',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'gen': u.count_event(*events) > 0 and u.first_event(*events)[1] -u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.gen].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('gen password rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who click \"Generate Root Password\" button')" ] }, { "cell_type": "markdown", "id": "26023a06-9eb1-4b35-937b-1efd24437459", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "e5c3670e-b4a9-4a62-a31c-0af84e7a76c2", "metadata": {}, "source": [ "##### User rate of who generate password but not copy\n", "\n", "The rate of unique user who not copy password afeter generate password\n", "\n", "- the bar shows how many users click \"Generate Root Password\" button the first time\n", "- the line shows the rate of user who not copy password after click \"Generate Root Password\" button\n", "\n", "**Note**: this chart don't distinguish devtier and dedicated clusters" ] }, { "cell_type": "code", "execution_count": null, "id": "844a1749-b985-464a-a66a-c5dd1a081ef0", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Generate Root Password Button Clicked')[1],\n", " 'copy': u.count_event('-Copy Button Clicked') > 0 and u.first_event('-Copy Button Clicked')[1] - u.first_event('Generate Root Password Button Clicked')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Generate Root Password Button Clicked') > 0])\n", "\n", "s = df[~df['copy']].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who generate password').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('not copy password rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate of who generate password but not copy')" ] }, { "cell_type": "markdown", "id": "c37779e4-39e3-4c64-ba5a-c37edab42f0a", "metadata": {}, "source": [ "## Task 4：Playground" ] }, { "cell_type": "markdown", "id": "b74c1ea1-250c-451f-aa4c-23f8c1578710", "metadata": {}, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "11c532c5-b020-497e-a8ab-7b8451a4c1fb", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "0fd8cf75-830e-4a7d-8a10-41514649bb41", "metadata": {}, "source": [ "##### User rate who click \"Run\" button or \"Ctrl/CMD + Enter\" once in playground\n", "\n", "The rate of unique user who click \"Run\" button or \"Ctrl/CMD + Enter\" within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click \"Run\" button or \"Ctrl/CMD + Enter\" within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "4ea9edee-1283-4741-abd8-2f5c742ba7f2", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'run': u.count_event('Playground Run Button Clicked') > 0 and u.first_event('Playground Run Button Clicked')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.run].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('run playground rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who click \"Run\" button or \"Ctrl/CMD + Enter\" once in playground')" ] }, { "cell_type": "markdown", "id": "af8d0be5-6f81-4251-9845-a031540bbd18", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "a3f71e8c-6a84-4478-909f-689f22504efd", "metadata": {}, "source": [ "##### P50 Run Button Clicked\n", "\n", "\"Playground Run Button\" click times for 50% user every week." ] }, { "cell_type": "code", "execution_count": null, "id": "eb949759-7da3-4fca-9a5f-e6778b8b5fdf", "metadata": {}, "outputs": [], "source": [ "df = event_df[event_df.event == 'Playground Run Button Clicked'].set_index('time').groupby([pd.Grouper(freq='D'), '$user_id']).event.count()\n", "df = df.reset_index('$user_id').groupby(pd.Grouper(freq='D')).event.median().rolling(7, min_periods=1).mean()\n", "ax = df.plot(figsize=(15, 9))\n", "ax.set(title='P50 Run Button Clicked')\n", "ax.set_ylabel('click count')" ] }, { "cell_type": "markdown", "id": "6cebaa32-9b87-46bc-a5fb-91e5aa5b9b86", "metadata": {}, "source": [ "### Feature 1：SQL compatibility experience" ] }, { "cell_type": "markdown", "id": "fd0a8333-a6db-474d-8c3e-22fd0e0fed7d", "metadata": { "tags": [] }, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "4ff178e0-43c8-4f95-8e20-c6e3b026343c", "metadata": {}, "source": [ "##### User rate who experience SQL compatibility capability\n", "\n", "The rate of unique user who experience SQL compatibility capability within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who experience SQL compatibility capability within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "1323a5be-2021-49c5-9660-125551551fcd", "metadata": {}, "outputs": [], "source": [ "tabs = [\n", " 'Get started with sample queries',\n", " 'Insights into programming languages',\n", " 'Kubernetes is so popular in open-source world',\n", "]\n", "fn = lambda r: r['event'] == 'Playground Run Button Clicked' and r['menu'] in tabs\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'run': u.count_event_with(fn) > 0 and u.first_event_with(fn)[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.run].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('experience rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who experience SQL compatibility capability')" ] }, { "cell_type": "markdown", "id": "843e0e16-4df9-4b19-b09d-0f05040702b8", "metadata": {}, "source": [ "#### Engagement metrics\n", "WIP" ] }, { "cell_type": "markdown", "id": "43dd9eb0-f7a8-49f6-8191-3f3f7963bbdc", "metadata": {}, "source": [ "### Feature 2：HTAP experience" ] }, { "cell_type": "markdown", "id": "e9a0d615-0af7-4f96-bdab-248eaec1d1d4", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "a06aafe8-8a2d-4cac-9652-cd5296368e4b", "metadata": {}, "source": [ "##### User rate who experience HTAP capability\n", "\n", "The rate of unique user who experience HTAP capability within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who experience HTAP capability within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "0b0a5680-e563-4178-9bc0-6bf6e9b0429d", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'run': u.events()[\n", " (u.events()['event'] == 'Playground Run Button Clicked') & \n", " (u.events()['menu'] == 'Learn HTAP with 3 SQL Statements')\n", " ].sql.nunique() > 3 and u.first_event('Playground Run Button Clicked')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.run].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('experience rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who experience HTAP capability')" ] }, { "cell_type": "markdown", "id": "3e3c6145-110a-4db3-af0c-cbdb424b813e", "metadata": {}, "source": [ "#### Engagement metrics\n", "WIP" ] }, { "cell_type": "markdown", "id": "92d00dad-3922-4843-bcb1-e0c3709fe7f0", "metadata": {}, "source": [ "### Feature 3：Run your sql" ] }, { "cell_type": "markdown", "id": "2f6dc2e3-18eb-402b-8f59-9d5a4e771f08", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "17c6f137-5d34-45d3-aac4-8bf342948a70", "metadata": {}, "source": [ "##### User rate who run your sql once\n", "\n", "The rate of unique user who run custom sql with playground within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who run custom sql with playground within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "f058aeb4-d3a1-4025-a8ee-bdfd685a7047", "metadata": {}, "outputs": [], "source": [ "tabs = [\n", " 'Edit and Run Your SQL',\n", "]\n", "fn = lambda r: r['event'] == 'Playground Run Button Clicked' and r['menu'] in tabs\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'run': u.count_event_with(fn) > 0 and u.first_event_with(fn)[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.run].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('run rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who run your sql once')" ] }, { "cell_type": "markdown", "id": "be01b2d1-d858-40b7-9941-3002405fbb23", "metadata": {}, "source": [ "#### Engagement metrics\n", "N / A" ] }, { "cell_type": "markdown", "id": "817561fe-b595-483f-9c9d-23cf473ed01e", "metadata": {}, "source": [ "## Task 5：Import" ] }, { "cell_type": "markdown", "id": "58a3ddf1-ad78-4ee0-93da-d3dae3b0aace", "metadata": {}, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "d43c7e08-6c1f-45ce-bf6b-ee1d7d14cecb", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "d7a5652c-ab23-443e-ae3e-8e39dba25e40", "metadata": {}, "source": [ "##### User rate who enter import page and submit import\n", "\n", "The rate of unique user who import data success within 24 hours after first click import button\n", "\n", "- the bar shows how many users click import button the first time\n", "- the line shows the rate of user who submit import success after they click import button\n", "\n", "**Note**: this chart don't distinguish devtier and dedicated clusters" ] }, { "cell_type": "code", "execution_count": null, "id": "8e374624-92c6-466e-bc9d-cd85ef017ae7", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'submit': u.count_event('Import Form Submission') > 0 and u.first_event('Import Form Submission')[1] - u.first_event('Import Button Clicked')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Import Button Clicked') > 0])\n", "\n", "s = df[df.submit].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who click import button').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('submit import rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate who submit import')" ] }, { "cell_type": "markdown", "id": "cd8a6d46-ba03-40f4-a5e8-bda5fa4b9fd7", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "46728502-b4fc-4381-880e-7e9a66aeed49", "metadata": {}, "source": [ "##### Import failure rate after submission\n", "\n", "The error rate of imports\n", "\n", "- the bar shows how many imports every week\n", "- the line shows the error rate of the imports" ] }, { "cell_type": "code", "execution_count": null, "id": "6778a3ab-943b-4c4f-b862-5b6e01fc7754", "metadata": {}, "outputs": [], "source": [ "devtier_count = import_df[import_df.cluster_type == 'devtier'].set_index('created_at').groupby(pd.Grouper(freq='W-MON')).id.count().to_frame('devtier imports')\n", "dedicated_count = import_df[import_df.cluster_type == 'dedicated'].set_index('created_at').groupby(pd.Grouper(freq='W-MON')).id.count().to_frame('dedicated imports')\n", "z = pd.merge(devtier_count, dedicated_count, on='created_at')\n", "f = pd.DataFrame({\n", " 'devtier failed rate': import_df[(import_df.cluster_type == 'devtier') & (import_df.status == 'failed')].set_index('created_at').groupby(pd.Grouper(freq='W-MON')).id.count() / devtier_count['devtier imports'],\n", " 'dedicated failed rate': import_df[(import_df.cluster_type == 'dedicated') & (import_df.status == 'failed')].set_index('created_at').groupby(pd.Grouper(freq='W-MON')).id.count() / dedicated_count['dedicated imports'],\n", "})\n", "df = pd.merge(f, z, on='created_at').fillna(0)\n", "ax = df[['devtier imports', 'dedicated imports']].plot(kind='bar', width=0.2, figsize=(15, 9))\n", "df[['devtier failed rate', 'dedicated failed rate']].reset_index(drop=True).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in df.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('import count')\n", "ax.set(title='Import failure rate after submission')" ] }, { "cell_type": "markdown", "id": "8d062bba-680b-4c4d-adf9-605af16fbd4c", "metadata": {}, "source": [ "##### User rate of each step page entrance" ] }, { "cell_type": "code", "execution_count": null, "id": "e43ebb52-ac4f-41b5-ac4f-923bb86a9fb4", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'step 1': u.count_event('Data Import Page View') > 0 and u.first_event('Data Import Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", " 'step 2': u.count_event('Data Import Step 2 Page View') > 0 and u.first_event('Data Import Step 2 Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", " 'step 3': u.count_event('Data Import Step 3 Page View') > 0 and u.first_event('Data Import Step 3 Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "f1 = df[df['step 1']].set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "f2 = df[df['step 2']].set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "f3 = df[df['step 3']].set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "\n", "ax = z.reset_index(drop=True).to_frame('User count who create cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "((pd.Series(0, index=z.index) if f1.empty else f1)/z).reset_index(drop=True).to_frame('step 1 entrance rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "((pd.Series(0, index=z.index) if f2.empty else f2)/z).reset_index(drop=True).to_frame('step 2 entrance rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "((pd.Series(0, index=z.index) if f3.empty else f3)/z).reset_index(drop=True).to_frame('step 3 entrance rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate of each step page entrance')" ] }, { "cell_type": "markdown", "id": "628af865-6886-4dda-87ee-2309872b7a07", "metadata": {}, "source": [ "##### P50 Duration of each step page stay time\n", "\n", "N / A" ] }, { "cell_type": "markdown", "id": "0b1e4088-10f1-4918-b766-be38dff99645", "metadata": {}, "source": [ "### Feature 1：Validation" ] }, { "cell_type": "markdown", "id": "24936985-174c-4fa3-ab75-bdb242c69c01", "metadata": {}, "source": [ "#### Usage metrics\n", "\n", "N / A" ] }, { "cell_type": "markdown", "id": "af3eed63-a179-4997-bb96-5bc19d58446a", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "775033f4-cd5f-4137-ac45-bbff565f4937", "metadata": {}, "source": [ "##### User rate who meet validation error in 3 steps" ] }, { "cell_type": "code", "execution_count": null, "id": "0379388b-eafa-4412-9250-9e5fdf248182", "metadata": {}, "outputs": [], "source": [ "df1 = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Step 1 Source Access Check Started')[1],\n", " 'failed': u.count_event('Step 1 Source Access Check Failed') > 0 and u.first_event('Step 1 Source Access Check Failed')[1] - u.first_event('Step 1 Source Access Check Started')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Step 1 Source Access Check Started') > 0], columns=['unique user', 'time', 'failed'])\n", "\n", "df2 = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Step 2 Filter And Rule Check Started')[1],\n", " 'failed': u.count_event('Step 2 Filter And Rule Check Failed') > 0 and u.first_event('Step 2 Filter And Rule Check Failed')[1] - u.first_event('Step 2 Filter And Rule Check Started')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Step 2 Filter And Rule Check Started') > 0], columns=['unique user', 'time', 'failed'])\n", "\n", "df3 = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Step 3 Submit Import Precheck Started')[1],\n", " 'failed': u.count_event('Step 3 Submit Import Precheck Failed') > 0 and u.first_event('Step 3 Submit Import Precheck Failed')[1] - u.first_event('Step 3 Submit Import Precheck Started')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Step 3 Submit Import Precheck Started') > 0], columns=['unique user', 'time', 'failed'])\n", "\n", "f1 = pd.DataFrame(columns=['time']) if df1.empty else df1[df1.failed].set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "f2 = pd.DataFrame(columns=['time']) if df2.empty else df2[df2.failed].set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "f3 = pd.DataFrame(columns=['time']) if df3.empty else df3[df3.failed].set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "\n", "s = df1.set_index('time').groupby(pd.Grouper(freq='W-MON')).size()\n", "df = pd.DataFrame({\n", " 'Step 1 User Count': s,\n", " 'Step 2 User Count': pd.Series(0, index=s.index) if df2.empty else df2.set_index('time').groupby(pd.Grouper(freq='W-MON')).size(),\n", " 'Step 3 User Count': pd.Series(0, index=s.index) if df3.empty else df3.set_index('time').groupby(pd.Grouper(freq='W-MON')).size(),\n", " 'Step 1 Failed Rate': pd.Series(0, index=s.index) if f1.empty else f1 / df1.set_index('time').groupby(pd.Grouper(freq='W-MON')).size(),\n", " 'Step 2 Failed Rate': pd.Series(0, index=s.index) if f2.empty else f2 / df2.set_index('time').groupby(pd.Grouper(freq='W-MON')).size(),\n", " 'Step 3 Failed Rate': pd.Series(0, index=s.index) if f3.empty else f3 / df3.set_index('time').groupby(pd.Grouper(freq='W-MON')).size(),\n", "})\n", "\n", "ax = df[['Step 1 User Count', 'Step 2 User Count', 'Step 3 User Count']].plot(kind='bar', width=0.1, figsize=(15, 9))\n", "df[['Step 1 Failed Rate', 'Step 2 Failed Rate', 'Step 3 Failed Rate']].reset_index(drop=True).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in df.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('count')\n", "ax.set(title='User rate who meet validation error in 3 steps')" ] }, { "cell_type": "markdown", "id": "f5eb4c29-5be3-4136-957e-98a9b4e5b5f3", "metadata": {}, "source": [ "## Task 6：Diagnosis" ] }, { "cell_type": "markdown", "id": "44d415cc-836c-4736-98d5-07129857db12", "metadata": {}, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "2cfd7743-b672-4f9c-a0b3-c6a4e17ff639", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "ef5ee455-2486-440f-bf3c-0820065cf08b", "metadata": {}, "source": [ "##### User rate who do Diagnosis Page View\n", "\n", "The rate of unique user who view Diagnosis Page within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who view Diagnosis Page within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "2158970d-47fc-4285-9bf7-47f0d203f896", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'view': u.count_event('Diagnosis Page View') > 0 and u.first_event('Diagnosis Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.view].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('view Diagnosis Page rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('count')\n", "ax.set(title='User rate who do Diagnosis Page View')" ] }, { "cell_type": "markdown", "id": "5b3448ee-bd51-4757-8e56-14ca2f340b6e", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "93c1f45d-ed89-406b-b133-15431bef87ea", "metadata": {}, "source": [ "##### Weekly distribution of time users spend on Diagnosis page\n", "\n", "Distributon of duration of users spend on Diagnosis page, each point is an unique user, the y label is duration the user spend on this page." ] }, { "cell_type": "code", "execution_count": null, "id": "fd9e86ff-a247-4749-902f-9a34d3e08b93", "metadata": {}, "outputs": [], "source": [ "events = [\n", " 'Diagnosis Page View',\n", "]\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'duration': u.event_duration_sum(*events).total_seconds()\n", "} for u in users if u.create_cluster_count() > 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')\n", "ax.set_ylabel('duration (seconds)')" ] }, { "cell_type": "markdown", "id": "8d2b3360-7c45-4a12-8e24-30efdd66704d", "metadata": {}, "source": [ "### Feature 1：SQL statement Diagnosis" ] }, { "cell_type": "markdown", "id": "e20cd0d5-532a-47bf-90a8-c1e92064e047", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "f513392a-77b2-4dd2-965b-b5bc32b83f80", "metadata": {}, "source": [ "##### User rate who click one sql statement page\n", "\n", "The rate of unique user who click one sql statement page within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click one sql statement page within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "9c7c81f3-8282-47d8-8ce0-0a2d49f39640", "metadata": {}, "outputs": [], "source": [ "fn = lambda r: r['event'] == 'Page Change' and r['$current_url'] == '#/statement/detail'\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'view': u.count_event_with(fn) > 0 and u.first_event_with(fn)[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.view].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('click one sql statement page rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('count')\n", "ax.set(title='User rate who click one sql statement page')" ] }, { "cell_type": "markdown", "id": "eef409cf-0398-43d8-82ff-91c1463988c8", "metadata": {}, "source": [ "#### Engagement metrics\n", "\n", "N / A" ] }, { "cell_type": "markdown", "id": "8b2532c8-ad8c-4a62-808e-863a3811df1c", "metadata": {}, "source": [ "### Feature 2：Key Viz" ] }, { "cell_type": "markdown", "id": "86f87aeb-2b00-49ef-bd4c-4b8c24625c44", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "9ac40000-fe11-488e-8937-4e3ae4d52c1f", "metadata": {}, "source": [ "##### User rate who click keyviz Tab\n", "\n", "The rate of unique user who click keyviz Tab within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click keyviz Tab within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "1334c59a-9ee6-4253-8f70-2d93a598d0b6", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'click': u.count_event('keyviz Tab Clicked') > 0 and u.first_event('keyviz Tab Clicked')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.click].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('click keyviz tab rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who click keyviz Tab')" ] }, { "cell_type": "markdown", "id": "58851daa-19af-4851-b767-3bf4a8a541da", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "0f11a469-39c4-41be-945d-b2ab1648f6bb", "metadata": {}, "source": [ "##### Weekly distribution of stay time user spend on keyviz\n", "\n", "Distributon of duration of users spend on keyviz page, each point is an unique user, the y label is duration the user spend on this page." ] }, { "cell_type": "code", "execution_count": null, "id": "c9a8d72b-cceb-413d-b39c-9a4e20a809b7", "metadata": {}, "outputs": [], "source": [ "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])\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": "markdown", "id": "23e51f29-50e0-4b3c-b47f-a23e4f7f5c06", "metadata": {}, "source": [ "##### User rate who change brigetness\n", "\n", "The rate of unique user who change brigetness within 24 hours after click keyviz Tab\n", "\n", "- the bar shows how many users click keyviz Tab the first time\n", "- the line shows the rate of user who change brigetness within 24 hours after they click keyviz Tab" ] }, { "cell_type": "code", "execution_count": null, "id": "f0d57b1d-8248-4af2-aa53-2215586024bd", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('keyviz Tab Clicked')[1],\n", " 'click': u.count_event('KeyViz: Change Bright') > 0 and u.first_event('KeyViz: Change Bright')[1] - u.first_event('keyviz Tab Clicked')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('keyviz Tab Clicked') > 0])\n", "\n", "s = df[df.click].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user clicked keyviz tab').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('change brigetness rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who change brigetness')" ] }, { "cell_type": "markdown", "id": "adfc9882-76c2-4977-8796-cdeec847c0de", "metadata": {}, "source": [ "##### User rate who change metrics\n", "\n", "The rate of unique user who change metrics within 24 hours after click keyviz Tab\n", "\n", "- the bar shows how many users click keyviz Tab the first time\n", "- the line shows the rate of user who change metrics within 24 hours after they click keyviz Tab\n", "\n", "**Note**: this chart don't distinguish devtier and dedicated clusters" ] }, { "cell_type": "code", "execution_count": null, "id": "78460873-26c7-466b-8afc-a07fb193c65d", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('keyviz Tab Clicked')[1],\n", " 'click': u.count_event('KeyViz: Change Metric') > 0 and u.first_event('KeyViz: Change Metric')[1] - u.first_event('keyviz Tab Clicked')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('keyviz Tab Clicked') > 0])\n", "\n", "s = df[df.click].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user clicked keyviz tab').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('change metrics rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who change metrics')" ] }, { "cell_type": "markdown", "id": "93ee87f0-1d31-4b56-8f7a-1087395640d2", "metadata": {}, "source": [ "## Task 7：Monitor cluster" ] }, { "cell_type": "markdown", "id": "49e5651e-fe87-4380-9ad1-30288d75d9a2", "metadata": {}, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "7cedc438-0e2e-4bf4-977a-4db72a476be1", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "837fb820-6522-4d40-88bd-c0d6b845c1ff", "metadata": {}, "source": [ "##### User rate who do Integrations Page View\n", "\n", "The rate of unique user who view Integrations Page within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who view Integrations Page within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "8df348d9-91a2-44c7-9115-c96f9cb44cee", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'view': u.count_event('Integrations Page View') > 0 and u.first_event('Integrations Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.view].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('view rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who do Integrations Page View')" ] }, { "cell_type": "markdown", "id": "99487b58-0196-4861-8261-6e87761fd853", "metadata": {}, "source": [ "#### Engagement metrics\n", "\n", "WIP" ] }, { "cell_type": "markdown", "id": "40e9a4db-8719-42a2-b1a1-1c2c9b2c89a3", "metadata": {}, "source": [ "### Feature 1：Alerts" ] }, { "cell_type": "markdown", "id": "6772a1b0-c2a0-4f5a-911e-f4702004ddd8", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "faa63c24-4dba-4ab9-80ae-8f2af3003bcd", "metadata": {}, "source": [ "##### User rate who click \"Subscribe\" button\n", "\n", "The rate of unique user who click Alerts Subscribe Button within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click Alerts Subscribe Button within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "aca813e0-083e-471f-b145-e922b83ae275", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'subscribe': u.count_event('Alerts Subscribe Button Clicked') > 0 and u.first_event('Alerts Subscribe Button Clicked')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.subscribe].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who create devtier cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('subscribe rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who click \"Subscribe\" button')" ] }, { "cell_type": "markdown", "id": "00922e87-99d8-4207-b105-416be25651d2", "metadata": {}, "source": [ "#### Engagement metrics\n", "\n", "WIP" ] }, { "cell_type": "markdown", "id": "4bddef8c-735e-4c31-9f93-f1dd81c63d01", "metadata": {}, "source": [ "## Task 8：Upgrade" ] }, { "cell_type": "markdown", "id": "04b713d6-9858-41bc-a583-9c681d6d46b4", "metadata": {}, "source": [ "### Task metrics" ] }, { "cell_type": "markdown", "id": "b91f4ee6-e015-46ba-b77a-0232956ba07c", "metadata": {}, "source": [ "#### Usage metrics\n", "\n", "N / A" ] }, { "cell_type": "markdown", "id": "09a2f877-643d-4acd-b3f1-bd57ada0ddf8", "metadata": {}, "source": [ "#### Engagement metrics\n", "\n", "N / A" ] }, { "cell_type": "markdown", "id": "1c5fde17-986b-4cc4-b476-eff149589170", "metadata": {}, "source": [ "### Feature 1：PoC application" ] }, { "cell_type": "markdown", "id": "da26241e-5bb1-472b-af46-0e00e887cbc2", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "c6c8cfe2-70f9-4ae8-a164-8fffc6452cc8", "metadata": {}, "source": [ "##### User rate who submit PoC application\n", "\n", "The rate of weekly active user who submit PoC application\n", "\n", "- the bar shows how many users active every week\n", "- the line shows the rate of user who submit PoC application" ] }, { "cell_type": "code", "execution_count": null, "id": "fa75bc1e-66a2-4054-a6e6-1cf4293972af", "metadata": {}, "outputs": [], "source": [ "s = event_df[event_df.event == 'PoC Form Submission'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "z = event_df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('weekly active user').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('PoC Form Submission Rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who submit PoC application')" ] }, { "cell_type": "markdown", "id": "d54bf90d-1456-4607-b2d7-cec38e2291dd", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "88cb6e23-2892-4ae2-899a-653d20e994bf", "metadata": {}, "source": [ "##### User rate who open PoC form but leave\n", "\n", "The rate of unique user who open PoC form but not submit\n", "\n", "- the bar shows how many users open PoC form the first time\n", "- the line shows the rate of user who not submit PoC form within 24 hours after they open PoC form" ] }, { "cell_type": "code", "execution_count": null, "id": "341d893e-07fc-4c68-9776-a6758d09d7c8", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Open PoC Form')[1],\n", " 'submit': u.count_event('PoC Form Submission') > 0 and u.first_event('PoC Form Submission')[1] - u.first_event('Open PoC Form')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Open PoC Form') > 0])\n", "\n", "s = df[~df.submit].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who open poc form').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('leave rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who open PoC form but leave')" ] }, { "cell_type": "markdown", "id": "b3b1ae0b-f299-4e88-8fcc-d2e37f0d3b5e", "metadata": {}, "source": [ "##### User rate who meet failure after submission\n", "\n", "The rate of the user who meet error after submit poc form\n", "\n", "- the bar shows how many users submit poc request\n", "- the line shows the rate of the user meet error after submit poc request" ] }, { "cell_type": "code", "execution_count": null, "id": "1fbd209e-70d8-4177-a943-35a5a77e4f56", "metadata": {}, "outputs": [], "source": [ "s = error_df[error_df.title.str.contains('PoC Form Submit Error')].set_index('time').groupby(pd.Grouper(freq='W-MON')).user_id.nunique()\n", "z = event_df[event_df.event == 'PoC Form Submission'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "\n", "if len(s) == 0:\n", " s = event_df[event_df.event == 'PoC Form Submission'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].agg(lambda xs: 0)\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who submit poc form').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).fillna(0).to_frame('submit error rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='error rate for cluster creation')" ] }, { "cell_type": "markdown", "id": "aedea1f5-d62e-4565-bc0b-b965a9a6653a", "metadata": {}, "source": [ "### Feature 2：Upgrade to create dedicated cluster" ] }, { "cell_type": "markdown", "id": "c95220db-4801-49f5-a7a4-8b020b211e3d", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "9afe1453-7417-459a-8f4d-916b165a25ed", "metadata": {}, "source": [ "##### User rate who click upgrade button\n", "\n", "The rate of unique user who click upgrade button within 24 hours after cluster creation\n", "\n", "- the bar shows how many users create a cluster the first time\n", "- the line shows the rate of user who click upgrade button within 24 hours after they create the cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "40b6d350-fab4-4de5-b78a-82b0ca12874e", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'click': u.count_event('Upgrade Link Clicked') > 0 and u.first_event('Upgrade Link Clicked')[1] - u.create_first_cluster_time()[1] < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s = df[df.click].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('upgrade link click rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who click upgrade button')" ] }, { "cell_type": "markdown", "id": "ff52d6a7-9bc7-452b-b446-acaf7eafd755", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "c88049d5-1fd2-42c4-9e6c-5de15d346212", "metadata": {}, "source": [ "##### User rate who open upgrade page but leave\n", "\n", "The rate of unique user who open upgrade page but not submit\n", "\n", "- the bar shows how many users open upgrade page the first time\n", "- the line shows the rate of user who not submit upgrade form within 24 hours after they open upgrade page" ] }, { "cell_type": "code", "execution_count": null, "id": "259b5181-7692-4af7-aabc-0f5a22f94166", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.first_event('Upgrade Link Clicked')[1],\n", " 'upgrade': len(u.dedicate_clusters()) > 0 and u.dedicate_clusters().iloc[0].created_at - u.first_event('Upgrade Link Clicked')[1] < timedelta(days=1),\n", "} for u in users if u.count_event('Upgrade Link Clicked') > 0])\n", "\n", "s = df[~df.upgrade].set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who click upgrade link click').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('leave rate').fillna(0).plot(secondary_y=True, ax=ax, color='y').set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who open upgrade page but leave')" ] }, { "cell_type": "markdown", "id": "e870a6ae-079d-49c1-94de-3b5935ee0ad6", "metadata": {}, "source": [ "### Feature 3：Bind credit card" ] }, { "cell_type": "markdown", "id": "27370fad-9669-4c94-8ab2-5cafcc3681d6", "metadata": {}, "source": [ "#### Usage metrics" ] }, { "cell_type": "markdown", "id": "17a4f369-87f6-4d0d-a424-598be3a78f23", "metadata": {}, "source": [ "##### User rate who Became A Billed Customer\n", "\n", "The rate of weekly active user who Became A Billed Customer\n", "\n", "- the bar shows how many users active every week\n", "- the line shows the rate of user who Became A Billed Customer" ] }, { "cell_type": "code", "execution_count": null, "id": "c41fae40-68b4-44bb-b5ff-6bc5a5938c78", "metadata": {}, "outputs": [], "source": [ "s = event_df[event_df.event == 'Became A Billed Customer'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "z = event_df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('weekly active user').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('Became A Billed Customer Rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who Became A Billed Customer')" ] }, { "cell_type": "markdown", "id": "f579c10a-ec63-4176-bb65-ca12beb513b5", "metadata": {}, "source": [ "##### User rate who click \"add new card\"\n", "\n", "The rate of weekly active user who click \"add new card\"\n", "\n", "- the bar shows how many users active every week\n", "- the line shows the rate of user who click \"add new card\"" ] }, { "cell_type": "code", "execution_count": null, "id": "7039a377-e8db-4152-b078-04a5ac1d9091", "metadata": {}, "outputs": [], "source": [ "s = event_df[event_df.event == 'Payment Add A New Card Button Clicked'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "z = event_df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "if len(s) == 0:\n", " s = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].agg(lambda xs: 0)\n", "\n", "ax = z.reset_index(drop=True).to_frame('weekly active user').plot(kind='bar', width=0.1, figsize=(15, 9))\n", "(s/z).reset_index(drop=True).to_frame('Payment Add A New Card Button Click Rate').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", "ax.set(title='User rate who click Add A New Card Button')" ] }, { "cell_type": "markdown", "id": "00023753-babe-4f3d-b73a-ef7035febe83", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "f0437c52-e29b-45fd-a3a5-2e2418ee01e5", "metadata": {}, "source": [ "##### User rate who click \"add new card\" but leave\n", "\n", "The rate of unique user who click \"add new card\" but not submit\n", "\n", "- the bar shows how many users click \"add new card\" the first time\n", "- the line shows the rate of user who not submit within 24 hours after they click \"add new card\"" ] }, { "cell_type": "code", "execution_count": null, "id": "fb2627d7-90e1-4998-b9d0-a6ea0307ce42", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.signup_time(),\n", " 'billed': u.count_event('Became A Billed Customer') > 0,\n", "} for u in users if u.count_event('Payment Add A New Card Button Clicked') > 0])\n", "\n", "if len(df) == 0:\n", " print('No Data')\n", "else:\n", " s = df[~df.billed].set_index('time').groupby(pd.Grouper(freq='D'))['unique user'].nunique()\n", " z = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].nunique()\n", " if len(s) == 0:\n", " s = df.set_index('time').groupby(pd.Grouper(freq='W-MON'))['unique user'].agg(lambda xs: 0)\n", "\n", " ax = z.reset_index(drop=True).to_frame('users clicked add card button').plot(kind='bar', width=0.1, figsize=(15, 9))\n", " (s/z).reset_index(drop=True).to_frame('users cancel add card').fillna(0).plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", " ax.set_xlabel('date')\n", " ax.set_ylabel('user count')\n", " _ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", " ax.set(title='User rate who click \"add new card\" but leave')" ] }, { "cell_type": "markdown", "id": "ba863f30-43fd-4bc3-a48d-116eee1b7413", "metadata": {}, "source": [ "##### User rate who meet failure after submission\n", "\n", "The rate of the user who meet error after click \"add new card\"\n", "\n", "- the bar shows how many users click \"add new card\"\n", "- the line shows the rate of the user meet error after click \"add new card\"" ] }, { "cell_type": "code", "execution_count": null, "id": "9bf1663c-3690-4865-8391-67995a8c487b", "metadata": {}, "outputs": [], "source": [ "s = error_df[error_df.title.str.contains('BindSupportPlanReq')].set_index('time').groupby(pd.Grouper(freq='W-MON')).user_id.nunique()\n", "z = event_df[event_df.event == 'Payment Add A New Card Button Clicked'].set_index('time').groupby(pd.Grouper(freq='W-MON'))['$user_id'].nunique()\n", "\n", "if len(s) == 0:\n", " s = z.apply(lambda _: 0)\n", " \n", "if len(z) == 0:\n", " print('No Data')\n", "else:\n", " ax = z.reset_index(drop=True).to_frame('user who try to bind a card').plot(kind='bar', width=0.1, figsize=(15, 9))\n", " (s/z).reset_index(drop=True).fillna(0).to_frame('bind error rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", " _ = ax.set_xticklabels([str(item)[:10] for item in z.index])\n", " ax.set_xlabel('date')\n", " ax.set_ylabel('user count')\n", " ax.set(title='error rate for bind card')" ] } ], "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 }