Client
Our client is a provider of subscription television, internet and mobile services. They have an international reach totalling over 22 million customers, and a history of developing innovative products and entertainment content.
Purpose
The client required a campaign effectiveness tool to assess the benefits of varying product offer combinations across different routes to market. The key requirements were to;
- Leverage existing customer data to base the forecast of future behaviour, informing customer decay curves and product attachment rates,
- Capture all associated costs and revenues associated with the forecasted customer movements,
- Combine the resulting cash flows over time and condense into NPV, IRR and payback period KPIs,
- Translation of forecasted results into a P&L view,
- Provide the ability to compare and contrast the impacts of differing campaigns.
Approach
Working closely with the finance team we were able to capture a comprehensive picture of associated costs and revenue for varying routes to market and product holdings. Using SQL to query data from internal databases we compiled a rich dataset, which allowed us to apply the appropriate customer behaviours based on offer depth/duration and route to market. A campaign capture element allowed quick and easy negotiation and comparison between different offer sets and their resulting KPIs, both at customer and estate P&L level.
Outcome
Completion of the tool allowed the finance team to appraise and optimise their campaign line-up, having confidence that the forecast results are founded in actual customer level data. The layout of the tool and the dashboards combined an easy to use interface with a powerful tool and ability to have oversight with a high degree of granularity.
The key outcomes:
- Live data connection to a database allowing instant refresh and the ability to draw on 3 years of customer history for each estate, with product holding and offer value KPIs,
- Users can slice data to look at particular combinations of offer holdings and their offer status,
- Campaign offers were assessed by NPV, IRR and payback, relative to a scenario chosen by the user,
- Multiple offers can be stored, overwritten and reinstated using macro controls. Multiple offers are then brought together in an output that helps the user select a group of offers based on a selected outcome.
