Current Pipeline metrics

[LindsayRidpath]

Hello! 

I'm using the interface to show our metrics, and I'm looking to show both cumulative pipeline numbers and current pipeline numbers. Under our Tasks table, we tag the pipeline status per task, so cumulative recall is straightforward. 

How would I record the metric for the current pipeline status? Our pipeline is:

Qualification, Initial Contact, Significant Engagement, Significant Follow-up, Proposal Submitted, Proposal Awarded, Closed: Project not awarded, Closed: other, Handoff...

For example, Within one project (projects are tracked in a different table) I have completed tasks for qualification, one for initial contact and now I have a completed task (with the checked mark) of significant engagement. I only want to count significant engagement to show where I am for this project. 

Is this possible? Are there other ways people show project progress/ sales pipeline progress? 

The goal is to see where people are at currently. I might have to reconfigure things to get this result and that's ok! 

Here's an example of the ideal situation: 

Thank you! 

[TheTimeSavingCo]

Hm, if you link all your tasks to a single record in another table called "Summary" or something, you could then do conditional rollup fields to count the number of tasks per pipeline status?

[LindsayRidpath]

Thank you for the idea! 

I'm working through that. I've changed the pipeline so there's a number associated. I'm trying to set up the rollup field to only pull the pipeline indicator with the highest number:

Do you know of a function that will do this? 

Thanks again!

[LindsayRidpath]

Hi all, I've been looking into functions to try to solve this. Would an extract function work, and is there a formula that will only extract the highest number? 

I though about numbering the pipeline, then extracting the highest number to show current pipeline status.

I didn't see anything in this source: https://support.airtable.com/docs/guide-to-regex-functions 

Thanks! 

[TheTimeSavingCo]

Hmm, if I were you I wouldn't put the number inside the select option, I would create a formula field that would output the appropriate number based on the select option that's selected with a SWITCH()

With this, you'll be able to use a rollup with "MAX()" to display the pipeline indicator with the highest number

[LindsayRidpath · Accepted solution]

Thank you @TheTimeSavingCo ! I was happy to see I was on the right track. I appreciate your suggestion for the Rollup field as well. It's working great! 

For anyone's future reference, we created a 'current engagement pipeline using TheTimeSavingCo suggestions. Our base is used to track engagements/projects on various topics. We wanted a cumulative pipeline tracking metric and a current pipeline tracking metric to see if any team members' projects are getting stuck in a particular area of the 'funnel.'

To track, we have four tables that connect to track tasks, engagements (projects), organizations (companies) and contacts. The cumulative tracking was easy to set up. We're pulling the pipeline tags set up in a single select field whose tasks also match the check box field. For the current pipeline tracking, in the Tasks table, I set up a formula to assign numbers to each pipeline tag: 

SWITCH( {Pipeline},

  "Qualification", 1, 

  "Initial Contact", 2,

  "Significant Engagement", 3,

  "Significant Follow-up", 4,

  "Proposal Submitted", 5,

  "Project Awarded", 6,

  "Closed: Project not awarded", 7,

  "Closed: Other", 8,

  "Handoff: Recommended to UA unit", 9, 

  "Handoff: Recommended to RDS associate", 10,

  "Paused", 11

)

Then in the Engagements tables, I used the Max(values) formula in the rollup field to pull the highest number of pipeline task of each engagement (project). 

It's working great!! 

😁