# Breaking down retention metrics

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   |
| 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 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 acqus_channel ac on ac.channel_id = uc.channel_id