A Calculations
This appendix provides additional information on a few of the core retail calculations to help aid your understanding. It is important to understand that a particular measure can be calculated in many ways because it is impacted by many different input measures. When a value is changed that impacts the other editable values, the impacted measures that then need to be recalculated are protected so that you cannot also update the values. This prevents deadlock or circular calculation in which you do not know where to start recalculating.
Calculations on Scorecards
The following table lists the calculations used in Scorecards.
ORCE Feature | Location | Tile/Data Field | Calculation |
---|---|---|---|
Promotion Scorecard |
revenue LY (tile) |
promotion performance |
SUM(CMP_PROMO_DEAL_RESULT.DIRECT_REVENUE) |
average transaction (tile) |
|||
average units (tile) |
TABLE: CMP_PROMO_TRAN_RESULT |
||
direct revenue |
SUM(TRAN_TOTAL - INDIRECT_REVENUE) |
||
indirect revenue |
SUM(INDIRECT_REVENUE) |
||
total revenue |
SUM(TRAN_TOTAL) |
||
Cost of goods sold |
SUM(COST_OF_GOODS_TOTAL) |
||
markdowns |
SUM(MARKDOWN_TOTAL) |
||
awards |
Number of Award accounts (AWD_AWARD_ACCT.ACCOUNT_ID) multiplied by the award certificate amount (CMP_PROMO_AWARD_CERTIFICATE.AMOUNT) |
||
gross profit |
SUM(TRAN_TOTAL) -SUM( COST_OF_GOODS_TOTAL) |
||
margin % |
gross profit / SUM(TRAN_TOTAL) * 100) |
||
promotion cost |
SUM(CMP_OFFER.FIXED_OFFER_COST) + SUM(COST_PER_THOUSAND) + (Sum of all associated CMP_PROMO_COST.AMOUNT) |
||
net profits |
gross profit - promotion cost |
||
Promotion Cost per Customer per audience |
promotion cost / total number of customers in target audience (CMP_CUST_PROMO_TARGET) |
||
Promotion Cost per responded customer |
promotion cost / total number of customers in target audience that have responded (CMP_CUST_PROMO_TARGET where CUST_RESPONDED_FLAG = 1) Note: If Target name is null or “Unidentified", the response will include a count of the distinct transactions from the CMP_PROMO_TRAN_RESULT table for the promotion. |
||
|
Revenue per customer in audience |
direct revenue / total number of customers in target audience (CMP_CUST_PROMO_TARGET) (For Points promotions this will be always be 0) |
|
Revenue per Responded Customer |
direct revenue / total number of customers in target audience that have responded (CMP_CUST_PROMO_TARGET where CUST_RESPONDED_FLAG = 1) Note: If Target name is null or "Unidentified", the response will include a count of the distinct transactions from the CMP_PROMO_TRAN_RESULT table for the promotion. (For Points promotions the INDIRECT_REVENUE is used in the calculation) |
||
Promotion Scorecard continued |
average units (tile) continued |
Net Profit per customer in audience |
net profits / total number of customers in target audience (CMP_CUST_PROMO_TARGET) |
Net Profit per responded customer |
net profits / total number of customers in target audience that have responded (CMP_CUST_PROMO_TARGET where CUST_RESPONDED_FLAG = 1) Note: If Target name is null or "Unidentified", the response will include a count of the distinct transactions from the CMP_PROMO_TRAN_RESULT table for the promotion. |
||
average transaction (tile) coupon promotion |
value of discount |
SUM(CMP_PROMO_DEAL_RESULT.TOTAL_DISCOUNT) for the coupon code, Promotion and target |
|
average sales by coupon |
SUM(CMP_PROMO_DEAL_RESULT.DIRECT_REVENUE) / Count of distinct RTL_TRAN_ID (Transaction Ids) in CMP_PROMO_DEAL_RESULT for the Coupon Code, Promotion and Offer. |
||
coupon revenue |
SUM(CMP_PROMO_DEAL_RESULT.DIRECT_REVENUE) for the Coupon Code, Promotion and Offer. |
||
Estimated distribution |
CMP_PROMODEAL_COUPONCODE.ESTIMATED_DISTRIBUTION (If CMP_PROMO.COUPON_SERIALIZED_FLAG is set, then CMP_PROMODEAL_COUPONCODE.COUPON_GEN_COUNT is used instead) |
||
response % |
(Count of distinct customers in CMP_PROMO_DEAL_RESULT for the coupon code, promotion, offer and target / Estimated distribution) * 100 |
||
points for purchase |
loyalty points tile |
SUM(NUM_PTS) from LYL_LOYALTY_ACCT_ACT for the promotion |
|
average points per transaction |
SUM(CMP_PROMO_DEAL_RESULT.LOYALTY_POINTS) / Count of distinct CMP_PROMO_DEAL_RESULT.RTL_TRAN_ID (Transaction Ids) |
||
response rate (tile) |
total |
COUNT of distinct Customer Ids in CMP_CUST_PROMO_TARGET for the promotion and target. |
|
sent |
COUNT of CMP_CUST_PROMO_TARGET records where the EMAIL_DELIVERED_FLAG is set for the promotion and target. |
||
opened |
COUNT of CMP_CUST_PROMO_TARGET records where the EMAIL_OPENED_FLAG is set for the promotion and target. |
||
clicked |
COUNT of CMP_CUST_PROMO_TARGET records where the EMAIL_CLICKED_THROUGH_FLAG is set for the promotion and target. |
||
undeliverable |
COUNT of CMP_CUST_PROMO_TARGET records where the EMAIL_UNDELIVERED_FLAG is set for the promotion and target. |
||
Net targeted |
total - undeliverable |
||
net delivery |
sent - undeliverable |
||
respondents |
COUNT of distinct Customer Ids in CMP_CUST_PROMO_TARGET where the CUST_RESPONDED_FLAG = 1 for the promotion and target. |
||
Promotion Scorecard continued |
response rate (tile) continued |
non- respondents |
total - respondents |
net response rate |
respondents / net delivery |
||
Response rate |
respondents / total |
||
award value |
"For points promotions - Sum of the number of points (NUM_PTS) from Loyalty Account Activity (LYL_LOYALTY_ACCT_ACT) for the promotion and target. For other promotions: Award Certificate amount multiplied by the number of accounts linked to the promotion and target." |
||
redeemed award value |
award value - remaining value (which is the sum of awd_award_coupons.amount for the award accounts linked to the promotion and target) |
||
Offer Scorecard |
Revenue LTD (tile |
direct revenue |
"SUM(CMP_PROMO_DEAL_RESULT.DIRECT_REVENUE) for the offer For Points Promotions, this value is always 0." |
last month revenue |
This value is the total direct revenue as calculated above for the duration selected on the scorecard. |
||
response rate |
respondents / Count of distinct customers from CMP_CUST_PROMO_TARGET for the promotion and offer |
||
respondents |
Count of distinct customers from CMP_PROMO_DEAL_RESULT for the offer. |
||
|
Average Unit Retail (tile) |
Direct Revenue |
"SUM(CMP_PROMODEAL.DIRECT_REVENUE) For Points Promotions - Total Points = SUM(CMP_PROMODEAL.LOYALTY_POINTS) " |
total cost of goods sold |
SUM(TRN_LINE_ITEM.ITEM_UNIT_COST) where VOID_FLAG=0 AND LINEITM_TYPCODE = Sale AND CMP_PROMO_DEAL_RESULT record exists for the transaction and offer |
||
total discounts |
SUM(CMP_PROMO_DEAL_RESULT.TOTAL_DISCOUNT) for the offer |
||
average units sold |
SUM(TRN_LINE_ITEM.ITEM_QUANTITY) where VOID_FLAG=0 AND LINEITM_TYPCODE = Sale AND CMP_PROMO_DEAL_RESULT record exists for the transaction and offer / Count of distinct RTL_TRAN_ID (Transaction Ids) from the CMP_PROMO_DEAL_RESULT table for the offer. |
||
margin % |
(net profit / direct revenue) * 100 |
||
net profits |
direct revenue - total cost of goods sold |
||
Segment Scorecard |
stratified, unstratified, personal list |
customer in segment |
Count of CUST_ID from CST_CUSTOMER joined to QRY_USER_QUERY and QRY_CUSTOMER_SEGMENT tables for the segment |
average value |
AVG(TOTAL_SALES_AMT) from CST_CUSTOMER joined to QRY_USER_QUERY and QRY_CUSTOMER_SEGMENT tables for the segment |
||
average days since purchase |
AVG(Current date - CST_CUSTOMER.LAST_TRANS_DATE) joined to QRY_USER_QUERY and QRY_CUSTOMER_SEGMENT tables for the segment |
||
Segment Scorecard continued |
stratified, unstratified, personal list continued |
average purchases LTD |
AVG(CST_CUSTOMER.TOTAL_TRANS_COUNT) joined to QRY_USER_QUERY and QRY_CUSTOMER_SEGMENT tables for the segment |
LTD Spend |
AVG(CST_CUSTOMER.TOTAL_SALES_AMT) joined to QRY_USER_QUERY and QRY_CUSTOMER_SEGMENT tables for the segment |
||
YTD Spend |
AVG(CST_CUSTOMER.YTD_SALES_AMT) joined to QRY_USER_QUERY and QRY_CUSTOMER_SEGMENT tables for the segment |
||
Program Home |
all active program |
Count of total programs in ACT_PROGRAM where the ACTIVE_FLAG = 1 |
|
active tender outstanding balance |
SUM(SVC_TENDER_ACCT.BALANCE * EXCHANGE_RATE obtained from DRV_CURRENCY_EXCHANGE_RATES) for all SVC_TENDER_PROGRAM records |
||
|
active award certrificates/value |
SUM(AWD_AWARD_COUPONS.AWARD_AMOUNT * EXCHANGE_RATE obtained from DTV_CURRENCY_EXCHANGE_RATES) from AWD_AWARD_ACCT (Award accounts) where the EXPIRATION_DATE is null or greater than/equal to the current date. |
|
active loyalty oustanding points |
SUM(EARNED_PTS_BALANCE) from LYL_LOYALTY_ACCT (Loyalty accounts) |
||
Promotion Home |
promotion revenue LTD |
SUM(CMP_PROMO_TRAN_RESULT.TRAN_TOTAL) for all promotions that are active on the current date and not terminated. |
|
offer revenue LTD |
SUM(CMP_PROMO_DEAL_RESULT.DIRECT_REVENUE) for all active promotions on the current date. |
||
Segment Home |
total active |
Count of QRY_USER_QUERY (segment) records where there is a Job associated (job_job_def) that has the enabled_flag set. |
ORCE Feature | Location | Tile/Data Field | Calculation | Metric/Graph |
---|---|---|---|---|
Tender Program Scorecard |
accounts analysis (toggle) |
active account rate |
The percentage of currently active accounts out of the total number of cards. |
metric |
|
|
current active accounts |
The total number of accounts currently active for the program. |
metric |
|
|
total cards |
The total number of cards in all of the cards series for the program. |
metric |
|
|
card series |
The number of card series for the program. |
metric |
|
|
active accounts |
The number of active accounts by month. |
graph |
|
activity analysis (toggle) |
issued value LTD |
The total value issued lifetime to date to all active account holders. This includes the activation amount, recharge amount and any amounts returned to the account. |
metric |
|
|
issued value MTD |
The total value issued month to date to all active account holders. This includes the activation amount, recharge amount and any amounts returned to the account. |
metric |
|
|
redeemed value LTD |
The total value redeemed lifetime to date for all active account holders. This includes the cash out amount, post authorize amount, redeemed amount and service charge amount. |
metric |
|
|
redeemed value MTD |
The total value redeemed month to date for all active account holders. This includes the cash out amount, post authorize amount, redeemed amount and service charge amount. |
metric |
|
|
outstanding balance |
The total value issued that has not yet been redeemed for all active account holders. |
metric |
|
|
average balance per account |
The average balance held on account for all active account holders. This is calculated by dividing the total outstanding balance by the total number of active accounts. |
metric |
|
|
issued value |
The total value issued by month. |
graph |
|
|
redeemed value |
The total value redeemed by month. |
graph |
|
|
outstanding balance value |
The total oustanding balance for all active account holders by month. |
graph |
Award Program Scorecard |
analysis |
redemption rate LTD |
The percentage of lifetime to date awards redeemed from those that have been issued. |
metric |
|
|
outstanding e-award value |
The total value of e-awards available for redemption. |
metric |
|
|
customers in program |
The percentage of customers that are active members for the program. This includes both primary and secondary account holders. |
metric |
|
|
current active accounts |
The total number of currently active accounts. This includes registered and anonymous accounts. |
metric |
|
|
registered accounts total |
The total number of accounts that are registered to customers and are not anonymous for the program. |
metric |
|
|
registered accounts percentage |
The percentage of active accounts that are registered to customers compared to all accounts (registered plus anonymous) for the program. |
metric |
|
|
issued awards |
The total number of award certificates and e-awards issued by month. |
graph |
|
|
issued award certificates |
The total number of award certificates issued by month. |
graph |
|
|
issued e-awards |
The total number of e-awards issued by month. |
graph |
|
|
redeemed awards |
The total number of award certificates and e-awards redeemed by month. |
graph |
|
|
redeemed award certificates |
The total number of award certificates redeemed by month. |
graph |
|
|
redeemed e-awards |
The total number of e-awards redeemed by month. |
graph |
|
|
outstanding awards |
The total number of award certificates and e-awards issued but not yet redeemed by month. |
graph |
|
|
outstanding award certificates |
The total number of award certificates issued but not yet redeemed by month. |
graph |
|
|
outstanding e-awards |
The total number of e-awards issued but not yet redeemed by month. |
graph |
|
|
outstanding e-award balance |
The total value of e-awards available for redemption by month. |
graph |
Loyalty Program Scorecard |
accounts analysis (toggle) |
customers in program |
The percentage of customers that are active members for the program. This includes both primary and secondary account holders. |
metric |
|
|
current active accounts |
The total number of accounts currently active for the program. |
metric |
|
|
registered accounts total |
The total number of accounts that are registered to customers and are not anonymous for the program. |
metric |
|
|
registered accounts percentage |
The percentage of active accounts that are registered to customers compared to all accounts (registered plus anonymous) for the program. |
metric |
|
|
newly registered accounts |
The total newly registered accounts by month. The total can be filtered down by loyalty level. |
graph |
|
|
total registered accounts |
The total registered accounts by month. The total can be filtered down by loyalty level. |
graph |
|
accounts analysis (toggle) accounts summary |
percent of program |
The percentage of all active accounts in the loyalty program level compared to the entire program. This includes both registered and anonymous account holders. |
accounts summary |
|
|
points issued |
The total number of lifetime to date points issued to all active account holders for the loyalty level. This includes both earned and bonus points. Escrow points are excluded. |
accounts summary |
|
|
points redeemed |
The total number of lifetime to date points redeemed by active account holders for the loyalty level. |
accounts summary |
|
|
members who received points |
The total number of unique customer ID's associate with a points issue transaction for the loyalty program level. This includes both primary and secondary account holders. |
accounts summary |
|
|
average points balance |
The average points balance for all active accounts for the loyalty program level. This includes both registered and anonymous account holders. This is calculated by dividing the total earned points balance by the total number of active accounts. |
accounts summary |
|
activity analysis (toggle) |
points issued LTD |
The total number of lifetime to date points issued to all active account holders for the loyalty level. This includes both earned and bonus points. Escrow points are excluded. |
metric |
|
|
points issued MTD |
The total number of month to date points issued to all active account holders for the loyalty level. This includes both earned and bonus points. Escrow points are excluded. |
metric |
|
|
points redeemed LTD |
The total number of lifetime to date points redeemed by active account holders for the program. |
metric |
|
|
points redeemed MTD |
The total number of month to date points redeemed by active account holders for the program. |
metric |
|
|
outstanding points |
The total number of points issued that have not yet been redeemed. This includes both earned and bonus points. Escrow points are excluded. |
metric |
|
|
average balance per account |
The average points balance for all active accounts for the loyalty program. This includes both registered and anonymous account holders. This is calculated by dividing the total earned points balance by the total number of active accounts. |
metric |
|
|
issued value |
The total points issued by month. The total can be filtered down by loyalty level. |
graph |
|
|
redeemed value |
The total points redeemed by month. The total can be filtered down by loyalty level. |
graph |
|
|
outstanding balance value |
The total points issued but not yet redeemed by month. The total can be filtered down by loyalty level. |
graph |
|
activity analysis (toggle) accounts summary |
percent of program |
The percentage of all active accounts in the loyalty program level compared to the entire program. This includes both registered and anonymous account holders. |
accounts summary |
|
|
points issued |
The total number of lifetime to date points issued to all active account holders for the loyalty level. This includes both earned and bonus points. Escrow points are excluded. |
accounts summary |
|
|
points redeemed |
The total number of lifetime to date points redeemed by active account holders for the loyalty level. |
accounts summary |
|
|
members who received points |
The total number of unique customer ID's associate with a points issue transaction for the loyalty program level. This includes both primary and secondary account holders. |
accounts summary |
|
|
average points balance |
The average points balance for all active accounts for the loyalty program level. This includes both registered and anonymous account holders. This is calculated by dividing the total earned points balance by the total number of active accounts. |
accounts summary |