Forecast future customers using the average months to convert

  • 20 March 2024
  • 1 reply

Userlevel 2
Badge +3

Dear Pigment Community,

I'm currently tackling a challenge with a modeling project and could really use your expertise.

Goal: Forecast future customers using the average months to convert.

Issue: I have a metric for my forecasted number of customers, but I'm stuck on how to shift these values forward into the future based on my metric that calculates average months to convert. 

Desired outcome: My ideal solution would take the forecasted customer values and shift them forward into the future by ‘x’ months based on what the average months to convert value is. 

Please find images and further explinations bellow.

This metric contains the number of custstomers expected.
I want to use this metric to push the values in # cust fcst forward into the future by the number of average months.

Example using the images above:

  • metric[# Customers FCST]: France, Inbound Nov 23 value will be shifted to Mar 24, 4 months into the future because the metric[Avg months to convert previous year] value is 4. 

I'm open to any suggestions or insights you might have to help me overcome this hurdle.

If you need any further details to grasp the problem better, please don't hesitate to ask.

Thank you all in advance for your invaluable help.


1 reply

Userlevel 5
Badge +9

Hi @darious ,

Since the your mapping is not fixed and variable by year, a possible solution would be creating an intermediate shift metric with the following formula:

Month + 'Avg Months to convert to Previous year'[SELECT: Year - 1][BY: Month.Year][ADD: 'Segment Simplified']

For example if my Avg Months to convert to previous year is as follows:

The Shift mapping would result in the following:

The above metric would be then used on the # Customers metric using the following metric:

'# Customers'[BY SUM: ->  'Shift Mapping']

which will allocate each months value to the corresponding month on the mapping metric.

Hope this helps,