{
 "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()\n",
    "\n",
    "def get_results_as_df(query):\n",
    "    \"\"\"\n",
    "    Run a BigQuery SQL query and return the results as a Pandas DataFrame.\n",
    "    \"\"\"\n",
    "    query_job = client.query(query)\n",
    "    results = query_job.result()\n",
    "    df = results.to_dataframe()\n",
    "    return df"
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "**We would like to inspect the relationships between tables, and have a look at data**\n",
    "\n",
    "1. Look at keys between tables\n",
    "2. Establish front-end / back-end field relationships\n",
    "3. Check nulls and stuff"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Inspect tables of interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [\n",
    "    \"stg_salesforce_campaign\",\n",
    "    \"stg_salesforce_campaign_member\",\n",
    "    \"stg_salesforce_campaign_member_status\",\n",
    "    \"stg_salesforce_contact\",\n",
    "    \"stg_salesforce_lead\",\n",
    "    \"stg_salesforce_opportunity\",\n",
    "    \"stg_salesforce_opportunity_contact_role\"\n",
    "]\n",
    "_sql_template = \"select * from `gcp-digital-bi-dev.sandbox_stg_ywang.{}`\"\n",
    "\n",
    "for table in tables:\n",
    "    query = _sql_template.format(table)\n",
    "    print(\"table: \", table)\n",
    "    _df = get_results_as_df(query)\n",
    "    inspect_columns(_df)\n",
    "    print(\"--------------------------------------------------\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Inspect specific cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery df_campaign\n",
    "# Here we verify that both orders have been pulled for this tenant\n",
    "select *\n",
    "from `gcp-digital-bi.stg_salesforce.stg_salesforce_campaign`\n",
    "WHERE campaign_name like '%EMEA_LinkedIn_ABM%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "params['campaign_id'] = df_campaign.campaign_id.values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --params $params\n",
    "select *\n",
    "from `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member`\n",
    "WHERE campaign_id = @campaign_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --params $params\n",
    "select *\n",
    "from `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member_status`\n",
    "WHERE campaign_id = @campaign_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "member status is probably not necessary during processing, since it only records the label & it's type.\n",
    "\n",
    "However, we may potentially use it for DQ checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --params $params\n",
    "select *\n",
    "from `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_lead`\n",
    "WHERE account_id is not null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No leads have corresponding accounts - but maybe converted leads may have accounts?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --params $params\n",
    "select *\n",
    "from `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member` member\n",
    "left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_lead` lead on member.lead_id = lead.lead_id\n",
    "left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_contact` contact on lead.converted_contact_id = contact.contact_id\n",
    "WHERE contact.account_id is not null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes leads will be converted into contacts after a campaign - potentially we also want to consider this scenario"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --params $params\n",
    "with campaign_contacts AS (\n",
    "    select\n",
    "        campaign.campaign_id, campaign.campaign_name, campaign.campaign_end_date,\n",
    "        member.first_responded_date as contact_response_date,\n",
    "        lead.converted_contact_id as contact_id\n",
    "    from \n",
    "    `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign` campaign\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member` member on campaign.campaign_id = member.campaign_id\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_lead` lead on member.lead_id = lead.lead_id\n",
    "    where member.lead_id is not null\n",
    "    union all\n",
    "    select\n",
    "        campaign.campaign_id, campaign.campaign_name, campaign.campaign_end_date,\n",
    "        member.first_responded_date as contact_response_date,\n",
    "        member.contact_id as contact_id,\n",
    "    from \n",
    "    `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign` campaign\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member` member on campaign.campaign_id = member.campaign_id\n",
    ")\n",
    "select\n",
    "    campaign.campaign_id, campaign.campaign_name, campaign.campaign_end_date, contact_response_date,\n",
    "    contact.contact_name, contact.account_id,\n",
    "    oppo.id, oppo.name, oppo.created_date, oppo.close_date,\n",
    "from\n",
    "    campaign_contacts campaign\n",
    "left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_contact` contact on campaign.contact_id = contact.contact_id\n",
    "left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_opportunity` oppo on contact.account_id = oppo.account_id\n",
    "where\n",
    "    contact.account_id is not null and contact_response_date is not null and (\n",
    "        DATE(oppo.created_date) between campaign.campaign_end_date and date_add(campaign.campaign_end_date, interval 3 month) or\n",
    "        DATE(oppo.created_date) between campaign.contact_response_date and date_add(campaign.contact_response_date, interval 3 month) or\n",
    "        DATE(oppo.close_date) between campaign.campaign_end_date and date_add(campaign.campaign_end_date, interval 3 month) or\n",
    "        DATE(oppo.close_date) between campaign.contact_response_date and date_add(campaign.contact_response_date, interval 3 month)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --params $params\n",
    "with campaign_contacts AS (\n",
    "    select\n",
    "        campaign.campaign_id, campaign.campaign_name, campaign.campaign_end_date,\n",
    "        member.first_responded_date as contact_response_date,\n",
    "        lead.converted_contact_id as contact_id\n",
    "    from \n",
    "    `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign` campaign\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member` member on campaign.campaign_id = member.campaign_id\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_lead` lead on member.lead_id = lead.lead_id\n",
    "    where member.lead_id is not null\n",
    "    union all\n",
    "    select\n",
    "        campaign.campaign_id, campaign.campaign_name, campaign.campaign_end_date,\n",
    "        member.first_responded_date as contact_response_date,\n",
    "        member.contact_id as contact_id,\n",
    "    from \n",
    "    `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign` campaign\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_campaign_member` member on campaign.campaign_id = member.campaign_id\n",
    "),\n",
    "\n",
    "oppo_with_campaign AS (\n",
    "    select\n",
    "        campaign.campaign_id, campaign.campaign_name, campaign.campaign_end_date, contact_response_date,\n",
    "        contact.contact_name, contact.account_id,\n",
    "        oppo.id, oppo.name as opportunity_name, oppo.created_date, oppo.close_date\n",
    "    from\n",
    "        campaign_contacts campaign\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_contact` contact on campaign.contact_id = contact.contact_id\n",
    "    left join `gcp-digital-bi-dev.sandbox_stg_ywang.stg_salesforce_opportunity` oppo on contact.account_id = oppo.account_id\n",
    "    where\n",
    "        contact.account_id is not null and contact_response_date is not null and (\n",
    "            DATE(oppo.created_date) between campaign.campaign_end_date and date_add(campaign.campaign_end_date, interval 3 month) or\n",
    "            DATE(oppo.created_date) between campaign.contact_response_date and date_add(campaign.contact_response_date, interval 3 month) or\n",
    "            DATE(oppo.close_date) between campaign.campaign_end_date and date_add(campaign.campaign_end_date, interval 3 month) or\n",
    "            DATE(oppo.close_date) between campaign.contact_response_date and date_add(campaign.contact_response_date, interval 3 month)\n",
    "        )\n",
    ")\n",
    "\n",
    "select opportunity_name, array_agg(campaign_name)\n",
    "from oppo_with_campaign\n",
    "group by all"
   ]
  },
  {
   "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
}