Intelligent Enterprise

Better Insight for Business Decisions

RSS
Webcasts
Whitepapers
Subscribe
Home

February 21, 2002

/020221/504analytic1_1.jhtml">

Marketing Calculations

Understanding the relationships among data will help you properly apportion marketing expenses

By Erik Thomsen

Continued from Page 1

"So let's do it right this time," said Lulu. "We need to bring in each kind of input data into a separate variable:


"STORE_MARKETING EXPENSES" ,
GEOG.STORE. ,
FOODCAKE.ALL ,
TIME.MONTH. <<
[INPUT FROM STORE BY MONTH 
STORE MARKETING DATA]
"BRAND_MARKETING EXPENSES" ,
GEOG.REGION. ,
TIME.MONTH. ,
FOODCAKE.ALL <<
[INPUT FROM REGION BY MONTH 
BRAND MARKETING DATA]
"PRODUCT LINE_MARKETING EXPENSES" ,
GEOG.REGION. ,
TIME.MONTH. ,
FOODCAKE.(VEGETARIAN + FISH).
<<
[INPUT FROM REGION BY MONTH 
PRODUCT LINE MARKETING DATA]

"Then we need to combine the variables. The simplest way is to define the aggregations for each variable separately and then combine the variables at any aggregation level through summation:


"STORE_MARKETING EXPENSES" ,
GEOG.STORE.ABOVE ,
TIME.MONTH.ABOVE ,
FOODCAKE.ALL = 
SUM( STORE_MARKETING 
EXPENSES, GEOG.STORE., 
FOODCAKE.ALL , TIME.MONTH.)
"BRAND_MARKETING EXPENSES" ,
GEOG.REGION.ABOVE ,
TIME.MONTH.ABOVE ,
FOODCAKE.ALL = 
SUM( BRAND_MARKETING 
EXPENSES , GEOG.REGION. ,
TIME.MONTH.,
FOODCAKE.ALL )
"PRODUCT LINE_MARKETING EXPENSES" ,
GEOG.REGION.ABOVE , 
TIME.MONTH.ABOVE , 
FOODCAKE.ALL = 
SUM( PRODUCT LINE_MARKETING 
EXPENSES , GEOG.REGION. ,
TIME.MONTH. , FOODCAKE.
(VEGETARIAN + FISH).)

"Now we can define the combined marketing expenses as:


"COMBINED MARKETING EXPENSES" ,
GEOG.STORE. ,
TIME.MONTH.ATABOVE ,
FOODCAKE.(VEGETARIAN + FISH).
ATABOVE = SUM ( STORE_MARKETING 
EXPENSES + BRAND_MARKETING 
EXPENSES + PRODUCT LINE_MARKETING
EXPENSES )

ALLOCATING MARKETING EXPENSES TO PRODUCT SALES

"Now we need to allocate all the incurred marketing expenses to the sale of food-cake products," said Lulu. "How would you handle it, Thor?" He replied, "I would like to allocate the combined marketing expenses to the store by product by month level, but I suspect I'm leaving out a step." Do you agree with Thor? What do you think?

Lulu replied, "You're right, Thor. You are leaving something out. And unfortunately we can't reuse our combined marketing variable. The problem with the combined marketing variable is that it, properly, combines product type specific (the product line) and product invariant (the brand and store) marketing expenses. Whereas we need to allocate each of these expenses separately to their respective product categories. We don't want to count fish cake marketing expenses in our calculation of marketing expenses for vegetarian cakes!

"Also, to be clear, given the granularity level of our marketing expense data, I would suggest that we calculate marketing expenses on a per product per product type basis commensurate with the product-line distinctions. Thus, our results will show marketing costs as a percentage of product sales per store averaged by month and by product type — fish cake or vegetarian cake."

Lulu said, "I would suggest the following formula with its (hypothetical) result set" (see Table 1):


MARKETING COSTS AS A PERCENTAGE 
OF PRODUCT SALES ,
GEOG.STORE. ,
FOODCAKE.(FISH + VEGETARIAN). ,
TIME.MONTH =
( STORE_MARKETING EXPENSES/
TOTAL_REVENUE {$}, FOOD-
CAKE.(FISH + VEGETARIAN). )
+
( BRAND_MARKETING EXPENSES,
GEOG.STORE.REGION / 
( COUNT(REGION.DOWN1.) X 
TOTAL_REVENUE {$}, FOODCAKE.
(FISH + VEGETARIAN). ) )
+
( PRODUCT LINE_MARKETING 
EXPENSES , GEOG.STORE.REGION /
( COUNT(REGION.DOWN1.) X 
TOTAL_REVENUE {$}, FOOD-
CAKE.(FISH + VEGETARIAN). ) )

Rate This Article

Comments:

Optional e-mail address:

To make solidly grounded sales and marketing decisions, you need to be able to accurately combine costs from a variety of sources and allocate those costs (which can not always be combined into a single variable) appropriately across product sales.

Erik Thomsen [erik@dimsys.com] is cofounder of Power Thinking Tools, which developed the first OLAP engine with integrated statistics, visualization, text processing, and object management. He is a researcher and consultant for Dimensional Systems and focuses on integrated multitechnology analytic solutions. He is the author of OLAP Solutions (John Wiley & Sons, 1997) and coauthor of Microsoft OLAP Solutions (John Wiley & Sons, 1999).