tchapgouv / tchap-stats

Scripts et organisation des statistiques fournies à Metabase provenant des données matrix
2 stars 1 forks source link

add retention view for 12 months with column and binary format #44

Open odelcroi opened 1 year ago

odelcroi commented 1 year ago

retention_12_months_binary_varchar is a mat view that represents when users connect or not the last months (max 12 months)

1 -> user has connected this month 0 -> user has not connected

in a varchar form it can be read like this :

111 -> user has connected the last 3 months 1000 -> user has connected 4 months ago and then became inactive 11111111111 -> user has connected every month since 11 months

/ exemple of results / \x00007a01d6c74adbf2e5ca29843989ff9db2e0a73e63fcbf7b1057c19a714336,100000000000 \x000082d929c3757acb5142326a3558c40689d93cb7a6bbbf161635a7090a4645,1111111111110 \x000084413b7a923f9606ece64a0e07aaa7c1aaff282ec13fc065d7a38df81bc2,1111111111110 \x00009f48ad9c7a906134650a617638629a526263327ed4a735fb1821fbdec872,1000000000110 \x0000f4b623e5f3c2aafc31af42ae164ad001a1726a40c6cef11e8216b1b4a1f6,1101110000000

Other views has been commented out, but the code can be interested