Calculating retention %

[Joony]

Hello,

I am looking into creating a retention table, and I have managed to make a pivottable using a calculated field [Datediff Active Date, User created date] combined with [distinctcount User ID] and it’s giving me what I need in user numbers, but I am struggling with making it into a %, maybe because I am using [distinctcount User ID]. It just does not seem to give me the option to play with percentages at all.

Does anyone have an idea if I can somehow work this out with calculated fields with what I have? Any advice or help is appreciated

See my attached example of where I am, and where I want to be.

[ErikG]

Hi @Joony
what about format with “Suffix”?

[Joony]

@ErikG Hey, thanks. A suffix would only add a %, not actually calculate the %, am I right? The above table is just a simple example that I made in Excel to demonstrate, the numbers in the “user count” are not the same as the numbers in the %. I need to perform an actual calculation of Day 0. Any idea if I can work around this using calculated fields somehow?

[ErikG]

But what would be the calculation or what do you want to show within the table. i don’t get it.

E.g. 100% of what on Day 0 or 24% of what on Day 1?

[Joony]

@ErikG The table shows how many users came back X days (columns) after install date (shown in rows). This is to calculate how our new customers are retained over time.

I want everything in the table to be calculated as a % of the values in Day 0. E.g. on Day 5, if we have 10 users left out of an original 200 that installed the game on day 0, this should show as 5 % retained (10 users left of 200) rather than the number of users.

A % table makes it easier to see the relative retention rate than showing absolute number of users, that may vary greatly day to day.

[ErikG]

So 24 User (created on 06/10/2023) came back after 1 day?

Do you have the “day n” as a dimension and the value as measure or is it a measure for “day n”?

[Joony]

@ErikG Exactly. 100 new customers on Oct 6th. Of those, 24 came back Oct 7th.

With Day N I assume that you mean the number of days between ActiveDate and UserCreated Date? I calculated that using the Datediff function.

Rows: UserCreated Date
Columns: Days between ActiveDate and InstallDate (calculated field using DateDiff)
Values: DistinctCount UserID

[Joony]

@ErikG I figured it out the hard way. It took time but it’s now showing in the desired way.

I made two separate calculated fields:

1. periodOverPeriodLastValue(distinct_count({Consumer ID}),{Consumer Created Date},DAY,0)
   This was to calculate the number of returning customers days after install.

2. maxOver(distinct_count({Consumer ID}),[{Consumer Created Date}])
   This was to get the max value per row.

Then I made a third one where I divided 1 by 2, and changed the format to %.

[rohit_SB]

Hi @Joony , I did something similar to what you did. I followed the steps from a video regarding cohort analysis on youtube and came up with this table:

I also made a similar table to what you did. What steps should I take to get this table working?
Here’s the way I calculated things:
periodSinceFirstOrder: dateDiff(clientFirstOrderDate, orderDate, 'MM')
clientFirstOrderDate: minOver(orderDate, [clientID], PRE_FILTER)

Similar to what you did, i did this:
numberOfOrdersAfterFirstOrder: periodOverPeriodLastValue(distinct_count(orderID), clientFirstOrderDate, MONTH, 0)
maxValuePerRow: maxOver(count(orderID), [clientFirstOrderDate])
However, it doesn’t give me a table similar to what you have there.