Daily Statistcs Automation & Script

[pjwelsford]

Hello - I'm developing a deal tracking system to help my team win more business and report quicker to management. One management requirement is to show trends over time on the size of the deal pipeline.

I can easily create a report or dashboard to show current statistics but management want to see how these statistics have changed over time. To do this I've created a new table in my Base for "Daily Stats" and I want to run a daily automation to calculate a snapshot of statistcs from my "Deals" table. Each statistic will have it's own field in the Daily Stats table with each record being the date/time of the automation. The type of stats I want to calculate are:

- Total number of deals in the table

- Number of open deals (filter out those with won and lost closed status')

- Value of all open deals  (each deal has a potential value, again need to be filtered)

- Average cost of open deals (have a field already for costs)

I can't figure a way to do this using formula/lookup/rollup fields which is why I'm looking at an automation; and I've been trying for days to use bits of scripting from other threads to create my own but no luck getting it work. Hope someone here can help give me some direction on how I could achieve what I'm looking for. Thanks in advance!

[TheTimeSavingCo · Accepted solution]

Hmm, because you need the value of all open deals as well as the average cost of the open deals you'd need to either handle this via formula fields or via a scripting action

Assuming you're doing this via formula fields, you'll need to create a "Helper" table, link all your "Deal" records to a single recordin the "Helper" table, and use conditional rollups/lookups with formula fields to get you the data you want.

After that, you'd create an option that would run once a day, perhaps at 11:50pm, to create a new record in the "Daily Stats" table, and it'd basically copy over all of the data from the "Helper" record that you set up in the previous paragraph

You'd probably also want to set up an automation that would run whenever a "Deal" record gets created that would link it to the record in the "Helper" table as well

[pjwelsford]

Hi Adam - thanks for this, you've hit upon a good working solution here. Have tested it and it seems to work well and should allow me to add further daily statistic tracking in the future also!