Help

Re: Formula for lead conversion percentage?

1343 0
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Cox
4 - Data Explorer
4 - Data Explorer

I’m fairly new to Airtable and very new to formulas in Airtable. I have a table to track leads. In that table, I have a field for status titled “Converted Lead?”. The converted lead field contains the drop down choices yes, no and not yet. Is there a way to see lead conversion percentage - a formula that will add the No’s and the Not yet’s and then divide them by the yes’s to get our conversion percentage?

2 Replies 2

First off, you’ll need to make a new table to do the processing. I typically call this table [Control]. In this table you’ll only have a single record. For simplicity, the primary field value of this record is just a period ("."). Back in your main table, you’ll add a Link field, point the first record to this lone record in [Control], then drag-fill that across all of the other records. Here’s what that might look like:

26%20PM

Whenever you add a new record to your primary table, you’ll need to also add this link, or it won’t count as part of the later calculations.

In the [Control] table, add a Rollup field:

52%20PM

That will give you something like this:

55%20PM

Make a new formula field named {Yes}, using this formula:

LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Rollup & "", "Not Yet", ""), "No", ""), "Yes", "Y"))

In short, this removes each “Not Yet” and “No”, replaces each “Yes” with “Y”, then counts the character length of the result. The three “Yes” entries become “YYY,” which is a length of 3.

Repeating this process with modified versions of that formula to make {No} and {Not Yet} fields, we have this:

30%20PM

One final formula field does the math you described above:

(No + {Not Yet}) / Yes

Screen Shot 2019-04-08 at 11.10.02 PM.png

David_Cox
4 - Data Explorer
4 - Data Explorer

Thanks for the in-depth description Justin. It has me well on my way!