Have a base that looks at deal flow, including anticipated close date and value of each deal. While we can easily view our "pipeline" at any time, struggling to figure out a way to store that snapshot of data on a routine basis. We don't store updates to pipeline as a record itself, but update the record to the anticipated value.
At the end of a specific month in the past, what did our pipeline look like in terms of deal value, grouped by when we expect it to close? Used to look at accuracy (anticipated vs actual) of our project pipeline projections.
Solved
Snaptshot Roll-Ups At a Specific Date
+8Best answer by TheTimeSavingCo
Each PROJECT has projected conversion date and total value (currency) fields.
For my SNAPSHOT record I can begin by having a crated field being the last day of the month ending. Where I am hitting a road block is how to create multiple records then not just one. I would need to create a the following records:
- Date of month ending
- Projected conversion date
- Roll-up sum of total value on that date
Hmm, what if you had an automation that would:
1. Run once a month
2. Find all the "Project" records
3. For each of the found project records, create a record in a "Snapshot Data" table with:
- Date of month ending (i.e. the run time of the automation)
- The project's projected conversion date
- The project's total value
With this information, you could have a "Summary" table that's linked to the "Snapshot Data" table, and each record in the "Summary" table would be one month. You could then rollup the Snapshot Data records per month and view it there, does that make sense?
+31Do you think you could create an automation that would run every month that would compile all the metrics you want and then create it as a new record in a "Snapshots" table or something?
+8Do you think you could create an automation that would run every month that would compile all the metrics you want and then create it as a new record in a "Snapshots" table or something?
Thanks!
Yes, that is where I was planning on going with it. Creating the roll-up formulas is where I am struggling as there are lots of contingencies.
+31Thanks!
Yes, that is where I was planning on going with it. Creating the roll-up formulas is where I am struggling as there are lots of contingencies.
Ahh roger that. Yeah...I can't think of a way around that as we'd need to have them set up to see the current metrics as well
Anything in particular you're getting stuck on?
+8Each PROJECT has projected conversion date and total value (currency) fields.
For my SNAPSHOT record I can begin by having a crated field being the last day of the month ending. Where I am hitting a road block is how to create multiple records then not just one. I would need to create a the following records:
- Date of month ending
- Projected conversion date
- Roll-up sum of total value on that date
+31Each PROJECT has projected conversion date and total value (currency) fields.
For my SNAPSHOT record I can begin by having a crated field being the last day of the month ending. Where I am hitting a road block is how to create multiple records then not just one. I would need to create a the following records:
- Date of month ending
- Projected conversion date
- Roll-up sum of total value on that date
Hmm, what if you had an automation that would:
1. Run once a month
2. Find all the "Project" records
3. For each of the found project records, create a record in a "Snapshot Data" table with:
- Date of month ending (i.e. the run time of the automation)
- The project's projected conversion date
- The project's total value
With this information, you could have a "Summary" table that's linked to the "Snapshot Data" table, and each record in the "Summary" table would be one month. You could then rollup the Snapshot Data records per month and view it there, does that make sense?
+8Hmm, what if you had an automation that would:
1. Run once a month
2. Find all the "Project" records
3. For each of the found project records, create a record in a "Snapshot Data" table with:
- Date of month ending (i.e. the run time of the automation)
- The project's projected conversion date
- The project's total value
With this information, you could have a "Summary" table that's linked to the "Snapshot Data" table, and each record in the "Summary" table would be one month. You could then rollup the Snapshot Data records per month and view it there, does that make sense?
Thank you! I was overcomplicating it. This worked.
+31Thank you! I was overcomplicating it. This worked.
Glad I could help!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.