Customizable Spreadsheet Template: Marketing Programs Analysis

This model estimates the effectiveness of marketing programs at selling particular products. This capability was only a dream for marketers, until adequate data, lower computing costs, and improved algorithms combined to make this kind of model an extremely useful tool in marketing management. It also includes measures of the profit performance of individual customers or customer groups and products or product groups.

The model can help you to identify top-performing programs that should be expanded, and under-performing programs and need a 'get-well plan'. You should combine insights from the model with insights of field experts to make better decisions about programs. The model has done the deep thinking for you. As a result, the input data and interpretation of results is relatively straightforward.

The key output is an estimate of the 'contribution margin' of each marketing program. (A contribution margin is a form of profit margin where you don't have to account for all the costs in the organization, so it is not an operating margin or net income.) The main steps in the process are:

1. Allocate gross margin (revenue - cost of goods sold) to marketing programs. That is, reward programs for generating gross margin dollars, not revenue, so programs are rewarded more for selling high-margin products. Allocate gross margin from each sales order to specific Programs, Program Events, Lead Events that touch customers who place orders. This is the key step. Definitions:
• A 'Program' is a spending program, such as webinars that promote a particular product.
• A 'Program Event' is part of a Program that occurs on a particular date in a particular location for a particular product line.
• A 'Lead Event' consists of one prospective customer person who attends a Program Event.
2. Allocate expenses to Programs, Program Events, Lead Events using fixed expense and variable expense per Lead Event.
• Allocating program expense to individual programs is usually relatively easy, and data of reasonable quality and completeness is available in most organizations.
• The model allows you to distinguish fixed expense and variable expense.
  • Fixed expense does not change with the number of Lead Events (customer persons who are touched by the program). Examples: development cost of a seminar or webinar.
  • Variable expense changes with the number of Lead Events. Examples: rented space for an event, expense for staff that attend.
• Compute contribution margin (of a Program, Program Event or Lead Event) = allocated gross margin - allocated costs.

The model makes some key assumptions that improve the results.

• How a customer is defined important to the analysis.
• A customer is a decision network that collectively decides to buy a product or service.
• A customer is not generally a person who attends a program event, or who places an order, or who uses the product. (Such a definition would prevent the model from connecting a user of the product who attends a marketing event and a purchasing agent who places the order.)
• In practical terms, we suggest defining a customer to be a unique combination of company name, division, and address. This rule isn't perfect, but it is simple and usually yields good results.
• The time lag between a Lead Event and a resulting sales order at the same customer is in important factor in estimating the impact of the Lead Event on the sale.
• Each type of Program has an effective lifetime. A program gets less credit for an order as the time lag between the Lead Event and the order increases. You can specify the lifetime and how fast the program effectiveness drops off with time.
• The model allocates a fraction of total expense for a Program Event to each time period in wihwch there are some Program Events. The computation resembles depreciation of manufacturing equipment, in which depreciation expense is allocated to each time period during the life of the equipment.
• If a marketing program is not fully 'depreciated' at the end of model time, then some 'book value' remains in the program, and that value is not included in the analysis of what happened during model time.

The key results are collected on worksheet 'Contrib Margin'. Advanced versions include a pivot table of customer information.

We got great results using this method in a company with tens of thousands of marketing events annually, and dozens of products. In companies with marketing programs of this complexity, no experts or small team can carry in their heads all the knowledge that the model can capture. On the other hand, the analysis does not know all the facts that field personnel know, so you should take into consideration both the results of the analysis and expert opinions.