Calculate Stickiness Metric

[Eldad_Levi]

Hey,
I am trying to calculate a stickiness metric, which is Daily Active Users/Weekly Active Users (DAU/WAU).
I tried to do it in 2 ways, but both of them do not work.

1- Using distinctCountOver

Column ‘Week from Day’ Means:

This is the error I got:
“Mismatched aggregation. Custom aggregations can’t contain both aggregate “distinctCountOver” and non-aggregated fields “DISTINCTCOUNTOVER(“USERID”, “Week from day”, PRE_AGG)”, in any combination.”

2- Using Distinct count with group by:

This is the error I get:
“The calculation operated on Level Aware Calculation (LAC) agg expressions is not valid”.

Please help me to solve those issues or suggest me a different method to calculate stickiness.

[sagmukhe]

@Eldad_Levi - Thank you for posting your query! Please try out the below calculation:

distinctCountOver({USERID}, [{DATE}], PRE_AGG) / distinctCountOver({USERID}, [truncDate(‘WK’, {DATE})], PRE_AGG)

I have tried to replicate it at my end using a sample dataset and it seems to be working. Sharing the snapshot for reference. Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!