Hi all,
I would like to calculate consent renewal rate.
On my dataset when a connection is created (connection_id 2001) we collect also a consent from the client (consent_id 8009). Consent has an expiration date. When the consent expired (for example 9/17/2022) the client can renew the consent for its connection.
If the client renew the consent, a new consent_id will be generate but the connection_id would NOT change. In the table below for connection_id 2001 when consent_id 8009 expired a new consent_id: 8055 is provided
What I actually want to do is to calculate Renewal rate.
So for those connection in which the consent is expired by at least 14 days (to give people some time to renew), I want to check if a new consent has been created after old one is expired.
Here how i would perform calculation
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 consent was created on 2022-03-09. Consent expired after 90 days (consent_id = 8009). Then it was renew (consent_id = 8055) on 2022-06-19.
Then again expired 2022-09-17 and was another time renewed (8002).
For this mock sample I provide random number, but actually all id in my dataset are progressive numbers
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
where consent_status = 'ok' and consen_valid_until < TODAY() -14
Then in this specific case
consent_renewal % = 2/3 = 67%. Out of the 4 connection only 3 (connection_id 2001, 2003, 2004 ) have a consent in status ok and expired by more than 14 days (consent_id 8055, 8057, 8016)
consent_renewal_success % = 1/2 = 50% Out of 2 renewal only 1 is with consent_status_ok (consent_id = 8002).
