Semana 2: PivotTables, Métricas y Visualizaciones

Semana 2: PivotTables, Métricas y Visualizaciones#

Dataset: Continuación clase anterior: sp2_almacen_ventas2.xlsx (Abrir desde aquí)

Objetivos de aprendizaje#

  • Construir y personalizar PivotTables para agrupar y resumir datos.

  • Filtrar, ordenar y aplicar diferentes funciones de agregacion (SUM, COUNT, AVERAGE, MAX, MIN).

  • Interpretar resultados y convertirlos en hallazgos para negocio.

  • Crear gráficos apropiados y usar formato condicional para resaltar valores clave.

  • Diseñar una pestaña-resumen tipo mini dashboard.

Agenda#

  1. PivotTables – fundamentos y primera tabla dinámica

  2. Filtrado y orden dentro de la PivotTable

  3. Cálculos y múltiples métricas

  4. Refinar el layout e interpretar resultados

  5. Gráficos correctos y narrativa

  6. Formato condicional para resaltar

  7. Mini dashboard de resumen

4. Analizando datos con PivotTables#

Por qué importa (teoría): una tabla dinámica estima estadísticos (SUM, AVG, COUNT, %) por dimensiones (Date/Region/Product/Salesperson) para condensar transacciones en KPIs. Esto reduce dimensionalidad, facilita comparar grupos y detectar patrones/outliers—paso clave del análisis descriptivo.

Construyendo tu primera PivotTable#

Pregunta guía: ¿Qué Regiones y Productos explican más Total_Sales?

Paso a paso en Google Sheets:

  1. Enriquecer datos: duplica la hoja Raw_sales, nombrala Sales_rich y utilizando XLOOKUP añade las columnas que consideres necesarias desde Products y Customers

  2. Ve a la hoja Sales_rich. Selecciona todo el rango de los datos.

  3. Insertar → Tabla dinámica → Nueva hoja.

  4. Nombra la hoja como Analysis.

  5. En el panel de la tabla dinámica:

    • Filas → Region.

    • Columnas → Product (opcional si deseas matriz Region×Product).

    • Valores → Total_Sales como SUMA.

  6. Activa Mostrar totales.

  7. QA rápido: el Total general debe coincidir con la suma de la columna Total_Sales.

4.2 Filtrado y orden dentro de la PivotTable#

Objetivo: Enfocarnos en lo relevante (tiempo/segmentos) y ordenar por contribución.

Paso a paso en Google Sheets:

  1. Filtro por fecha: agrega Date a Filtros → Entre → selecciona un rango (p.ej., 2024-01-01 a 2024-03-31).

  2. Filtro por vendedor: agrega Salesperson a Filtros si deseas excluir cuentas de prueba.

  3. Ordenar: en Valores, usa el menú de tres puntos → Ordenar por SUM de Total_Sales (desc) para ver top regiones/productos.

4.3 Resumen de información con múltiples métricas#

Preguntas guía:

  • ¿Cuál es el ticket promedio (ingreso por unidad) por Region/Product?

  • ¿Qué % del total aporta cada Región?

Paso a paso en Google Sheets:

  1. En la misma tabla dinámica (o crea otra):

    • Valores → Total_Sales como SUMA (Ingresos).

    • Valores → Units_Sold como SUMA (Volumen).

  2. Ticket promedio (dos alternativas):

    • Fácil (tabla dinámica): agrega Total_Sales como SUMA y Units_Sold como SUMA y crea un Campo calculado:

      • TicketProm = Total_Sales / Units_Sold

  3. % del total (participación): en la Pivot, si tu editor lo permite, usa Mostrar como → % del total sobre Total_Sales

Interpretación: identifica segmentos con alto ticket pero bajo volumen (nicho premium) vs alto volumen y bajo ticket (masivo).

4.4 Refinando el layout e interpretando resultados#

Objetivo: organizar la información para facilitar interpretación y comunicación. Layout sugerido:

  • Filas: Region → Product (jerarquía).

  • Valores: SUM(Total_Sales), SUM(Units_Sold), Campo calculado TicketProm.

  • Totales y subtotales activos.

  • Formato numérico: moneda para Total_Sales y TicketProm.

Cuadro de hallazgos (añade un cuadro de texto al lado):

  1. Top 3 regiones por ingresos y su % del total.

  2. Producto con mayor ticket en cada región.

  3. Hipótesis: ¿pricing? ¿mix? ¿promociones? ¿disponibilidad?

5. Resultados visuales y narrativa#

Trabajaremos con Total_Sales, Units_Sold, TicketProm y las dimensiones Date, Region, Product, Salesperson.

Teoría: elige el gráfico según la tarea (comparar, seguir tendencia, ver composición). Evita saturación (top-10) y etiquetas innecesarias.

5.1 El gráfico correcto para cada análisis#

Configuraciones concretas en Sheets:

  • Comparación por categoría (Top productos por ingresos):

    1. Crea una tabla de apoyo con las mayores ventas por Product

    2. Insertar → Gráfico → Barras. Eje X: Total_Sales; Eje Y: Product.

  • Tendencia temporal (ingresos mensuales):

    1. Pivot: Filas → Month, Valores → SUM(Total_Sales).

    2. Insertar → Gráfico → Líneas.

  • Composición (Region dentro del total):

    1. Pivot: Filas → Region, Valores → SUM(Total_Sales).

    2. Gráfico → Barras apiladas (mejor que pastel si >5 categorías).

5.2 Formato condicional para resaltar#

Objetivo: destacar insights clave (top-performers, outliers, KPIs).

Ejemplos prácticos en Sheets:

  1. En una tabla de Total_Sales por Region, aplica Formato → Formato condicional → Escala de color (del p10 al p90).

  2. Umbral KPI: resalta celdas > objetivo trimestral (por ejemplo, > 120000).

  3. Outliers de ticket: en la tabla con TicketProm, resalta valores > p90 o < p10 (usa Percentiles si tu Sheets lo permite).

  4. Añade nota: “Reglas aplicadas y fecha de cálculo”.

Actividad práctica:#

Actividad en grupos: análisis de ventas con Google Sheets

A cada grupo se le asigna un reto de negocio.
En todos los casos deben usar:

  • XLOOKUP

  • Tablas dinámicas (pivot tables)

  • Formato condicional aplicado sobre la tabla dinámica

  • Campos calculados sencillos dentro de la tabla dinámica

Grupo 1 – ¿Qué región genera más ingresos netos?

Objetivo de negocio: Identificar qué región aporta más ventas después de aplicar descuentos.

Tareas:

  1. Usa XLOOKUP para traer el nombre completo del Salesperson desde la hoja Salespersons al rango de Raw_sales.

  2. Crea una tabla dinámica con:

    • Filas: Region

    • Valores: Suma de Total_Sales y Promedio de Discount_Pct.

  3. Agrega un campo calculado llamado Net_Sales:

    =Total_Sales * (1 - Discount_Pct)

  4. Aplica formato condicional en la tabla dinámica para resaltar en verde la región con mayor Net_Sales.

Responde: ¿Qué región genera más ingresos netos y qué rol parecen jugar los descuentos?

Grupo 2 – Vendedores y cumplimiento de metas

Objetivo de negocio: Evaluar qué vendedores están cumpliendo mejor sus objetivos.

Tareas:

Usa XLOOKUP para traer la meta de ventas mensual (Target) desde la hoja Targets usando el nombre de Salesperson.

Construye una tabla dinámica con:

Filas: Salesperson

Columnas: Month

Valores: Suma de Total_Sales.

Agrega un campo calculado llamado Achievement_Rate:

=Total_Sales / Target

Aplica formato condicional para marcar en verde los casos donde Achievement_Rate > 1.

Responde: ¿Qué vendedor(es) supera(n) más consistentemente sus metas a lo largo de los meses?

Grupo 3 – Clientes de alto valor

Objetivo de negocio: Identificar a los clientes más valiosos por frecuencia y monto de compra.

Tareas:

Usa XLOOKUP para traer información adicional de los clientes desde la hoja Customers (por ejemplo, nombre, segmento o ciudad) usando Customer_ID.

Crea una tabla dinámica con:

Filas: Customer_ID (y opcionalmente el nombre del cliente)

Valores:

Conteo de Order_ID (número de compras)

Suma de Total_Sales

Agrega un campo calculado llamado Avg_Order_Value:

=Total_Sales / Order_ID

(En caso necesario, pueden usar el conteo de Order_ID como campo auxiliar en la pivot).

Usa formato condicional para resaltar a los clientes con:

  • Ventas totales altas y

  • Alto valor promedio por pedido (Avg_Order_Value).

Responde: ¿Qué clientes considerarías “VIP” y qué estrategia propondrías para retenerlos?

5.3 Mini dashboard de resumen#

Estructura:

  • Hoja dashboard con:

    • KPI 1 — Ingresos (rango de fechas):
      =SUMIFS('Raw Data'!G:G, 'Raw Data'!A:A, ">="&$B$1, 'Raw Data'!A:A, "<="&$B$2)
      (donde B1 = fecha inicio, B2 = fecha fin).

    • KPI 2 — Ticket promedio:
      =SUM('Raw Data'!G:G)/SUM('Raw Data'!D:D)

    • Barras Top-10 productos por ingresos (gráfico vinculado a tabla auxiliar).

    • Línea de ingresos mensuales (desde la Pivot por Month).

    • Tabla dinámica por Region con SUM(Total_Sales), SUM(Units_Sold), TicketProm.

  • Segmentadores (Slicers): Datos → Segmentador para Region y Salesperson.

  • Tema visual: títulos claros, sin rebasar 3–4 colores, formato moneda/fecha coherente.

5.4 Narrativa ejecutiva#

En resumen, escribe 3 bullets:

  1. Qué pasó: p.ej., “West y North concentran 62% de ingresos; enero es el pico mensual”.

  2. Por qué: hipótesis (mix de productos, precios, promociones, vendedores).

  3. Qué hacer: acciones con responsable y fecha (reposición, bundles, objetivo de ticket).

Tips y buenas prácticas#

  • Nombra tus campos de forma clara; guarda versiones.

  • Prefiere tablas (Ctrl/Cmd+T) como origen de datos para rango dinámico.

  • Documenta filtros aplicados (fecha, región) al exportar.

  • En Sheets, usa Slicers para filtros visuales en dashboards.

  • Evita sobrecargar gráficos; menos es más.