Informazioni sul rendimento delle vendite

Questo esempio di soluzione fornisce un esempio di come accelerare la generazione di informazioni sullo stato del rendimento delle vendite utilizzando i dati di SAP ERP o SAP BDC.

Con questa soluzione puoi:

  • Ottimizzare le entrate per prodotto, regione e organizzazione di vendita.
  • Monitorare il rendimento su canali di distribuzione e divisioni.
  • Utilizzare l'AI per scoprire i colli di bottiglia di evasione o le tendenze di consegna puntuale che influiscono su entrate e costi.

Architettura di riferimento

Un'architettura di riferimento di alto livello fornisce una panoramica dei prodotti di dati specifici dell'origine necessari per questo caso d'uso.

Architettura di riferimento di esempio della soluzione Sales Performance Insights per Cortex Framework

Figura 1. Architettura di riferimento dell'esempio di soluzione Informazioni sul rendimento delle vendite per Cortex Framework.

Prodotti di dati richiesti

Se crei prodotti di dati basati su SAP ERP, sono necessari i seguenti prodotti di dati di Cortex Framework:

  • Clienti
  • Documenti di vendita
  • Organizzazioni di vendita
  • Materiali

Inoltre, i clienti possono anche combinare senza problemi altre tabelle di base di SAP ERP, ad esempio KNVV, per migliorare le informazioni, come mostrato negli snippet di codice.

Se crei prodotti di dati basati su SAP BDC, sono necessari i seguenti prodotti di dati BDC:

  • Cliente
  • Ordine di vendita
  • Struttura dell'organizzazione di vendita
  • Società
  • Prodotto

Snippet di codice

Il seguente snippet di codice fornisce un esempio di come creare un modello dei dati BigQuery per le informazioni sul rendimento delle vendite basato sui prodotti di dati SAP ERP di Cortex Framework o sull'integrazione dei prodotti di dati 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

Attivare gli agenti AI

Dopo aver creato il modello dei dati Informazioni sul rendimento delle vendite in BigQuery, puoi creare un agente di dati personalizzato. In questo modo, puoi eseguire query sulle tendenze di vendita e sulle metriche di rendimento direttamente utilizzando il linguaggio naturale, senza la necessità di SQL complessi.