{ "cells": [ { "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": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery \n", "\n", "SELECT *\n", "FROM `gcp-digital-bi-dev.sandbox_legacy_ywang.dwm_monthly_order_info_calculation11_of_revrec_tenant_v3`\n", "WHERE bill_month = '2025-01' and category_of_order_process is null" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery df_orders\n", "\n", "with orders as (\n", " select\n", " id as order_id,\n", " effective_date AS order_start_date,\n", " Order_End_Date_c AS order_end_date,\n", " accumulate_revenue_to_org_c AS accumulate_revenue_to_org_id,\n", " from `gcp-digital-bi.salesforce.order`\n", " where\n", " _fivetran_deleted = false and is_deleted = false\n", "),\n", "order_products as (\n", " select *\n", " from `gcp-digital-bi.salesforce.order_product_c`\n", " where _fivetran_deleted = false and is_deleted = false\n", "),\n", "rev as (\n", " select * except(amount_before_adjust_to_use_c),\n", " CASE\n", " WHEN amount_before_adjust_to_use_c < 0.01 THEN amount_c / exchange_rate_to_use_c\n", " WHEN amount_before_adjust_to_use_c IS NULL THEN amount_c / exchange_rate_to_use_c \n", " ELSE amount_before_adjust_to_use_c END AS amount_before_adjust\n", " from `gcp-digital-bi.salesforce.revenue_c`\n", " where _fivetran_deleted = false and is_deleted = false\n", "),\n", "get_net_fees as (\n", " select\n", " o.*,\n", " sum(cast(net_fees_c as int64)) as net_fees\n", " from orders o\n", " left join order_products op on o.order_id = op.order_c\n", " group by all\n", "),\n", "get_revenue as (\n", " select\n", " o.*,\n", " sum(coalesce(amount_before_adjust, 0)) as accumulated_revenue,\n", " sum(CAST(amount_c AS INT64)) as revenue_amount,\n", " from get_net_fees o\n", " left join rev on o.order_id = rev.order_c\n", " group by all\n", "),\n", "calc_some_stuff as (\n", " select\n", " *,\n", " net_fees - accumulated_revenue as deferred_revenue,\n", " from get_revenue\n", ")\n", "SELECT *\n", "FROM calc_some_stuff" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Issue 1.2\n", "\n", "## Investigate commitment plan 超额\n", "\n", "tenant id: 1372813089209212842" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery\n", "# Here we have a quick look at available orders for the tenant\n", "SELECT\n", " ordernumbername,\n", " orderstartdate,\n", " orderenddate,\n", " order_is_terminated,\n", " termination_date,\n", " netfees,\n", " total_fees_usd,\n", " deferredrevenue,\n", " deferredrevenueusd,\n", " order_reason,\n", " productfamily,\n", " accumulate_revenue_to_org,\n", " accumulated_revenue_before_adjust,\n", " accumulated_credit_amount,\n", "FROM `gcp-digital-bi.model_salesforce_and_fenxiangxiaoke.dwm_salesforce_order_basic_info`\n", "WHERE accumulate_revenue_to_org = '1372813089209212842'\n", "ORDER BY OrderStartDate DESC\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see that there are 2 'valid' orders for Jan 2025:\n", "1. JP-OF-25020514 with netfees 3160 and total_fees_usd 3476.847\n", "2. JP-OF-24032103 with netfees 100000 and total_fees_usd 110000\n", "\n", "now we check dbt logic" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%bigquery\n", "# Here we verify that both orders have been pulled for this tenant\n", "SELECT *\n", "FROM `gcp-digital-bi-dev.sandbox_legacy_ywang.dwm_monthly_revrec_tenant_order_in_need_v3`\n", "WHERE\n", " accumulate_revenue_to_org = '1372813089209212842'\n", " AND bill_month = '2025-01'\n", "ORDER BY OrderStartDate DESC\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 }