SK Assessment/BI_lead.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BI Lead Challenge\n",
    "\n",
    "## Juan Camilo Florez Caro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para iniciar, antes de la construcción del tablero debemos entender el objetivo del negocio; el proposito que perseguimos con el mismo. En esta etapa definiremos cuales son los KPI/ OKR que buscamos mejorar en la campaña de mercadeo, posteriormente procesaremos la data para construir estos indicadores y finalmente definir el tablero que mejor se alinee con este propósito.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Entendiendo que el proposito del análisis es entender el funnel de conversión que tiene una campaña de mercadeo, la cual va desde que se intenta contactar a un cliente hasta el momento en el cual se le entrega (Bien sea de forma Fisica o digital), debemos iniciar conociendo cual es nuestra base potencial (Total de Leads).\n",
    "\n",
    "A partir de allí encontramos que es importante definir como indicadores escenciales:\n",
    "\n",
    "* 1) % Respuesta = # Clientes que respondieron a la campaña / Total de Leads\n",
    "* 2) % Aprobación = # Clientes Aprobados / Total de clientes que llegaron a riesgo\n",
    "* 3) % Entrega = # Plásticos entregados / # Plásticos Aprobados\n",
    "* 4) % Uso = # Tarjetas con al menos una TX  / # Plásticos Entregados\n",
    "* 5) $ Uso = Sumatoria de las transacciones sobre las cuales se generarán utilidades\n",
    "\n",
    "Así mismo, surgen varios interrogantes como detalle del funnel anterior sobre brechas que podrían cerrar areas operativas de Mercadeo, Riesgo o Servicio al cliente revisando el detalle de la información, como lo son:\n",
    "\n",
    "* 1) ¿Como logramos una mayor tasa de respuesta en funcion de los clientes que no nos contestaron? \n",
    "* 2) ¿Cómo logramos mejorar la tasa de aprobación ¿Cuales son los motivos por los cuales estámos negando?\n",
    "* 3) ¿Cómo estan percibiendo los clientes el servicio de entrega de tarjetas fisicas?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('BusinessBIBA_Challenge.csv', sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename({'Unnamed: 0':'ORDER'}, axis=1, inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Entendiendo la logica que tiene el campo STATUS, poblaremos los campos vacios con NOT RESPOND cuando tengamos un cliente que no haya respondido a la campaña de mercadeo, y TX cuando el registro contenga una Trasacción con la tarjeta\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['STATUS'] = np.where((df['STATUS'].isnull()==True) & (df['TXN'].isnull()==True) , 'NOT RESPOND',\n",
    "                np.where((df['STATUS'].isnull()==True) & (df['TXN'].isnull()==False), 'TX',df['STATUS']))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para mayor practicidad separamos la información correspondiente al funnel de conversion de la informacióin relacionada con las trasacciones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORDER</th>\n",
       "      <th>ID</th>\n",
       "      <th>UPDATE</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>MOTIVE</th>\n",
       "      <th>INTEREST_RATE</th>\n",
       "      <th>AMOUNT</th>\n",
       "      <th>CAT</th>\n",
       "      <th>CP</th>\n",
       "      <th>DELIVERY_SCORE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>59:50.4</td>\n",
       "      <td>RESPONSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>46:41.4</td>\n",
       "      <td>RISK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>14:50.5</td>\n",
       "      <td>APPROVED</td>\n",
       "      <td>DIGITAL</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>NOT RESPOND</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>NOT RESPOND</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>01:07.0</td>\n",
       "      <td>RESPONSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>27:50.6</td>\n",
       "      <td>RISK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>49:54.2</td>\n",
       "      <td>APPROVED</td>\n",
       "      <td>PLASTIC</td>\n",
       "      <td>32.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>00:52.7</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11560.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>59:27.9</td>\n",
       "      <td>RESPONSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ORDER  ID   UPDATE       STATUS   MOTIVE  INTEREST_RATE   AMOUNT   CAT  \\\n",
       "0       0   1  59:50.4     RESPONSE      NaN            NaN      NaN   NaN   \n",
       "1       1   1  46:41.4         RISK      NaN            NaN      NaN   NaN   \n",
       "2       2   1  14:50.5     APPROVED  DIGITAL           40.0   1300.0  49.0   \n",
       "4       4   2  00:00.0  NOT RESPOND      NaN            NaN      NaN   NaN   \n",
       "5       5   3  00:00.0  NOT RESPOND      NaN            NaN      NaN   NaN   \n",
       "6       6   4  01:07.0     RESPONSE      NaN            NaN      NaN   NaN   \n",
       "7       7   4  27:50.6         RISK      NaN            NaN      NaN   NaN   \n",
       "8       8   4  49:54.2     APPROVED  PLASTIC           32.0  10000.0  39.0   \n",
       "9       9   4  00:52.7    DELIVERED      NaN            NaN      NaN   NaN   \n",
       "14     14   5  59:27.9     RESPONSE      NaN            NaN      NaN   NaN   \n",
       "\n",
       "         CP  DELIVERY_SCORE  \n",
       "0       NaN             NaN  \n",
       "1       NaN             NaN  \n",
       "2       NaN             NaN  \n",
       "4       NaN             NaN  \n",
       "5       NaN             NaN  \n",
       "6       NaN             NaN  \n",
       "7       NaN             NaN  \n",
       "8       NaN             NaN  \n",
       "9   11560.0             3.0  \n",
       "14      NaN             NaN  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df_funnel = df.loc[:, df.columns != 'TXN'][df['STATUS']!='TX']\n",
    "df_funnel.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para poder construir una metrica del total de leads que respondieron como quienes no respondieron a la campaña de maketing creamos un status denominado LEADS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\1016038645\\AppData\\Local\\Temp\\ipykernel_10244\\3870721461.py:5: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_funnel = df_funnel.append(campaign)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "campaign = df_funnel[(df_funnel['STATUS']=='RESPONSE')|(df_funnel['STATUS']=='NOT RESPOND')][['ID']].drop_duplicates()\n",
    "campaign['STATUS'] = 'LEADS'\n",
    "campaign['UPDATE'] = '00:00.0'\n",
    "\n",
    "df_funnel = df_funnel.append(campaign)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORDER</th>\n",
       "      <th>ID</th>\n",
       "      <th>UPDATE</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>TXN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>08:23.4</td>\n",
       "      <td>TX</td>\n",
       "      <td>434.112066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>25:47.0</td>\n",
       "      <td>TX</td>\n",
       "      <td>360.504173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>22:22.1</td>\n",
       "      <td>TX</td>\n",
       "      <td>175.061765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>44:53.3</td>\n",
       "      <td>TX</td>\n",
       "      <td>3719.618354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>53:04.3</td>\n",
       "      <td>TX</td>\n",
       "      <td>1171.027491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ORDER  ID   UPDATE STATUS          TXN\n",
       "3       3   1  08:23.4     TX   434.112066\n",
       "10     10   4  25:47.0     TX   360.504173\n",
       "11     11   4  22:22.1     TX   175.061765\n",
       "12     12   4  44:53.3     TX  3719.618354\n",
       "13     13   4  53:04.3     TX  1171.027491"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tx = df[['ORDER','ID','UPDATE','STATUS','TXN']][df['STATUS']=='TX']\n",
    "df_tx.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rectificamos que data tienen las bases ya que esta será la que llevaremos a los tableros así como también validamos cual sería el resultado de los KPI definidos inicialmente\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Validacion de KPIs\n",
      "El dataset cuenta con  3341  clientes\n",
      "El dataset cuenta con  1255  clientes que han hecho al menos una transacción\n",
      "% Respuesta:  0.6878180185573182\n",
      "% Aprobación:  0.654917319408181\n",
      "% Entrega:  1.0\n",
      "% Uso:  0.8338870431893688\n",
      "$ Uso:  7582878.185593394\n"
     ]
    }
   ],
   "source": [
    "total_leads = len(df_funnel['ID'].unique())\n",
    "clientes_con_tx = len(df_tx['ID'].unique())\n",
    "respondieron = len(df_funnel['ID'][df_funnel['STATUS']=='RESPONSE'].unique())\n",
    "riesgo = len(df_funnel['ID'][df_funnel['STATUS']=='RISK'].unique())\n",
    "aprobados = len(df_funnel['ID'][df_funnel['STATUS']=='APPROVED'].unique())\n",
    "entregadas = len(df_funnel['ID'][df_funnel['STATUS']=='DELIVERED'].unique())\n",
    "plasticos = len(df_funnel['ID'][df_funnel['MOTIVE']=='PLASTIC'].unique())\n",
    "total_tx = df_tx['TXN'].sum()\n",
    "\n",
    "print('Validacion de KPIs')\n",
    "print('El dataset cuenta con ',str(total_leads), ' clientes')\n",
    "print('El dataset cuenta con ',str(clientes_con_tx), ' clientes que han hecho al menos una transacción')\n",
    "print('% Respuesta: ',str(respondieron/total_leads))\n",
    "print('% Aprobación: ',str(aprobados/riesgo))\n",
    "print('% Entrega: ',str(entregadas/plasticos))\n",
    "print('% Uso: ',str(clientes_con_tx/aprobados))\n",
    "print('$ Uso: ',str(total_tx))\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora para evaluar el servicio al cliente hemos decidido crear la metrica del NPS:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "El NPS es : -0.9005736137667304\n"
     ]
    }
   ],
   "source": [
    "\n",
    "nps = df_funnel[df_funnel['STATUS']=='DELIVERED']\n",
    "total_enc = len(nps['ID'].unique())\n",
    "detractores = len(nps[nps['DELIVERY_SCORE']<=3]['ID'].unique())\n",
    "pasivos = len(nps[nps['DELIVERY_SCORE']==4]['ID'].unique())\n",
    "promotores = len(nps[nps['DELIVERY_SCORE']==5]['ID'].unique())\n",
    "\n",
    "nps_sc = (promotores-detractores)/total_enc\n",
    "\n",
    "print('El NPS es :',str(nps_sc))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tras lo anterior nos damos cuenta que las principales acciones deberían estar encaminadas a:\n",
    "1) Mejorar el % de aprobación ya que tan solo el 65% de los leads cuentan con el aval de Riesgo y siendo una campaña esta podría ser más cercana al 100% entendiendo que se preseleccionan los clientes a quienes se le hace la oferta\n",
    "2) Mejorar la calidad en el servicio de la entrega de las tarjetas plásticas, ya que el NPS obtenido es del -90%"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Habiendo validado los KPI que requerimos y la calidad en la data que necesitamos, exportamos las bases con las que construiremos los tableros\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df_funnel.to_csv('df_funnel.csv',sep='|', index= False, decimal = ',')\n",
    "df_tx.to_csv('df_tx.csv',sep='|', index= False, decimal = ',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Construcción del tablero"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con lo anterior, entendiendo los tres tipos de Clientes Internos que podrian observar el tablero, se deciden construir 3 lienzos a tres niveles distintos:\n",
    "\n",
    "*Lienzo Estratégico:* Un lienzo donde se observen los KPI, que no cuente con filtros para garantizar que en la organización se tenga una misma óptica sobre el desempeño de la campaña de mercadeo, un lienzo enfocado en los grandes tomadores de decisiones o C-Level\n",
    "\n",
    "*Lienzo Táctico:* Un lienzo que permita llegar un poco más a fondo en el analisis exploratorio y descriptivo sobre la data que lo alimenta, un lienzo enfocado en los mandos medios e intermedios\n",
    "\n",
    "*Lienzo Operativo:* Un lienzo enfocado en mostrar el detalle de los datos, enfocado en el uno a uno de aquellos puntos donde se tengan accionables que permitan movilizar los KPI inicialmente definidos. Un lienzo enfocado en quienes tienen contacto directo con el cliente\n",
    "\n",
    "Para lo anterior el resultado obtenido lo creamos en el siguiente tablero:\n",
    "\n",
    "LINK DEL TABLERO: https://github.com/juankfc/DataScientist-Roadmap/blob/afdc417571d5c157fa8dcfa68bad07eb3936acf7/SK%20Assessment/Credit%20Card%20Delivery%20Funnel.pbix\n",
    "\n",
    "Donde encontraremos el siguiente resultado:\n",
    "\n",
    "*Portada:* un menú de botones donde encontraremos los accesos cada uno de los lienzos previamente definidos\n",
    "\n",
    "<img src=\"https://github.com/juankfc/DataScientist-Roadmap/blob/main/SK%20Assessment/0.png?raw=true\">\n",
    "\n",
    "*Lienzo Estrategico:* Un lienzo donde tuvimos que importar algunas visualizaciones del marketplace para construir los formatos condicionales en los KPI\n",
    "\n",
    "<img src=\"https://github.com/juankfc/DataScientist-Roadmap/blob/main/SK%20Assessment/1.png?raw=true\">\n",
    "\n",
    "*Lienzo Táctico:* Un lienzo donde podemos explorar la data\n",
    "<img src=\"https://github.com/juankfc/DataScientist-Roadmap/blob/main/SK%20Assessment/2.png?raw=true\">\n",
    "\n",
    "*Lienzo Operativo:* Un lienzo con la data accionable\n",
    "<img src=\"https://github.com/juankfc/DataScientist-Roadmap/blob/main/SK%20Assessment/3.png?raw=true\">\n"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "bc2c41fc61a3e74ec67c653956da0bb75b1b52edf9a809a0a62c60a6d6978ece"
  },
  "kernelspec": {
   "display_name": "Python 3.8.12 ('pycaret_env')",
   "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.8.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}