Monthly History tracking

[pablo10]

Hi,

I wonder if anyone else has had this use case and if they managed to solve it.

My base has a collection of deals, and each deal will always be assigned a stage. I want to track time spent (in days) in the current stage and time spent in each subsequent stage as the transaction progresses through the deal lifecycle.  I want to be able to report on this data.

Some reports I would like to run include:

1. how many deals are in stage A, in August
2. how many deals are in stage B in July vs August vs September
3. how much time is spent in each stage? What are the averages
4. how much time is spent moving from stage A to B and C to D, and what are the averages over time

Another similar use case: for these deals, I would like to count the number of deals captured each month and the average over time. I would also like to include the monthly financial position for these deals and track them over time, too.

Any pearls of wisdom would be greatly appreciated. 

[TheTimeSavingCo]

Yeap I've done this before!

Try:
1. Create a new table called "Stage Tracking" or something with a "Date" field and a "Single Select" field to log the stage value
2. In "Stage Tracking", create a linked field to the same table
  - We'll use this linked field to link stages together so we can do math on it to get time spent
  - Add a lookup field to display the "Date" and "Single Select" fields
3. Create an automation that'll trigger whenever a Deal's stage gets updated
4. Give that automation a "Find record" step to look for another record in "Stage Tracking" that is linked to the same Deal, but its linked field from step 2 is empty, i.e. this was the previous Stage
5. Make that automation:
  a. Create a new record in "Stage Tracking" linked to the triggering record and set the Stage value,
  b. Update the record found in Step 4 by linking it to the newly created record in 5a.

If it doesn't find a record, we just do 5a instead

Now you've got a table where each record represents the transition from one Stage to another for a single deal

Consider "Deal 1".  Let's say we update its Stage to "Prospect" on 1 August 2024, which is the first possible Stage.  The automation runs and tries to find another record in "Stage Tracking", and since it can't, it'll create a new record in Stage Tracking with the value of "Prospect" and the date

On 5 August 2024, we update the Stage value to "Warm Lead".  The automation runs again and finds the "Prospect" record we created previously.  It creates a new record in "Stage Tracking" with the value of "Warm Lead" and the date, and then updates the "Prospect" record by linking it to the new "Warm Lead" record

The "Prospect" record now has the creation date of "1 August 2024", and the lookup fields we added show "Warm Lead" and "5 August 2024".  Now you can do math to get the days this Deal spent in "Prospect