{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "f598aa78-6b71-4cce-818a-fb6fc9f93a19", "metadata": { "tags": [] }, "outputs": [], "source": [ "import os\n", "import boto3\n", "import pandas as pd\n", "from IPython.display import Markdown as md\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "import numpy as np\n", "from users import load_users, activeqps, 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\"" ] }, { "cell_type": "code", "execution_count": null, "id": "9db1a158-69a1-4ec4-86a5-fbb9ea579361", "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": "code", "execution_count": null, "id": "351a4f0b-2a8f-4ed4-b3e7-39a65a59e5a3", "metadata": { "tags": [] }, "outputs": [], "source": [ "users = load_users(begin)\n", "all_users = [u for u in users if not u.is_pingcap()]\n", "\n", "users = all_users" ] }, { "cell_type": "code", "execution_count": null, "id": "58e88924-db49-4f6c-af68-38b905eddfb6", "metadata": { "tags": [] }, "outputs": [], "source": [ "def filter_user(users):\n", " TZ = timezone(timedelta(hours=8))\n", " return [u for u in users if u.signup_time() >= datetime(2023, 2, 1, tzinfo=TZ) and (not u.events().empty) and 'dedicated' not in u.clusters().deploy_type.unique()]" ] }, { "cell_type": "code", "execution_count": null, "id": "e47d826c-56e9-4bdf-962c-4cf49de219e7", "metadata": { "tags": [] }, "outputs": [], "source": [ "users = filter_user(users)" ] }, { "cell_type": "code", "execution_count": null, "id": "0ea5d8e1-766e-443c-98f4-f99afb383369", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Job\n", "Wandering = 'Wandering'\n", "Get_Started = 'Get Started'\n", "Env_Preparation_and_Maintenance = 'Env Preparation and Maintenance'\n", "Application_Development = 'Application Development'\n", "Analytical_Dev = 'Analytical Dev'\n", "Disaster_Recovery = 'Disaster Recovery'\n", "Stability_Management = 'Stability Management'\n", "Performance_Management = 'Performance Management'\n", "Exchange_Data = 'Exchange Data'\n", "Integrations = 'Integrations'\n", "Cost_Management = 'Cost Management'\n", "Security_Management = 'Security Management'\n", "Service_Agreement_Assurance = 'Service Agreement Assurance'\n", "\n", "# put these jobs in job_list\n", "job_list = [Get_Started, Env_Preparation_and_Maintenance, Application_Development, Analytical_Dev, Disaster_Recovery, Stability_Management, Performance_Management, Exchange_Data, Integrations, Cost_Management, Security_Management, Service_Agreement_Assurance]" ] }, { "cell_type": "code", "execution_count": null, "id": "639150d3-0502-4a47-a679-0e1dad834c9a", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Sub Job and Job Group\n", "\n", "# Get Started\n", "Get_Started_Group = [Get_Started]\n", "\n", "# Env Preparation and Maintenance\n", "Env_Preparation_and_Maintenance_Group = [Env_Preparation_and_Maintenance]\n", "\n", "# Application Development\n", "MySQL_Ecosystem_Development = 'MySQL Ecosystem Development'\n", "Data_API_Development = 'Data API Development'\n", "Open_API_Development = 'Open API Development'\n", "TP_SQL_Development = 'TP SQL Development'\n", "Application_Development_Group = [MySQL_Ecosystem_Development, Data_API_Development, Open_API_Development, TP_SQL_Development]\n", "\n", "# Analytical Dev\n", "AP_SQL_Development = 'AP SQL Development'\n", "Analytical_Dev_Group = [AP_SQL_Development]\n", "\n", "# Disaster Recovery\n", "Backup_and_Restore = 'Backup and Restore'\n", "Disaster_Recovery_Group = [Backup_and_Restore]\n", "\n", "# Stability Management\n", "Cluster_Monitoring = 'Cluster Monitoring'\n", "Cluster_Alerting = 'Cluster Alerting'\n", "Monitor_Event_Log = 'Monitor Event Log'\n", "Stability_Management_Group = [Cluster_Monitoring, Cluster_Alerting, Monitor_Event_Log]\n", "\n", "# Performance Management\n", "Work_with_TiDB = 'Work with TiDB'\n", "Diagnosis_and_Troubleshooting = 'Diagnosis and Troubleshooting'\n", "Performance_Management_Group = [Work_with_TiDB, Diagnosis_and_Troubleshooting]\n", "\n", "# Exchange Data\n", "Data_Importing_as_a_File = 'Data Importing as a File'\n", "Data_Exporting_as_a_File = 'Data Exporting as a File'\n", "Data_Exporting_as_a_Stream = 'Data Exporting as a Stream'\n", "Migration_from_Other_Databases = 'Migration from Other Databases'\n", "Exchange_Data_Group = [Data_Importing_as_a_File, Data_Exporting_as_a_File, Data_Exporting_as_a_Stream, Migration_from_Other_Databases]\n", "\n", "# Integrations\n", "Integrations_Group = [Integrations]\n", "\n", "# Cost Management\n", "Order_Management = 'Order Management'\n", "Payment_Management = 'Payment Management'\n", "Billing_Management = 'Billing Management'\n", "Data_Analysis_and_Reporting = 'Data Analysis and Reporting'\n", "Cost_Management_Group = [Order_Management, Payment_Management, Billing_Management, Data_Analysis_and_Reporting]\n", "\n", "# Security Management\n", "Organization_Management = 'Organization Management'\n", "Data_Security_Management = 'Data Security Management'\n", "Security_Management_Group = [Organization_Management, Data_Security_Management]\n", "\n", "# Service Agreement Assurance\n", "Service_Agreement_Assurance_Group = [Service_Agreement_Assurance]" ] }, { "cell_type": "code", "execution_count": null, "id": "b76d69ee-3db2-46b3-90c0-e8c65420ed41", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Task\n", "Create_Serverless_Clusters = 'Create Serverless Clusters'\n", "Clone_Serverless_Clusters = 'Clone Serverless Clusters'\n", "Set_Cluster_Password = 'Set Cluster Password'\n", "Connect_Cluster = 'Connect Cluster'\n", "Try_Connect_Cluster = 'Try Connect Cluster'\n", "Try_Playground_to_Get_Product_Value = 'Try Playground to Get Product Value'\n", "Try_SQL_in_Chat2Query = 'Try SQL in Chat2Query'\n", "Heavy_SQL_Usage_in_Chat2Query = 'Heavy SQL Usage in Chat2Query'\n", "Try_AI_to_Explore_Data_in_Chat2Query = 'Try AI to Explore Data in Chat2Query'\n", "Heavy_AI_Usage_to_Explore_Data_in_Chat2Query = 'Heavy AI Usage to Explore Data in Chat2Query'\n", "Upload_Small_File_from_Local = 'Upload Small File from Local'\n", "Import_Sample_Data_from_S3 = 'Import Sample Data from S3'\n", "Import_User_Own_Data_from_S3 = 'Import User Own Data from S3'\n", "Check_Monitoring = 'Check Monitoring'\n", "Integrate_to_Datadog = 'Integrate to Datadog'\n", "Integrate_to_New_Relic = 'Integrate to New Relic'\n", "Diagnosis_SQL = 'Diagnosis SQL'\n", "Data_Service_Deployment = 'Data Service Deployment'\n", "Develop_Endpoint_in_Data_Service_IDE = 'Develop Endpoint in Data Service IDE'\n", "Open_API_Key_Management = 'Open API Key Management'\n", "Data_API_Key_Management = 'Data API Key Management'\n", "Backup_and_Restore = 'Backup and Restore'\n", "User_Management = 'User Management'\n", "Project_Management = 'Project Management'\n", "Vercel_Integration = 'Vercel Integration'\n", "Submit_Contact_Us_Form = 'Submit Contact Us Form'\n", "Check_Pricing = 'Check Pricing'\n", "Bind_a_Credit_Card = 'Bind a Credit Card'\n", "Check_Bills = 'Check Bills'\n", "Check_Invoice = 'Check Invoice'\n", "Check_Audit_Log = 'Check Audit Log'" ] }, { "cell_type": "code", "execution_count": null, "id": "29e65cb9-340f-401b-88ff-906fc239c2bb", "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", "password_events = [\n", " 'Reset password Button Clicked',\n", " 'Create password Button Clicked',\n", "]\n", "\n", "monitoring_events = [\n", " 'Monitoring Nav Clicked',\n", " 'Metrics Page View',\n", " 'Monitoring: Click Manual Refresh',\n", "]\n", "\n", "diagnosis_events = [\n", " 'SQL Statement Tab Clicked',\n", " 'Sql Statement Page View',\n", " 'Slow Query Tab Clicked',\n", " 'Slow Query Page View',\n", "]\n", "\n", "price_events = [\n", " 'Node Price Link Clicked',\n", " 'Pricing Details Link Clicked',\n", "]\n", "\n", "sample_database = [\n", " 's3://ossinsight/workshop/oss_database_sample/',\n", " 's3://tidbcloud-samples/data-ingestion/',\n", " 's3://tidbcloud-samples/import-playground',\n", " 's3://developer.pingcap.com/bookshop/',\n", " 's3://tidbcloud-samples/import-playground-v2/',\n", " 's3://tidbcloud-sample-data/data-ingestion/',\n", " 's3://tidbcloud-samples/gharchive/',\n", " 's3://tidbcloud-samples/game-sample/',\n", " 's3://tidbcloud-samples/sp500-insight/',\n", "]" ] }, { "cell_type": "code", "execution_count": null, "id": "bd2c84cf-b3e7-4a56-9d22-7501b49d1902", "metadata": { "tags": [] }, "outputs": [], "source": [ "SESSION_SECONDS = 60 * 60 * 1\n", "from users import interfering_sql\n", "def count_event_with_time_range(u, start_time=None, end_time=None, *event_name):\n", " events = u.events()\n", " if start_time is not None:\n", " events = events[events.time >= start_time]\n", " if end_time is not None:\n", " events = events[events.time <= end_time]\n", " return len(events[events.event.isin(event_name)])\n", "\n", "def match_event(u, start_time=None, end_time=None, event_name=None, include_sample=False):\n", " end_time = end_time + timedelta(seconds=SESSION_SECONDS)\n", " if event_name == 'Upload':\n", " imports = u.imports()\n", " imports = imports[imports.status == \"completed\"]\n", " if start_time is not None:\n", " imports = imports[imports.created_at >= start_time]\n", " if end_time is not None:\n", " imports = imports[imports.created_at <= end_time]\n", " return imports.local_import_cfg.notnull().sum()\n", "\n", " if event_name == 'Import':\n", " imports = u.imports()\n", " if not include_sample:\n", " imports = imports[~imports.source_url.isin(sample_database)]\n", " else:\n", " imports = imports[imports.source_url.isin(sample_database)]\n", " imports = imports[imports.status == \"completed\"]\n", " if start_time is not None:\n", " imports = imports[imports.created_at >= start_time]\n", " if end_time is not None:\n", " imports = imports[imports.created_at <= end_time]\n", " return imports.local_import_cfg.isnull().sum()\n", "\n", " if event_name == 'Execute SQL':\n", " sql_editor_info = u.sql_editor_info()\n", " if start_time is not None:\n", " sql_editor_info = sql_editor_info[sql_editor_info.created_at >= start_time]\n", " if end_time is not None:\n", " sql_editor_info = sql_editor_info[sql_editor_info.created_at <= end_time]\n", " sql_editor_info = sql_editor_info[sql_editor_info.sql_content.apply(interfering_sql) == False]\n", " sql_editor_info = sql_editor_info[(sql_editor_info.status == 1) & (sql_editor_info.event_type == 'exec_sql') & (sql_editor_info.chat2query_sid.isnull())]\n", " return len(sql_editor_info)\n", "\n", " if event_name == 'Execute AI SQL':\n", " sql_editor_info = u.sql_editor_info()\n", " if start_time is not None:\n", " sql_editor_info = sql_editor_info[sql_editor_info.created_at >= start_time]\n", " if end_time is not None:\n", " sql_editor_info = sql_editor_info[sql_editor_info.created_at <= end_time]\n", " sql_editor_info = sql_editor_info[sql_editor_info.sql_content.apply(interfering_sql) == False]\n", " sql_editor_info = sql_editor_info[(sql_editor_info.status == 1) & (sql_editor_info.event_type == 'exec_sql') & (sql_editor_info.chat2query_sid.notnull())]\n", " return len(sql_editor_info)\n", "\n", " return 0\n", "\n", "def event_duration_sum_with_time_range(u, start_time=None, end_time=None, *event_name):\n", " events = u.events().copy().sort_values(\"time\")\n", " if start_time is not None:\n", " events = events[events.time >= start_time]\n", " if end_time is not None:\n", " events = events[events.time <= end_time]\n", " events[\"duration\"] = (events.shift(-1).time - events.time).fillna(timedelta(0))\n", " events.loc[events.duration > timedelta(seconds=SESSION_SECONDS), \"duration\"] = timedelta(0)\n", " events = events[events.event.isin(event_name)]\n", " return events.duration.sum()" ] }, { "cell_type": "code", "execution_count": null, "id": "8f69f339-6235-4e90-9e6b-f658f195d45c", "metadata": { "tags": [] }, "outputs": [], "source": [ "task_annotations = {}\n", "for u in users:\n", " session_count, session_info = u.count_session()\n", " task_annotations[u.email()] = {}\n", " # determine if each task is completed in the session\n", " for i, s in enumerate(session_info):\n", " annotation = []\n", " if count_event_with_time_range(u, s[0], s[1], \"Created A Cluster\") > 0:\n", " annotation.append(Create_Serverless_Clusters)\n", " #TODO: Clone_Serverless_Clusters\n", "\n", " if count_event_with_time_range(u, s[0], s[1], *password_events) > 0:\n", " annotation.append(Set_Cluster_Password)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], *copy_events) > 0 and i < 3:\n", " annotation.append(Try_Connect_Cluster)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], *copy_events) > 0:\n", " annotation.append(Connect_Cluster)\n", "\n", " if 0 < match_event(u, s[0], s[1], 'Execute SQL') and i < 3:\n", " annotation.append(Try_SQL_in_Chat2Query)\n", "\n", " if match_event(u, s[0], s[1], 'Execute SQL') > 10:\n", " annotation.append(Heavy_SQL_Usage_in_Chat2Query)\n", "\n", " if 0 < match_event(u, s[0], s[1], 'Execute AI SQL') and i < 3:\n", " annotation.append(Try_AI_to_Explore_Data_in_Chat2Query)\n", "\n", " if match_event(u, s[0], s[1], 'Execute AI SQL') > 10:\n", " annotation.append(Heavy_AI_Usage_to_Explore_Data_in_Chat2Query)\n", "\n", " if match_event(u, s[0], s[1], 'Upload') > 0:\n", " annotation.append(Upload_Small_File_from_Local)\n", "\n", " if match_event(u, s[0], s[1], 'Import', include_sample=True) > 0:\n", " annotation.append(Import_Sample_Data_from_S3)\n", "\n", " if match_event(u, s[0], s[1], 'Import', include_sample=False) > 0:\n", " annotation.append(Import_User_Own_Data_from_S3)\n", "\n", " if event_duration_sum_with_time_range(u, s[0], s[1], *monitoring_events) > timedelta(seconds=60):\n", " annotation.append(Check_Monitoring)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Integration To Datadog Test Integration Button Clicked') > 0:\n", " annotation.append(Integrate_to_Datadog)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Integration To New Relic Test Integration Button Clicked') > 0:\n", " annotation.append(Integrate_to_New_Relic)\n", "\n", " if event_duration_sum_with_time_range(u, s[0], s[1], *diagnosis_events) > timedelta(seconds=60):\n", " annotation.append(Diagnosis_SQL)\n", "\n", " \n", " if count_event_with_time_range(u, s[0], s[1], 'DataService deploy endpoint clicked') > 0:\n", " annotation.append(Data_Service_Deployment)\n", " annotation.append(Data_Service_Deployment)\n", "\n", " if event_duration_sum_with_time_range(u, s[0], s[1], 'Data Service page') > timedelta(seconds=180):\n", " annotation.append(Develop_Endpoint_in_Data_Service_IDE)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Complete API Key creation') > 0:\n", " annotation.append(Open_API_Key_Management)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Create api key button clicked') > 0:\n", " annotation.append(Data_API_Key_Management)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Restore Button Clicked') > 0:\n", " annotation.append(Backup_and_Restore)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Invite User Completed') > 0:\n", " annotation.append(User_Management)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Create Project Confirm Button Clicked') > 0:\n", " annotation.append(Project_Management)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Vercel Add Integration and Return to Vercel Button Clicked') > 0:\n", " annotation.append(Vercel_Integration)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Submit Contact Us From') > 0:\n", " annotation.append(Submit_Contact_Us_Form)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], *price_events) > 0:\n", " annotation.append(Check_Pricing)\n", "\n", " if count_event_with_time_range(u, s[0], s[1], 'Add A New Card Successfull') > 0:\n", " annotation.append(Bind_a_Credit_Card)\n", "\n", " if event_duration_sum_with_time_range(u, s[0], s[1], 'Bills Page View') > timedelta(seconds=60):\n", " annotation.append(Check_Bills)\n", "\n", " if event_duration_sum_with_time_range(u, s[0], s[1], 'Invoices Page View') > timedelta(seconds=60):\n", " annotation.append(Check_Invoice)\n", "\n", " if event_duration_sum_with_time_range(u, s[0], s[1], 'Audit Log Page View') > timedelta(seconds=60):\n", " annotation.append(Check_Audit_Log)\n", "\n", " # Over task judgement\n", " if len(annotation) == 0:\n", " annotation.append(Wandering)\n", " task_annotations[u.email()][i] = annotation\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "83914db8-3d05-4419-9b76-50ba6ccebf80", "metadata": { "tags": [] }, "outputs": [], "source": [ "# sort the task annotations to put the user with more sessions first\n", "task_annotations = {k: v for k, v in sorted(task_annotations.items(), key=lambda item: len(item[1]), reverse=True)}\n", "# task_annotations" ] }, { "cell_type": "code", "execution_count": null, "id": "490a3e5c-699a-45ff-af35-2dbc19c5d32f", "metadata": { "tags": [] }, "outputs": [], "source": [ "job_tasks_map = {\n", " Get_Started: [Try_SQL_in_Chat2Query, Try_AI_to_Explore_Data_in_Chat2Query, Upload_Small_File_from_Local, Import_Sample_Data_from_S3, Try_Connect_Cluster],\n", " Env_Preparation_and_Maintenance: [Create_Serverless_Clusters, Vercel_Integration],\n", " MySQL_Ecosystem_Development: [Set_Cluster_Password, Connect_Cluster],\n", " Data_API_Development: [Data_API_Key_Management, Develop_Endpoint_in_Data_Service_IDE, Data_Service_Deployment],\n", " Open_API_Development: [Open_API_Key_Management],\n", " AP_SQL_Development: [Heavy_SQL_Usage_in_Chat2Query, Heavy_AI_Usage_to_Explore_Data_in_Chat2Query],\n", " Backup_and_Restore: [Backup_and_Restore],\n", " Cluster_Monitoring: [Check_Monitoring, Integrate_to_Datadog],\n", " Diagnosis_and_Troubleshooting: [Diagnosis_SQL],\n", " Data_Importing_as_a_File: [Import_User_Own_Data_from_S3],\n", " Order_Management: [Check_Pricing],\n", " Payment_Management: [Bind_a_Credit_Card],\n", " Billing_Management: [Check_Bills, Check_Invoice],\n", " Organization_Management: [User_Management, Project_Management, Check_Audit_Log],\n", " Service_Agreement_Assurance: [Submit_Contact_Us_Form],\n", "}" ] }, { "cell_type": "code", "execution_count": null, "id": "2bbacc9b-893e-4446-91f2-1574036578b4", "metadata": { "tags": [] }, "outputs": [], "source": [ "def job_complete(job, status):\n", " if job == Wandering:\n", " return False\n", " if (job == Get_Started or\n", " job == Env_Preparation_and_Maintenance or\n", " job == AP_SQL_Development or\n", " job == Cluster_Monitoring or\n", " job == Order_Management or\n", " job == Payment_Management or\n", " job == Billing_Management or\n", " job == Organization_Management):\n", " return any(status.values())\n", " return all(status.values())" ] }, { "cell_type": "code", "execution_count": null, "id": "be12f71d-dbe5-45bf-835c-cd0445094389", "metadata": { "tags": [] }, "outputs": [], "source": [ "user_job_info = pd.DataFrame(columns=['user']+list(job_tasks_map.keys()))" ] }, { "cell_type": "code", "execution_count": null, "id": "9d3e21ba-e053-4cd5-b0dc-5e9198bfbc37", "metadata": { "tags": [] }, "outputs": [], "source": [ "# determine which job the task in the session is completing\n", "job_annotations = {}\n", "for user, sessions in task_annotations.items():\n", " job_annotations[user] = {}\n", " job_status = {job: {task: False for task in tasks} for job, tasks in job_tasks_map.items()}\n", " for session, tasks in sessions.items():\n", " annotation = []\n", " for task in tasks:\n", " # a task only belongs to one job\n", "\n", " for job, job_tasks in job_tasks_map.items():\n", " if session < 3:\n", " if job != Get_Started and job != Env_Preparation_and_Maintenance:\n", " continue\n", " if job == Env_Preparation_and_Maintenance and task == Create_Serverless_Clusters:\n", " continue\n", " if session >= 3:\n", " if job == Get_Started:\n", " continue\n", "\n", " if task in job_tasks:\n", " if job not in annotation:\n", " annotation.append(job)\n", " job_status[job][task] = True\n", " break\n", " if len(annotation) == 0:\n", " annotation.append(Wandering)\n", " job_annotations[user][session] = annotation\n", " user_job_info.loc[len(user_job_info)] = [user] + [job_complete(job, status) for job, status in job_status.items()]\n", "\n", "# job_annotations" ] }, { "cell_type": "code", "execution_count": null, "id": "6eee3ab7-6b7d-4a15-b942-271b02e3f0fd", "metadata": { "tags": [] }, "outputs": [], "source": [ "def user_in_class(u, class_list):\n", " for job in class_list:\n", " if u[job]:\n", " return True\n", " return False" ] }, { "cell_type": "code", "execution_count": null, "id": "87437f1c-7683-4ca1-b4cf-aec29ee0679b", "metadata": { "tags": [] }, "outputs": [], "source": [ "first_class = [Order_Management, Payment_Management, Service_Agreement_Assurance]\n", "second_class = [Backup_and_Restore, Cluster_Monitoring, Diagnosis_and_Troubleshooting]\n", "third_class = [Env_Preparation_and_Maintenance, MySQL_Ecosystem_Development, Data_API_Development, Open_API_Development, AP_SQL_Development, Data_Importing_as_a_File, Billing_Management, Organization_Management]\n", "fourth_class = [Get_Started]\n", "\n", "\n", "class_df = pd.DataFrame(columns=['user', 'class', 'job', 'class2'])\n", "for _, row in user_job_info.iterrows():\n", " classified = False\n", " u = row['user']\n", " class_flag = Wandering.lower()\n", " if user_in_class(row, fourth_class):\n", " if user_in_class(row, third_class):\n", " if user_in_class(row, second_class):\n", " if user_in_class(row, first_class):\n", " class_flag = 'first'\n", " else:\n", " class_flag = 'second'\n", " else:\n", " class_flag = 'third'\n", " else:\n", " class_flag = 'fourth'\n", "\n", " for job in first_class:\n", " if row[job]:\n", " class_df.loc[len(class_df)] = [u, 'first', [j for j in row.index if row[j] == True], class_flag]\n", " classified = True\n", " break\n", " if classified:\n", " continue\n", " for job in second_class:\n", " if row[job]:\n", " class_df.loc[len(class_df)] = [u, 'second', [j for j in row.index if row[j] == True], class_flag]\n", " classified = True\n", " break\n", " if classified:\n", " continue\n", " for job in third_class:\n", " if row[job]:\n", " class_df.loc[len(class_df)] = [u, 'third', [j for j in row.index if row[j] == True], class_flag]\n", " classified = True\n", " break\n", " if classified:\n", " continue\n", " for job in fourth_class:\n", " if row[job]:\n", " class_df.loc[len(class_df)] = [u, 'fourth', [j for j in row.index if row[j] == True], class_flag]\n", " classified = True\n", " break\n", " if classified:\n", " continue\n", " class_df.loc[len(class_df)] = [u, 'wandering', [j for j in row.index if row[j] == True], class_flag]" ] }, { "cell_type": "code", "execution_count": null, "id": "9479f623-1536-4ce9-9485-24c54ec394a0", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "id": "99eb2198-2f16-4122-8668-f425c8ff05cd", "metadata": {}, "source": [ "# Class Overview" ] }, { "cell_type": "code", "execution_count": null, "id": "3bdad20c-962e-4af1-8fa4-7510c9e82977", "metadata": { "tags": [] }, "outputs": [], "source": [ "from IPython.display import display, HTML" ] }, { "cell_type": "code", "execution_count": null, "id": "d537eff7-7665-46fc-b5af-b538460b5997", "metadata": { "tags": [] }, "outputs": [], "source": [ "class_count = class_df[\"class2\"].value_counts(ascending=True)\n", "overview_df = class_count.rename_axis('class').reset_index(name='counts')\n", "overview_df['percentage'] = overview_df['counts'].apply(lambda x: str(round(x/len(class_df)*100, 2))+'%')\n", "display(HTML(overview_df.to_html(index=False)))" ] }, { "cell_type": "code", "execution_count": null, "id": "3a8d59fb-5799-49e7-afec-cf21be5895ea", "metadata": { "tags": [] }, "outputs": [], "source": [ "user_info = {}\n", "for u in users:\n", " user_info[u.email()] = {\n", " 'tenant_id': u.id(),\n", " 'signup_time': u.signup_time().date(),\n", " 'region': u.region()\n", " }" ] }, { "cell_type": "code", "execution_count": null, "id": "c8a1b5c2-cfdd-40f4-aa8c-cbea05d9c104", "metadata": { "tags": [] }, "outputs": [], "source": [ "res = pd.DataFrame(columns=['email'] + ['tenant_id', 'class', 'signup_time', 'region'] + list(job_tasks_map.keys()))\n", "for _, row in class_df.iterrows():\n", " u = row['user']\n", " res.loc[len(res)] = [u, user_info[u]['tenant_id'], row['class2'], user_info[u]['signup_time'], user_info[u]['region']] + list(user_job_info[user_job_info.user==u].drop(columns=['user']).values[0])" ] }, { "cell_type": "markdown", "id": "af63c967-7a0f-47ec-8a76-3ef07a599db2", "metadata": {}, "source": [ "# Class 1: Production - Middle and Large Size (Paid)" ] }, { "cell_type": "code", "execution_count": null, "id": "fce434b3-2d9e-4d01-a253-4e99020fab06", "metadata": { "tags": [] }, "outputs": [], "source": [ "display(HTML(res[res['class'] == 'first'].to_html(index=False)))" ] }, { "cell_type": "markdown", "id": "55721984-09c9-4c19-bb73-c9a7004031f4", "metadata": {}, "source": [ "# Class 2: Production - Small Size (Free)" ] }, { "cell_type": "code", "execution_count": null, "id": "2b71f37d-db4d-4d5e-a336-edca03be26fa", "metadata": { "tags": [] }, "outputs": [], "source": [ "display(HTML(res[res['class'] == 'second'].to_html(index=False)))" ] }, { "cell_type": "markdown", "id": "5b1bd294-3448-49fb-96cc-8de477b87773", "metadata": { "tags": [] }, "source": [ "# Class 3: Dev / Testing / POC - Small Size" ] }, { "cell_type": "code", "execution_count": null, "id": "3a8d690c-6013-483d-b56b-0ed0b779c3c1", "metadata": { "tags": [] }, "outputs": [], "source": [ "display(HTML(res[res['class'] == 'third'].to_html(index=False)))" ] }, { "cell_type": "markdown", "id": "025c9137-5d24-4999-92bf-808844662f36", "metadata": { "tags": [] }, "source": [ "# Class 4: Learning and Exploration" ] }, { "cell_type": "code", "execution_count": null, "id": "170bb52b-0dd9-4b6f-990e-998112b6b736", "metadata": { "tags": [] }, "outputs": [], "source": [ "display(HTML(res[res['class'] == 'fourth'].to_html(index=False)))" ] }, { "cell_type": "markdown", "id": "e7c09514-45d6-4a66-ae2a-e2dff1ed3bcd", "metadata": { "tags": [] }, "source": [ "# Wandering: Signup" ] }, { "cell_type": "code", "execution_count": null, "id": "5127c3c8-b793-4388-ae68-dc4ce8a8e3d8", "metadata": { "tags": [] }, "outputs": [], "source": [ "display(HTML(res[res['class'] == 'wandering'].to_html(index=False)))" ] } ], "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 }