Calculate Stickiness

Question & Answer
,
1

Hello, I hope you are well, I would like to ask for help with the calculation of the Stickiness metric as a field calculated as follows: Daily Users/ Monthly Users

I found the following post where information about this comes out, explaining that it could be calculated with the following formula: “distinctCountOver({USERID}, [{DATE}], PRE_AGG) / distinctCountOver({USERID}, [truncDate(‘WK’, {DATE})], PRE_AGG)”. Despite this, when I apply the adapted formula to my dataset I get the following error about the partitions.

Captura de Pantalla 2023-06-19 a la(s) 10.53.37
Captura de Pantalla 2023-06-19 a la(s) 10.53.37824×79 10 KB

My dataset has basically the following 3 columns:

  • “time_date”: refers to the date or day of the event.
  • “name”: gives the name of the event that is being measured.
  • “distinct”: is the id of the user who performs the event

Also, I would like to know if in the partition a condition can be added, since I need the name of the event to be “View Calendar Page”, I understand that I could do it with an ifelse prior to the calculation but I am not sure about this.

Thanks for your help

2

Can you try and make a calculated field for the truncDate?

time_month = truncDate('MM',{time_date})

And then use this calculated field instead of making the truncDate in the distinctCountOver