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

# calculating forecast conversion by week

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

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

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.