Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I would like to calculate reconnection rate.
My dataset is compose in this way:
Calendar Table (Date, Year, Month, etc.)
Connections Table (connection_id, etc.)
Consents Table (consent_id, connection_id, valid_until, etc.)
My business case:
I provide internet service licenses. When a customer of mine subscirbe a licence pack he create a connection (es. connection_id 2001). During the subscription I ask the client to provide a consent for treating its personal data (es. consent_id 8009).
The consent has an expiration date (for example 25/08/2022).
I would like to know how many customers after the first subscription, when the consent expires provide another the consent for the data treatment.
Please note that in my dataset when the client renew the consent, a new consent_id will be generate (consent_id 8055) but the connection_id would NOT change (connection_id 2001).
Here more info:
consent_business_id | connection_business_id | fetch_business_id | consent_status | consent_creation_date | consent_valid_until | consent_ok | consent_new | consent_fail |
8009 | 2001 | 7771 | ok | 2022-03-09 | 2022-06-07 | 1 | 0 | 0 |
8055 | 2001 | 7772 | ok | 2022-06-19 | 2022-09-17 | 1 | 0 | 0 |
8002 | 2001 | 7773 | ok | 2022-09-18 | 2022-12-17 | 1 | 0 | 0 |
8081 | 2002 | 7774 | new | 2022-09-19 | 0 | 1 | 0 | |
8057 | 2003 | 7775 | ok | 2022-03-21 | 2022-06-19 | 1 | 0 | 0 |
8053 | 2004 | 7776 | fail | 2022-09-19 | 0 | 0 | 1 | |
8016 | 2004 | 7777 | ok | 2022-04-02 | 2022-07-01 | 1 | 0 | 0 |
8017 | 2004 | 7778 | new | 2022-07-01 | 0 | 1 | 0 |
as you may see for connection_business_id = 2001 a first consent (consent_id: 8009) was given on 2022-03-09. This consent expired after 90 days and it was renewed on 2022-06-19 (consent_id: 8055).
Then after even consent_id: 8055 expired a third consent (consent_id: 8002) was granted on 9/18/2022.
As you may see in the table all threes has the same connection_id
Otherwise, connection_id = 2003 after expiration no attempt of reneal was made.
While connection_id = 2004 the first consent expired on 7/1/2022 an attempt of renewal begun the same 7/1/2022 but it fwas never completed so consent_status = new.
What I want to get is a KPI, reporting the % of how many conenctions are renew. So I want to take into consideration only consent ok and expired by at least 14 days (to give people time to renew).
where consent_status = 'ok' and consent_valid_until < TODAY() -14
Then in this specific case
consent_renewal % = 2/3 = 66.7%.
Out of the 4 connections only 3 (2001, 2003, 2004) have a consent with status = ok and expired by more than 14 days (consent_id: 8055, 8057, 8016). Connection: 2001, reconnect successfully and create consent 8002, connection: 2003 never reconnect, conenction: 2004 attempt unsuccessfully the reconnect. consent_renewal % = 2/3 = 66.7%. (I count here connection 2001, 2004)
consent_renewal_success % = 1/2 = 50% Out of 2 renewal only 1 is with consent_status_ok (connection_2001)
Thanks,
Solved! Go to Solution.
Hi, @keivan
You can try the following methods.
Measure:
Expires 14 days =
VAR _Maxdate =
CALCULATE ( MAX ( 'Table'[consent_valid_until] ),
FILTER ( ALL ( 'Table' ),
[consent_status] = "ok"
&& [connection_business_id] = SELECTEDVALUE ( 'Table'[connection_business_id] )
)
)
RETURN
IF ( _Maxdate <> BLANK () && TODAY () - _Maxdate > 14, 1, 0 )
consent_renewal% =
Var _N1=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_status]="ok"))
Return
DIVIDE(_N1,_N2)
consent_renewal_success% =
Var _fail=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_valid_until]=BLANK()&&[consent_status]="fail"))
Var _success=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Return
DIVIDE(_success-_fail,_success)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @keivan
You can try the following methods.
Measure:
Expires 14 days =
VAR _Maxdate =
CALCULATE ( MAX ( 'Table'[consent_valid_until] ),
FILTER ( ALL ( 'Table' ),
[consent_status] = "ok"
&& [connection_business_id] = SELECTEDVALUE ( 'Table'[connection_business_id] )
)
)
RETURN
IF ( _Maxdate <> BLANK () && TODAY () - _Maxdate > 14, 1, 0 )
consent_renewal% =
Var _N1=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_status]="ok"))
Return
DIVIDE(_N1,_N2)
consent_renewal_success% =
Var _fail=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_valid_until]=BLANK()&&[consent_status]="fail"))
Var _success=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Return
DIVIDE(_success-_fail,_success)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |