{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "776485fc-b76f-4dd5-83b9-f82ea80678db", "metadata": { "tags": [] }, "outputs": [], "source": [ "import os\n", "import pandas as pd\n", "from IPython.display import Markdown as md\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "import numpy as np\n", "from users import load_users, User, dbaas_cluster_usedsize, dbaas_paied_cluster_ids\n", "from datetime import timedelta, date, datetime, timezone\n", "\n", "os.environ[\"S3_BUCKET\"] = \"pingcap-plg\" \n", "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"/home/ubuntu/gcpbigquery.json\"\n", "os.environ[\"CLINIC_API_KEY\"] = os.getenv(\"NOTEBOOK_CLINIC_API_KEY\")" ] }, { "cell_type": "code", "execution_count": null, "id": "60666ac3-885e-4187-996c-e9f4c6302816", "metadata": { "tags": [] }, "outputs": [], "source": [ "begin = datetime.now(timezone.utc) - timedelta(days=90)\n", "md(f'''\n", "report generate time: **{datetime.now().date()}**\n", "\n", "only include tenants signup after **{begin.date()}**\n", "''')" ] }, { "cell_type": "markdown", "id": "333740e0-7b18-4bc3-a140-d5f9f2bcc555", "metadata": {}, "source": [ "# Feature Evaluation Report V1.1\n", "\n", "You can comment on [the template](https://pingcap.feishu.cn/wiki/wikcnZSVCds2WEENkUlzG4eYJRg)\n", "\n", "- [1、Free Trial Stage ( Feature Metrics for Serverless Clusters)](1、Free-Trial-Stage-(-Feature-Metrics-for-Serverless-Clusters-))\n", " - [Task：Create Cluster](#Task：Create-Cluster)\n", " - [Task ：Connect Cluster](#Task-：Connect-Cluster)\n", " - [Feature 1：Connecting Serverless Cluster by Client Tool](#Feature-1：Connecting-Serverless-Cluster-by-Client-Tool)\n", " - [Feature 2：Connecting Serverless Cluster by Web Tool](#Feature-2：Connecting-Serverless-Cluster-by-Web-Tool)\n", " - [Task：Playground](#Task：Playground)\n", " - [Feature 1：SQL compatibility learning](#Feature-1：SQL-compatibility-learning)\n", " - [Feature 2：HTAP learning](#Feature-2：HTAP-learning)\n", " - [Feature 3：Run your sql](#Feature-3：Run-your-sql)\n", " - [Task: SQL editor](#Task：SQL-editor)\n", " - [Task：Import](#Task：Import)\n", "- [2、Production Stage ( Feature Metrics for Dedicated Tier)](#2、Production-Stage-(-Feature-Metrics-for-Dedicated-Tier))\n", " - [Task：Create Cluster](#Task：Create-Cluster)\n", " - [Task : Import Data](#Task：Import-Data)\n", " - [Task : Monitor Cluster](#Task-:-Monitor-Cluster)\n", " - [Task : SQL Diagnosis](#Task-:-SQL-Diagnosis)" ] }, { "cell_type": "markdown", "id": "d5eb2b48-4e58-45c1-a37e-be3b44a0843d", "metadata": {}, "source": [ "## 1、Free Trial Stage ( Feature Metrics for Serverless Clusters)" ] }, { "cell_type": "code", "execution_count": null, "id": "45131512-f0dc-4f7f-87c0-f00785d52ec0", "metadata": { "tags": [] }, "outputs": [], "source": [ "users = load_users(begin)\n", "all_users = [u for u in users if not u.is_pingcap()]\n", "users = [u for u in all_users if u.signup_time() >= begin]\n", "event_df = pd.concat([u.events() for u in users])\n", "event_df = event_df[event_df.time < datetime.now().strftime('%Y-%m-%d')]\n", "import_df = pd.concat([u.imports() for u in users])" ] }, { "cell_type": "markdown", "id": "46abbb49-27f2-428d-9ffc-2239793dde5f", "metadata": {}, "source": [ "### Task：Create Cluster \n", "#### Task Metrics" ] }, { "cell_type": "markdown", "id": "4f31e44c-c97e-4d78-b8ed-d241ed8eaf45", "metadata": {}, "source": [ "##### Usage Metrics： Weekly Cluster Creation Rate\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 serverless cluster or dedicated cluster every week" ] }, { "cell_type": "code", "execution_count": null, "id": "c992cb35-18ff-4d1d-929f-673d2ee7c8e0", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'unique user': u.id(),\n", " 'time': u.signup_time(),\n", " 'serverless cluster': len(u.serverless_clusters()) > 0 and (u.serverless_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-THU'))['unique user'].nunique()\n", "f = pd.DataFrame({\n", " 'create serverless cluster ratio': df[df[\"serverless cluster\"]].set_index('time').groupby(pd.Grouper(freq='W-THU'))['unique user'].nunique(),\n", "})\n", "\n", "ax = z.reset_index(drop=True).to_frame('signup user').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = f.div(z, axis=0).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "rax.set_ylabel('ratio')\n", "rax.set_ylim(ymin=0)\n", "ax.set_xticklabels(z.index.strftime('%m-%d'), rotation=90)\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": "abda1e28-10c7-42ad-bfbc-5a43df7e0b7a", "metadata": {}, "source": [ "##### Engagement metrics: Weekly Serverless tier cluster creation time" ] }, { "cell_type": "code", "execution_count": null, "id": "e1584d1c-5413-4eb6-bf98-987a76f23d5b", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = pd.concat([u.serverless_clusters() for u in users])\n", "df = df[df.creation_finished_time.notna()]\n", "df['duration'] = (df.creation_finished_time - df.created_at).dt.total_seconds()\n", "\n", "z = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).size()\n", "#p50 = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).duration.median()\n", "#p99 = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).duration.apply(lambda s: s.quantile(.99))\n", "p90 = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).duration.apply(lambda s: s.quantile(.90))\n", "\n", "ax = z.plot(kind='bar', width=0.2, figsize=(15, 9))\n", "ax.set_xticklabels(z.index.strftime('%m-%d'), rotation=90)\n", "ax.set(title='Weekly Serverless tier cluster creation time')\n", "ax.set_ylabel('cluster count')\n", "\n", "rax = pd.DataFrame({\n", " 'p90': p90,\n", "}).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "rax.set_ylabel('duration')\n", "_ = rax.set_ylim(ymin=0)" ] }, { "cell_type": "markdown", "id": "df1950c4-62c4-45de-937c-27df4ae0abd5", "metadata": {}, "source": [ "### Task: Enter Cluster Overview Page\n", "#### Task metrics" ] }, { "cell_type": "markdown", "id": "3c60ccec-b7eb-48e1-88e4-f1245dc09967", "metadata": {}, "source": [ "##### Usage metrics: Weekly User Enter Rate %\n", "\n", "- Bar 1: Weekly User Count who created serverless tier cluster\n", "- Line 1：User rate who enter cluster overview page" ] }, { "cell_type": "code", "execution_count": null, "id": "5da0e651-416b-48b6-b3ae-5669aabc4a09", "metadata": { "tags": [] }, "outputs": [], "source": [ "# function CreatedServerlessCluster(user) judges whether the created cluster is a serverless tier\n", "def CreatedServerlessCluster(user):\n", " TZ = timezone(timedelta(hours=0))\n", " if user.signup_time() >= datetime.strptime(\"2023-01-12 00:00:00\", \"%Y-%m-%d %H:%M:%S\").astimezone(TZ):\n", " clusters = user.clusters()\n", " return 'dedicated' not in clusters.deploy_type.unique() and len(clusters) and (not user.events().empty)\n", " else:\n", " return user.count_event_with(lambda e: e.event == 'Created A Cluster' and e['Cluster Type'] == 'Serverless Tier') > 0\n", "\n", "\n", "cluster_overview_events = 'Overview Page View'\n", "df = pd.DataFrame.from_records([{\n", " 'id': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'enter': (u.count_event(cluster_overview_events) > 0 and (u.first_event(cluster_overview_events)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1)) \n", " or (u.count_event(\"To Cluster Detail Link Clicked\") > 0 and (u.first_event(\"To Cluster Detail Link Clicked\")[1] - u.create_first_cluster_time()[1]) < timedelta(days=1))\n", " or (u.count_event(\"Go To SQL Editor Button Clicked\") > 0 and (u.first_event(\"Go To SQL Editor Button Clicked\")[1] - u.create_first_cluster_time()[1]) < timedelta(days=1)),\n", "} for u in users if CreatedServerlessCluster(u)])\n", "\n", "s = df[df['enter']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['id'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['id'].nunique() \n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = (s/z).reset_index(drop=True).to_frame('user rate who enter cluster overview page').plot(secondary_y=True, ax=ax, color='orange')\n", "rax.set_ylabel('ratio')\n", "rax.set_ylim(ymin=0)\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 Overview Page Enter Rate')" ] }, { "cell_type": "markdown", "id": "f5a15774-46e4-47b8-9763-b5aeb705a0a6", "metadata": {}, "source": [ "##### Engagement Metrics:\n", "- P50 User Clicks from creating cluster to entering cluster overview page for the first time\n", " - Bar 1：Weekly User Count who created serverless tier cluster\n", " - Line1：P50 User Clicks Counts from creating cluster to entering cluster overview page for the first time" ] }, { "cell_type": "code", "execution_count": null, "id": "dee8f5fb-2b09-4e47-ba5d-94e8126b78c3", "metadata": { "tags": [] }, "outputs": [], "source": [ "# function EnterOverviewPage(u) is used to select the user who enter the overview page within 24h\n", "def EnterOverviewPage(u):\n", " if not u.create_first_cluster_time()[1]:\n", " return False\n", " if not u.first_event(cluster_overview_events)[1]:\n", " return False\n", " return (u.first_event(cluster_overview_events)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1)\n", "\n", "df = pd.DataFrame.from_records([{\n", " 'id': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'steps': u.first_event(cluster_overview_events)[0] - u.create_first_cluster_time()[0],\n", "} for u in users if (EnterOverviewPage(u) and CreatedServerlessCluster(u))])\n", "\n", "p50 = df.set_index('time').groupby(pd.Grouper(freq='W-THU')).steps.median()\n", "user_count = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['id'].nunique()\n", "\n", "ax = user_count.reset_index(drop=True).to_frame('users who enter overview').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = p50.reset_index(drop=True).to_frame('P50 click steps').plot(secondary_y=True, ax=ax, color='orange')\n", "rax.set_ylim(ymin=0)\n", "rax.set_ylabel('clicks')\n", "_ = ax.set_xticklabels([str(item)[:10] for item in user_count.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='P50 User click counts from creating cluster to enter overview')" ] }, { "cell_type": "markdown", "id": "f91b9485-0f34-4964-af22-d6a9343aca21", "metadata": {}, "source": [ "### Task ：Connect Cluster\n", "#### Task metrics" ] }, { "cell_type": "markdown", "id": "f47a664a-116a-4466-8e99-b91e701dbb49", "metadata": {}, "source": [ "##### Usage metrics：Weekly Cluster Try Connect Rate\n", "- Bar 1: User Count who created serverless tier cluster\n", "- Line 1：User rate who copy connetion string\n", "- Line 2：User rate who run sql in playground\n", "- Line 3：User rate who execute sql in sql editor" ] }, { "cell_type": "code", "execution_count": null, "id": "1253cd12-b1e6-463e-b898-9f55c9cd9d4a", "metadata": { "tags": [] }, "outputs": [], "source": [ "copy_events = [\n", " 'MySQL-Copy Button Clicked', \n", " 'MyCLI-Copy Button Clicked',\n", " 'JDBC-Copy Button Clicked',\n", " 'Python-Copy Button Clicked',\n", " 'Go-Copy Button Clicked',\n", " 'Node.js-Copy Button Clicked',\n", "]\n", "\n", "run_sql_in_playground_event = \"Playground Run Button Clicked\"\n", "execute_sql_in_sql_editor_event = \"SQL Editor Executed\"\n", "df = pd.DataFrame.from_records([{\n", " 'id': u.id(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'copy': u.count_event(*copy_events) > 0 and (u.first_event(*copy_events)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", " 'execute_sql_in_sql_editor': u.count_event(execute_sql_in_sql_editor_event) > 0 and (u.first_event(execute_sql_in_sql_editor_event)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", " 'connect': u.first_connect() is not None and (u.first_connect() - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", "\n", "} for u in users if CreatedServerlessCluster(u)])\n", "\n", "users_create_serverless_cluster = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['id'].nunique()\n", "users_copy_connection_string = df[df['copy']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['id'].nunique()\n", "users_execute_sql_in_sql_editor = df[df['execute_sql_in_sql_editor']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['id'].nunique()\n", "\n", "ax = users_create_serverless_cluster.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "\n", "copy_connection_string_rate = (users_copy_connection_string/users_create_serverless_cluster).reset_index(drop=True).to_frame('copy connect rate').plot(secondary_y=True, ax=ax)\n", "execute_sql_in_sql_editor_rate = (users_execute_sql_in_sql_editor/users_create_serverless_cluster).reset_index(drop=True).to_frame('execute sql in sql editor').plot(secondary_y=True, ax=ax)\n", "\n", "execute_sql_in_sql_editor_rate.set_ylabel('ratio')\n", "\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 Try Connect Rate')" ] }, { "cell_type": "markdown", "id": "073c0921-cfb7-4e85-8c25-54977c91d6c1", "metadata": {}, "source": [ "### Task ：Copy Connection String\n", "#### Task metrics" ] }, { "cell_type": "markdown", "id": "337b15f8", "metadata": {}, "source": [ "##### Usage metrics：User rate who connect serverless cluster by client tool\n", "\n", "- Bar 1: User Count who create serverless clusters\n", "- Line 1: User rate who copy connection string\n", "- Line 2: User rate who copy sample code" ] }, { "cell_type": "code", "execution_count": null, "id": "220109ec-0431-4389-90fc-69f985f42fee", "metadata": { "tags": [] }, "outputs": [], "source": [ "events1 = ['MySQL-Copy Button Clicked', 'MyCLI-Copy Button Clicked']\n", "events2 = [\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_string': u.count_event(*events1) > 0 and (u.first_event(*events1)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", " 'copy_code': u.count_event(*events2) > 0 and (u.first_event(*events2)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", "} for u in users if u.create_cluster_count() > 0])\n", "\n", "s1 = df[df['copy_string']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['unique user'].nunique()\n", "s2 = df[df['copy_code']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['unique user'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create serverless cluster').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "(s1/z).reset_index(drop=True).to_frame('copy connection string rate').plot(secondary_y=True, ax=ax).set_ylabel('ratio')\n", "(s2/z).reset_index(drop=True).to_frame('copy sample code 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 try to connect with client tool')" ] }, { "cell_type": "markdown", "id": "04efce7e-25b6-4906-8157-eaae4f8f8f50", "metadata": {}, "source": [ "##### Engagement metrics: \n", "- P50 User click counts from creating cluster to copy action\n", " - Bar 1：User count who creates cluster and havs copy action\n", " - Line 1：P50 click counts" ] }, { "cell_type": "code", "execution_count": null, "id": "c6f233b5-9a92-4a9b-a0af-67e011ef6d66", "metadata": { "tags": [] }, "outputs": [], "source": [ "events = events1 + events2\n", "\n", "def f(u):\n", " if not u.create_first_cluster_time()[1]:\n", " return False\n", " if not u.first_event(*events)[1]:\n", " return False\n", " return (u.first_event(*events)[1] - u.create_first_cluster_time()[1]) < timedelta(days=1)\n", "\n", "df = pd.DataFrame.from_records([{\n", " 'unique user': u.email(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'steps': u.first_event(*events)[0] - u.create_first_cluster_time()[0],\n", "} for u in users if f(u)])\n", "\n", "s = df.set_index('time').groupby(pd.Grouper(freq='W-THU')).steps.median()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['unique user'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('users who click copy button').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = s.reset_index(drop=True).to_frame('P50 click steps').plot(secondary_y=True, ax=ax)\n", "rax.set_ylim(ymin=0)\n", "rax.set_ylabel('clicks')\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='P50 User click counts from creating cluster to copy action')" ] }, { "cell_type": "markdown", "id": "3a9c3cc3-0498-46cd-ab31-13a50346e3b3", "metadata": {}, "source": [ "### Task：Chat2Query" ] }, { "cell_type": "markdown", "id": "276b36b8-99e7-4bc8-85d7-4ffa54efaa1b", "metadata": {}, "source": [ "#### Task Metrics\n", "##### Usage metrics User rate who enter Chat2Query page %\n", "\n", "- the bar shows user count who creates cluster\n", "- the line shows user rate who enter Chat2Query page %" ] }, { "cell_type": "code", "execution_count": null, "id": "a191d20d-60a5-4ff8-ac9a-67e5f5e452c5", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'email': u.email(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'view': (u.count_event('Chat 2 Query Page View') > 0 \n", " and u.first_event('Chat 2 Query Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1)) \n", " or (u.count_event('Sql Editor Page View') > 0 \n", " and u.first_event('Sql Editor Page View')[1] - u.create_first_cluster_time()[1] < timedelta(days=1)),\n", "} for u in users if CreatedServerlessCluster(u)])\n", "\n", "s = df[df.view].set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who create cluster').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = (s/z).reset_index(drop=True).to_frame('view rate').plot(secondary_y=True, ax=ax)\n", "rax.set_ylim(ymin=0)\n", "rax.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 enter Chat2Query')" ] }, { "cell_type": "markdown", "id": "e5a9a810-d737-4ea2-8830-8a3c725baa3c", "metadata": {}, "source": [ "##### User rate who execute after entering Chat2Query %\n", "\n", "- the bar shows user count who enters Chat2Query\n", "- the line shows user rate who execute SQL after entering Chat2Query" ] }, { "cell_type": "code", "execution_count": null, "id": "49564b01-447a-468f-aefb-b5616e9e06ed", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'email': u.email(),\n", " 'time': u.first_event('Sql Editor Page View')[1] if u.first_event('Sql Editor Page View')[1] is not None else u.first_event('Chat 2 Query Page View')[1],\n", " 'run': u.count_event('SQL Editor Executed') > 0 and u.first_event('SQL Editor Executed')[1] - (u.first_event('Sql Editor Page View')[1] if u.first_event('Sql Editor Page View')[1] is not None else u.first_event('Chat 2 Query Page View')[1]) < timedelta(days=1),\n", "} for u in users if CreatedServerlessCluster(u) and (u.count_event('Sql Editor Page View') > 0 or u.count_event('Chat 2 Query Page View') > 0)])\n", "\n", "s = df[df.run].set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who enter Chat2Query page').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = (s/z).reset_index(drop=True).to_frame('execute rate').plot(secondary_y=True, ax=ax)\n", "rax.set_ylim(ymin=0)\n", "rax.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 execute sql in Chat2Query')" ] }, { "cell_type": "markdown", "id": "37296b3c", "metadata": {}, "source": [ "##### User click counts from creating cluster to execute sql\n", "\n", "- the bar shows user count who execute sql\n", "- the line shows p50 click counts" ] }, { "cell_type": "code", "execution_count": null, "id": "8c6c0850", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'email': u.email(),\n", " 'time': u.first_event(\"SQL Editor Executed\")[1],\n", " 'steps': u.first_event(\"SQL Editor Executed\")[0] - u.create_first_cluster_time()[0],\n", "} for u in users if u.count_event(\"SQL Editor Executed\") > 0 and u.create_cluster_count() > 0])\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "s = df.set_index('time').groupby(pd.Grouper(freq='W-THU')).steps.median()\n", "ax = z.reset_index(drop=True).to_frame('user who execute sql').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "\n", "rax = s.reset_index(drop=True).to_frame('p50 click counts').plot(secondary_y=True, ax=ax)\n", "rax.set_ylim(ymin=0)\n", "rax.set_ylabel('p50')\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 click counts from creating cluster to execute sql')" ] }, { "cell_type": "markdown", "id": "d2991cdc", "metadata": {}, "source": [ "##### Execute Button Clicked per user in Chat2Query\n", "\n", "- the bar shows user count who execute sql in Chat2Query\n", "- the line1 shows p50 execute button clicked\n", "- the line2 shows p90 execute button clicked" ] }, { "cell_type": "code", "execution_count": null, "id": "c88c13f4", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'email': u.email(),\n", " 'time': u.first_event(\"SQL Editor Executed\")[1],\n", " 'clicks': u.count_event(\"SQL Editor Executed\"),\n", "} for u in users if u.count_event(\"SQL Editor Executed\") > 0 and u.count_event(\"Chat 2 Query Page View\") > 0])\n", "\n", "z = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "p50 = df.set_index('time').groupby(pd.Grouper(freq='W-THU')).clicks.median()\n", "p90 = df.set_index('time').groupby(pd.Grouper(freq='W-THU')).clicks.apply(lambda s: s.quantile(.90))\n", "\n", "ax = z.reset_index(drop=True).to_frame('user who execute sql in Chat2Query').plot(kind='bar', width=0.2, figsize=(15, 9))\n", "\n", "rax = pd.DataFrame({\n", " 'p50': p50,\n", " 'p90': p90,\n", "}).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "rax.set_ylim(ymin=0)\n", "rax.set_ylabel('execute button clicked')\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='Execute Button Clicked per user in Chat2Query')" ] }, { "cell_type": "markdown", "id": "c0bd65c8-8b5b-434b-8c77-1f95a2e2631e", "metadata": {}, "source": [ "### Task：Upload local file" ] }, { "cell_type": "markdown", "id": "bdd67a3f-87b5-4041-9b94-5f2f6023305c", "metadata": {}, "source": [ "#### Task Metrics\n", "##### Usage metrics: User rate of each step in the upload pages %\n", "- Bar 1：User count who creates serverless cluster\n", "- Line 1：User rate of \"upload local file success\" %\n", "- Line 2：User rate of \"Start import from local file\" %" ] }, { "cell_type": "code", "execution_count": null, "id": "f7efe31d-5af3-439f-85ae-6113917b8d95", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records([{\n", " 'email': u.email(),\n", " 'time': u.create_first_cluster_time()[1],\n", " 'upload succeed': u.count_event('Upload a local file succeed') > 0 and (u.first_event('Upload a local file succeed')[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", " 'start upload': u.count_event('Upload a local file started') > 0 and (u.first_event('Upload a local file started')[1] - u.create_first_cluster_time()[1]) < timedelta(days=1),\n", "} for u in users if CreatedServerlessCluster(u)])\n", "\n", "user_count = df.set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "start_upload = df[df['start upload']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "upload_succeed = df[df['upload succeed']].set_index('time').groupby(pd.Grouper(freq='W-THU'))['email'].nunique()\n", "\n", "ax = user_count.reset_index(drop=True).plot(kind='bar', width=0.2, figsize=(15, 9))\n", "rax = pd.DataFrame({\n", " 'User rate of \"upload local file success\"': upload_succeed / user_count,\n", " 'User rate of \"Start import from local file\"': start_upload / user_count,\n", "}).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "_ = ax.set_xticklabels([str(item)[:10] for item in user_count.index])\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('user count')\n", "ax.set(title='User rate of each step in the upload pages')" ] }, { "cell_type": "markdown", "id": "56b45dd1-3dd1-4ee0-ae38-12ea750d230b", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "aba09a12-e7f6-4fdd-9813-8a8807a24e20", "metadata": { "tags": [] }, "source": [ "##### import tasks failure rate after submission\n", "\n", "import tasks failure rate after submission % (only for upload's import not count import from s3)" ] }, { "cell_type": "code", "execution_count": null, "id": "a2a3fc6d-e139-4deb-a565-41486d883101", "metadata": {}, "outputs": [], "source": [ "df = users[0]._import_df\n", "df = df[ df['local_import_cfg'].notnull() & (df['created_at'] > begin)]\n", "fail = df[ df['status'] == 'failed' ].set_index('created_at').groupby(pd.Grouper(freq='W-THU')).id.count()\n", "df = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).id.count()\n", "ax = (fail / df).fillna(0).plot(figsize=(15, 9))\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('ratio')\n", "ax.set(title='Import failure rate after submission')" ] }, { "cell_type": "markdown", "id": "c155b479-71f2-43c9-9930-1c332b0f367e", "metadata": {}, "source": [ "## 2、Production Stage ( Feature Metrics for Dedicated Tier)" ] }, { "cell_type": "code", "execution_count": null, "id": "d1d8ff55-3508-4128-9e70-0bcdcb2c8565", "metadata": {}, "outputs": [], "source": [ "users = [u for u in all_users if len(u.dedicate_clusters()) > 0 and len(u.dedicate_clusters()) > 0]\n", "event_df = pd.concat([u.events().assign(id=lambda _: u.id()) for u in users])\n", "event_df = event_df[event_df.time < datetime.now().strftime('%Y-%m-%d')]\n", "import_df = pd.concat([u.imports() for u in users])" ] }, { "cell_type": "markdown", "id": "f917ef75-6d09-47a5-835c-b818e0151a01", "metadata": {}, "source": [ "### Task：Create Cluster" ] }, { "cell_type": "markdown", "id": "95ce0943-0dee-4573-b319-b7ad03fea458", "metadata": {}, "source": [ "#### Task Metrics" ] }, { "cell_type": "markdown", "id": "f017ba52-82f5-4d95-bd5e-622e02fbbc4d", "metadata": {}, "source": [ "##### Engagement metrics: Weekly Dedicated tier cluster creation time" ] }, { "cell_type": "code", "execution_count": null, "id": "7f881d3d-1058-40e1-ab76-9163c5fe7a7b", "metadata": {}, "outputs": [], "source": [ "df = pd.concat([u.dedicate_clusters() for u in users])\n", "df = df[df.creation_finished_time.notna() & (df.created_at > begin)]\n", "df['duration'] = (df.creation_finished_time - df.created_at).dt.total_seconds()\n", "\n", "z = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).size()\n", "p50 = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).duration.median()\n", "p99 = df.set_index('created_at').groupby(pd.Grouper(freq='W-THU')).duration.apply(lambda s: s.quantile(.99))\n", "\n", "ax = z.plot(kind='bar', width=0.2, figsize=(15, 9))\n", "ax.set_xticklabels(z.index.strftime('%m-%d'), rotation=90)\n", "ax.set(title='Weekly Dedicated tier cluster creation time')\n", "ax.set_ylabel('cluster count')\n", "\n", "rax = pd.DataFrame({\n", " 'p50': p50,\n", " 'p99': p99,\n", "}).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "rax.set_ylabel('duration')\n", "_ = rax.set_ylim(ymin=0)" ] }, { "cell_type": "markdown", "id": "e9871d9a-f206-49c1-b586-6fc3d0ebf33f", "metadata": {}, "source": [ "### Task：Import Data" ] }, { "cell_type": "markdown", "id": "828a4a0a-e844-4ecf-958b-d01f93d5c165", "metadata": {}, "source": [ "#### Task Metrics\n", "##### Usage metrics：User rate who enter import page and submit import\n", "\n", "The rate of unique user who submit import\n", "\n", "- the bar shows how many users click import button\n", "- the line shows the rate of user who submit import form" ] }, { "cell_type": "code", "execution_count": null, "id": "afcdfb4a-59ec-432b-bc89-13b196f9c8f4", "metadata": { "tags": [] }, "outputs": [], "source": [ "# s1 = event_df[event_df.event == 'Data Import Page View'].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s2 = event_df[event_df.event == 'Data Import Step 2 Page View'].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s3 = event_df[event_df.event == 'Data Import Step 3 Page View'].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s4 = event_df[event_df.event == 'Import Form Submission'].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "\n", "# df = pd.DataFrame({\n", "# 'step 1 user count': s1,\n", "# 'step 2 user count': s2,\n", "# 'step 3 user coout': s3,\n", "# 'submit user count': s4,\n", "# })\n", "# ax = df.plot(kind='bar', width=0.5, figsize=(15, 9))\n", "# ax.set_xticklabels(df.index.strftime('%m-%d'), rotation=90)\n", "# rax = pd.DataFrame({\n", "# 'step 1 conversion rate': s2 / s1,\n", "# # 'step 2 conversion rate': s3 / s2,\n", "# # 'step 3 conversion rate': s4 / s3,\n", "# }).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylabel('ratio')\n", "# rax.set_ylim(ymin=0)\n", "# ax.set_xlabel('date')\n", "# ax.set_ylabel('user count')\n", "# ax.set(title='User rate of each step in the import pages')\n" ] }, { "cell_type": "markdown", "id": "319b3b55-de91-416b-bc85-0b4a84de9a64", "metadata": {}, "source": [ "#### Engagement metrics" ] }, { "cell_type": "markdown", "id": "7ef3ca98-2262-4079-8c98-c997d8bab8a5", "metadata": {}, "source": [ "##### Step 1 Validation Event Number" ] }, { "cell_type": "code", "execution_count": null, "id": "802c7f91-fd5d-4071-9120-42c751aa8b82", "metadata": {}, "outputs": [], "source": [ "# df = pd.DataFrame({\n", "# 'Step 1 Source Access Check Started': event_df[\n", "# event_df.event == 'Step 1 Source Access Check Started'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique(),\n", "# 'Step 1 Source Access Check Succeed': event_df[\n", "# event_df.event == 'Step 1 Source Access Check Succeed'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique(),\n", "# 'Step 1 Source Access Check Failed': event_df[\n", "# event_df.event == 'Step 1 Source Access Check Failed'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique(),\n", "# })\n", "# ax = df.plot(kind='bar', width=0.5, figsize=(15, 9))\n", "# ax.set_xticklabels(df.index.strftime('%m-%d'), rotation=90)\n", "# rax = pd.DataFrame({\n", "# 'Step 1 Validation Error Rate': df['Step 1 Source Access Check Failed'] / df['Step 1 Source Access Check Started'],\n", "# }).fillna(0).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylim(ymin=0)\n", "# rax.set_ylabel('ratio')\n", "# ax.set_xlabel('date')\n", "# ax.set_ylabel('user count')\n", "# ax.set(title='Users events on step 1')" ] }, { "cell_type": "markdown", "id": "3093115e-23f3-4e74-adcb-3f0a222a70ef", "metadata": {}, "source": [ "##### Step 2 Validation Event Number" ] }, { "cell_type": "code", "execution_count": null, "id": "6e0621f3-e7e1-4aa8-be0a-0c2a95097417", "metadata": {}, "outputs": [], "source": [ "# s1 = event_df[\n", "# event_df.event == 'Step 2 Filter And Rule Check Started'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s2 = event_df[\n", "# event_df.event == 'Step 2 Filter And Rule Check Succeed'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s3 = event_df[\n", "# event_df.event == 'Step 2 Filter And Rule Check Failed'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "#\n", "# df = pd.DataFrame({\n", "# 'Step 2 Filter And Rule Check Started': s1,\n", "# 'Step 2 Filter And Rule Check Succeed': s2,\n", "# 'Step 2 Filter And Rule Check Failed': s3,\n", "# }, index = s1.index)\n", "# ax = df.plot(kind='bar', width=0.5, figsize=(15, 9))\n", "# ax.set_xticklabels(df.index.strftime('%m-%d'), rotation=90)\n", "# rax = pd.DataFrame({\n", "# 'Step 2 Validation Error Rate': df['Step 2 Filter And Rule Check Failed'] / df['Step 2 Filter And Rule Check Started'],\n", "# }).fillna(0).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylabel('ratio')\n", "# rax.set_ylim(ymin=0)\n", "# ax.set_xlabel('date')\n", "# ax.set_ylabel('user count')\n", "# ax.set(title='Users events on step 2')" ] }, { "cell_type": "markdown", "id": "90cca8d3-ac6d-471b-8f73-e50deb0d744a", "metadata": {}, "source": [ "##### Step 3 Validation Event Number" ] }, { "cell_type": "code", "execution_count": null, "id": "e59dc1bb-21eb-429b-8d1a-0cf4b5c188cb", "metadata": {}, "outputs": [], "source": [ "# s1 = event_df[\n", "# event_df.event == 'Step 3 Submit Import Precheck Started'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s2 = event_df[\n", "# event_df.event == 'Step 3 Submit Import Precheck Succeed'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s3 = event_df[\n", "# event_df.event == 'Step 3 Submit Import Precheck Failed'\n", "# ].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "#\n", "# df = pd.DataFrame({\n", "# 'Step 3 Submit Import Precheck Started': s1,\n", "# 'Step 3 Submit Import Precheck Succeed': s2,\n", "# 'Step 3 Submit Import Precheck Failed': s3,\n", "# }, index = s1.index)\n", "# ax = df.plot(kind='bar', width=0.5, figsize=(15, 9))\n", "# ax.set_xticklabels(df.index.strftime('%m-%d'), rotation=90)\n", "# rax = pd.DataFrame({\n", "# 'Step 3 Validation Error Rate': df['Step 3 Submit Import Precheck Failed'] / df['Step 3 Submit Import Precheck Started'],\n", "# }).fillna(0).reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylabel('ratio')\n", "# rax.set_ylim(ymin=0)\n", "# ax.set_xlabel('date')\n", "# ax.set_ylabel('user count')\n", "# ax.set(title='Users events on step 3')" ] }, { "cell_type": "markdown", "id": "7056af51-2e8f-4dcd-95c1-b1e1116e9986", "metadata": {}, "source": [ "##### import tasks 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": "5d490239-54b4-4f95-a071-d39eceaaebcb", "metadata": {}, "outputs": [], "source": [ "# df = import_df[import_df.cluster_type == 'dedicated'].set_index('created_at').groupby(pd.Grouper(freq='W-THU')).id.count().to_frame('dedicated imports')\n", "# df['dedicated failed rate'] = import_df[(import_df.cluster_type == 'dedicated') & (import_df.status == 'failed')].set_index('created_at').groupby(pd.Grouper(freq='W-THU')).id.count() / df['dedicated imports']\n", "# df = df[df.index > begin].fillna(0)\n", "# ax = df[['dedicated imports']].plot(kind='bar', width=0.2, figsize=(15, 9))\n", "# rax = df[['dedicated failed rate']].reset_index(drop=True).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylabel('ratio')\n", "# rax.set_ylim(ymin=0)\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')\n", "# \"gcp-digital-bi.metadb.dbaas_imports NO DATA\"" ] }, { "cell_type": "markdown", "id": "e76fd137-3a7b-465b-9f48-3a63d87bd113", "metadata": {}, "source": [ "### Task : Monitor Cluster (NO DATA)\n", "#### Task Metrics\n", "##### Usage metrics：User rate whose dedicated tier cluster has workload and enter monitor page\n", "\n", "The rate of unique user who created dedicated cluster and view Integrations Page\n", "\n", "- the bar shows how many users create dedicated clusters and the cluster has workload (cluster QPS > 100 in 2 weeks)\n", "- the line shows the rate of user who view Integrations page" ] }, { "cell_type": "code", "execution_count": null, "id": "82a00025-fa9e-40ba-8691-5df22ccdbb92", "metadata": {}, "outputs": [], "source": [ "# f = event_df.apply(lambda x: activeqps_df[\n", "# (activeqps_df.tenant_id.astype(int) == x['id']) & \n", "# (activeqps_df.timestamp < x['time'].timestamp()) &\n", "# (activeqps_df.timestamp > x['time'].timestamp() - 14 * 24 * 60 * 60)\n", "# ].size > 0, axis=1)\n", "# edf = event_df[f]" ] }, { "cell_type": "code", "execution_count": null, "id": "1e71867d-c7d1-41a6-a420-f711343fcf11", "metadata": {}, "outputs": [], "source": [ "# z = edf.set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s = edf[(edf.event == 'Page Change') & (edf['$current_url'] == '#/monitoring')].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "\n", "# ax = pd.DataFrame({\n", "# \"Dedicated tier users with cluster's QPS > 100\": z,\n", "# \"User count of monitor page view\": s,\n", "# }).plot(kind='bar', width=0.2, figsize=(15, 9))\n", "\n", "# rax = (s/z).reset_index(drop=True).to_frame('monitor page view rate').fillna(0).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylabel('ratio')\n", "# rax.set_ylim(ymin=0)\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 view monitor page')" ] }, { "cell_type": "markdown", "id": "12b7f88d-ac2a-4b97-9b5c-b311398e0997", "metadata": {}, "source": [ "### Task : SQL Diagnosis\n", "#### Task Metrics\n", "##### Usage metrics：User rate whose dedicated tier cluster has workload and enter diagnosis page\n", "\n", "The rate of unique user who created dedicated cluster and view diagnosis page\n", "\n", "- the bar shows how many users create dedicated clusters and the cluster has workload (cluster QPS > 100 in 2 weeks)\n", "- the line shows the rate of user who view diagnosis page" ] }, { "cell_type": "code", "execution_count": null, "id": "5a77a1ea-1ac0-47b3-b120-9bc55f62ba94", "metadata": {}, "outputs": [], "source": [ "# z = edf.set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "# s = edf[(edf.event == 'Page Change') & (edf['$current_url'] != '#/monitoring')].set_index('time').groupby(pd.Grouper(freq='W-THU'))['$user_id'].nunique()\n", "\n", "# ax = pd.DataFrame({\n", "# \"Dedicated tier users with cluster's QPS > 100\": z,\n", "# \"User count of diagnosis page view\": s,\n", "# }).plot(kind='bar', width=0.2, figsize=(15, 9))\n", "\n", "# rax = (s/z).reset_index(drop=True).to_frame('Diagnosis Page View Rate').fillna(0).plot(secondary_y=True, ax=ax)\n", "# rax.set_ylabel('ratio')\n", "# rax.set_ylim(ymin=0)\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 do Diagnosis Page View')" ] }, { "cell_type": "markdown", "id": "753fa7ae-38cf-4ec0-8a9e-9ea560bf8d86", "metadata": {}, "source": [ "## 4、Integration" ] }, { "cell_type": "code", "execution_count": null, "id": "44364c46-64e3-4f97-9d4d-c63ead04d944", "metadata": {}, "outputs": [], "source": [ "users = [u for u in all_users if u.signup_time() >= begin]" ] }, { "cell_type": "markdown", "id": "1e8bc7fa-7720-49b4-a30e-c051d5382b82", "metadata": {}, "source": [ "### Vercel\n", "#### Usage metrics：Weekly new user count who add integration from Vercel side\n", "\n", "The users comes from vercel, only include the users who hasn't signup before" ] }, { "cell_type": "code", "execution_count": null, "id": "9bbf5c31-daa5-4024-b61b-6f311e2b967f", "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame.from_records({\n", " 'id': u.id(),\n", " 'signup_time': u.signup_time(),\n", " 'link': u.count_event('Link Page View') > 0,\n", " 'integration': u.count_event('Vercel Add Integration and Return to Vercel Button Clicked') > 0,\n", "} for u in users if u.initial_referrer() is not None and u.initial_referrer() == 'vercel.com')\n", "\n", "f = pd.DataFrame({\n", " 'users count who enter link page': df[df.link].set_index('signup_time').groupby(pd.Grouper(freq='W-THU')).id.nunique(),\n", " 'users count who add integration': df[df.integration].set_index('signup_time').groupby(pd.Grouper(freq='W-THU')).id.nunique(),\n", "})\n", "ax = f.plot(kind='bar', figsize=(15, 9))\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('User Count')\n", "ax.set(title='New user count who add integration from Vercel side')\n", "_ = ax.set_xticklabels(f.index.strftime('%m-%d'), rotation=90)" ] }, { "cell_type": "code", "execution_count": null, "id": "32b2058b88b4ffd7", "metadata": { "collapsed": false }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.16" } }, "nbformat": 4, "nbformat_minor": 5 }