Diccionario de datos para Strategy

Cada sección es un tema de negocio y tiene una tabla por objeto de la base. Las columnas de esas tablas son siempre las mismas:

Rol en Strategy: cuando dice atributo va a una carpeta de atributos; métrica se arma con una agregación (Sum, Count, Max) sobre esa columna; clave es un ID de join que no se muestra al usuario; técnica no se publica en tableros.

Refresco: bi_rebuild se recalcula dos veces por día (Cloud Run Job bi-rebuild-incremental, 00:00 y 12:00 ART). Los cubos de Strategy deben refrescarse después de esas corridas.

Si se respetan estas diez, el 90% de los números "que no cierran" desaparece.

1. La plata es fact_sale.net_sale. Nunca sumar total_amount. public.sales mezcla transacciones de Betterez con filas sintéticas R-<boleto> (devolución) y PI-<ítem> (seguro, percepción) que desglosan plata ya incluida en otra transacción. net_sale las pone en 0 cuando tienen contenedor; Sum(total_amount) las cuenta dos veces. Sum(net_sale) = pagos con signo (ADR 2026-09-29).
2. Boletos se cuentan en fact_ticket_mstr (o fact_ticket) con Count(ticket_id), nunca contando ventas. Una venta trae 1,59 boletos en promedio: contar ventas subestima ~40%.
3. Importes por medio de pago van en fact_payment / fact_sale, nunca repartidos por boleto. fact_ticket_mstr.primary_payment_type es sólo un atributo (con qué se pagó), no un monto.
4. No se suman monedas distintas. Toda métrica de dinero lleva filtro de currency (ARS, CLP, BOB, USD). No hay conversión de tipo de cambio en bi_rebuild.
5. Banderas is_* / has_* son smallint 1/0, no verdadero/falso. Filtrar con = 1 / = 0. Única excepción de texto: viaja_con_menor_3 vale 'Si' / 'No'.
6. sale_date ya está en hora argentina (ART). Una venta a las 22:00 del 30/09 es del 30/09, no del 01/10. No recalcular desde UTC.
7. Boleto pagado = is_paid = 1 (neto de cancelaciones y cambios). Para cuadrar contra el reporte web de Betterez por agencia, is_paid_including_partial_cancel = 1.
8. dim_manifest.origin / destination describen el RECORRIDO del bus (primera y última parada), no dónde subió el pasajero. Para el tramo del pasajero: fact_ticket.origin / destination.
9. Tres "alcances" distintos que no se unifican: alcance_ruta (etiqueta del manifiesto), alcance_tramo (geografía del viaje del pasajero) y tipo_liquidacion (N/P/I con que liquidó SAP). Para cuadrar con SAP, tipo_liquidacion; para analizar viajes, alcance_tramo.
10. Itinerario = schedule_id, no el texto. Hay varios schedule_id con el mismo nombre. El km del servicio se agrega con Max(schedule_distance_km), nunca Sum por boleto.

Reglas complementarias: PI- no significa seguro (seguro = is_insurance = 1); el schema bi (plural: fact_tickets, fact_sales) es legacy y no se usa; los tableros leen bi_rebuild, nunca public.

Diagrama de relaciones (claves de join):

dim_date.date_id ─────────── fact_*.sale_date  (y manifest_date en fact_manifest_leg / dim_manifest)
dim_mes_anio.id_mes_anio ─── dim_date.id_mes_anio ─── fact_ticket.mes_anio
dim_agency.agency_id ─────── fact_sale / fact_ticket / fact_payment / fact_sold_item .agency_id
dim_channel.channel ──────── fact_*.channel
fact_sale.sale_id ────────── fact_ticket.sale_id, fact_payment.sale_id, fact_sold_item.sale_id
fact_ticket.ticket_id ────── fact_ticket_mstr.ticket_id (1:1), fact_sold_item.ticket_id
dim_manifest.manifest_id ─── fact_ticket(.mstr).manifest_id, fact_manifest_leg.manifest_id, bridge_manifest_tag.manifest_id
dim_schedule.schedule_id ─── fact_ticket(.mstr).schedule_id, dim_manifest.schedule_id, fact_manifest_leg.schedule_id, dim_schedule_leg.schedule_id
dim_route.route_id ───────── fact_ticket(.mstr).route_id, dim_schedule.route_id, dim_manifest.route_id
dim_payment_method.payment_type ── fact_payment.payment_type, fact_sale.primary_payment_type, fact_ticket_mstr.primary_payment_type
dim_sold_item_product.product_name ── fact_sold_item.product_name
dim_manifest_tag.tag_name_norm ── bridge_manifest_tag.tag_name_norm

3.1 bi_rebuild.fact_sale — 31 columnas

Grano: 1 fila de public.sales. Incluye transacciones de Betterez y las filas sintéticas R- y PI-. ETL: paso 8 (y 8c para las columnas de pago). Guías: GUIA_TABLERO_AGENCIAS §5.3, GUIA_FORMA_DE_PAGO, ADR docs/decisions/2026-09-29_devoluciones_contenedor_y_grano_transaccion.md.

Vistas derivadas:

• v_fact_sale_parent_only (28 columnas, las mismas de fact_sale sin sale_type ni

source_transaction_id): sólo ventas padre, sin R-, PI- ni cancelaciones. Es venta bruta, no neta. Nombre sugerido: "Ventas padre (bruto)".

• v_fact_sale_agency_canonica (15 columnas): fact_sale con agency_id_reporting (agencia

canónica). Uso técnico; no publicar sin revisar con Ingeniería.

Métricas recomendadas:

4.1 bi_rebuild.fact_payment — 15 columnas

Grano: 1 pago de una venta (una venta puede tener varios). ETL: paso 8c, desde public.payments (arreglo data.payments[] del webhook). Guía: GUIA_FORMA_DE_PAGO_BI_REBUILD.md.

Métrica: Importe por medio de pago (neto) = Sum(CASE WHEN fact_sale.total_amount < 0 THEN -amount ELSE amount END) con is_success = 1 y currency fija. Desde el 28/09/2026 no hay pagos duplicados en la tabla.

4.2 bi_rebuild.dim_payment_method — 11 columnas

Grano: 1 código de medio de pago (32 medios). Semilla manual en bi_rebuild_schema_patch_once.sql.

5.1 bi_rebuild.fact_ticket — 85 columnas

Grano: 1 boleto (1 pasajero en 1 tramo). ETL: paso 8b (base), 9–10 (etiquetas y alcance de ruta), 10-sync (nombre de itinerario), 10-geo (provincias y alcance_tramo), 10b (transportista, alícuota), 10d (liquidación SAP), 10e (transaction_id), 10f (conexiones), 11 (viaja_con_menor_3). Guías: SCHEMA_FACT_TICKET.md, GUIA_ESTADOS_TICKET_BI_REBUILD.md.

Identificación y venta

Importes, tarifa e impuestos

Estado del boleto

Detalle y árbol de decisión: GUIA_ESTADOS_TICKET_BI_REBUILD.md.

Viaje, ruta e itinerario

Fechas del viaje y manifiesto

Conexiones (viaje con trasbordo)

Betterez vende un viaje con trasbordo como un boleto por tramo, en la misma venta. Guía: GUIA_CONEXIONES_BI_REBUILD.md. Regla: un boleto es un tramo; para contar viajes, filtrar tramo_orden = 1.

Liquidación SAP y ancillaries

5.2 bi_rebuild.fact_ticket_mstr — 68 columnas

Grano: 1 boleto (misma cantidad de filas que fact_ticket, 1:1 por ticket_id). Es la tabla "ancha" pensada para el cubo de Strategy. ETL: paso 10 (+ 10b, 10c, 10d, 10e, 10f, 11). Guía: SCHEMA_FACT_TICKET_MSTR.md.

53 columnas compartidas con fact_ticket, con el mismo significado (ver §5.1): ticket_id, sale_date, departure_date_ar, travel_date, ticket_amount, manifest_id, channel, channel_group, origin, destination, route_id, itinerary, is_paid, effective_ticket_status, product_type, product_family, has_nacional, has_internacional, has_linea_provincial, has_linea, has_refuerzo, currency, ticket_taxes, fare, pax_segment, is_child_fare, schedule_id, schedule_distance_km, successor_ticket_number, successor_transaction_id, successor_match_method, viaja_con_menor_3, origin_province, destination_province, origin_country, destination_country, alcance_tramo, manifest_date, operating_company_name, vat_rate, numero_liquidacion, fecha_liquidacion, tipo_liquidacion, transaction_id, journey_id, tramo_orden, cantidad_tramos, is_conexion, parada_conexion, origen_viaje, destino_viaje, es_linea_provincial, origen_linea_provincial.

15 columnas propias:

Lo que NO tiene (y hay que ir a fact_ticket por ticket_id): agency_id / agency_code / agency_label, ticket_number, sale_id, sale_hour, mes_anio, alcance_ruta, tipo_servicio, is_partial_cancel, is_paid_including_partial_cancel, is_cancelled, is_refunded, refund_amount, manifest_match_method, trip_type, travel_distance.

5.3 bi_rebuild.v_conexion_trasbordo — 35 columnas (vista)

Desde 02/10/2026 (migración v_conexion_trasbordo). Una fila por trasbordo: une el tramo que llega a la parada de conexión (llega_*: ticket, itinerario, schedule_id, manifiesto, transportista, salida y llegada en UTC) con el que parte de ella (parte_*), dentro del mismo viaje y pasajero. Un viaje de 3 tramos da 2 filas (trasbordo_orden 1 y 2). Especificación del tablero: ESPEC_TABLERO_CONEXIONES_MSTR.md.

• viaje_id es el journey_id; en el histórico sin journey_id es v: + un hash de venta, viaje

y pasajero (no expone el documento). 12 tramos históricos ambiguos quedan afuera.

• espera_min: minutos entre la llegada programada del tramo que llega y la salida del

siguiente. Promediar solo con espera_valida = 1 (entre 0 y 24 h).

• is_paid_trasbordo = 1 cuando los dos tramos están vigentes. No filtra: un trasbordo con un

tramo cancelado también es dato (llega_is_paid, parte_is_paid).

• Métrica "Pasajeros que trasbordan" = Count(llega_ticket_id) con is_paid_trasbordo = 1.
• Septiembre 2026: 1.771 trasbordos, 1.573 vigentes, espera mediana 70 min. Pares más frecuentes:

PPCO → GJ01 en Córdoba (205) y JG03 → COPB en Córdoba (173).

• No duplica boletos: cada tramo aparece a lo sumo una vez como "llega" y una como "parte". Se

calcula al consultarla (~0,2 s el reporte por par de servicios).

Un manifiesto es un servicio (itinerario) en un día concreto: el bus que salió el 15/03 a las 13:00. Guías: GUIA_FACT_MANIFEST_LEG_BI_REBUILD.md, GUIA_TABLERO_ETIQUETAS_MANIFIESTO.md.

6.1 bi_rebuild.dim_manifest — 32 columnas

Grano: 1 manifiesto. ETL: paso 7, desde public.manifest_legs y public.manifest_entries (Betterez GET /manifests/{id}, webhooks manifests.* y job andesmar-manifests-sync).

6.2 bi_rebuild.fact_manifest_leg — 21 columnas

Grano: 1 tramo entre dos paradas consecutivas de un manifiesto (manifest_id + leg_ord). ETL: paso 7b (y un UPDATE posterior al paso 10 para alcance_ruta / product_type), desde public.manifest_legs (Betterez manifest.legs[]). Cubre desde abril de 2025.

6.3 Etiquetas: dim_manifest_tag (9) y bridge_manifest_tag (2)

7.1 bi_rebuild.dim_agency — 11 columnas

Grano: 1 agencia Betterez (319). ETL: paso 4 + backfill_dim_agency_catalog.sql (export de agencias de Betterez). Guía: GUIA_TABLERO_AGENCIAS_BI_REBUILD.md.

agency_blacklist (5 columnas: agency_id, agency_code, agency_label, motivo, created_at) es la lista decidida por Andesmar de la que se deriva is_blacklisted. No se publica.

7.2 bi_rebuild.dim_channel — 2 columnas

Plataforma 10 no es un canal: es una agencia (agency_code = 'PTF') dentro del grupo web.

Tres ideas que se mezclan: itinerario (el servicio con horario: dim_schedule), ruta (agrupador comercial de Betterez: dim_route) y tramo (lo que compró el pasajero: fact_ticket.origin/destination). Detalle: CONVENCIONES §4.1.

8.1 bi_rebuild.dim_schedule — 20 columnas

Grano: 1 itinerario. ETL: paso 6c, desde public.schedules (webhooks schedule.* y backfill_schedules.py).

8.2 bi_rebuild.dim_schedule_leg — 8 columnas

Grano: 1 tramo del itinerario (schedule_id, leg_ord). Columnas: from_id, from_name, to_id, to_name, distance_km (km del tramo), duration_minutes. Es la única fuente de km por tramo (la usa la carga media, §12). Cuidado: el leg_ord del itinerario no siempre coincide con el del manifiesto; unir por schedule_id + from_id + to_id.

8.3 bi_rebuild.dim_route — 7 columnas

Grano: 1 ruta (181). ETL: paso 6.

8.4 Estaciones: dim_station (4), dim_station_geo (5), dim_province (3)

Guía: GUIA_TABLERO_SEGUROS_Y_MENORES.md, SCHEMA_FACT_SOLD_ITEM.md.

9.1 bi_rebuild.fact_sold_item — 27 columnas

Grano: 1 ítem vendido aparte del pasaje (fila PI-). ETL: paso 11, desde public.sold_item_details.

9.2 Catálogo y vistas

• dim_sold_item_product (4): product_name (clave), product_category, betterez_catalog_name,

is_active. 7 productos: VIAJE PROTEGIDO, 5 variantes de BOLETO SEGURO, IMPUESTO NACIONAL ARGENTINO - RG3819.

• v_insurance_kpi (7) y v_minor_kpi (7): agregados por sale_date, channel, channel_group,

agency_id, currency, con insurance_items_sold / insurance_revenue y minor_items_sold / minor_revenue. Ya filtran status = paid. Nombres sugeridos: "Seguros vendidos", "Recaudación seguros", "Boletos menor de 3 vendidos", "Recaudación boletos menor de 3".

Viven como columnas de fact_ticket y fact_ticket_mstr (§5.1): numero_liquidacion, fecha_liquidacion, tipo_liquidacion, más operating_company_name y vat_rate del Tablero Contable. Origen: sap.liquidacion_sap_ticket (pipeline sap-liquidacion-pipeline, 05:00 ART). fecha_liquidacion también está en fact_sale, fact_payment y fact_sold_item (desde 02/10/2026, paso 11b), para filtrar plata, pagos e ítems por fecha contable.

• Venta provincial: es_linea_provincial = 1 (no has_linea_provincial, que pierde los boletos

devueltos). Neto por cohorte = Sum(ticket_amount) − Sum(refund_amount) por sale_date. Neto por movimientos del mes = Sum(ticket_amount) por sale_date − Sum(refund_amount) por fecha_devolucion. Septiembre 2026 (ARS): bruta $272,2 M, neto A $259,3 M, neto B $259,0 M. Guía: GUIA_VENTA_PROVINCIAL_TABLERO_CONTABLE.md.

• NULL es legítimo: call center, ventas en Chile / Bolivia / Perú, agencias excluidas de SAP

(sap.ag_excluidas) y todo mayo de 2026 (la tabla SAP arranca en junio).

• El número es por agencia: la liquidación 799 existe en muchas agencias. Clave =

agency_code + numero_liquidacion.

• Fecha contable: Andesmar fecha el boleto en sus planillas por fecha_liquidacion, no por

sale_date.

Los tres alcances (no son intercambiables):

alcance_tramo y tipo_liquidacion coinciden ~96%. La diferencia es de definición, no un error.

11.1 bi_rebuild.dim_date — 9 columnas y dim_mes_anio — 2 columnas

ETL: pasos 2, 2b y 3.

11.2 ¿Qué fecha uso?

Pregunta abierta con Mariela Ramos: si "CM de un servicio" es carga media. Mientras se confirma, esto es lo que se puede calcular hoy con los datos existentes.

12.1 Qué hay

fact_manifest_leg tiene, para cada tramo entre dos paradas de un manifiesto, los pasajeros a bordo (load) y el % de ocupación (load_factor), además de suben / bajan. dim_manifest tiene la capacidad (bus_capacity) y el pico (peak_load). El km de cada tramo no está en fact_manifest_leg: hay que traerlo de dim_schedule_leg.distance_km. Ejemplo real (servicio Caleta Olivia → Resistencia, 20/09/2026, 58 asientos): arranca vacío, llega a 56 pasajeros entre Sierra Grande y Las Grutas, y baja a 16 después de Rosario.

12.2 Tres definiciones posibles

La carga media ponderada por km es la definición habitual en transporte de pasajeros (pasajeros-km sobre kilómetros recorridos). Si Planificación entiende "CM" como pasajeros transportados por servicio, la fórmula es Sum(pax_on), que ya está disponible sin nada extra.

Para un servicio en un período (ej. "el 13:00 Mendoza-Uspallata en septiembre"), agregar pasajeros-km y km de todos sus manifiestos y dividir al final (no promediar los promedios).

12.3 En Strategy: v_manifest_carga_media (desde 29/09/2026)

Implementado (migración carga_media_manifiesto): fact_manifest_leg.distance_km trae el km de cada tramo (paso 7b del incremental, unido por estaciones) y la vista bi_rebuild.v_manifest_carga_media da una fila por manifiesto, lista para Strategy sin joins:

Métricas en Strategy para un servicio o un período (sumar primero, dividir al final; nunca promediar carga_media_km):

• Carga media = Sum(pax_km) / Sum(km_con_dato)
• Ocupación media % = 100 × Sum(pax_km) / Sum(km_con_dato × capacidad) (conviene exponer

km_con_dato × capacidad como hecho, o calcularlo en la vista del reporte)

• Filtro por defecto: manifest_status = 'published'.

Validación: scripts/sql/bi_rebuild/operativo/validate_manifest_carga_media.sql. Backfill del histórico: scripts/sql/bi_rebuild/backfill/backfill_fact_manifest_leg_km.sql (aplicado 29/09).

12.4 Limitaciones medidas (29/09/2026)

1. Km faltante. distance_km es NULL cuando Betterez informa el tramo en 0 (~17% en septiembre, ej. Rawson → Trelew) o el itinerario no está en dim_schedule_leg. Cobertura de tramos con km: ~78% en septiembre 2026, 58–65% antes de abril 2026 (itinerarios viejos menos cubiertos). En septiembre, de 7.839 manifiestos publicados: 4.462 con km completo, 3.037 parcial, 340 sin km. La carga media se pondera solo con los tramos que tienen km; para comparaciones finas, filtrar km_completo = 1.
2. No unir por leg_ord: el orden del itinerario no coincide con el del manifiesto en ~12% de los tramos (la vista ya une por estaciones).
3. load_factor viene redondeado a entero por Betterez: no promediarlo; usar la vista.
4. Manifiestos no operativos: emptied, canceled, paused y Sin datos tienen carga 0 o parcial: filtrar published.
5. Si Planificación entiende "CM" como pasajeros transportados, es Sum(pasajeros_subidos).

Relevadas el 29/09/2026 comparando las guías contra la base. La base manda sobre qué columnas y valores existen; las guías, sobre el significado.

Propuesta para quien administra MSTR. Criterio: una carpeta por tema de negocio, adentro Atributos y Métricas separados, y el grano en el nombre de la métrica cuando puede confundirse.

Andesmar BI (bi_rebuild)
├── 01 Ventas y plata
│   ├── Atributos: Tipo de transacción, Estado de la venta, Canal de venta, Moneda
│   └── Métricas:  Venta neta, Venta bruta, Cantidad de transacciones, Cobrado neto
├── 02 Boletos
│   ├── Atributos: Estado del boleto, Tarifa, Segmento de pasajero, Tipo de producto,
│   │              Alcance del tramo, Origen / Destino del pasajero, Provincia / País
│   └── Métricas:  Boletos pagados, Boletos pagados (criterio Betterez web),
│                  Boletos cancelados, Importe de pasajes, IVA de pasajes, Km vendidos
├── 03 Forma de pago
│   ├── Atributos: Medio de pago, Categoría de pago, Grupo de pago, Pago combinado
│   └── Métricas:  Importe por medio de pago, Boletos por medio de pago, % pago combinado
├── 04 Seguros y menores de 3
│   ├── Atributos: Producto, Categoría de producto
│   └── Métricas:  Seguros vendidos, Recaudación seguros, Boletos menor de 3, Recaudación menor de 3,
│                  Percepción RG3819
├── 05 Servicios, manifiestos y ocupación
│   ├── Atributos: Manifiesto, Fecha del servicio, Hora de salida, Estado del manifiesto,
│   │              Origen / Destino del recorrido, Parada, Orden de la parada, Alcance de la ruta,
│   │              Tipo de servicio, Etiquetas (Línea, Refuerzo, La Cuyanita, ...), Vehículo, Chofer
│   └── Métricas:  Suben, Bajan, Pasajeros a bordo, Pico de pasajeros, Ocupación pico (%),
│                  Carga media, Ocupación media (%), Pasajeros transportados, Capacidad
├── 06 Agencias y canales
│   ├── Atributos: Agencia, Código de agencia, País de la agencia, Agencia activa,
│   │              Canal de venta, Grupo de canal
│   └── Métricas:  (las de 01 y 02, filtradas por agencia; no se duplican)
├── 07 Rutas e itinerarios
│   ├── Atributos: Itinerario, Ruta, Recorrido comercial, Transportista, Marca comercial,
│   │              Cabecera / Destino final
│   └── Métricas:  Km del itinerario (Max), Duración del itinerario
├── 08 Contable y liquidación SAP
│   ├── Atributos: N.º de liquidación SAP, Fecha de liquidación, Tipo de liquidación SAP,
│   │              Alícuota de IVA, Transportista
│   └── Métricas:  Importe liquidado, IVA liquidado
├── 09 Fechas
│   └── Atributos: Día, Semana, Mes, Trimestre, Año, Día de la semana
│                  (una jerarquía por fecha: de venta, de viaje, del servicio, de liquidación)
├── 90 Auditoría (no usar en tableros)
│   └── Total informado, Estado crudo, Método de match de manifiesto, Resolución de agencia, ...
└── 99 Obsoleto — schema bi (a borrar)

Convenciones de nombres sugeridas:

1. En castellano, sin nombres técnicos visibles: "Venta neta", no net_sale.
2. Métricas de dinero con la moneda en el filtro de la métrica y, si hay varias, en el nombre: "Venta neta (ARS)", "Venta neta (CLP)".
3. Métricas de conteo con la unidad: "Boletos pagados", "Transacciones", "Ítems de seguro". Nunca "Ventas" a secas (no se sabe si son boletos o transacciones).
4. Atributos de fecha con el calificativo: "Fecha de venta", "Fecha del servicio", "Fecha de viaje", "Fecha de liquidación". Nunca "Fecha" sola.
5. Las banderas se muestran como Sí / No en el atributo (el dato es 1/0).
6. Un solo nombre por concepto en todo el proyecto: si "Agencia" es agency_label, no hay otra "Agencia" armada con agency_name.

• Al agregar una columna a bi_rebuild (siempre por migración Alembic), agregar su fila acá y en

la guía temática, con COMMENT ON COLUMN en la migración.

• Verificación de columnas contra la base:

SELECT table_name, column_name, data_type,
       col_description(format('%I.%I', table_schema, table_name)::regclass, ordinal_position) AS comentario
FROM information_schema.columns
WHERE table_schema = 'bi_rebuild'
ORDER BY table_name, ordinal_position;

• Guías relacionadas: CONVENCIONES_Y_GLOSARIO_TABLERO_MSTR.md

(glosario), GUIA_TABLERO_METRICAS_BI_REBUILD.md (índice de métricas), INFORME_MAESTRO_BI_REBUILD.md (capa técnica). Versión HTML: docs/presentaciones/diccionario-datos.html.