Campaign Development Lifecycle Reporting

Marketing activity needs to be measurable!

Control Groups

Every campaign needs to have clearly identified goals, and an expected rate of response. The difficulty in measuring the outcome of any marketing activity is that customers are dynamic. They will do things anyway. This is often referred to as natural buy rate, natural attrition and so on. The point is that whether or not you chose to undertake a marketing campaign, a certain number of your target market may naturally exhibit your desired response anyway (e.g. natural buy rate). The way to overcome this problem is to hold-out a representative sample of your target group and measure their natural response rate. We then hold this group aside and don’t offer them anything to see what they would do anyway. If you then compare this hold-out (control) group against the offer group you will be able to determine the effect of your marketing effort. This measurement is the response rate over control, also referred to as uplift above control.


Cell Total Responders Response Rate Uplift Incremental Responders
Control 2000 34 1.7%
Mailed 10000 450 4.5% 2.8% 280


Choosing the correct size of a control group

Control group sizing is a very difficult task to get right, but it needn’t be. The most important thing to remember about control groups is that they are mandatory. You should always take a control group for every marketing campaign (as opposed to service/informational or legal campaigns – where they aren’t necessary). If you are making an offer then the effectiveness of the offer needs to be measurable. Correct control group sizing is a function of size and response rate. As long as you have a rough mailing volume and an expected response rate you should be able to size your control group successfully to ensure statistical significance. In the link below is an Excel document created by a colleague in the Analytics department at ANZ – Lyndon D’Arcy (Feel free to use and distribute it but keep giving him the credit!).

Statistical Significance Calculator
Click to Download


Statistical Significance

Statistical significance is the probability that an effect is not due to just chance alone. The calculation below tells us that there is a 95% confidence that the ‘real’ result lies within that range. As well as showing uplift to stakeholders you should also include statistical significance to prove that the result hasn’t occurred due to chance alone. You should aim to include a measure of the confidence intervals with all reporting. Please see the table below for a graphic representation of the calculation. As long as the upper and lower limits don’t overlap between the mail/control cells then the result is deemed to be statistically significant with 95% confidence.

95% Confidence Interval
p= response rate n= cell size 1-p SE(P) 1.96*SE(P) Lower Limit Upper limit
Mail 4.50% 10,000 0.955 0.002073041 0.004063161 4.09% 4.91%
Control 1.70% 2,000 0.983 0.002890588 0.005665553 1.13% 2.27%


Below is another useful Excel Spreadsheet. It has pre-built calculations which allow you to determine the confidence interval for a campaign result.
All you need are Total Volumes for Mail and Control cells as well as their respective Response Rates. The formulas within the spreadsheet will advise if your campaign has significance at the 95%, 90% and 80% Confidence Levels.

How to Determine Statistical Significance Confidence Level for Reporting Purposes
Click to Download


“Anecdotal evidence is based on individual accounts, rather than on reliable research or statistics, and so may not be valid” Collins Online Dictionary

Quick TipDon’t allow “Anecdotal evidence” to be a term used during your campaign review sessions. Anecdoctal evidence is generally a random example that someone uses to justify a marketing campaign. It might have happened anyway and should not be the basis for justifying the continuation of a campaign that is statistically proven to be failing. Stick with the facts, you will earn greater respect this way.

Return on Marketing Investment (ROMI)

Return on marketing investment (ROMI) is the contribution attributable to marketing (net of marketing spending), divided by the marketing ‘invested’ or risked. There are various approaches based on the type of product being sold and whether or not there was already existing value before the offer was made. For example, did the customer already have a product and we are trying to offer them a more superior more expensive product? If so we need to remove the value of the existing product when we determine how effective the marketing offer was. ROMI normally results in a simple index measuring the dollars of revenue for every dollar of marketing spend (see image below).

Return on Marketing Investment (ROMI) Calculation


Where…  Incremental value produced by a campaign = (Value produced by a campaign) – (Value we would produce if we didn’t run a campaign)


EG IconCalculating the value if we have a campaign that sells a new product or service to an existing customer.

Am image showing how to calculate the value of a new product/service sale.


EG IconCalculating the value if we have a campaign that attempts to move a customer from an existing product/service to a higher-value product or service.

How to calculate the value when moving to a higher value product or service within the same category.


Building Reports

It always amazes me how some organisations have coped without even basic reporting for their campaigns. How do they justify their budgets? How are they measured in performance reviews? Reporting should be the cornerstone of the campaign management function. It is one of the few business activities that can scientifically prove it’s worth. If you have a respresentative control group and a statistically significant result, as well as meeting the targets and objectives of the campaign – then you have succeeded!

Develop your own reporting capability using Excel

If you want to design and build your own reporting it is possible to do with Microsoft Excel. You will need to have a power-user who understands how to use advanced Excel formulas as well as someone who thoroughly understands your campaign data. At a very high level you would follow these steps:

  1. Create History and Response tables. These tables should include key data – customer_id, date, campaign_code, offer_code (can be used for control group), etc.
    Note:If using campaign management software you should be able to use the built-in tables instead of creating your own.
  2. Create table indexes, housekeeping routines and checks. These steps are related to efficient processing and correct data capture (e.g. checking for duplicate records).
  3. Automatic population of records using the Campaign Management tool (e.g. IBM Unica Campaign). After a campaign has run using the campaign management software you should automatically log both those selected for mail/control cells (to promotion_history), and those who have met response criteria (to response_history).
  4. Build a separate report for every campaign in Excel. Excel allows you to set up ODBC data connections with your marketing database. Set up these connections with SQL pass-through queries to the data held on the server-side database. Use VBA to save the Excel reports as values only – your reports should only hold summary data, no individual customer data should be available in the reports. This means that the reports will still small in size and can be safely distributed to stakeholders.
  5. A Dashboard (again in Excel) can be built to display everything in a simple and clear format with traffic lights (using conditional formatting) for campaign status.
  6. An Access database can be used to create code that logs and updates all reports, including refreshing all Excel reports automatically against updated information on the server. It also saved the reports to SharePoint and distributed an email to stakeholders saying that reports where ready.

This is an involved piece of work, but it is imperative that reporting is available for all campaigns and updated at regular and reliable intervals. Without reporting you will continue to run campaigns that simply don’t make economic sense. A dashboard makes it easy for management to decide which campaigns stay, which go and which need refinement. It is also very easy to have discussions around campaign performance when the dashboard is presented on an overhead projector.

Below is a screen print of one of the reporting dashboards that I have developed. Most campaigns ran monthly and the dashboard summarised the results to provide a financial year-to-date view. A link within the dashboard enabled individual campaign reports to be launched in another reporting spreadsheet. Individual reports went down to campaign cell/offer level, thus providing the results for A/B testing, etc. Key metrics were tested by rules and conditional formatting applied if campaigns weren’t meeting their objective (bold and red for negative uplift).

An image showing a Reporting Dashboard in Excel

The Basics

You don’t need to get this involved straight away, but ensure that you do have the following key information and measures as soon as possible:

  • Campaign Name and Description
  • Run Date
  • Frequency
  • Cell/Offer
  • Total Contacted
  • Total Control Group
  • Uplift over control
  • Incremental Responders/Accounts as a result of the campaign


A dashboard is a visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. Dashboards have three main attributes:

  • Dashboards are typically graphical in nature, providing visualizations that help focus attention on key trends, comparisons, and exceptions.
  • Dashboards often display only data that are relevant to the goal of the dashboard
  • Because dashboards are designed with a specific purpose or goal, they inherently contain predefined conclusions that relieve the end user from performing his own analysis (source)

Quick TipIf you want to try and develop your own dashboards in Excel and you are relatively proficient with the use of Excel already then you should consider getting hold of a copy of Dashboards and Reports for Dummies. The copy that I used was for Excel 2007 but there are probably more recent versions. The key to a lot of the dashboarding functions are some little known Excel formulas – Vlookup, HLookup, Offset, SumIf, Average, etc. Try looking into these formulas a little more to see what they do and how you can use them.

Reporting Software

Nowadays most campaign management software has some type of bundled reporting (e.g. see IBM Campaign Example hereOpens in a new tab). Which should theoretically save you from having to build your own reports.

Some things that you will need to consider:

  1. A data aggregation layer will be required for reporting purposes
  2. Do you have the required infrastructure to support it? Is a new reporting database and server required? Will all departments be able to access the information (browser based capability)?
  3. Do we have IT support available? Does the IT team have the skills and capability to support this requirement? What will it cost? What is the up-time?

If the above points are covered then the reports themselves are very powerful and allow not only smooth and professional charts and graphs, but drill-down capability as well. One piece of software that I have heard a lot about is called QlikView (Opens in a new tab), which comes highly regarded by colleagues.

Phase 10. Discussion of Outcomes