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