Reporte financiero interactivo para empresa E-commerce

Descripción del problema

En este proyecto se aborda un problema de una empresa de E-commerce de la India, la cual tiene un conjunto de datos de los pedidos realizados en el periodo 2018/04 — 2019/03 y los objetivos de ingresos (revenue) por mes. Con esta información se quiere:

Donde se pueda utilizar una herramienta fácil de usar por personas no técnicas para consultar dicha información y, si se quiere, poder revisar con detalle la información de interés.

El conjunto de datos

El conjunto de datos de la empresa consiste de tres archivos CSV, cada uno con una tabla. Estos archivos son: List of Orders.csv, Order Details.csv y Sales target.csv, los cuales contienen las siguientes características:


List of Orders.csv


Tabla con la información esencial de los pedidos. En principio debería contener solamente una fila por cada pedido realizado.

Order Details.csv.csv


Tabla con la información de los productos incluidos en cada pedido. Puede haber más de una fila por pedido, puesto que cada pedido puede contener productos de diferente categoría y sub-categoría.

Sales target.csv


Tabla con la información de los objetivos financieros de la empresa por mes, por categoría.

Puntos clave del problema

Propuesta de solución

Se propone crear un archivo / reporte de Excel con 3 hojas como se puede ver en este ejemplo de propuesta:

  1. Ingresos, Margen bruto y comparación con Ingresos objetivo en el tiempo
  2. Ingresos y Margen bruto por Categoría y Sub-categoría de productos
  3. Ingresos, Margen bruto e Ingresos promedio por Estado

Se puede decir que esta propuesta puede ser utilizada por gente no técnica de manera fácil ya que es desarrollada en Excel y cuenta con una interfaz bastante estándar (gráficas y tablas dinámicas). También, aborda los puntos clave del problema:

Ejemplo de propuesta

Reporte 3
Reporte 1 Reporte 2

Implementación de solución en Excel

Extraer, Transformar y Cargar datos con Power Query y Power Pivot

Primero, se cargaron los tres archivos List of Orders.csv, Order Details.csv y Sales target.csv en Power Query como archivos CSV (posteriormente se utilizó una conexión a este repositorio de GitHub). Después, se aplicaron las siguientes pipelines a cada una de las tablas.


Pipeline Order Details

  1. Source: extraer datos de este repositorio de GitHub
  2. Promoted Headers: usar primera fila del archivo CSV como nombres de columnas
  3. Changed Type: auto-detectar tipos de dato de las columnas

    4. --- Limpieza de datos ---

  4. Quitar filas vacías
  5. Renombrar columna Amount a Revenue

Pipeline List of Orders

  1. Source: extraer datos de este repositorio de GitHub
  2. Promoted Headers: usar primera fila del archivo CSV como nombres de columnas
  3. Changed Type: auto-detectar tipos de dato de las columnas

    4. --- Limpieza de datos ---

  4. Quitar filas vacías
  5. Quitar duplicados de Order ID

    6. --- Transformación de datos ---

  6. Agregar columna Month a partir de Order Date
  7. Agregar columna Month number a partir de Order Date
  8. Cambiar tipo de dato de Month number a número
  9. Agregar columna Year a partir de Order Date

Pipeline Sales target

  1. Source: extraer datos de este repositorio de GitHub
  2. Promoted Headers: usar primera fila del archivo CSV como nombres de columnas
  3. Changed Type: auto-detectar tipos de dato de las columnas

    4. --- Transformación de datos ---

  4. Agregar columna Year a partir de Month of Order Date
  5. Agregar columna Month a partir de Month of Order Date
  6. Agregar columna Date id a partir de las columnas Year, Month y Category (ejemplo de ID: 201804Furniture)
    • Se puede construir un ID único para cada fila de esta manera puesto que solo hay un ingreso objetivo para cada categoría en cada año y cada mes de ese año
  7. Cambiar tipo de dato de Date id a texto
ETL en Power Query

Modelo de datos en Power Pivot

Para poder juntar (JOIN) la información del conjunto de datos, las tablas se identificaron de la siguiente manera:

Tablas con claves principales (primary keys)

Tabla con claves externas (foreign keys)

Primero se hace la conexión de Order Details con List of Orders y se crean las columnas Year y Monthname usando las expresiones DAX:


=RELATED('List of Orders'[Month])

=RELATED('List of Orders'[Year])


Para luego crear la columna Date id con al siguiente expresión DAX:


=CONCATENATE(FORMAT([Year]*100+[Monthnumber],"General Number"),Category])


Luego, ya es posible crear la conexión de Order Details con Target Sales. Así, se obtiene el siguiente modelo de datos:

Modelo de datos en Power Pivot 2

Finalmente, se usan expresiones DAX de la forma =RELATED('Tabla'[Columna]) para juntar (JOIN) la información. Así, se obtiene la siguiente tabla:

Modelo de datos en Power Pivot 1

Creación de métricas con Power Pivot

Ya para finalizar el procesamiento de los datos, se crearon las siguientes métricas y métricas auxiliares para mostrar la información relevante y requerida en los reportes:

Resultado final

Reporte 1
Reporte 2-1 Reporte 3-1
Reporte 2-2 Reporte 3-2

Origen de los datos

Origen de los datos: https://www.kaggle.com/datasets/benroshan/ecommerce-data/data?select=Sales+target.csv

Licencia original de los datos: CC0: Public Domain