Analysis of customer behaviour and preferences.

Comas, Lima

Desarrollo del proyecto


Extracción de datos

Para este procedimiento, tenemos 2 formas. La primera forma es mediante el uso del lenguaje Python, haciendo uso de las librerías [pandas, Seaborn] en el entorno de Visual Studio Code, de la siguiente manera:

1. Uso de Python bajo el entorno de Jupyter Usamos las credenciales del token para descarga de data. {"username":"######","key":"####################"} En un entorno de Google Colab para ejecución de código Python:

Con los codelines de [2] y [3] garantizamos que no haya un archivo igual ‘.kaggle’ en la carpeta raíz.

Con el codeline[14], podemos validar todos los dataset creados por el autor ‘zeesolver’. Procedemos a descargar el dataset de primera opción consumer-behavior-and-shopping-habits-dataset.csv.

Podemos apreciar su tamaño y tipo de licenicia. Con los siguientes codelines procedemos a validar los primeros registros de la tabla “.head()”, así como su estructura, tipo de datos, posibles valores nulos, limpieza y transformación inmediata de ser necesario, entre otras funcionalidades.



2. Uso de Excel y power query

La segunda forma es más sencilla, pero presenta mayor limitación en cuanto a funcionalidades, es decir, no nos va permitir hacer una limpieza y transformación ágil en caso de haber valores nulos y errores ortográficos. Sin embargo, presenta la ventaja de ser más intuitivo y amigable.

Se procede a extraer el archivo en formato csv, la herramienta reconoce el signo delimitador entre campos y los divide por columnas. En este caso la transformación y limpieza se puede realizar de forma manual dadas las herramientas dispuestas en la barra superior.

Posterior a estas correcciones, se carga nuevamente en el Excel para su modelamiento o normalización. Esto con la finalidad de que la data sea coherente, integra, se evite redundancias o datos duplicados.

Power Query es una herramienta que nos va facilitar conexión con diversas fuentes de datos, tales como la nube, sea sharepoint, blob storage, Data lakes. Por otro lado, orígenes de base de datos relacionales, tales como Oracle, SQL Server, MySQL. Archivos xml, json, csv, etc.

2. Etapa de modelamiento de datos

Para este proyecto, relacionado con las preferencias de producto y hábitos de consumidores, es necesario modelar el dataset, ya que le añade mayor capacidad de análisis, hace la tabla fact o tabla de hechos más ligera y por tanto más ágil y eficiente en las respuestas ante consultas.
Cumpliendo con los criterios de normalización:

1.Primera forma normal (1FN): Atributos atómicos (Sin campos repetidos)
2.Segunda forma normal (2FN): Cumple 1FN y cada registro de la tabla debe depender de un campo con clave única [Primary key].
3.Tercera forma normal (3FN): Cumple 1FN y 2FN y los campos que NO son clave, NO deben tener dependencias.
4. Cuarta forma normal (4FN): Cumple 1FN, 2FN, 3FN y los campos multivaluados se identifican por una clave única.
Se tiene como resultado un esquema de tipo estrella, se establecen las relaciones con apoyo de la herramienta power pivot.



Igualmente, podemos desarrollar este bosquejo modelamiento dentro de la plataforma dbdiagram.io, el cual nos brindarà los recursos necesarios para la construcciòn de las dimensiones, establecer los tipos de dato y las relaciones.

3. Etapa de SQL Exploratory

Nos apoyamos de la interacciòn entre el motor de BD con el IDE MSSMS, para conectarnos de forma remota o importar las tablas creadas desde excel.

Se indica al asistente, el origen desde donde se extrae la data, considerando la versión de Excel más reciente posible e indicando que la primera columna es con encabezados.

Acto siguiente, se realiza el mapeo de tablas origen a tablas de destino, considerando el correcto tipo de dato por campo y tamaño de longitud de cadena.



Posterior a ello, ya se procede a instanciar la base de datos, para hacer las consultas relacionadas a los hábitos de compra y preferencias del consumidor. A continuación, se muestra una lista de consultas SQL, cuyo código está documentado en mi cuenta GitHub : https://github.com/Datec97/Proyectos_DA

Lista de consultas y respuestas en SQL Server

1. ¿Top 5 de los establecimientos en donde se realizó una mayor actividad de consumo en el año 2023?

  • En este caso, consideramos 2 campos clave (región y unidades vendidas),
  • generamos una función de agregación sum() para determinar el total de unidades vendidas por región,
  • Lo agrupamos por región y ordenamos por el total de unidades vendidas de forma descendente, posterior a ello usamos la sentencia Where y YEAR(), para filtrar el año solicitado.
  • Para finalizar, con la sentencia TOP(5) solo mostramos la cantidad de elementos región solicitada.

    • 2. ¿Ranking de productos más vendidos por género, el 2024?

  • Para este query, necesitamos hacer una suma de todas unidades vendidas en el 2024, agrupándolo por genero y producto.
  • En primera instancia, se hace un left join de las tablas participantes
  • Posteriormente, se necesitó implementar una sentencia condicional case-when con alias “genero”, que nos permite agrupar por varón y mujer.
  • Se procede a agrupar por producto y case-when (“genero”), ordenándolo por el total de productos vendidos de forma descendente

    • 3. ¿Ranking de preferencia de compra por rango etario?

  • Para este caso, primero unimos con join las tablas pertinentes
  • Posterior a ello hacemos uso de la sentencia condicional case-when con alias (“rango etario”) para agrupar la sumatoria de las unidades de producto más comprado.
  • Finalmente, agrupamos por articulo y (“rango etario”), ordenándolo de forma descendente por total de unidades vendidas



    • 4. ¿Preferencias de compra por producto y genero?

  • Se necesita determinar el valor máximo de la sumatoria de unidades de los productos, agrupándolo por genero.
  • En este caso se unen las tablas pertinentes
  • Se filtra por genero respectivo mediante un where y se hace uso de la sentencia IN() para incluir solo el valor máximo de unidades vendidas.
  • Finalmente se agrupa por género, articulo y se ordena de forma descendente por el total de unidades vendidas.

    • 5. ¿Calificación de satisfacción del cliente por producto recibido en el 2023?

  • Se procede al unir solo la tabla de hechos junto a la tabla de producto
  • Se hace una sumatoria del puntaje total por producto brindado por los clientes
  • Se filtra con la sentencia where, el año indicado
  • Se agrupa finalmente por el articulo y se ordena por la sumatoria total de las calificaciones de forma descendente.

    • 6. ¿Ranking de métodos de pago más utilizado por rango etario?

  • Se procede a hacer uso de la función de agregación count() para contabilizar los métodos de pagos por rango etario
  • Nuevamente, usamos la sentencia condicional when-case para segmentar las edades de los clientes en 2 grupos [18-44][45-70] años.
  • Agrupamos por método de pago y (“rango etario”). Finalmente, ordenamos de forma descendente por la cantidad total de métodos de pago.



    • 7. ¿Ranking de tipos de envío más solicitado por género?

  • Se anexan las tablas de cliente y fact_table
  • Se implementa la sentencia condicional case-when con alias “genero”, que nos permite agrupar por varón y mujer
  • Se usa la función de agregación count() para contabilizar la totalidad de los diferentes tipos de envío

    • 8. ¿Top de 15 clientes más fieles?

  • Para este query, es vital previamente, determinar hasta que año se encuentran suscritos los usuarios, a partir de allí en adelante se evalúa la fidelidad.
  • Para esto se anexan la tabla de hechos y la tabla de clientes.
  • Se determina la suscripción activa por cliente a través del estado de suscripción ‘yes’y se evalúa desde la fecha última de suscripción hasta hoy con la expresión GETDATE().
  • Finalmente se agrupa por nombre de cliente y estado de suscripción

    • 9. ¿Ingresos por temporada en 2023?

  • Se determina la sumatoria total de ingresos por el campo ‘season’
  • Se agrupa por temporada y se ordena de forma ascendente por Ingresos



    • 10. ¿Ranking de ingresos por producto en el último trimestre del 2023?

  • Se anexan las 3 tablas (producto, cliente y hechos)
  • Se utiliza la condicional case-when para establecer un rango del periodo, del ‘01/10/2023’ al ‘31/12/2023’, bajo el alias ‘last_quarter_2023’
  • Se ejecuta la sumatoria por las compras realizadas
  • Se agrupa por articulo y (“last_quarter_2023”)
  • Se ordena por monto de forma descendente

    • 4. Etapa de procesamiento - ETL

    4.1 Recolección de datos

    Para llevar a cabo este proceso de extracción de datos de fuentes o repositorios, nos vamos a apoyar de la herramienta visual de Microsoft en la categoria de servicios On-premise; este es SSIS o también llamado SQL Server Integration Services.

    '-> Creamos un nuevo flujo de datos en el flujo de control, para mover la data del punto A al punto B
    '-> Elegimos “Asistente de origen” para seleccionar la fuente y gestionar las conexiones
    Ingresamos nuestro server_name y elegimos la base de datos con la cual queremos trabajar. (probamos conexión)


    Primero configuramos la consulta o la tabla al conector para traer la data y conectarla con el destino y acto siguiente validamos con “iniciar” si todo Ok!

    Pasamos a configurar ahora el destino, es decir, la data del origen se insertará en una tabla del datawarehouse en la primera instancia del landing (recolección en landing)

    Una vez se hayan conectado las 2 cajas (origen de OLE DB y Destino de OLE BD), hacemos un mapeo de asignación entre campos. La cual deben estar configurados con las mismas restricciones, tipo de datos y longitud de cadena en caso de tipo de dato varchar().

    Nótese Hay una caja del medio (Columna derivada), se configuró para establecer el campo faltante en la tabla origen, de manera que pueda hacer match en todos los campos con la tabla destino.

    Al dar iniciar, podemos ver que se trasladan al destino del dwh los 50 registros de mi tabla clientes.

    Sin embargo, recordemos que estamos volcando data al dwh, de tablas dimensionales que aportan información contextual, por tanto no almacenará registros cada vez que se ejecute el procedimiento, no obstante si pueden actualizarse. Para esta finalidad, es importante contar con la herramienta Truncate de SQL Server.

    Se procede a configurar los parámetros señalados y se inserta la sentencia “truncate table lnd.tb_customer" para evitar almacenar datos cada vez que se ejecute el procedimiento.



    Similar procedimiento para las tablas tb_product y tb_calendar, cuyo origen en este caso es CSV.

    Se extrae también la carga de la tabla fact mediante el dataflow. En este caso, la extracción es mediante el uso de un script SQL, ya que al ser una tabla con una densidad alta de registros, puede ser ineficiente traer todos los registros de forma directa. Es por eso que se aplica un extractor condicional de tiempo, como se ve en el siguiente ejemplo:

    4.2 limpieza , transformación y carga ( en SQL Sevrer )

    Posterior al proceso de extracción, se realiza unas transformaciones más elaboradas de la data para su almacenamiento en datawarehouse y su carga definitiva en datamarts. Esto se llevó a cabo por cada dimensión y tabla de hechos, mediante la implementación de código SQL, el cual se encuentra en el repositorio de GitHub: https://github.com/Datec97/Proyectos_DA/querys_etl_habits_project Para la salida de las tablas dimensionales, se cambió el tipo de dato de la llave principal a nvarchar para agregarle caracteres, se agregó campo GETDATE() para determinar la fecha y hora de carga, se cambia el idioma de los campos y finalmente se traslada al datamart para su puesta en producción.

    Las zonas que se comprenden en el ETL, tendrán una misión u objetivo distinto. Corroboremos la zona staging, datamart y su contenido.




    5. Etapa de cubos

    La implementación de cubo mediante la herramienta SSAS (Sql Server Analysis Services), nos va proporcionar 2 ventajas respecto al tratamiento de la data:

  • Compresión de información
  • Gestión del conocimiento

    • Siguiendo el flujo de solución de datos, el cubo nos va permitir obtener insights y oportunidades en el proceso de negocio. Esto ya que vamos a poder añadirle a la data capacidad de análisis y comprensión, al hacer consultas por diferentes cortes o aristas.

    Nótese: Las herramientas BI en la actualidad, disponen en su propio motor, esta capacidad de comprimir la data, brinda una mayor agilidad y eficiencia en el flujo de trabajo con datos y es gracias a que ya no trabaja con tablas físicas que ocupan espacio en disco, sino se mantienen flotando en la memoria RAM con una magnitud de uso muy inferior.

    Para la implementación de cubos de información, nos vamos a apoyar de la herramienta VISUAL STUDIO como entorno para gestionar estos modelos de tablas tabular.

    Para esto, vamos a generar un nuevo proyecto tabular de Analysis Services, configurando parámetros como nombre de proyecto, ruta de almacén, actual instancia de .Net Framework, etc.

    Probamos conectividad con la BD, considerando el nivel de compatibilidad SQL Server 2019 / Azure Analysis Services (1500).

    Procedemos con la conexión al origen y proceso de autenticación con el servidor, indicando la base de datos



    Nos conectamos y extraemos únicamente las tablas en producción o puestas en el datamart del proceso ETL. Pues es justamente de aquì donde vamos a contar con la data limpia y preparada para su anàlisis.

    Procedemos a establecer las relaciones entre tablas, para que puedan comunicarse entre si. Siempre es importante considerar 3 puntos, los origenes de datos, relaciones y las tablas. Tal como lo muestra la siguiente información

    Una vez, tengamos los 3 elementos Ok!, vamos al apartado de ventana derecha, “explorador de soluciones” y click derecho compilar e implementar. Esto nos va a permitir hacer una depuraciòn de la data y actualizar las tablas, en caso de añadirse o eliminarse datos.

    Desplegamos el servicio de SSAS en SQL Server management studio 2019. Dentro del modelo tabular podemos visualizar las tablas desplegadas para ser utilizada por cualquier software en la misma red.

    Con esto ya tendremos el cubo implementado y desplegado en el servicio de SSAS. Acto siguiente, es posible conectar este servicio de modelo tabular con excel, con POWER BI y con otras herramientas de visualización.

    Vamos por la conexión en MS Excel : Nos dirigimos a pestaña “datos” de la barra de herramientas, luego obtener datos desde una base de datos, elegimos Analysis Services



    Nos conectamos al cubo de información centralizado para proceder con el análisis y entedimiento de datos.

    Procedemos ahora con la conexión del modelo tabular con power BI. : En el entorno, nos dirigimos a “obtener datos”, bases de datos y Bases de datos SQL Server Analysis Services.

    Agregamos las credenciales, nos conectamos en directo

    Una vez importados los datos, procedemos a hacer el análisis respectivo.

    Nótese: Tanto SQL Server Analysis Services como Power Pivot de Excel, brindan la capacidad de aligerar los tamaños o densidades de las cantidades de datos que se manejan una vez se construyen los cubos.Sin embargo, en la siguientes imágenes podemos apreciar la diferencia abismal del factor de compresión que ofrece cada herramienta

    06. Etapa de la visualización

    Para efectos del desarrollo del dashboard y análisis de estos datos, se hará uso de la herramienta Power BI, pues las tecnologías de procesamiento y carga por el que ha pasado el flujo de datos del dataset han sido del propietario Microsoft. Esto nos garantiza una mayor compatibilidad de conexión y por tanto éxito en el desarrollo de la solución.