Sales Performance Insights
This solution sample provides an example on how to accelerate insights into sales performance health using SAP ERP or SAP BDC sourced data.
With it you can:
- Optimize revenue by product, region, and sales organization.
- Track performance across distribution channels and divisions.
- Use AI to uncover fulfillment bottlenecks or on-time delivery trends that impact revenue and costs.
Reference architecture
A high level reference architecture provides an overview of the source specific data products required to feed this use case.
Required data products
If building on top of SAP ERP sourced data the following Cortex Framework data products are required:
- Customers
- Sales Documents
- Sales Organizations
- Materials
In addition, customers can also seamlessly combine other SAP ERP base tables,
for example KNVV
and VBAP
, to enhance insights as shown in the code snippets
.
If building on top of SAP BDC sourced data the following BDC data products are required:
- Customer
- Sales Order
- Sales Organization Structure
- Company
- Product
Code snippets
The following code snippet provides an example on how to build a BigQuery data model for sales performance insights on top of Cortex Framework sourced SAP ERP data products or integration of SAP BDC data products.
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
Enable AI agents
After creating your Sales Performance Insights data model in BigQuery, you can build a custom data agent . This lets you query sales trends and performance metrics directly using natural language, bypassing the need for complex SQL.
