{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Data Discovery for Salesforce Opportunities" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To understand how opportunities work.\n", "\n", "- Process flow\n", "- Data quality\n", "- etc..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data Sources\n", "\n", "source data:\n", "```SQL\n", "gcp-digital-bi.salesforce\n", "\n", "opportunity tables:\n", " - opportunity\n", " - opportunity_history\n", " - opportunity_field_history\n", " - opportunity_product_c\n", " - opportunity_related_delete_log\n", " - opportunity_team_member_history\n", "\n", "potentially related tables:\n", " - account\n", " - order\n", " - quote\n", "\n", "```\n", "\n", "\n", "Some tables by huoyao:\n", "```SQL\n", "gcp-digital-bi.fp_and_a\n", "gcp-digital-bi.bo\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Common Setup" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%load_ext autoreload\n", "%autoreload 2\n", "\n", "%load_ext google.cloud.bigquery\n", "\n", "import os\n", "import pandas as pd\n", "\n", "from google.cloud import bigquery\n", "from datetime import datetime, timedelta\n", "\n", "from itables import init_notebook_mode, show\n", "init_notebook_mode(all_interactive=True)\n", "\n", "os.environ[\"GCLOUD_PROJECT\"] = \"gcp-digital-bi-dev\"\n", "\n", "client = bigquery.Client()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "params = {\n", " \"project\": os.environ[\"GCLOUD_PROJECT\"],\n", "}" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def inspect_columns(df):\n", "\n", " columns = df.columns\n", "\n", " # For each column, we would like to know:\n", " # - The number of unique values\n", " # - The number of null/NaN/emtpy string\n", " # - The number of unique values in the last 6 months\n", " # - The number of null/NaN/emtpy string in the last 6 months\n", "\n", " # We will store the results in a dataframe\n", " df_stats = pd.DataFrame(columns=[\"column\", \"n_non_null\", \"n_unique\", \"n_null\", \"n_unique_6m\", \"n_null_6m\"])\n", "\n", " # Define the date 6 months ago from today\n", " six_months_ago = pd.to_datetime(datetime.now() - timedelta(days=6*30))\n", "\n", " for column in columns:\n", " n_unique = df[column].nunique()\n", " n_null = df[column].isnull().sum() + (df[column] == '').sum()\n", " n_non_null = len(df) - n_null\n", " \n", " df_last_6m = df[df['system_modstamp'].dt.date > six_months_ago.date()]\n", " n_unique_6m = df_last_6m[column].nunique()\n", " n_null_6m = df_last_6m[column].isnull().sum() + (df_last_6m[column] == '').sum()\n", " \n", " new_row = pd.DataFrame({\n", " \"column\": column,\n", " \"n_non_null\": n_non_null,\n", " \"n_unique\": n_unique,\n", " \"n_null\": n_null,\n", " \"n_unique_6m\": n_unique_6m,\n", " \"n_null_6m\": n_null_6m\n", " }, index=[0])\n", "\n", " df_stats = pd.concat([df_stats, new_row], ignore_index=True)\n", "\n", " print(\"row count: \", len(df))\n", "\n", " show(df_stats)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_sample_data\n", "SELECT *\n", "FROM `gcp-digital-bi.salesforce.opportunity`\n", "LIMIT 1\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "First we really would like to know a bit more about the columns in the opportunity table. Which ones are used? Which ones are less used? Which ones may potentially be outdated / duplicated?" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_opportunity\n", "WITH o AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.opportunity`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", "),\n", "a AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.account`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", ")\n", "SELECT o.*, a.region_c\n", "FROM o LEFT JOIN a ON o.account_id = a.id" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "_ = df_opportunity\n", "\n", "_.sample(5)[['id', 'stage_name', 'name', 'region_c', 'amount']]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "inspect_columns(df_opportunity)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Observations*\n", "1. There are a lot of fields in the table (234), \n", " - 100~ (50%) are underused (<10% populated)\n", " - 20~ (1%) are never used\n", "2. More TBD, probably will offload this to LLMs." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Next, we are keen in some specific columns that potentially indicates the state of the opportunity pipeline:\n", "\n", "- `stage_name`\n", " `last_stage_change_date`\n", "- `next_milestone_c`\n", "- `next_key_action_c`\n", "- `key_updates_c`\n", "- `key_deal_c`" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "columns = ['stage_name', 'next_milestone_c', 'next_key_action_c', 'key_updates_c', 'key_deal_c']\n", "\n", "_ = df_opportunity\n", "\n", "_['region_c'].fillna('Unknown', inplace=True)\n", "\n", "# Columns that are free text \n", "txt_columns = ['next_milestone_c', 'next_key_action_c', 'key_updates_c']\n", "\n", "# Columns that are categorical\n", "cat_columns = ['stage_name', 'key_deal_c']\n", "\n", "\n", "for column in cat_columns:\n", " unique_values = _[column].unique()\n", "\n", " _df = pd.DataFrame(columns=[\"value\"])\n", "\n", " for value in unique_values:\n", " if pd.isnull(value) or value == '':\n", " count_by_region = _[_[column].isnull()].groupby('region_c').size()\n", " \n", " else:\n", " count_by_region = _[_[column] == value].groupby('region_c').size()\n", "\n", " total_count_by_region = _.groupby('region_c').size()\n", " \n", " new_row = {\n", " \"value\": value,\n", " }\n", " for region in total_count_by_region.index:\n", " count = count_by_region.get(region, 0)\n", " total_region = total_count_by_region[region]\n", " percent_within_region = (count / total_region) * 100 if total_region > 0 else 0\n", " new_row[f\"{region}\"] = count\n", " new_row[f\"{region}%\"] = round(percent_within_region, 2)\n", "\n", " new_row = pd.DataFrame(new_row, index=[0])\n", "\n", " _df = pd.concat([_df, new_row], ignore_index=True)\n", " \n", " print(f\"===================\\nColumn: {column}\")\n", " show(_df)\n", "\n", "for column in txt_columns:\n", " print(f\"===================\\nColumn: {column}\")\n", " # We want to know the non-null counts for each region\n", " _df = pd.DataFrame(columns=[\"region\", \"total\", \"non_null\", \"percent\"])\n", "\n", " for region in _['region_c'].unique():\n", " count = len(_[(_[column].notnull()) & (_['region_c'] == region)])\n", " total_region = len(_[_['region_c'] == region])\n", " percent_within_region = (count / total_region) * 100 if total_region > 0 else 0\n", "\n", " new_row = {\n", " \"region\": region,\n", " \"total\": total_region,\n", " \"non_null\": count,\n", " \"percent\": round(percent_within_region, 2)\n", " }\n", "\n", " new_row = pd.DataFrame(new_row, index=[0])\n", "\n", " _df = pd.concat([_df, new_row], ignore_index=True)\n", "\n", " show(_df)\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Observations**\n", "\n", "Pretty standard stuff. However, it seems that the `key_milestones` / `key_action` / `key_updates` columns are:\n", "1. generally underused, and\n", "2. varies by region.\n", "\n", "Potentially this ties in with a comment from @Chen Du where sales teams in different regions behave differently - this is going to be a bit challenging for us when trying to determine sales team efficiency, as we may need to analyse data based on region and hence derive region-specific metrics." ] }, { "cell_type": "markdown", "metadata": {}, "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Following from the above, we are now keen to understand what the below tables do:\n", "\n", "- `opportunity_history`\n", "- `opportunity_field_history`\n", "\n", "We would like to understand the scope of each log table, and how we can use them to trace the stage/status/milestone updates for each opportunity." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_opportunity_history\n", "WITH o AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.opportunity`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", "),\n", "a AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.account`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", "),\n", "oh AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.opportunity_history`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", ")\n", "SELECT oh.*, o.account_id, o.name, o.stage_name, o.system_modstamp AS o_system_modstamp, a.region_c\n", "FROM oh\n", "LEFT JOIN o ON oh.opportunity_id = o.id\n", "LEFT JOIN a ON o.account_id = a.id" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df_opportunity_history[df_opportunity_history['opportunity_id'] == '006RC00000BmEFFYA3']\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_opportunity_field_history\n", "WITH o AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.opportunity`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", "),\n", "a AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.account`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", "),\n", "oh AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.opportunity_field_history`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", ")\n", "SELECT oh.*, o.account_id, o.name, o.stage_name AS o_stage_name, o.system_modstamp AS o_system_modstamp, a.region_c\n", "FROM oh\n", "LEFT JOIN o ON oh.opportunity_id = o.id\n", "LEFT JOIN a ON o.account_id = a.id" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "_ = df_opportunity_field_history\n", "\n", "_[_['opportunity_id'] == '006RC00000BmEFFYA3']\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Observations**\n", "\n", "1. `history` does not preserve a lot of history at all, probably can only get amount and stage.\n", " We can get richer information from `field_history`.\n", "2. field names in `field_history` is snake case\n", "3. Negotiating is called Bidding / Negotiating in `history` \n", "\n", "\n", "**Next Steps**\n", "\n", "1. Realistically we can preserve a lot of update history in the dwh opportunity table. Notably, we can add arrays:\n", " - `stage_progression`\n", " - `amount_history`\n", " - `name_aliases`\n", "\n", " And potentially more history (eg, key milestone history, key next step history, historical users etc). We should start with stage progression and key milestone / next step history." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_quote\n", "WITH q AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.quote`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", ")\n", "SELECT *\n", "FROM q" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "_ = df_quote\n", "\n", "show(_.describe())\n", "\n", "show(_.sample(5))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "inspect_columns(df_quote)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Observations**\n", "\n", "Quote looks quite standard.\n", "1. Less than half of fields with 90%+ null rate. 25% out of all fields are completely empty.\n", "2. Should contract_id be more populated?\n", "3. What is the relationship between\n", " - opportunity_product\n", " - quote_product" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_quote_product\n", "WITH q AS (\n", " SELECT *\n", " FROM `gcp-digital-bi.salesforce.quote_product_c`\n", " WHERE is_deleted = false AND _fivetran_deleted = false\n", ")\n", "SELECT *\n", "FROM q" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "inspect_columns(df_quote_product)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.12.9" } }, "nbformat": 4, "nbformat_minor": 2 }