Using historic data to improve planning and forecasting

1. Using historic data to improve planning and forecasting TFM&A 2014 David Lockwood: Direct Wines Terry Hogan: Golden Orb

2. Context • UK planning based on multiple linked spreadsheets • Other countries followed different approaches • Budget process took up a lot of resource • No single reporting tool or data warehouse • UK business on different systems from the rest of the world • Assembling data for marketing meetings very time-consuming • Forecasting continuity sales cumbersome and inflexible • Different terminology/KPIs in use in different parts of the business • In some cases the same term being used to mean something different in different countries • Decided to build a global marketing system to support marketing across all countries

3. Outline – system fundamentals A single system to store and report on past campaigns • Automatic feeds from UK and international transactional systems One system for reporting and planning future campaigns • Learnings from past campaigns feed into planning future ones Shared database rather than Excel • Single version of the truth • One set of definitions, terminology • Standard, automated methodology for calculating/forecasting

4. Modules

5. Campaign-level sales reporting Gross/net Orders – by day, order channel, response code... Comparison with budget/ plan Detailed campaign financials Numbers split out in various ways • Response code • Test • List/publication Products sold

6. Time-based crosstab reporting Aggregates the results of multiple campaigns over time Can split out results across several dimensions Typical ‘dimensions’ for reporting Campaign Response code attributes (activity, media type, list...) Time – various levels Order channel Ability to view top-level numbers and then to drill into the detail

7. Sales reporting - considerations Need a feed of order information from transactional systems • Orders/revenue • Product costs • Aggregate numbers by e.g. response code, day • Doesn’t need details of individual orders or customers Probably a daily feed needed for campaign sales (overnight) • Other data can be updated/stored weekly Don’t over-complicate or request too much detail • There are always trade-offs between detail and performance • Different levels of detail are appropriate for different measures

8. Campaign planning: summary of the process VolumeXResponse rate % Cases per order P&P, AOV and product cost Existing customer% Response curve & channel split Phasing Orders Cases Revenue/margin Recruits Application of direct/indirect fulfilment and marketing costs gives net contribution To phase the sales forecast over time, you need to build a sales profile/response curve

9. Building a response curve Choose a number of representative campaigns Express each response code’s sales in terms of days after start date Calculate the overall percentage of orders received by day 1, 2, 3... Build different curves for different campaign types and media Requires good data, correct campaign details

10. Phasing the campaign forecast Apply sales profile to the top-level campaign numbers Plus order channel split if relevant For Direct Wines, we generate a daily sales forecast by order channel The system can build an aggregate curve if the start dates are different Unless you have a very simple product range, this sort of tool is not appropriate for detailed product planning Primarily a tool for planning marketing activity

11. Reforecasting a campaign after it has started The response curve can be applied to the actual sales to date to create a revised forecast of final sales Can apply a different percentage to orders through each order channel Ideally needs to be done every day for each response code and order channel Day-of-week adjustments may be needed for an accurate reforecast Changes in forecast flow through to customer service, merchandising, finance

12. Modelling non-campaign sales Direct Wines have a continuity business in addition to ‘standard’ customer marketing The continuity business generates sales without an associated ‘campaign’ that needs planning Sales to existing continuity members can be modelled on the basis of their existing memberships We can start to forecast continuity back-end sales when planning recruitment or ‘upgrade’ campaigns The main continuity forecast is rebuilt weekly However changes to recruitment or upgrade campaigns have an immediate knock-on effect

13. Continuity modelling A complex area – the full details are beyond the scope of this talk Continuity behaviour is quite predictable over time for a reasonably large group of customers Simple approach – apply a curve to the initial recruits Two enhancements: • Actions at cycle n depend on actions at cycle n-1 • For current cycle, we can use the actions received to date to adjust our estimate

14. Thank You www.golden-orb.ltd.uk @GoldenOrbLtd