Cohort Metrics on Nested Pivots

[heber_brandao]

Hey team,
Currently I am quite struggling on working with nested pivots and table calculations. Hopefully someone can give me some light on that.

What I am trying to do? -> Calculate Retention Rate on Month 6, with several breakdowns.

Here is how my data is structured:

• Dimensions
  • Conversion Month (rows)
  • Month Diff (Pivot 1) - Those are my cohorts
  • Macro Region (Pivot 2) 
• Measures
  • # New Customers
  • # Customers EoP (End of Period)

The final outcome is to create a table calculation % Retention in which:
- # Customers EoP / # New Customers Month diff 0

${number_of_customers} /
pivot_where(${month_diff} = 0, ${number_of_new_customers})

If I only have the first pivot in place, this calculation works as intended. However, If I want to see % Retention by month diff AND macro region, I am quite unsure how to write this function once a new pivot comes into play.

Does someone have any advice here?

[a_aleinikov]

Hi @heber_brandao :

Yes, once you add a second pivot (Macro Region), your table calculation needs to handle both pivot dimensions together.

You should adjust the pivot_where to filter by both:
 ${month_diff} = 0
${macro_region} = current macro region

Try something like:
${number_of_customers} /
pivot_where(${month_diff} = 0 AND ${macro_region} = ${macro_region}, ${number_of_new_customers})

This ensures you pull the correct denominator within the same region.

If needed, use a pivot_row() or a combination of filters to explicitly align dimensions.

[heber_brandao]

In theory, that works. But this is very hard to deploy as I need to have a table calculation for each macro region