This article discusses BI approach and provides guidelines on implementing brand and product contribution reporting functionality. It shows why brand contribution is important in evaluating marketing investments, business planning and forecasting in the FMCG industry. The author reviews points important to BI design and concept definition, when consolidating financial, project, and sales data. Technical details are reviewed on implementing financial data split functionality and addressing related performance issues. Concepts of "marketing view" and "market pressure points" are introduced in relation to management and marketing reports.
1. Preword
Once upon a time there was Kingdom X. It was spending zillions of dollars on promoting its products. Although Kingdom’s image was relatively good and the products were quite popular, it was facing a sever pressure from its neighbors (competition). When a new king (CEO) came to power he announced he wants to have a tighter control over Kingdom’s marketing spend.
As marketing budget was the biggest expense and as it was closely linked with new product launches, having a sharper view on it as well as providing better business planning capabilities was essential. This is where Business Intelligence technologies were considered by royal advisors. Specifically, brand contribution forward looking statements, when implemented, were supposed to help the king making better decisions and stay ahead of competition.
Kingdom X was a marketing empire spending over 30% of its revenue on marketing and advertising. Marketing job is to maximize the impact of this spend. The idea is not just to maximize sales volume, but to optimize it, in other words, make growth more profitable.
For growing sales volume in fast moving consumer goods industry it is important to understand growth drivers, analyze marketplace variables, explain impact of seasonality and market condition changes. A big role here can be played by ad-hoc revenue and expense reporting tools that are updated with quality information on a regular basis. This would allow producing a brand/product contribution view.
Brand Contribution is revenue generated by the brand less direct costs associated with it. Direct costs may include manufacturing, sales and marketing costs.
Proper brand contribution reporting besides actual numbers should contain planning data. Ad-hoc tools presenting actual and forecasted numbers for sales, revenue and profitability by product can be used as a starting point in producing forward looking statements.
Depending on incoming data granularity and business needs our brand contribution reporting can be done by trade channel and/or customer. This opens new capabilities to brand managers in making timely adjustments to marketing investments based on changing business results and market conditions.
It is common in today’s corporate world to have multiple platforms and data sources supporting similar business functions. Budgeting and planning may be performed in one environment, transactional data may be handled using one or several ERP applications, and management reporting can be done using a separate set of tools.
To make things simple let us imagine in Kingdom X all transactional data is handled in SAP R/3 or Netweaver. We have all major modules implemented across all geographical regions and we can access the following data sources:
•· Financial (FIGL) data – direct costs and overheads
•· Campaign (PS, MM) data – budgeted and ordered costs for each marketing campaign by brand and period
•· Sales (SD, BPS) data – budgeted and actual sales figures
Here are examples of relevant data flows on the transactional side representing three major business processes:
Every year marketing associates set up a plan for sales volume and marketing spend during business planning process.
Technically, they have to enter marketing project structures (campaigns) in the PS module.
During the year marketing associates make adjustments to sales volume plans.
Previously planned marketing activities are adjusted; budgets are shifted between brands, customers and campaigns.
Finance associates perform regular data entries of actuals on Purchase Orders, Accounts Payable, goods receipt.
Period closing procedures are performed by accounting/finance associates at the end of each month. Special entries are done in SAP PA, PS and BPS related to costing, accruals, budget copy and adjustments to forecasts.
In many cases we face situations when certain reporting functionality is relevant for one business function within an organization and makes absolutely no sense for another one. If we produce a brand split expense report for marketing purposes it definitely cannot be used in financial reporting or statutory accounting.
This is due to the fact that historical data cannot be changed according to GAAP rules, while for marketing purposes we may want to have multiple views on what has happened in the past.
The following chart illustrates how marketing project updates (restatements) impact historical brand split. Each time we allocate costs to a certain project they have to be split according to project structure. When budgets are adjusted brand split allocation changes. While restated view on costs is essential for brand contribution reporting it does not make sense for statutory accounting:
Note: In the scenario proposed in this article restatements are addressed when loading data from the 1st to the 2nd level (see 4.3).
The goal of BI tools is collecting multiple data sources together and presenting information in such a way so that it becomes easy to make tangible business decisions. When we integrate financial, campaigns and sales data we have to come up with a list of common analysis codes (dimensions) that would allow us analyzing consolidated information.
In the case of brand contribution reporting it is recommended to use the following dimensions in the multiprovider:
We can be sure that Time, Geography and to some extent Product breakdown is common for all three data sources (financial, campaigns and sales). According to GAAP rules product or band split for overhead expenses is usually available in PA module as soon as product costing and period closing procedures are executed. However, for marketing reporting GAAP accounting does not always apply.
As for the other dimensions in the multiprovider source data will give us the following level of detail:
- Account Category: available for Financial (FIGL) data
- Project: available for Campaign (PS, MM) + Financial (FIGL) data
- Customer: available for Sales (SD, BPS) data
- Trade Channel: available for Sales (SD, BPS) data
Depending on source data granularity different views on income statements can be produced. Usually we do not get FIGL or PO data broken down by customer or trade channel. Therefore if we do not implement additional transformations we cannot produce a brand contribution view by customer or channel.
In SAP BI it is possible to increase granularity of source data by using a multi layer BI setup. In case brand contribution planning is done by trade channel and/or customer we may want to implement trade channel and/or customer group split for FIGL data. In the multi layer BW architecture we process data using several steps (layers). The following approach can be used in this case:
When moving data from DSO1 to DSO2 implement logic that would split each financial record into several based on certain business requirements, such as sales split for the same day. The idea is that we know sales by product, trade channel and customer group from our SD source. So when processing FIGL data we want to use sales data as a lookup for distributing finance results (costs) over sales analytics (trade channel and customer group) in proportion to products sold during the day.
Similar approach can be implemented when splitting marketing related purchase orders over customers, trade channels or profit centers.
In order to produce a marketing view for financial reports we want to apply market pressure points to direct costs. Market pressure points define how marketing budget for each campaign is spread over brands, periods, etc. The point is that invoices posted in a certain fiscal period may not have impact on the market during that period. However, marketing associates want to see when expenses impact the market and which brands they are related to. Therefore, in order to produce a “marketing view” every invoice or purchase order related to the project has to be “pressurized” according to market pressure points.
An example is advertising air time. If we pay an advertising bill in January, but the ad is going to be aired during the whole year, we want to spread the cost of the campaign over the year according to market pressure points specified in the marketing project (PS). Technically we need to split each record of relevant invoice/order into multiple records, depending on how many pressure points we get from the corresponding project (WBS code). Every generated record is assigned to a proper fiscal period and brand.
As mentioned earlier, “marketing view” is not going to match financial reports and cannot be used for GAAP reporting. It is used purely by marketing associates, helping them to manage marketing investments and campaigns.
Brand contribution reporting functionality can be delivered with the following data flows:
Here are a few points to keep in mind when implementing such scenario:
- 2nd level objects should contain only project relevant details needed at the time these objects are used. For instance, FIGL DataStore object should contain only those expenses that are relevant for brand contribution reporting.
- When applying project structure and sales split in the update rules of FIGL_02 DataStore objects take into account that addressing performance is critical. Heavy lookups may kill the whole concept. Performance issues have to be addressed in the data warehouse design from the very beginning.
- If we have to implement two splits (market pressure points and customer split) make sure you split data in the right sequence. Market pressure points define brand and time for each FIGL record, which are needed for sales data lookup. Therefore, the sequence should be: apply market pressure points first, and then perform customer split.
- Multiprovider has to link details from each infocube at the lowest possible level of detail. For instance, if we deliver customer split with FI data we should link customer code from Actuals infocube with customer code from Sales, etc.
In the described scenario we are facing performance issues when processing millions of FIGL or MM transaction lines over millions of SD or PS lines. In the case of history loads these numbers could be billions.
How do we make sure the split and lookup logic is going to work? Addressing performance in the backend data processing is extremely important! Here are a few tips on how you may want to address performance issues when implementing heavy lookups:
- Make sure you process only relevant records (FIGL or MM): filter out all items that are not in the project scope. Keep only direct expenses that you want to be used in brand contribution reports.
- Set up proper indexes in the DataStore objects that are used as lookups.
- Make sure internal tables filled from lookup DataStore objects contain only relevant records and are properly indexed.
- In case DataStore object indexes do not help improving performance or when indexes are not allowed due to various reasons we may want to implement “intelligent lookup tables” approach. (For more details see: Optimize FIGL Processing with Intelligent Lookup Tables and Pseudo Delta Loads)