Documentación de Consultas BigQuery

Repositorio de las consultas SQL utilizadas para la sincronización y cálculo de métricas.

Sincronización de Métricas Semanales

Esta consulta extrae los datos granulares desde BigQuery para poblar la tabla metricas_semanales en PostgreSQL. Se filtra por rol (HUMAN) y rango de fechas.

            SELECT
            call_date AS fecha,
            market_code AS mercado,
            factory_name AS fabrica,
            skill_name AS skill,
            all_supervisor_bp_numbers AS supervisor_id,
            category AS proceso,
            factor,
            aht,
            nota_calidad,
            fcr,
            nps AS nps_score,
            canal,
            is_human
            FROM `data-exp-contactcenter.100x100.third_calculated`
            WHERE call_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
            AND is_human != 'NOT_HUMAN'
        

Agregación de KPIs Mensuales

Lógica utilizada para generar la tabla optimizada kpis_mensuales. Calcula promedios ponderados y ratios.

            -- AHT (Promedio Ponderado)
            SUM(CASE WHEN aht > 0 AND aht < 7200 THEN aht * factor ELSE 0 END) / NULLIF(SUM(CASE WHEN aht> 0 AND aht <
                    7200 THEN factor ELSE 0 END), 0) -- NPS (Score) (SUM(CASE WHEN nps_score>= 9 THEN factor ELSE 0 END)
                    - SUM(CASE WHEN nps_score <= 6 THEN factor ELSE 0 END)) * 100.0 / NULLIF(SUM(CASE WHEN nps_score IS
                        NOT NULL THEN factor ELSE 0 END), 0) -- FCR (%) SUM(CASE WHEN fcr=1 THEN factor ELSE 0 END) *
                        100.0 / NULLIF(SUM(CASE WHEN fcr IN (0, 1) THEN factor ELSE 0 END), 0) -- Calidad SUM(CASE WHEN
                        nota_calidad> 0 THEN nota_calidad * factor ELSE 0 END)
                        / NULLIF(SUM(CASE WHEN nota_calidad > 0 THEN factor ELSE 0 END), 0)
        

Demanda Mensual

Consulta para obtener el volumen mensual total para validación y tendencias macro.

            SELECT
            EXTRACT(YEAR FROM call_date) as anio,
            EXTRACT(MONTH FROM call_date) as mes,
            canal,
            SUM(factor) as total_factor
            FROM `data-exp-contactcenter.100x100.third_calculated`
            WHERE EXTRACT(YEAR FROM call_date) IN (2024, 2025)
            AND is_human != 'NOT_HUMAN'
            GROUP BY 1, 2, 3
        