Pipeline for Sales Forecasting and Projections
Hi,
I was dabbling around in Pipelines, trying to setup an automation that will do some forecasting for future quarters.
Sales has Proposals and Sales Goals. Proposals have a text field with start and end quarter, in the format YYYYQQ (202101, etc.).
Both have to be 'current' and start cannot be < end. Some fancy formulas keep all this working and Sales happy. In a hidden formula field, I do a multi-text/text that calculates out the "span" for work. This is used for production capacity planning, along with our Sales team knowing what quarters are lacking in the future. Ex: Start is 202102 and end is 202201. My "Quarter Span" field has the values 202102 ; 202103 ; 202104 ; 202201 inside of it. Then, another field takes the Proposal Grand Total and splits based on the number of quarters. Billings aren't always equal but this gives us valuable insight as to what can be sold/when and when things are lacking.
Sales Goals has a field with YYYYQQ.
I need to do projections based on the TOTAL of these "Projected Amounts" that are divided up based on what I mentioned above. So, if I'm projecting for 202201 and there are 50 proposals that are not Lost, I add all these amounts together and get a sum. We'll also be using an identical method, since each line item has estimates per department, for capacity and workforce planning.
I have a table report that partially represents this but I need to put this into a "summary" table. I can easily write this in Python and have it triggered via a nightly cron/Automation/webhook, but wanted to give this a shot in Pipelines.
Is there "addition" or "math" in Pipelines that can be performed? The Bucket Channel is, unfortunately, vague as to documentation. And, I didn't see any tutorials doing something like this.
I figured the steps would go something like this:
Fetch CSV -> report of these proposals in the pipeline.
Quickbase->Query Sales Goals and get all YYYYQQ.
Iterate over each line of CSV. If Quarter Span (the field with all quarters covered between Start/End) contains the YYYYQQ variable, grab the Projected Amount and add into a running total.
Once done iterating over all lines in the CSV, take only the sum for the YYYYQQ and inject back into Sales Goals. Run nightly or every other night to keep the total fresh.
Likewise, I am not sure how to best iterate, without also tearing up my step count. Trying this out yesterday, I tore through 1000 steps without knowing it.
Thanks!
------------------------------
Andrew Ryder
------------------------------
I was dabbling around in Pipelines, trying to setup an automation that will do some forecasting for future quarters.
Sales has Proposals and Sales Goals. Proposals have a text field with start and end quarter, in the format YYYYQQ (202101, etc.).
Both have to be 'current' and start cannot be < end. Some fancy formulas keep all this working and Sales happy. In a hidden formula field, I do a multi-text/text that calculates out the "span" for work. This is used for production capacity planning, along with our Sales team knowing what quarters are lacking in the future. Ex: Start is 202102 and end is 202201. My "Quarter Span" field has the values 202102 ; 202103 ; 202104 ; 202201 inside of it. Then, another field takes the Proposal Grand Total and splits based on the number of quarters. Billings aren't always equal but this gives us valuable insight as to what can be sold/when and when things are lacking.
Sales Goals has a field with YYYYQQ.
I need to do projections based on the TOTAL of these "Projected Amounts" that are divided up based on what I mentioned above. So, if I'm projecting for 202201 and there are 50 proposals that are not Lost, I add all these amounts together and get a sum. We'll also be using an identical method, since each line item has estimates per department, for capacity and workforce planning.
I have a table report that partially represents this but I need to put this into a "summary" table. I can easily write this in Python and have it triggered via a nightly cron/Automation/webhook, but wanted to give this a shot in Pipelines.
Is there "addition" or "math" in Pipelines that can be performed? The Bucket Channel is, unfortunately, vague as to documentation. And, I didn't see any tutorials doing something like this.
I figured the steps would go something like this:
Fetch CSV -> report of these proposals in the pipeline.
Quickbase->Query Sales Goals and get all YYYYQQ.
Iterate over each line of CSV. If Quarter Span (the field with all quarters covered between Start/End) contains the YYYYQQ variable, grab the Projected Amount and add into a running total.
Once done iterating over all lines in the CSV, take only the sum for the YYYYQQ and inject back into Sales Goals. Run nightly or every other night to keep the total fresh.
Likewise, I am not sure how to best iterate, without also tearing up my step count. Trying this out yesterday, I tore through 1000 steps without knowing it.
Thanks!
------------------------------
Andrew Ryder
------------------------------
