Intelligent Enterprise

Better Insight for Business Decisions

RSS
Webcasts
Whitepapers
Subscribe
Home

February 21, 2002

Marketing Calculations

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

Erik Thomsen

Although both sales and marketing expenses vary along product, geography, and time dimensions, they frequently originate from different sources. Sales typically originate with point-of-sale (POS) transactions, while marketing expenses may originate at multiple levels within an organization (such as budgeted line items for product, brand, and regional managers).

Properly apportioning marketing expenses by product sales therefore requires a combination of aggregations and allocations. In other words, for each dollar's worth of product sold per store per time, determine how much total money was spent on marketing.

This installment, based on the recently finished manuscript of my new book on OLAP to be published by John Wiley & Sons, takes you through some of the main steps and associated reasoning involved with calculating marketing expenses by product, time, and geography. The context for this installment is a fictitious company called Food Cakes International (FCI), a large multinational producer of vegetarian and fish cakes and two of its analysts, Lulu and Thor. FCI has traditionally engaged in marketing at a brand, product line, and store level.

Assume a time dimension composed of days, months, and quarters, a geography dimension composed of store, region, and country levels, and a product dimension composed of individual products, product categories, and brands. Assume also the following sales and marketing (S&M) schema:


[S&M] SCHEMA:
(TIME. OX GEOG. OX PRODUCT.)
TOTAL_REVENUE{$}

"How do you want to load in the marketing expense data?" Lulu asked Thor. "Should we create a distinct variable for each kind of marketing expense, or should we load all the different marketing expenses into a single 'marketing expense' variable? What do you think? How would you decide?"

Thor responded that his decision was based on whether there would be any occasions when the different marketing expenses would not aggregate together. If the brand, product line, and store marketing expenses always sum together — whether for aggregation purposes or for allocation to the product level — then he would prefer to load all the marketing expenses into a single variable.

"But what about differences in measurement processes?" said Lulu. "What if the way that FCI calculates brand-marketing expenses makes certain assumptions that aren't made for product-line marketing expenses?" Thor replied that any necessary annotations could be made within a single variable on a location-range-by-location-range basis. "The central issue," continued Thor, "is whether the values for each of the marketing expenses can be freely combined. Are total marketing expenses equal to the sum of brand, product line, and store marketing, yes or no? If the answer is no, then whatever transformations need to occur to enable the aggregation of expense data need to occur regardless of whether the expense data resides in a singe variable or in three variables." Do you agree with Thor?

Lulu didn't. "You're missing something, Thor. You're going to have problems aggregating if you map all the input data to a single variable. Let's assume that you did load all the inputs into different location ranges of a single variable:


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

"Now when it comes to aggregating, you might try writing a formula like the following:


"MARKETING EXPENSES" ,
GEOG.STORE.ABOVE ,
TIME.MONTH.ABOVE ,
FOODCAKE.(VEGETARIAN + FISH).
ATABOVE = 
SUM( MARKETING EXPENSES, 
GEOG.STORE., TIME.MONTH., 
FOODCAKE.(VEGETARIAN + FISH).)

"The problem is that you can't really aggregate the variable 'Marketing expenses' because the higher-level inputs, in this case brand, interfere with the aggregation of lower-level data, in this case store and product line. Mapping distinct inputs into differing location ranges of a variable only works when the location ranges are not hierarchically related. Any aggregation function would try to put the value of the sum of store and product line data into a region by all product cell, except that it will find the input value for brand expenses in those cells.

"You could treat the brand-level data as manual overrides of the other expenses, or you could overwrite the brand-level data, but there's no way to combine the store and product line data at the appropriate region by month by 'allproduct' level with the brand data entered there, within a single variable." "You're right, Lulu," Thor conceded.