2 Replies Latest reply on Dec 8, 2013 2:40 PM by erin.barr

    calculating forecast conversion by week

    erin.barr

      I need to forecast conversion from lead to sales and have a conversion distribution applied.  Example:

      I receive 1,000 leads in week 1.  I know after a period of time, I should have 300 Sales (maturity of sales).  it takes upwards of 13 weeks.  Over the course of 13 weeks, the distribution of the 300 sales should be along the lines of:

      WK1 - 11%, WK2 - 12.5%, WK3 - 18%, WK4 - 13%, WK5 - 12%, WK6 - 7%, etc. translating into a projection of 11 sales week 1, 37.5 WK2, 54 WK3, etc.  Therefore, if I received 1,000 leads last week, then I would like to see a projection of 11 sales this week, 37.5 NEXT week, 54 the following week etc.  In addition, I would need to see the "waterfall" since this week would not only consist of the projected sales from leads last week, but also the sum total of the previous 10 weeks projected distributions.  The question - how do I create this calculation that allows me to see those forecasted projections (the breakout of what each week forecasts to be) and line it up with the actuals as they come in to validate the projections.  Please help. Attached is example in Excel - but obviously need to Tableau - thank you for your help in advance.

        • 1. Re: calculating forecast conversion by week
          Prashant Sharma

          Hi Erin,

          From here you can learn forecasting in Tableau: - Forecasting

          From here you can learn waterfall charts in Tableau: - Waterfall Charts | Tableau Software

          I will look into your data & will revert back to you when I will be free.

           

          Warm Regards,

          Prashant Sharma - India | LinkedIn

          • 2. Re: calculating forecast conversion by week
            erin.barr

            Thank you - I've looked in forecasting that you suggested and here's my challenge(s):

            1) The leads that I generate this week will "convert" over the course the next several weeks (therefore, I have two date ranges that I use - the lead added date and the lead converted (sales) date).

            2) From what I can tell from forecasting - it bases the future based on the past with the ability to remove the most recent dates that may not be mature (good).  However, I'm not seeing where you can base a forecast on a calculation.  Meaning - the forecast of sales is directly related to the number of leads.  Unless I'm missing something - I can forecast sales, but that forecast is based on the historical sales - not the number of leads generated to hit those sales.  Meaning - if I generate 1,000 leads per week (based on the earlier example), I can expect 300 sales.  HOwever, those 300 sales aren't realized in total until 10 weeks (fully mature - roughly 90% of all sales accumulate within 10 weeks - distributed within the weeks).

            3)  therefore, unless I'm missing something - the basic forecasting in Tableau won't help.  I need to apply the "mature" conversion of leads to sales (in this example, 30%) for the leads that occur in a given week.  Then, distribute those 300 sales across the next 10 weeks utilizing a distribution curve identified in the historical conversion & distribution rates for the upcoming 10 weeks.  Since any given week for sales is based on previous 10 weeks lead volumes, their respective 'maturity' conversion rate, and subsequent distribution curves, I need to see what is originating the SALES for this week (the breakout/sum total of the previous 10 weeks); and that needs to be a moving total.  I also need to see the leads generated in a given week - and how the sales will project over the next 10 weeks from that original lead week.

             

            Make sense?  Thank you for your reply - I appreciate it.

             

            PS - the leads that I generate in any given week are based on how much is spent in marketing - and therefore, that's a component as well.  If I cut my spending in half - then my leads for that week would be cut - and the forecasted sales would also decrease.  If I merely forecasted on previous sales (assuming a constant level of spend), i don't believe the forecast would take into account the reduced spend?