Help with showing user retention

[ksfx77]

Hi Guys - I hope you are all well !

I have a conundrum, and would appreciate your guidance 🙂

I have an events table, as per the following example.

Date	UserGuid	Action
1/26/2021	<unique guid>	REGISTER
1/26/2021	<unique guid>	LOGIN
1/26/2021	<unique guid>	LOGIN
1/26/2021	<unique guid>	REGISTER

I need to generate a user retention graph showing those who continue to login after 1 day, 7 days, and 20 days (and potentially other days).

• The REGISTER event will happen once per user, and shows when they first signed up.
• The LOGIN event occurs every time a user logs in to the platform.

So I need to work out for each user;

• Their register date
• If they logged in after registering (Day 1) - same day or >
• If they logged in >= 7 days (from their register date)
• If they logged in >= 30 days (from their register date)

This would then be shown in an aggregated manner, so what % of all users log in after 1 day, 7 days and so on.

I would very much appreciate any guidance!

Thanks all

Kevin

[v-eqin-msft · Accepted solution]

Hi @ksfx77 ,

According to my understanding, you want to set a flag for each User based on the date diff between the first register date and the first login date, and then calculate the percentage of all records, right?

You could use the following formula to create a calculated table :

Table 2 =
ADDCOLUMNS (
    DISTINCT ( 'Table'[UserGuid] ),
    "Flag",
        VAR _regiDate =
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[UserGuid] ),
                    'Table'[Action] = "REGISTER"
                )
            )
        VAR _firstLogin =
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER ( ALLEXCEPT ( 'Table', 'Table'[UserGuid] ), 'Table'[Action] = "Login" )
            )
        VAR _datediff =
            DATEDIFF ( _regiDate, _firstLogin, DAY )
        RETURN
            IF (
                _datediff >= 30,
                "30 Days",
                IF ( _datediff >= 7, "7 Days", IF ( _datediff >= 0, "1 Days" ) )
            )
)

Then directly create a line visual with Flag column like this:

Or create a Percentage column for table visual:

Percentage =
CALCULATE (
    COUNT ( 'Table 2'[Flag] ),
    ALLEXCEPT ( 'Table 2', 'Table 2'[Flag] )
)
    / COUNTROWS ( 'Table 2' )

 

Please take a look at the pbi file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

please provide more representative sample data, with real fake user ids, and sufficient time range.