Calculating & Displaying Sales Funnel Conversion

[dctx]

Hi, I coud use some help on what I think is a relatively simple few concepts.

I have a base called "Sales Pipeline" where I am tracking:

1. Deal Name (Acme, Inc)
2. Pipeline Stage (intro, qualified, opportunity etc)
3. Opportunity Status (active, lost, won)

Let's say there are 100 total records, each with a Deal Name, a Pipeline Stage and an Opp Status

I'd like to be able to: 

1. Calculate the Intro to Qualified conversion % (so if we've had 100 records at the "Intro Stage" and 62 have converted to the "Qualified" stage, then we should show a 62% conversion)
2. Same for Qualified to Opportunity etc
3. Similarly, I want to be able reflect the Opportunity Status conversion % as well. So for example, if we have 100 records that entered the funnel at the "Intro" Stage, and 62 converted to "Qualified" but since then 32 of those have an Opportunity Status of "Lost", we'd really only want to reflect an Intro > Qualified (Active) conversion of 30%. 

I suspect I can do this using a 1) separate base, that 2) is linked to my Sales pipeline, where I can then 3) use Rollups to count the # of records at each pipeline stage, or opportunity status and calculate that in the table. From there I can create Interface charts to reflect he overall conversion metrics. 

Is this the right approach? I am stuck trying to rollup the "deals" in the sales pipeline, but also not sure if this is the right approach either way.

[Mike_AutomaticN]

Hey!

If you want to see that on an interface, you might want to check out this discussion, addressing this same situation! Otherwise, your suggested approach (diff table for stages and rollups) will work! 

I hope this helps!

Mike, Consultant @Automatic Nation

[TheTimeSavingCo]

Hmm, when you say separate base I believe you may be referring to a separate table instead?

If so, I've put something together here for how I'd do it

I think I'd just use Count fields instead of rollups actually

How you calculate things is also quite dependent on your business logic.  In the screenshot above, I got the number 62 by counting all the deals that did not have a "Pipeline Status" of "Intro", which implied that they had went past that stage.  I don't know whether that holds up for your workflows though

Similarly, for the "Intro > Qualified (Active)" conversion, I counted all the deals that were "Qualified" that were "Active"

[dctx]

Is there a way to have all opportunities automatically show up in the Linked column? I see that I can manually click to link each one, but what happens when a new Opportunity record comes in?

Once I solve that then I think I can look into trying the Count method, too

thanks appreciate the help!

[TheTimeSavingCo]

I'd recommend an automation that triggers when a new Opportunity record comes in, and its action would be to link itself to the Summary record!