Forum Discussion
Reconnection rate -
Thanks Greg,
I checked but I did not find this specific topin on most popular questions.
I would always add table in future.
here more info:
date are in format yyyy-mm-dd hh:mm:ss.000
| consent_business_id | connection_business_id | consent_status | consent_creation_date | consent_creation_date_1 | consent_valid_until | consent_ok | consent_new | consent_fail |
| 11 | 2 | ok | 2022-09-05 12:00:00.000 | 2022-09-05 | 2022-12-04 12:00:00.000 | 1 | 0 | 0 |
| 15 | 2 | ok | 2022-12-06 05:54:20.000 | 2022-12-06 | 2023-03-06 05:54:20.000 | 1 | 0 | 0 |
| 12 | 69 | ok | 2022-08-06 08:42:15.000 | 2022-12-06 | 2022-11-04 08:42:15.000 | 1 | 0 | 0 |
| 16 | 69 | new | 2022-08-09 08:42:15.000 | 0 | 1 | 0 | ||
| 17 | 77 | fail | 2022-12-06 05:53:16.000 | 0 | 0 | 1 |
as you may see for connection_business_id = 2 a connection was created on 5th Sept. it expired after 90 days (consent_id = 11). Then it was renew (consent_id = 15).
connection_id = 69 after the first consent = 12 expired, an attempt of renewal begun but it fail. consent_status = fail.
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 consen_valid_until < TODAY() -14
Then in this specific case
consent_renewal % = 2/2 = 100%. Out of the 5 consents only 3 are ok and only 2 are expired by more than 14 days (consent_id 11 and 12)
consent_renewal_success % = 1/2 = 50% Out of 2 renewal only 1 is with consent_status_ok (consent_id = 15)
Pleas elet me know if it is clear or still confusing the question.
Thanks,
Hi Greg_Deckler
did you had chance to take a look?