Estadísticas de rendimiento de ventas

En este ejemplo de solución, se muestra cómo acelerar las estadísticas sobre el estado del rendimiento de ventas con datos de SAP ERP o SAP BDC.

Con él, puedes realizar las siguientes tareas:

  • Optimizar los ingresos por producto, región y organización de ventas
  • Hacer un seguimiento del rendimiento en los canales de distribución y las divisiones
  • Usar IA para descubrir cuellos de botella en el cumplimiento o tendencias de entrega a tiempo que afecten los ingresos y los costos

Arquitectura de referencia

Una arquitectura de referencia de alto nivel proporciona una descripción general de los productos de datos específicos de la fuente necesarios para este caso de uso.

Arquitectura de referencia de muestra de la solución de estadísticas de rendimiento de ventas para Cortex Framework

Figura 1. Arquitectura de referencia del ejemplo de solución de estadísticas de rendimiento de ventas para Cortex Framework

Productos de datos obligatorios

Si compilas sobre datos de SAP ERP, se requieren los siguientes productos de datos de Cortex Framework

  • Clientes
  • Documentos de ventas
  • Organizaciones de ventas
  • Materiales

Además, los clientes también pueden combinar sin problemas otras tablas base de SAP ERP, por ejemplo KNVV, para mejorar las estadísticas, como se muestra en los fragmentos de código.

Si compilas sobre datos de SAP BDC, se requieren los siguientes productos de datos de BDC:

  • Cliente
  • Pedido de venta
  • Estructura de la organización de ventas
  • Empresa
  • Producto

Fragmentos de código

En el siguiente fragmento de código, se proporciona un ejemplo de cómo compilar un modelo de datos de BigQuery para obtener estadísticas de rendimiento de ventas sobre productos de datos de SAP ERP de Cortex Framework o la integración de productos de datos de SAP BDC.

WITH date_dimension as (
  
SELECT
  dt as date,
  CAST(FORMAT_DATE('%Y%m%d', dt) AS INT64) as date_int,
  FORMAT_DATE('%Y%m%d', dt) as date_str,
  FORMAT_DATE('%Y-%m-%d', dt) as date_str2,
  EXTRACT(YEAR FROM dt) as cal_year,
  IF(EXTRACT(QUARTER FROM dt) IN (1, 2), 1, 2) as cal_semester,
  EXTRACT(QUARTER FROM dt) as cal_quarter,
  EXTRACT(MONTH FROM dt) as cal_month,
  EXTRACT(WEEK FROM dt) as cal_week,
  CAST(EXTRACT(YEAR FROM dt) AS STRING) as cal_year_str,
  IF(EXTRACT(QUARTER FROM dt) IN (1, 2), '01', '02') as cal_semester_str,
  IF(EXTRACT(QUARTER FROM dt) IN (1, 2), 'S1', 'S2') as cal_semester_str2,
  '0' || EXTRACT(QUARTER FROM dt) as cal_quarter_str,
  'Q' || EXTRACT(QUARTER FROM dt) as cal_quarter_str2,
  FORMAT_DATE('%B', dt) as cal_month_long_str,
  FORMAT_DATE('%b', dt) as cal_month_short_str,
  '0' || (EXTRACT(WEEK FROM dt)) as cal_week_str,
  FORMAT_DATE('%A', dt) as day_name_long,
  FORMAT_DATE('%a', dt) as day_name_short,
  EXTRACT(DAYOFWEEK FROM dt) as day_of_week,
  EXTRACT(DAY FROM dt) as day_of_month,
  DATE_DIFF(dt, DATE_TRUNC(dt, QUARTER), DAY) + 1 as day_of_quarter,
  IF(
    EXTRACT(QUARTER FROM dt) IN (1, 2),
    EXTRACT(DAYOFYEAR FROM dt),
    IF(
      EXTRACT(QUARTER FROM dt) = 3,
      EXTRACT(DAYOFYEAR FROM dt) - EXTRACT(DAYOFYEAR FROM (DATE_TRUNC(dt, QUARTER) - 1)),
      EXTRACT(DAYOFYEAR FROM dt) - EXTRACT(DAYOFYEAR FROM (DATE_TRUNC(DATE_SUB(dt, INTERVAL 3 MONTH), QUARTER)))
    )
  ) as day_of_semester,
  EXTRACT(DAYOFYEAR FROM dt) as day_of_year,
  IF(
    EXTRACT(QUARTER FROM dt) IN (1, 2),
    EXTRACT(YEAR FROM dt) || 'S1',
    EXTRACT(YEAR FROM dt) || 'S2'
  ) as year_semester,
  EXTRACT(YEAR FROM dt) || 'Q' || EXTRACT(QUARTER FROM dt) as year_quarter,
  CAST(FORMAT_DATE('%Y%m', dt) AS STRING) as year_month,
  EXTRACT(YEAR FROM dt) || ' ' || FORMAT_DATE('%b', dt) as year_month2,
  FORMAT_DATE('%Y%U', dt) as year_week,
  (DATE_TRUNC(dt, YEAR) = dt) as is_first_day_of_year,
  (LAST_DAY(dt, YEAR) = dt) as is_last_day_of_year,
  (EXTRACT(MONTH FROM dt) IN (1, 7) AND EXTRACT(DAY FROM dt) = 1) as is_first_day_of_semester,
  ((EXTRACT(MONTH FROM dt) IN (6) AND EXTRACT(DAY FROM dt) IN (30))
    OR (EXTRACT(MONTH FROM dt) IN (12) AND EXTRACT(DAY FROM dt) IN (31))) as is_last_day_of_semester,
  (DATE_TRUNC(dt, QUARTER) = dt) as is_first_day_of_quarter,
  (LAST_DAY(dt, QUARTER) = dt) as is_last_day_of_quarter,
  (DATE_TRUNC(dt, MONTH) = dt) as is_first_day_of_month,
  (LAST_DAY(dt, MONTH) = dt) as is_last_day_of_month,
  (DATE_TRUNC(dt, WEEK) = dt) as is_first_day_of_week,
  (LAST_DAY(dt, WEEK) = dt) as is_last_day_of_week,
  ((MOD(EXTRACT(YEAR FROM dt), 4) = 0 AND MOD(EXTRACT(YEAR FROM dt), 100) != 0)
    OR MOD(EXTRACT(YEAR FROM dt), 400) = 0) as is_leap_year,
  (FORMAT_DATE('%A', dt) NOT IN ('Saturday', 'Sunday')) as is_week_day,
  (FORMAT_DATE('%A', dt) IN ('Saturday', 'Sunday')) as is_week_end,
  (DATE_TRUNC(dt, WEEK)) as week_start_date,
  (LAST_DAY(dt, WEEK)) as week_end_date,
  (DATE_TRUNC(dt, MONTH)) as month_start_date,
  (LAST_DAY(dt, MONTH)) as month_end_date,
  (EXTRACT(WEEK FROM LAST_DAY(dt, ISOYEAR)) = 53) as has_53_weeks
FROM UNNEST(GENERATE_DATE_ARRAY(
  DATE_SUB(
    DATE_TRUNC(CURRENT_DATE(), YEAR), INTERVAL 20 YEAR),
  LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 20 YEAR)),
  INTERVAL 1 DAY)
) as dt

),
delivered_qty AS (
  SELECT
    client_mandt,
    internal_reference_document_number_vgbel AS sales_document_id,
    internal_reference_document_item_vgpos AS item_id,
    SUM(actual_quantity_delivered_in_sales_units_lfimg) AS total_delivered_qty
  FROM <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.delivery_document_items
  GROUP BY client_mandt, internal_reference_document_number_vgbel, internal_reference_document_item_vgpos
)
SELECT
  header.document_number_vbeln AS sales_document_id,
  item.item_number_posnr AS item_id,
  header.sales_organization_vkorg AS sales_organization_id,
  salesorg.name_vtext AS sales_organization_name,
  header.division_spart AS division_id,
  division.name_vtext AS division_name,
  header.sold_to_party_kunnr AS customer_id,
  customer.name1_name1 AS customer_name,
  item.material_number_matnr AS product_id,
  product.material_text_maktx AS product_name,
  item.net_value_of_the_sales_document_item_in_document_currency_netwr AS net_value,
  header.document_currency_waerk AS document_currency,
  header.requested_delivery_date_vdatu AS requested_delivery_date,
  header.delivery_block_lifsk AS delivery_block_reason,
  
  -- Using vbap.gbsta for overall status in S4
  foundation_item.gbsta AS overall_status,
  
  -- Deriving delivery status based on delivered quantity
  CASE 
    WHEN delivered_qty.total_delivered_qty IS NULL OR delivered_qty.total_delivered_qty = 0 THEN 'A'
    WHEN delivered_qty.total_delivered_qty < item.cumulative_order_quantity_kwmeng THEN 'B'
    ELSE 'C'
  END AS delivery_status,
  
  salesarea.bzirk AS sales_region,
  
  -- Date Dimensions for Document Date
  dimensional_document_date.cal_year AS year_of_sales_document,
  dimensional_document_date.cal_month AS month_of_sales_document,
  dimensional_document_date.cal_quarter AS quarter_of_sales_document,
  
  -- Date Dimensions for Requested Delivery Date
  dimensional_delivery_date.cal_year AS year_of_requested_delivery,
  dimensional_delivery_date.cal_month AS month_of_requested_delivery,
  dimensional_delivery_date.cal_quarter AS quarter_of_requested_delivery,
  
  -- Calculated fields
  CASE 
    WHEN CURRENT_DATE() > header.requested_delivery_date_vdatu 
      AND (delivered_qty.total_delivered_qty IS NULL 
      OR delivered_qty.total_delivered_qty < item.cumulative_order_quantity_kwmeng) 
    THEN TRUE 
    ELSE FALSE 
  END AS is_delivery_overdue

FROM <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.sales_document_headers AS header
JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.sales_document_items AS item
  ON header.client_mandt = item.client_mandt
  AND header.document_number_vbeln = item.document_number_vbeln

-- Joins with Master Data Products for Text
LEFT JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.customers_md AS customer
  ON header.client_mandt = customer.client_mandt
  AND header.sold_to_party_kunnr = customer.customer_number_kunnr

LEFT JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.materials_md AS product
  ON item.client_mandt = product.client_mandt
  AND item.material_number_matnr = product.material_number_matnr

LEFT JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.sales_organizations_md AS salesorg
  ON header.client_mandt = salesorg.client_mandt
  AND header.sales_organization_vkorg = salesorg.sales_organization_vkorg

LEFT JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.divisions_md AS division
  ON header.client_mandt = division.client_mandt
  AND header.division_spart = division.division_spart

-- Join with KNVV for Sales Org Region
LEFT JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_FOUNDATION_DATASET>.knvv AS salesarea
  ON header.client_mandt = salesarea.mandt
  AND header.sold_to_party_kunnr = salesarea.kunnr
  AND header.sales_organization_vkorg = salesarea.vkorg
  AND header.distribution_channel_vtweg = salesarea.vtweg
  AND header.division_spart = salesarea.spart

-- Join with vbap directly to get gbsta (Overall Status)
LEFT JOIN <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_FOUNDATION_DATASET>.vbap AS foundation_item
  ON item.client_mandt = foundation_item.mandt
  AND item.document_number_vbeln = foundation_item.vbeln
  AND item.item_number_posnr = foundation_item.posnr

-- Join with delivered_qty CTE
LEFT JOIN delivered_qty AS delivered_qty
  ON item.client_mandt = delivered_qty.client_mandt
  AND item.document_number_vbeln = delivered_qty.sales_document_id
  AND item.item_number_posnr = delivered_qty.item_id

-- Joins for Date Dimensions
LEFT JOIN date_dimension AS dimensional_document_date
  ON header.document_date_audat = dimensional_document_date.date
LEFT JOIN date_dimension AS dimensional_delivery_date
  ON header.requested_delivery_date_vdatu = dimensional_delivery_date.date

Habilita los agentes de IA

Después de crear tu modelo de datos de estadísticas de rendimiento de ventas en BigQuery, puedes compilar un agente de datos personalizado. Esto te permite consultar las tendencias de ventas y las métricas de rendimiento directamente con lenguaje natural, sin necesidad de usar SQL complejo.