תובנות לגבי ביצועי מכירות
הפתרון לדוגמה הזה מספק דוגמה לאופן שבו אפשר להגיע לתובנות לגבי תקינות ביצועי המכירות מהר יותר באמצעות נתונים שמקורם ב-SAP ERP או ב-SAP BDC.
באמצעות הכלי הזה, אפשר:
- אופטימיזציה של ההכנסות לפי מוצר, אזור וארגון מכירות.
- מעקב אחר הביצועים בערוצי הפצה ובחטיבות.
- אפשר להשתמש ב-AI כדי לזהות צווארי בקבוק בתהליך אספקת המוצרים או מגמות של אספקת מוצרים בזמן שמשפיעות על ההכנסות והעלויות.
תרשים עזר לארכיטקטורה
ארכיטקטורת הפניה ברמה גבוהה מספקת סקירה כללית של מוצרי הנתונים הספציפיים למקור שנדרשים כדי להזין את תרחיש השימוש הזה.
מוצרי נתונים נדרשים
אם אתם בונים על נתונים שמקורם ב-SAP ERP, אתם צריכים את מוצרי הנתונים הבאים של Cortex Framework:
- לקוחות
- מסמכי מכירות
- ארגוני מכירות
- חומרים
בנוסף, לקוחות יכולים גם לשלב בצורה חלקה טבלאות בסיס אחרות של SAP ERP, למשל KNVV ו-VBAP, כדי לשפר את התובנות, כפי שמוצג בקטעי הקוד.
אם אתם בונים על נתונים שמקורם ב-SAP BDC, אתם צריכים את מוצרי הנתונים הבאים של BDC:
- לקוח/ה
- הזמנת מכירה
- מבנה ארגון המכירות
- חברה
- מוצר
קטעי קוד
קטע הקוד הבא מספק דוגמה לאופן שבו אפשר ליצור מודל נתונים של BigQuery לתובנות לגבי ביצועי המכירות על סמך מוצרי נתונים של SAP ERP שמקורם ב-Cortex Framework או על סמך שילוב של מוצרי נתונים של 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
הפעלת סוכני AI
אחרי שיוצרים את מודל הנתונים של תובנות לגבי ביצועי מכירות ב-BigQuery, אפשר ליצור סוכן נתונים בהתאמה אישית. כך תוכלו לשלוח שאילתות לגבי מגמות מכירות ומדדי ביצועים ישירות באמצעות שפה טבעית, בלי שתצטרכו להשתמש ב-SQL מורכב.