매출 실적 통계
이 솔루션 샘플에서는 SAP ERP 또는 SAP BDC 소스 데이터를 사용하여 판매 실적 상태에 대한 통계를 빠르게 얻는 방법을 보여줍니다.
이 기능을 사용하면 다음 작업을 할 수 있습니다.
- 제품, 리전, 판매 조직별로 수익을 최적화합니다.
- 유통 채널 및 부문 전반의 실적을 추적합니다.
- AI를 사용하여 수익과 비용에 영향을 미치는 주문 처리 병목 현상이나 정시 배송 추세를 파악하세요.
참조 아키텍처
개략적인 참조 아키텍처는 이 사용 사례에 필요한 소스별 데이터 제품의 개요를 제공합니다.
필수 데이터 제품
SAP ERP 소스 데이터를 기반으로 빌드하는 경우 다음 Cortex Framework 데이터 제품이 필요합니다.
- 고객
- 판매 문서
- 영업 조직
- 소재
또한 고객은 코드 스니펫에 표시된 것처럼 다른 SAP ERP 기본 테이블(예: KNVV)을 원활하게 결합하여 통계를 개선할 수 있습니다.
SAP BDC 소스 데이터를 기반으로 빌드하는 경우 다음 BDC 데이터 제품이 필요합니다.
- 고객
- 판매 주문
- 영업 조직 구조
- 회사
- Product
코드 스니펫
다음 코드 스니펫은 Cortex Framework 소스 SAP ERP 데이터 제품 또는 SAP BDC 데이터 제품 통합을 기반으로 판매 실적 통계를 위한 BigQuery 데이터 모델을 빌드하는 방법을 보여줍니다.
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을 사용하지 않고도 자연어를 사용하여 판매 추세와 실적 측정항목을 직접 쿼리할 수 있습니다.