PROYECTO 5: Servicios de telefonía
1. CREACIÓN DE AMBIENTE DE TRABAJO
Para iniciar este proceso de exploración necesitas descargar las bases de datos. Luego de haber
realizado la descargar nos dirigimos al entorno inicial de Google Cloud Platform (link), donde el
puntero del mouse lo colocaremos en la opción de BigQuery.
Figura 1: Inicio de Google Cloud Platform.
Dado que hemos dado clic en la opción del cuadro verde, la ventana que nos mostrará será el
entorno de BigQuery:
1
Figura 2: Espacio de trabajo BigQuery SQL.
¡Bien!, ahora que nos adentramos al entorno de Bigquery SQL, el siguiente paso que debemos
realizar es importar nuestras 5 fuentes de datos a BigQuery, para ello es fundamental crear
nuestro proyecto propio de la siguiente manera:
Figura 3: Ubicación de la opción creación de proyecto.
De la Figura (3) observar el cuadro naranja y dar clic a la opción My First Proyect, luego nos
mostrará la siguiente ventana:
Figura 4: Ventana para la creación de proyecto nuevo.
En la ventana emergente (Figura 5) nos mostrará las opciones de Nombre del proyecto (en esta
sección puedes editar o colocar el nombre que desees) para este desarrollo lo llamaremos
laboratoria-telco, a su vez la opción de ubicación lo mantenemos como Sin organización,
finalmente dar clic en Crear. A continuación se muestra la ventana:
2
Figura 5: Ejemplo de la estructura del dataset
¡Buen trabajo!, ahora que se tiene el proyecto creado, el siguiente paso es poder crear un
esquema donde trabajaremos la importación de las 5 fuentes de datos de la empresa TELCO,
para ello dirigirnos al proyecto creado (laboratoria-prueba) donde nos mostrará tres puntitos
verticales a la cual debemos dar clic y posteriormente Crear un conjunto de datos.
Figura 6: Indicaciones para la
creación de esquema o conjunto
de datos.
La ventana emergente que nos mostrará al Crear un conjunto de datos es la siguiente:
Figura 7: Ventana para la creación de un conjunto de datos
3
En la Figura 7, la primera opción ID del proyecto nos indica la ubicación donde se creará el
esquema o conjunto de datos, la segunda opción es el nombre del esquema que nosotros
colocaremos (para este desarrollo se llamará Dataset_telco) las opciones restantes las
dejaremos tal y como están, finalmente dar clic en Crear conjunto de datos.
Posterior a los pasos anteriores es posible que se muestre el mensaje de la Figura 8 en el
entorno de BigQuery SQL por lo que no debe alarmarse ya que se ha creado correctamente el
esquema.
Figura 8: Mensaje posterior a la creación del conjunto de datos o esquema.
Figura 9: Sección para validar la creación del esquema.
¡Bien!, ahora para validar que se ha creado correctamente el esquema en el correspondiente
proyecto, dirigirnos a la parte izquierda del entorno de BigQuery SQL (Explorador) como se
muestra en la siguiente imagen:
4
En la Figura (9) corroboramos que el conjunto de datos dataset_telco se ha creado
correctamente en el proyecto adecuado.
Hemos desarrollado un gran avance hasta este momento, lo que se necesita ahora es entender
cómo importar nuestras 5 fuente de datos, para ello direccionamos nuestro cursor en el
esquema proyecto_telco y damos clic en los tres puntos verticales como se muestra a
continuación en la siguiente imagen:
Figura 10: Sección para creación de esquema.
Al realizar el clic correspondiente al cuadro verde (Figura 10), nos mostrará la siguiente ventana:
5
Figura 11: Ventana para la creación del esquema.
El cuadro verde que se muestra en la Figura (11) explica el origen de la fuente de datos a
importar, mientras que el cuadro amarillo determina la ubicación a la cual vamos a dirigir nuestra
importación de datos, para ello en el cuadro amarillo debemos centrarnos y validar si son las
direcciones correctas que deseamos.
Posteriormente pasamos a determinar el origen de los datos, para ello dar clic en el cuadro
verde (Figura 11), cuyas opciones mostradas será las siguientes:
Figura 12: Ventana de opciones para importar
las fuentes de datos.
Figura 12: Selección del archivo a importar.
Como podemos observar en la Figura (12) hemos seleccionado el primer archivo a importar que
sería Telco_customer_churn_demographics.csv, luego de ello debemos ir a la sección destino
como se muestra a continuación:
Figura 13: Selección del proyecto, esquema y nombre de la tabla.
6
Como se puede observar en la Figura (13) en el cuadro rojo es donde se coloca el nombre de la
tabla de cómo queremos que se llame nuestro archivo a importar, para este desarrollo lo
llamaremos churn_demographics.
Figura 14:
Opciones
a marcar
y verificar
para la
importación del archivo csv.
En la Figura (14) notamos un cuadro celeste cuya opción debe ser marcada, por otro lado la
siguiente sección que debemos considerar es el cuadro verde (Opciones avanzadas) para lo
cual debemos seleccionar que el delimitador sea coma, finalmente damos clic en Crear tabla.
A continuación te mostrará el siguiente mensaje:
Figura 15: Mensaje luego de crear la tabla.
7
La Figura (15) es el mensaje posterior a la creación de la tabla, no te alarmes esto es común al
momento de cargar datos que no están limpios o que la detección automática de esquema no
logra identificar, con el check que hemos realizado en la opción Filas irregulares permitirá
aceptar aquellos registros que son nulos.
Figura 16: Sección explorador para validar la creación
de la tabla.
Para validar que los datos se hayan cargado correctamente puedes realizar la siguiente consulta
en el entorno de BigQuery SQL:
SELECT*
FROM `proyecto.esquema.tabla`
LIMIT 10
¡Excelente!, ahora que ya sabes cómo importar los archivos de la empresa TELCO, te reto a que
puedas realizar la importación de los 4 archivos restantes:
● Telco_customer_churn_location.csv – nombre de tabla (churn_location)
● Telco_customer_churn_population.csv – nombre de tabla (churn_population)
● Telco_customer_churn_services.csv – nombre de tabla (churn_services)
● Telco_customer_churn_status.csv – nombre de tabla (churn_status)
2. CREACIÓN Y EXPLORACIÓN DE TABLAS
¡Gran trabajo!, ahora que has logrado importar las 5 fuentes de datos de la empresa TELCO,
vamos a aprender a cómo poder explorar los datos que pertenecen a cada uno de las tablas
para ello te sugerimos el comando LIMIT y continuar con la siguiente estructura:
SELECT*
FROM `proyecto.esquema.tabla`
LIMIT 6
8
Figura 17: 6 registros de la tabla churn_location.
De acuerdo con la Figura (17) podemos notar que la consulta realizada muestra todas las
variables (9) de la tabla churn_location debido a que se ha usado el (*), para el lenguaje SQL
explica el llamado a todas las variables de la tabla correspondiente.
Veámos lo siguiente, si deseamos observar solo Customer_ID, Country, sState, City y Lat_Long
se realizaría la siguiente consulta:
SELECT Customer_ID,Country,State,City,Lat_Long
FROM `proyecto.esquema.tabla`
LIMIT 6
Figura 18: 6 registros de la tabla churn_location.
Como podrás observar en la Figura (18) se muestra solamente las variables que deseamos
observar, por lo que podría variar de acuerdo a las necesidades que desea analizar.
¡Ahora es tu turno!, dado que has aprendido explorar una pequeña cantidad de registros y
mostrar sólo las variables que deseas, considera que deseamos mostrar 10 registros de la tabla
9
churn_location con las siguientes variables Customer_ID, Customer_Status,
Churn_Value,Churn_Category y Churn_Reason.
Adicionalmente, se recomienda hacer una exploración para las 3 tablas restantes e identificar
cuáles son las variables en común para posteriormente tratar de enlazar o crear un tablón que se
abastezca de las 4 fuentes.
Como podrás observar el explorar las tablas por separado es un poco complicado debido a que
estamos constantemente cambiando el nombre de la tabla a la cuál se desea realizar un
exploratorio a su vez si deseamos realizar cruce entre las variables de una tabla u otra se nos
hace complicado, es por ello que ahora vamos a aprender cómo podemos relacionar o unir cada
una de las tablas a partir de una llave o variable en común, para ello véase el siguiente link que
le permitirá entender la ventaja de las funciones INNER JOIN, LEFT JOIN , RIGHT JOIN y entre
otras.
¡Manos a la obra!, antes que nada el desarrollo de los análisis se realizarán solamente con 4
tablas las cuáles serán:
● Telco_customer_churn_location.csv
● Telco_customer_churn_demographics.csv
● Telco_customer_churn_services.csv
● Telco_customer_churn_status.csv
Dado que se tiene información estrictamente de los clientes de la empresa TELCO, y el archivo
Telco_customer_churn_population.csv solo muestra la población que existe en cada ciudad, si
desea indagar o combinar con esa tabla le reto a que pueda armar un tablón con las 5 fuentes
de datos.
¡Vamos a comenzar!, ahora que has logrado leer cómo trabaja internamente las funciones INNER
JOIN, LEFT JOIN, RIGHT JOIN y entre otras, se sugiere trabajar solamente con un tablero donde
reúna toda la información posible de distintas fuentes de información, para ello debe tener en
claro estos puntos:
➔ Conocer cada una de las variables de cada tabla que se tiene, para posteriormente
considerarlas en el tablón final.
➔ Entender e identificar qué variables tienen en común en cada una de las tablas.
➔ Conocer la estructura SQL para la creación de tablas.
10
El flujo que seguiremos ya se ha mencionado en los puntos anteriores, por lo que ahora se
mostrará a continuación la estructura SQL que se necesita para la creación del tablón final (7043
filas , 48 variables o columnas) en función de las 4 fuentes de datos de la empresa TELCO:
CREATE TABLE `proyecto.esquema.tabla` AS (
SELECT a.* EXCEPT(Zip_Code),
b.Gender,b.Age,b.Under_30,b.Senior_Citizen,
b.Married,b.Dependents,b.Number_of_Dependents,
c.Quarter,c.Referred_a_Friend,c.Number_of_Referrals,
c.Tenure_in_Months,c.Offer,c.Phone_Service,
c.Avg_Monthly_Long_Distance_Charges,c.Multiple_Lines,
c.Internet_Service,c.Internet_Type,c.Avg_Monthly_GB_Download,
c.Online_Security,c.Online_Backup,c.Device_Protection_Plan,
c.Premium_Tech_Support,c.Streaming_TV,c.Streaming_Movies,
c.Streaming_Music,c.Unlimited_Data,c.Contract,c.Paperless_Billing,
c.Payment_Method,c.Monthly_Charge,c.Total_Charges,c.Total_Refunds,
c.Total_Extra_Data_Charges,c.Total_Long_Distance_Charges,c.Total_Revenue,
d.Customer_Status,d.Churn_Label,d.Churn_Value,
d.Churn_Category,d.Churn_Reason
FROM `proyecto.esquema.churn_location` a
LEFT JOIN `proyecto.esquema.churn_demographics` b
ON a.Customer_ID=b.Customer_ID
LEFT JOIN `proyecto.esquema.churn_services` c
ON b.Customer_ID=c.Customer_ID
LEFT JOIN `proyecto.esquema.churn_status` d
ON c.Customer_ID=d.Customer_ID
)
El esquema que se ha compartido crea una tabla final donde hacemos uso de la función SELECT
para poder seleccionar las variables que necesitamos, la cual puede verificar su creación en la
sección donde se encuentran las tablas importadas, recuerde que puede colocar el nombre que
desea a la tabla creada.
Figura 19: Validación del tablón final creado a partir de las 4
tablas importadas de la empresa TELCO.
Considerar que el nombre asignado para el desarrollo fue dado
como “master_churn”.
11
Figura 20: Visualización de algunas variables del tablón final.
Parte de los beneficios que tiene SQL es que puedes armar lógicas de acuerdo a la forma de
como quieres visualizar tus variables en la tabla final, para ello se debe seguir un orden de
selección de variables dando a entender a SQL de donde proviene cierta variable, en este
instante es donde ingresa el término de alias, por ejemplo:
“FROM `proyecto.esquema.churn_location` a” en esta pequeña sección vemos que
al finalizar la dirección o ruta de la tabla colocamos la letra “a” que se entiende como el alias o
sobrenombre de la tabla, y esta característica lo podrá observar en las tablas restantes.
Usted se preguntará porqué se realiza esto, la respuesta es la siguiente, a partir de este alias
permitirá al lenguaje de SQL identificar de qué tabla proviene la variable que queremos
seleccionar así como pueden existir tablas con algunas variables iguales que puede generar
confusión a la hora de procesar la consulta.
¡Gran avance!, ahora imagínate que deseas seleccionar todas las variables de una tabla excepto
una, la primera opción sería seleccionar uno a uno las variables excepto la que no deseas añadir
en tu tablón, pero el querer realizar esta acción es muy ineficiente por lo cual SQL te ayuda con
la siguiente función EXCEPT(), que se ha utilizado para la creación del tablón final. (Véase el
siguiente link)
3. DESCRIPCIÓN DE TABLAS
¡Excelente, buen avance!, ahora se viene lo más interesante que es la parte descriptiva de los
datos y el desarrollo de las métricas o indicadores nuevos que permitirán conocer los datos que
posteriormente brindarán información para la toma de decisiones.
Para empezar podemos realizar un conteo del total de registros que tiene nuestro tablón final,
para ello considere la función COUNT:
Ejemplo 1:
SELECT COUNT(*)
FROM `proyecto.esquema.tabla`
LIMIT 10
12
A partir de esta consulta podemos validar que el total de registros es de 7043, este valor es
acorde al total de clientes únicos que se tiene en las fuentes de datos de TELCO.
Conocer las características de nuestra cartera de clientes es muy importante para determinar
ciertos perfiles y a partir de ello realizar ciertas reglas de negocio. Primero validemos que todos
los registros pertenecen a Estados Unidos y al estado de California, para ello se utilizará la
función DISTINCT:
Ejemplo 2:
SELECT DISTINCT Country
FROM `proyecto.esquema.tabla`
Se observa que todos los registros de la cartera de clientes pertenecen a Estados Unidos.
¡Ahora es tu turno!, dado el ejemplo 2, ¿podrías validar si los registros pertenecen solamente al
estado de California?.
¡Buen trabajo!, el poder conocer la cantidad de ciudades que alcanzan recibir los servicios de la
empresa TELCO es un gran foco de negocio ya que de esta manera permite identificar dónde se
puede generar nuevas alianzas y vínculos con nuevos clientes. Para este exploratorio se tomará
en cuenta el uso de la función COUNT (DISTINCT variable), para ello veamos el siguiente
ejemplo:
Ejemplo 3:
SELECT COUNT(DISTINCT City)
FROM `proyecto.esquema.tabla`
Podemos identificar que los servicios que realiza TELCO abarca 1,106 ciudades de Estados
Unidos.
¡Ahora es tu turno!, dado el ejemplo 3, ¿podrías mostrar algunas ciudades a las cuales la
empresa TELCO presta servicios?
¡Genial!, ahora aprenderemos a cómo identificar valores máximos o mínimos, como también
sumas y promedios de variables cuantitativas a partir de las funciones de agregación MIN, MAX
y AVG (para más detalle véase el siguiente link), para ello una de las variables que es de interés
explorar es la distribución de las edades de nuestra cartera de clientes, por lo que veamos el
siguiente ejemplo:
Ejemplo 4:
SELECT MIN(Age) AS edad_minima,MAX(Age) AS edad_maxima,AVG(Age) AS edad_promedio
FROM `proyecto.esquema.tabla`
13
Este resultado del Ejemplo 4 en comparación con los ejemplos anteriores vemos que las
variables que se han creado a partir de las funciones de agregación tienen nombre, esto se debe
al alias que se puede colocar con el comando AS.
Como se puede apreciar la edad mínima de la cartera de clientes es de 19 años, mientras que la
edad máxima es de 119 años, mientras que la edad promedio de nuestra cartera es de 47 años.
Si nos ponemos a analizar la edad máxima vemos que no es algo coherente tener a clientes con
edad de 119 años, por lo que podemos tratar a estos casos como anomalías en los datos, que
comúnmente ocurren en las bases de datos. Posteriormente esta anomalía que existe en la
variable “Age” será tratada más adelante.
¡Ahora es tu turno!, dado el ejemplo 4, ¿podrías mostrar el pago mensual mínimo, máximo y
promedio de la cartera de clientes de la empresa TELCO? (Se sugiere usar la variable Monthly
Charge).
¡Bien, vas por buen camino!, hemos aprendido a usar las funciones de agregación, ahora
aprenderemos a poder agrupar estos indicadores, para ello la función GROUP BY nos ayudará.
Si deseamos saber la cantidad de clientes por género que se tiene en la cartera de la empresa
TELCO la estructura la podemos observar en el siguiente ejemplo:
Ejemplo 6:
SELECT Gender, COUNT(Customer_ID) AS clientes_totales
FROM `proyecto.esquema.tabla`
GROUP BY 1
Podemos agrupar las cantidades según la ubicación de la variable o según el nombre.
SELECT Gender, COUNT(Customer_ID) AS clientes_totales
FROM `proyecto.esquema.tabla`
GROUP BY Gender
Ambos resultados nos mostrará lo mismo, usted escoge la manera más fácil para usted de como
poder agrupar los indicadores. El resultado de la consulta es el siguiente:
14
De acuerdo con el Ejemplo 6 podemos notar que la proporción de Male y Female son
aproximadamente equitativas, pero necesitamos realizar un preprocesamiento para homologar
las categorias..
¡Ahora es tu turno!, dado el ejemplo 6 ¿podrías mostrar el número de clientes que son
casados y no casados según el tipo de género? (Se sugiere usar las variables Gender y Married) .
¡Buenísimo!, gran parte de las funciones de agrupación son muy importantes porque permiten
realizar o armar segmentos de características de la población es por ello que está relacionado
con el orden de los registros que se puedan mostrar en la visualización de la tabla, para este
caso el uso del ORDER BY es fundamental.
Si requerimos saber cuales son las 5 ciudades top con mayor número de clientes de la cartera
de la empresa TELCO, veamos la siguiente estructura:
Ejemplo 7:
SELECT City, COUNT(Customer_ID) AS clientes_totales
FROM `proyecto.esquema.tabla`
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5
De acuerdo con el Ejemplo 7 podemos notar que las ciudades con mayor número de clientes
son: Los Angeles, San Diego, San Jose, Sacramento y San Francisco. También en la estructura de
la consulta se añadido la función ORDER BY que permite ordenar los datos a partir de una
variable, en este caso se consideró la variable de posición 2 (clientes_totales), otro dato
15
importante es como deseamos ver el orden ya sea descendente o ascendente para ello se usa
DESC o ASC respectivamente.
4. LIMPIEZA DE TABLAS
En la sección de exploración hemos encontrado algunas anomalías en los datos, por ejemplo en
la variable “Age” la edad máxima era de 119, y como reto se tenía que hacer un exploratorio de la
variable “Monthly Charge” por lo que se encontraron algunos registros (110) que tenían valores
negativos y ceros.
Con respecto a la variable “Age” previamente Sebástian nos había comentado que la edad
máxima admitida de acuerdo al historial de servicios es de 80 años, y que los pagos mensuales
no podría ser negativos ni ceros ya que las bases registraban a los clientes que aceptaron los
servicios prestados por la empresa TELCO. Dado todo estas anomalías o errores de las bases de
datos, se procede a realizar la limpieza respectiva, es decir, se realizará un filtro para evitar
considerar aquellos registros de clientes cuyas edades superan los 80 años y no considerar
registros negativos ni ceros en la variable Monthly Charge.
Adicionalmente, necesitamos homologar las categorías de la variable gender, es decir convertir
la categoría “M” a Male y la categoría “F” a Female.
Para ello requerimos de algunas funciones de condición como el WHERE y para la actualización
de la tabla final se debe considerar la función CREATE OR REPLACE TABLE, a continuación se
mostrará la estructura de la consulta a realizar:
CREATE OR REPLACE TABLE `proyecto.esquema.tabla` AS
(
SELECT * EXCEPT(gender),
CASE
WHEN gender = 'M' THEN 'Male'
WHEN gender = 'F' THEN 'Female'
ELSE gender END as gender
FROM `proyecto.esquema.tabla`
WHERE Age<=80
AND Monthly_Charge > 0
)
Al realizar esta consulta, obtendremos un total de 6,815 registros con 48 variables a la cual se
realizarán las respectivas visualizaciones en POWER BI para poder comprender las
características de la cartera de clientes de TELCO.
5. CREACIÓN DE DASHBOARD
La creación de dashboards es un arte que se va puliendo a medida que practiquemos e
indaguemos en los trabajos realizados por otros compañeros, ya que de cada uno de ellos se
16
puede aprender u obtener algo extra que nosotros quizás dejemos de lado. También es bueno
recordar que el realizar un dashboard tiene relación con la historia que deseas contar, así que
¡manos a la obra!. (Véase el siguiente link para algunos tips a considerar)
Desarrollo 1
Para comenzar, recordemos que en la parte exploratoria se ha estado viendo frecuencias más no
proporciones por lo que la herramienta de POWER BI nos ayuda a realizar ese cálculo y a su vez
la parte visual, para este ejemplo se desarrollará un gráfico de pie o gráfico circular de la variable
Gender:
Figura 21: Distribución del número de clientes según Género.
Se muestra que la distribución de la cartera de clientes de la empresa TELCO es equitativa tanto
para Male como Female. Se podría considerar que no es muy importante esta variable para las
reglas próximas a establecer.
¡Ahora es tu turno!, armar una gráfica circular que muestre la proporción de clientes según el
número de clientes casados y el número de clientes dependientes.
Desarrollo 2
17
Ahora trataremos de realizar algo más complejo, como por ejemplo segmentar las edades según
rango de edades que serán de 19 a 40 años, 41 a 60 y de 61 a más. Sugerencia: usar la función
CASE WHEN (véase el siguiente link) y la función WITH (véase el siguiente link).
La estructura de esta consulta es la siguiente:
WITH tabla AS (
SELECT *,
CASE
WHEN Age < 21 THEN '1. 0 a 20 años'
WHEN Age BETWEEN 21 AND 60 THEN '2. 21 a 60 años'
ELSE '3. Más de 60 años'
END AS rango_edad
FROM `proyecto.esquema.tabla`
SELECT rango_edad, COUNT(DISTINCT Customer_ID) AS total_clientes
FROM tabla
GROUP BY 1
El uso de la función WITH permite crear tablas temporales, para luego ser usadas y evitar
modificar la tabla final que tenemos, pero si deseas modificar la tabla final puede hacer uso del
CREATE OR REPLACE TABLE.
Esta consulta creada permite generar el siguiente reporte:
¡Ahora es tu turno!, armar una gráfica de barras que muestre las 5 ciudades top con mayor
número de clientes.
PLUS: Si deseas armar una presentación completa donde puedas unir todas estas gráficas en la
cual el dashboard que armes consulte sólo una tabla para ello debes asegurar que hayas
18
actualizado tu tabla final con la nueva variable de rango_edad. Para este ejercicio adicional
adicionamos algunos paneles de control y la variable City, revise la siguiente consulta:
SELECT City,
Gender,
Married,
Dependents,
rango_edad,
COUNT(DISTINCT Customer_ID) AS total_clientes
FROM `proyecto.esquema.tabla`
GROUP BY 1,2,3,4,5
Desarrollo 3
¡Excelente trabajo!, ahora que conoces algunos tipos de gráficas vamos realizar nuevas
visualizaciones, para este caso usaremos la visualización de árbol.
Se requiere identificar si las personas casadas tienen alguna relación con el número de referidos,
para ello se creará una nueva variable a partir de los rangos siguientes: 0 referidos, 1 a 4
referidos, 5 a 8 referidos y más de 8 referidos. Sugerencia: uso de la función CASE WHEN para
la creación de la variable y para generar el reporte usar COUNT y GROUP BY.
19
¡Ahora es tu turno!, armar una gráfica circular que muestre la proporción de clientes según el
rango de referidos y armar una gráfica de barras mostrando las ciudades top con mayor número
de referidos.
PLUS: Si deseas armar una presentación completa donde puedas unir todas estas gráficas en la
cual el dashboard que armes consulte sólo una tabla para ello debes asegurar que hayas
actualizado tu tabla final con la nueva variable de rango_referidos. Para este ejercicio adicional
adicionamos algunos paneles de control y la variable City, revise la siguiente consulta:
SELECT City,
Married,
rango_referidos,
COUNT(DISTINCT Customer_ID) AS total_clientes ,
SUM(Number_of_Referrals) referidos_totales
FROM `proyecto.esquema.tabla`
GROUP BY 1,2,3
¡Ahora es tu turno!, armar una gráfica de barras combinada con gráfica de línea que muestre las
ciudades top con mayor número de Ingresos totales y que muestre el ingreso promedio de estas
20
ciudades. La variable a considerar es Total Revenue, a continuación se muestra el posible
resultado que debería realizar:
Reto
¡Asume el reto!, armar el siguiente dashboard que se mostrará a continuación, considere las
variables Contract, Multiple_Lines y rango_persistencia_meses. Para la variable
rango_persistencia_meses las categorías son 1 a 12 meses, 13 a 24 meses, 25 a 36 meses, 37 a
48 meses, 49 a 60 meses, 61 a 72 meses.
21
6. ESTABLECER CHURN POR SEGMENTOS
En la reunión que se ha tenido con el equipo de la empresa TELCO Sebástian comentó que tenía
ciertas hipótesis acerca de segmentos para los clientes que fugan, estas hipótesis son las
siguientes:
I. Los que tienen una probabilidad de fuga muy alta son aquellos clientes cuyo tipo de
contrato es “Month-to-Month” y son mayores a 64 años, este grupo es denominado G1.
II. Los que tienen una probabilidad de fuga alta son aquellos clientes cuyo tipo de contrato
es “Month-to-Month” y son menores a 64 años, este grupo es denominado G2.
III. Los que tienen una probabilidad de fuga baja son aquellos clientes cuyo tipo de contrato
es diferente de “Month-to-Month” y son mayores a 64 años, este grupo es denominado
G3.
IV. Los que tienen una probabilidad de fuga muy baja son aquellos clientes cuyo tipo de
contrato es diferente de “Month-to-Month” y son menores a 40 años, este grupo es
denominado G4.
Como Data Analists tenemos que usar de referencia estas hipótesis para poder segmentar a los
clientes, para ello recrearemos estas hipótesis en una lenguaje SQL y crear una variable que sea
denominado grupo_riesgo. La creación de esta variable se apoyará de la función CASE WHEN,
vea a continuación la estructura de la consulta.
CREATE OR REPLACE TABLE `proyecto.esquema.tabla` AS (
22
SELECT a.Customer_ID,
a.Contract,
a.Churn_Value,a.Total_Revenue,
CASE WHEN a.Contract = 'Month-to-Month' AND a.Age > 64 THEN 'G1' ELSE
CASE WHEN a.Contract = 'Month-to-Month'
AND a.Age < 64
AND a.Number_of_Referrals <= 1 THEN 'G2' ELSE
CASE WHEN a.Contract != 'Month-to-Month'
AND a.Age > 64
AND a.Number_of_Referrals <= 1 THEN 'G3' ELSE
CASE WHEN a.Contract != 'Month-to-Month'
AND a.Tenure_in_Months < 40 THEN 'G4' ELSE
“No pertenece a ningún grupo” END END END END AS grupo_riesgo
FROM `proyecto.esquema.tabla` a
)
Luego nos interesa medir el riesgo de churn de cada grupo, podemos tomar como referencia el
riesgo de toda la población que es 0.26. Aquellos grupos que tengan mayor riesgo puedes
considerarlo como riesgo alto. Un dato adicional, puedes obtener el riesgo de churn por tipo de
contrato, es decir, la población de clientes cuyo contrato es de tipo “Month-to-month” es de 0.45.
Por lo tanto, puedes tomarlo como referencia para los grupos cuyo contrato es
“Month-to-month”.
¡Asume el reto!, Anímate a encontrar otros segmentos, mide su riesgo de churn y compáralos
contra los riesgo de churn referenciales.
A partir de esta consulta se creará añadirá una nueva variable en la tabla final, que caracterizará
a cada cliente según el grupo de riesgo al que pertenece. Dado la distribución de clientes que
fugan y no fugan, 27% y 73% respectivamente, se realizará el foco en aquellos clientes que no
han llegado a fugarse en el último mes de la recopilación de los datos. Para poder calcular el
valor que tiene un cliente, es necesario pensar en la antigüedad promedio (considerar de
referencia la variable Tenure_in_Months) qué puede tener un cliente según el tipo de contrato.
Para ello se requiere realizar la siguiente consulta:
23
SELECT Contract,AVG(Tenure_in_Months) AS promedio_antiguedad
FROM `proyecto.esquema.tabla`
GROUP BY 1
Esta consulta permitirá proyectar el ingreso total que nos podría generar el cliente si es que llega
a alcanzar la antigüedad promedio según el tipo de contrato, esta variable se entiende como la
cantidad de meses que el cliente pueda permanecer solicitando servicios de TELCO. Podemos
observar que la antigüedad promedio para un cliente de tipo contrato “Month-to-Month” tiende a
permanecer 17 meses (1 año y 5 meses), mientras que los clientes de tipo contrato “One Year”
llegan a permanecer 41 meses (3 años y 5 meses) y finalmente el tipo contrato “Two Year” llegan
a permanecer 53 meses ( 4 años y 5 meses).
7. VALOR DE LOS CLIENTES
Para poder determinar el valor de un cliente, tenemos que calcular el ingreso total mensual (en
los registros importados se tiene a nivel trimestral) multiplicado por la antigüedad promedio que
podría durar el cliente según el tipo de contrato. Para simplificar este proceso se considerará el
uso de la función WITH, LEFT JOIN y para actualizar nuestra tabla final se utilizará CREATE OR
REPLACE TABLE, la estructura a utilizar será la siguiente:
CREATE OR REPLACE TABLE `proyecto.esquema.tabla` AS(
WITH base_antiguedad_prom AS (
SELECT Contract,AVG(Tenure_in_Months) AS promedio_antiguedad
FROM `proyecto.esquema.tabla`
GROUP BY 1
)
24
SELECT a.*,
b.promedio_antiguedad
FROM `proyecto.esquema.tabla` a
LEFT JOIN base_antiguedad_prom b
ON a.Contract = b.Contract
)
¡Buen trabajo!, ahora que has creado la variable de antigüedad promedio y lo has añadido a
nuestro tablón final, nos queda crear la variable que valorice al cliente, para ello se requiere de
funciones básicas de matemáticas como el producto que en lenguaje SQL se simboliza con “ * ”.
La estructura que se realizará es la siguiente:
CREATE OR REPLACE TABLE `proyecto.esquema.tabla` AS(
SELECT a.*,
a.promedio_antiguedad*Total_Revenue/3 AS ingreso_estimado
FROM `proyecto.esquema.tabla` a
)
¡Estupendo!, hemos logrado obtener el valor del cliente en función del ingreso estimado ahora
el siguiente paso es armar segmentos o cuartiles únicamente a los clientes que no han fugado
(categorización en el Churn Value es de 0) y por cada tipo de contrato. Para poder realizar esta
segmentación se requiere de la función NTILE ( ) OVER (PARTITION BY __ ORDER BY __ ).
(Para más detalle véase el siguiente link)
La estructura que debe tener esta consulta es la siguiente:
25
CREATE OR REPLACE TABLE `proyecto.esquema.tabla` AS (
SELECT *,NTILE(4) OVER( PARTITION BY Contract
ORDER BY ingreso_estimado ASC) AS cuartil_estimado
FROM `proyecto.esquema.tabla`
WHERE Churn_Value=0
)
Obteniendo como resultado los cuartiles correspondientes para cada conjunto de clientes según
el tipo de contrato, parte del resultado final se muestra a continuación:
Para comprender la definición de cuartil véase el siguiente link.
Debe comprender que a medida que se le asigne al cliente un mayor cuartil (calculado en
función de Total Revenue) este estaría en una posición de Total Revenue muy alta, es decir, los
ingresos a futuro que puede generar son muy elevados en comparación a otros cuartiles. Para
nuestro desarrollo consideramos importante a aquellos clientes que pertenecen al cuartil 3 y 4,
demos una vista de cómo se distribuyen según el tipo de contrato a partir de la siguiente
consulta:
SELECT Contract,
grupo_riesgo,
COUNT(Customer_ID) AS clientes_totales
FROM `proyecto.esquema.tabla`
WHERE cuartil_estimado IN (3,4)
GROUP BY 1,2
ORDER BY 1
De acuerdo con los grupos de riesgo determinados en la sección 6, debemos recordar que los
grupos G1 tienen una mayor probabilidad de fuga con la empresa, a partir de ello hemos tratado
de cruzar esta variable con el tipo de contrato (Contract) y los cuartiles estimados para poder
identificar en nuestra base de clientes que no fugaron (Churn Value es de 0) quienes tienen
mayor probabilidad de fuga y cuáles de estos generan altos ingresos. Considerando el escenario
mencionado anteriormente se ha denominado a estos clientes como “Clientes de alto valor”, por
26
lo que en el resultado de la consulta observamos que los clientes que tienen ese categorización
de G1, adicional que pertenecen a los cuartiles 3 y 4 son únicamente 73 clientes considerados de
“alto valor”.
27
