Convesion metrics based on single-select field (mult records)

[Rasha]

I have an Interactions and an Investors table. There are vaious interaction types as investors move from cold > warm > diligence, etc. These interaction types are managed via a single-select and piped in via various automations. Two metrics I’m trying to highlight using a number element in the Interface Designer:

Simplistically:

Cold → Pitch conversions
Pitch → Diligence conversions

So far, I tried grouping interactions by investor and leveraing rollups in the linked Investors table. Don’t think that’s the way. In my mind this looks like No status (0), Cold (1), Pitch (2), Diligence (3). For any investor, there could be 2 colds, 2 pitches, etc. It’s not always 1 per intercation type.

How might I go about deiving % of total interactions that converted into these two stages?

NB: At this point in the client’s project, major restructuring of the base is not an option.

Thanks friends!

[TheTimeSavingCo]

Hm, feels like conditional count fields might work for this. May I know what issues you faced when trying to use those?

[Rasha]

That’s where I started but couldn’t find a way to eliminate zeros (no corresponding interaction) without adding 5 more formula fields. I then tried the same approach with a rollup but I’m not seeing an aggregation formula that only returns a numeric value if the conditions are met. ARRAYCOMPACT(values) returns a text string. Essentially, I want to arrive at a % filled.

[TheTimeSavingCo]

Hmm, okay. Yeah the path you were going down would be the simplest, one count field for each status, sum it all, and then get the percentage, but I take it we don’t want to create ten new fields?

You could do this with just one rollup field for each percentage you wanted to get by getting a count of the number of times the specific status you’re looking for appeared, and then divide that by a COUNTA(values)?

And here’s the formula I used:

(
  LEN(ARRAYJOIN(values)) -
  LEN(
    SUBSTITUTE(
      ARRAYJOIN(values),
      "Todo",
      'Tod'
    )
  )
) / 
COUNTA(values)

[Rasha]

Thank you! This almost works but the data structure on Table 1 gets in the way. I’m curious about ‘Tod’ in your formula - I’m not sure how that works :slightly_smiling_face: If I switch it to anything else it fails. What’s the underlying logic?

[TheTimeSavingCo]

The idea is since we want to count the number of times “Todo” appears, we’ll use a SUBSTITUTE() to replace all instances of “Todo” with “Tod”, reducing the number of characters by 1 for each instance of “Todo” that exists. We then subtract the number of characters from this newly modified string from the number of characters from the original string, giving us the number of times “Todo” was in said string

As such, you’ll need to switch that “Tod”, “Todo” bit to the exact thing you’re trying to count, so an example for your case would be:

(
  LEN(ARRAYJOIN(values)) -
  LEN(
    SUBSTITUTE(
      ARRAYJOIN(values),
      "Diligence",
      'Diligenc'
    )
  )
) / 
COUNTA(values)

[Rasha]

Brilliant. Thanks for taking the time! Always learning something new here.