Basic sales dashboard formulas

[Oliver_Gatterma]

Hello all,

I have a single base to use as my CRM. A tab for Leads and one for Opportunities. They each have their own statuses.

Lead status has 8 steps.
Opportunity status has 6 steps.

What I am trying to achieve

1. A formula counting how many records are at a certain status (i.e Demos) in that month, to output that into an interface.
2. A formula that tracks how many days it takes to go from status 1,2,3 to 4. It may be 1 to 4, or 2 to 4.
3. A formula that counts, in days total time from status 3 (in Leads tab) to status 16 (in Opportunities tab)

What i Have so far

Following this airtable article, I have the automations to output when a status has been modified and outputs into a column for each step.

When attempting to create a formula, I get an error. (no ,its not the emojis, I tried without, same error). But this formula would only count from step one step to the other, and not from any to a specific step.

Thanks in advance :upside_down_face:

[TheTimeSavingCo]

Oliver_Gattermayr:

A formula counting how many records are at a certain status (i.e Demos) in that month, to output that into an interface.

Hm, may I know what issues you faced when attempting to use a Number element in your Interface?

It feels well suited to this

Oliver_Gattermayr:

A formula that tracks how many days it takes to go from status 1,2,3 to 4. It may be 1 to 4, or 2 to 4

If the sequence doesn’t matter, one way you could accomplish this would be to create two Date fields called “Date at Status 1” and “Date at Status 4” or some such, and a Last modified time field that watched the Status field

You’d then create an automation that would trigger on record update, and would watch the Status field

This automation would have two conditional actions:

1. Conditional 1 would be if the Status value is 1, and its action would be to paste the date value in Last modified time into the Date at Status 1 field
2. Conditional 2 would be if the Status value is 4, and its action would be to paste the date value in Last modified time into the Date at Status 4 field

You would now have the information of when the record was at Status 1 and when it was at Status 2, and you could then use a DATETIME_DIFF() to get the number of days

Oliver_Gattermayr:

A formula that counts, in days total time from status 3 (in Leads tab) to status 16 (in Opportunities tab)

The suggestion above should broadly work for this as well, just need to change out some of the conditonals, fields etc

[Oliver_Gatterma]

Hey Adam,

As for the first point, the Number in the interface designer allows me to show the total amount of records, and there are some filtering options. How would I get it to only show me the records that are relevant in that month?
I.e I may have 300 records, but only 22 this month. Id like to show 22, not 300.

If I understood correctly, your second suggestion for getting DATETIME_DIFF() is in line with the article I was following. My question is:
Do I have to create an automation from 1 to 4, 2 to 4, 3 to 4, 4 to 4 (and later from 1 to 16, 2 to 16…) or is there a simpler way to consider if status (1,2,3) then 4 timestamp?

Cheers

[TheTimeSavingCo]

Oliver_Gattermayr:

How would I get it to only show me the records that are relevant in that month?
I.e I may have 300 records, but only 22 this month. Id like to show 22, not 300.

Hmm, how do you determine what records are relevant in that month? If it’s by, say, whether the record was created in that month, you could create a Created time field and make it filter based off of that? Assuming 22 records were created so far in the current month, the number element would then show 22

Oliver_Gattermayr:

Do I have to create an automation from 1 to 4, 2 to 4, 3 to 4, 4 to 4 (and later from 1 to 16, 2 to 16…) or is there a simpler way to consider if status (1,2,3) then 4 timestamp?

Ah, it’s possible that the Status field never gets set at 1 I take it? If so, I think I’d change up the conditionals in the automation where condition 1 is “If Status is 4” and condition 2 is “If Status is not 4”, with the same underlying logic

[MaisieRowe]

I know it's been a while, but they can be super helpful. However, the formulas can sometimes feel like trying to solve a puzzle. Honestly, I find it best to keep things simple. Start with metrics like leads generated, conversion rates, and total sales. These give you a clear picture of how things are going.If you want to dive deeper, using tools like Lead Gen Calculators for Financial Advisors can help you get more insights without losing your mind over complicated formulas. They simplify things and help you focus on what matters most.Remember, the key is to track what’s relevant to your goals. So, don’t stress too much over the numbers; just make sure you’re keeping an eye on the big picture.