2
\$\begingroup\$

Does this code calculate the retention rate per acquisition channel correctly?

$$ R = \frac{E - N}{S} * 100 $$

Where \$E\$ is the number of customer at the end of a period, \$N\$ is the number of new customers acquired during that period, and \$S\$ is the number of customers at the start of that period.

 +-------------+------------+---------------+--------------+
 | users       | orders     | acqus_channel | user_channel |
 +-------------+------------+---------------+--------------+
 | login_id    | orderid    | channel_id    | channel_id   |
 |  last_login | login_id   | channel_name  | login_id     |
 | first_login | total      |               |              |
 | cancel_date | subtotal   |               |              |
 |             | date       |               |              |
 |             | channel_id |               |              |
 |             |            |               |              |
 +-------------+------------+---------------+--------------+

Let's say we are calculating for Feb 2018:

  • To get end period customers, I calculated (existing + new - cancelled)

  • Timestamps are unixtime

SELECT e.channel_id channel, 
((e.exisiting + n.new - c.cancelled)-n.new)/e.existing))*100 Retention_Rate
from existing_customers e
JOIN new_customers n on e.channel = n.channel
JOIN cancelled_customers c on n.channel = c.channel
GROUP BY channel


(  
SELECT a.channel_id channel, COUNT(DISTINCT u.login_id) new from 
users u
JOIN user_channel uc on uc.login_id = u.login_id
JOIN acqus_channel ac on ac.channel_id = uc.channel_id
WHERE DATE(u.first_login) BETWEEN '2018-02-01' and '2018-02-28'
AND DATE(u.cancel_date) > '2018-02-28'
GROUP BY channel
) new_customers

(
SELECT a.channel_id channel, COUNT(DISTINCT u.login_id) existing
FROM users u
JOIN user_channel uc on uc.login_id = u.login_id
JOIN acqus_channel ac on ac.channel_id = uc.channel_id
WHERE DATE(u.first_login) < '2018-02-01'
and DATE(u.cancel_date) > '2018-02-01' OR NULL
GROUP BY channel
) existing_customers

( 
SELECT a.channel_id channel, COUNT(DISTINCT u.login_id) cancelled
FROM users u
JOIN user_channel uc on uc.login_id = u.login_id
JOIN acqus_channel ac on ac.channel_id = uc.channel_id
WHERE DATE(u.cancel_date) BETWEEN '2018-02-01' and '2018-02-28'
GROUP BY channel
) cancelled_customers
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.