b3DTnr5K

1All metrics calculated using NY time zone shift. All raw data are stored in UTC timezone.
2Subscriptions
3Unsubscribers (https://analytics.scentbird.com/subscriptions/unsubscribers)
4
5Counted by subscription_entity table using subscription_end_date field. 'giftOrder' type is excluded. Count uniq number of users who has subscription ends in current month. In case user have several unsubscriptions in current month, then he counted once.
6
7Subscriptions could have subscription_end_date (in a case of 'Unsubscribe of the period end') or this field could be empty in case of active subscription. If user make unsubscription and create new subscription the same month, he should be counted as '+1' only in a case when subscription has filled subscription_end_date with date in current month. Otherwise (subscription_end_date filled with date after current month or is empty) unsubscription of this user should not be counted.
8
9Unsubscribe count does not include unsubscribes done by Vindicia, so from total number excluded unsubscribes with reason types: VINDICIA_CARD_CLOSED, VINDICIA_CHARGE_BACK_RECEIVED, VINDICIA_UNABLE_TO_COLLECT_PAYMENT.
10
11Tricky case: user make unsubscription in May, so he counted as '+1' for unsubs in May. In September he decided to resubscribe and create a new subscription. In this case, he will disappear from May's unsubs, because he has active subscription ending outside May. So unsubs metrics could change slightly during the time. So it looks like this:
12
13    Unsubcription(May)-> Unsubs for May +1 -> Resubscription(Sep) -> Unsubs for May (-1)
14
15Daily/Monthly subscribers (https://analytics.scentbird.com/subscriptions/dailySubscriptions)
16
17Calculated by subscription_status_log table using subscription_date field. Deleted records are taken into account because metric represents a number of Subscriptions, not only alive users.
18
19'giftOrder' type is excluded by default (could be included by GUI).
20Cohort (https://analytics.scentbird.com/subscriptions/subscribersCohort)
21
22Cohorts are calculated based on subscription and its start date. Once a subscription is created, it has subscription_date and it is used to calculate this subscription per definite month(cohort) (In the case of resubscribing will create new subscription entity, so a user will appear in a new cohort, but in calculation he still should appear in cohort of his first subscription month). Every subscription should have a corresponding data in segmented_subscription_date for filtering by segment. Additional data are gathered from stripe tables (it is deprecated).
23
24Cohorts are calculated in several steps.
25
26    For target month N amount of new subscribers are calculated.
27    For N+1 month calculated amount of active subscribers who have subscription date in N month but still active in N+1 month.
28    Step 2 repeated for next M monthes.
29
30At step 1 numbers are calculated by subscription_entity tables. Deleted records are also taken into account because metric represents a number of Subscriptions, not only alive users. 'giftOrder' type is excluded by default (could be amended by GUI). !!! Highly important that counting of new subscribers counts only users who creates their first subscription in current month. So if user creates second subscription several months (at month M) later after the first subscription (at month N), he does not counted as new subscriber for month M, he will be new subscriber for month N at any case. In details getting new subscribers for current month are done in such way:
31
32    Get all new subscriptions created in current month.
33    Get all users who owns new subscriptions in current month by subscription from step 1.
34    For every user pick-up his the most early created subscription.
35    If you user has his first subscription in current month, then he counted as '+1' for new subscribers for the month.
36
37At steps 2,3 used table subscriber_status_log and subscription_entity. Deleted order are taken into account. 'giftOrder' type is excluded always (does not affected by GUI). subscriber_status_log is deprecated starting Jun 2017, so current cohorts are working only up to May 2017.
38
39At current moment (July 2017) subscriber_status_log replaced with subscription_status_log. User considered to be active for month M in cohort N in case he has his first subscription in started in month N and his current subscription in any of next states for month M: 'Subscribed', 'SkipMonth', 'Casual', 'Addicted', 'Reactivated', 'Referral', 'Unsubscribed' (call these states as active in next text). There is one exception from active statuses: 'Unsubscribed' does not count as active status in case it follows 'CardFailed' status. In case user stops his subscription in month M he is still considered to be active for month M, that is why status 'Unsubscribed' also is taken into account. Subscription status are tracked once per day. To consider subscriber as active the latest status of his subscription for current month should be at active status. Step by step calculation of active subscribers for month M for cohort N:
40
41    Get all subscriptions where the latest status is in active for month M.
42    All 'giftOrders' subscriptions are filtered.
43    Get all users who owns new subscriptions in current month by subscription from step 1.
44    For every users pick-up his the most early created subscription.
45    If you user has his first subscription in cohort month N, then he counted as '+1' for active subscribers in month M for cohort N.
46
47Tricky cases of cohorts:
48
49    Subscription(Jun)->Unsubscription(Jul)->Subscription(Aug) = User should appear and disappear in June cohort for Jul and Aug.
50    Sub->Unsub->Sub->Unsub->Sub (same month) = User should appear in cohort (1 time or 3 times?)
51
52Possible problems could be caused by mess in statuses in subscription_status_log. same_datestamp_status
53
54At screenshot Unsubscribe status has the same date as Skip Month and lower ID, but Unsubscribe is actual last status. As confirmed it was caused by one-time job that refilled missed statuses (amount of case is small and could not affect stats significantly).
55
56Examples:
57
58    User joined in May. Unsubscribed in July and rejoin in September.
59
60Cohort for June:
61
62May: 1/1 (New subscribers/Active subscribers)
63June: (0)/1
64July: (0)/1
65August: (0)/0
66September: (0)/1
67
68User does not counted as new subscriber to September cohort, cause his first subscription was in May. New subscribers are counted only once, for May. In other months new subscribers just to show that no actions could lead get user from May cohort to appear as new subscribers for other months.
69
70Cohort for September:
71
72September: 0/0
73
74    User joined in May, unsubscribed in May and joined in the same May.
75
76Cohort for May:
77
78May: 1/1 
79
80It does not matter how many subscriptions are done by user, only new clients clients should be counted, not subscriptions!
81
82Segmentation for cohort At current moment segmentation for cohorts are working for Offer, Copoun, UTMs and AppCraft UTMs (Utms downloaded from AppCraft and merged with stored data). All segmentations are working as additional filters for cohort logic. In case several filters are included (e.g. Offer = 'SECOND_MONTH_FREE', UtmSource = 'Facebook') they are joined with 'AND' clause. In AppCraft exported from AppCraft also included subscriptions done without any UTM label (direct traffic), they are also could be used for building cohorts reports. Such record have special label in UI: 'DIRECT' (AppCraft UTM source) or 'NONE' (AppCraft UTM medium). In both case the same records would be processed, so it is enough to choose only segment 'UTM source' or 'UTM medium'.
83Skip Month Cohort (https://analytics.scentbird.com/subscriptions/skipMonthCohort)
84
85Metric to show the number of people who perform skip month action in current month. Initial number of people who perform a skip month calculated in such way: taken all users who has a SkipMonth status at the end of cohort month and who has one of active statuses at the end of previous month. Calculated only users who make a transition from Active user-> Skip month user during current month.
86
87Calculation of cohort number for next month depends on choosen chart type: Retention/Churn.
88
89Retention chart: shows number of people in next monthes who make a trasition from Skip Month User -> Active User. Active user statuses are: 'Subscribed', 'Casual', 'Addicted', 'Reactivated', 'Referral'. It is important to understand that return to active status, does not mean charge of user. Example: on 2nd of Feb user make a skip for 1 month, skip end on 3rd of March, however user will be charged later. That is the reason why in Retention cohort some people return to active even on the 1st month, but the least possible SkipMonth duration is 1 month (so, it is expected that no people will return to active on 1st month). Example of retention chart: Initial skips on Feb - 100 March - 20 Arpil - 15 May - 20
90
91How it could be? On march 20 user become in active state, on may part of those user make unsubscribe or get card failed and go out of active user cohort, meanwhile some users who performed initial skip month of Feb also returns into active users but it was not enough to cover users lost due to CardFailed/Unsubscribed. On May there was the same situation but lost users number was less that number of users who make transition SkipMonth->Active.
92
93Churn chart: shows number of users who makes a transition SkipMonth->Inactive user. Inactive statuses: 'Card Failed', 'Unsubscribed'.
94
95Some of SkipMonth users could be lost due to cancel from Vindicia in the middle of skip month period. In this case user can stay in skipmonth status, but producing of statuses for next months will be stopped.
96Card Failed (https://analytics.scentbird.com/subscriptions/cardFailed)
97
98Metric is showing a number of subscriptions that were active at the start of target month but were not charged until the end of the month due to technical problems with payment gateway (not enough money, outdated card, etc.). It is important to know that 'Card Failed' should be the latest action with a subscription in the current month to be calculated. If we have a chain: Active->Card Failed->Skip Month, it should not be calculated as 'Card Failed' (actually we could not charge used the current month, but it will be counted by SkipMonth metric).
99
100Calculated by subscription_status_log table. Complex sql is used to calculate this metric. E.g. need to get the number for month N.
101
102    All subscriptions that have no card failed (in statuses 'Referral', 'Subscribed', 'SkipMonth', 'Casual', 'Addicted', 'Reactivated') at the last day of N-1 month are gathered.
103    Any subscription from step 1 that have no status 'Card Failed' at least once in month N will be deleted.
104    Any subscription from step 2 that were active (in statuses 'Casual', 'Addicted', 'SkipMonth', 'Reactivated', 'Subscribed') at the last day of month N should be deleted (it means that till end of month payment was done).
105
106'giftOrder' type is excluded.
107Plan Upgrade (https://analytics.scentbird.com/subscriptions/planUpgrade)
108
109Plan Upgrade metric shows amount of subscriptions that performed upgrade to more expensive plan. It is calculated on subscription_order table. Take into account orders in statuses 'PROCESSED','UPGRADED','UNPROCESSED' (Processed/Unprocessed means if shipment status for current month at the date of upgrade) and with shipping reasons equals 'NEW_SUBSCRIBER_UPGRADE','UPGRADE_SUBSCRIBER'.
110Churn rate (https://analytics.scentbird.com/subscriptions/churnRateExperimental2)
111
112Churn rate shows percentage of users who has churned. It calculated with number of users at start of first and with the amount of users at end of last day in month. Calculated Card Failed users and Unsubscribed users. Next calculated churn rate:
113
114churn rate % = (unsubscribed+card_failed) * 100/(start_month_users + 0.5 * (end_month_users - start_month_users))
115card failed churn rate %  = card_failed * 100/(start_month_users + 0.5 * (end_month_users - start_month_users))
116unsubscribed churn rate %  = unsubscribed * 100/(start_month_users + 0.5 * (end_month_users - start_month_users))
117
118As reference document for such formulas used this.
119LTV Cohort (https://analytics.scentbird.com/finance/ltvCohort)
120
121LTV metric show Lite Time Value of User = Amount of Money spend by average user, starting from date of subscription till date of unsubscription. LTV include subscription payment + ecommerce payments by user.
122
123It is not easy to calculate LTV in terms of current database schema. All payments are collected in table charge, but there is no separation on subscription and ecommerce - from time to time subscription and ecommerce are going in the same transaction. In this terms LTV calculated in this way:
124
125    Calculate total amount of charge for cohorts for users who have subscription (Cohort date in this case - first subscription date).
126    Calculate total amount of charge for cohorts for users who have NO subscription. Such users just register and make a payment for ecommerce only (Cohort date in this case - date of user subscription).
127    Calculate ecommerce total for cohorts for users who have subscription. Used table user_order table.
128    Calculate ecommerce total for cohorts for users who NO have subscription. Used table user_order table.
129    Ecommerce amount calculated as = 3 + 4
130    Subscription amount calculated as = (1 + 2) - (3 + 4)
131    Calculated number of users who has at least 1 charge in table charge. In this case users with subscription type 'giftAccepted' does not taken into account - there is no charge for such subscription.
132    Subscription LTV = 6 / 7
133    Ecommerce LTV = 5 / 7
134
135Cohorts by charge (https://analytics.scentbird.com/subscriptions/chargedCohort)
136
137Metrics is used to show amount of users that are make a subscription payment from month to month. As base of every cohort amount of new subscribers used. Amount of users who has payed in current month are calculated with subscription_order table:
138
139    Take all users who have subscription order in target month with status 'PROCESSED'.
140    Filter users by date of subscription (define cohort).
141    Exclude free orders (orders by referal points) using table shipment_type.
142
143shipment_type is a table that existing in Redshift datastore. The table is populated by job from subscription_credit_log table. In calculation of new subscribers/paying subscribers 'giftAccept' and 'giftOrder' orders are excluded. Subscription order data exists from Jan 2017. Shipment type data exists from Jun 2017.