Retention and engagement

Modeling upgrades, downgrades and renewals as daily movements

How many members upgrade, renew or lapse each day, by site and acquisition source?

Based on this implementation

dating-platform: Product, revenue and customer analytics. LookML, Redshift SQL

2 source files

Code excerpts are short and verbatim. SQL is translated from them and has not been run against a warehouse.

Trust key

Trust: Source-defined: Directly observable in the repo.
Trust: Translated: Generated from the source definitions automatically; not run against a warehouse.
Trust: Inferred: Interpretation based on names, usage or structure.
Trust: Suggested: Not in the source. An alternative or extension.
Trust: Missing: Needed to establish correctness but not in the repo.

See the full dating-platform source model

Short answer

Inferred

Treat each movement as a dated event, count it on the day it happens at one grain (here site, source and day), and check every branch counts the population you mean, once. In this project the downgrade branch joins to every upgrade a member made, so one lapse can count three times and members who never upgraded are missed, and there is no active count to turn movements into a churn rate.

Decision it supports: Where retention effort and renewal reminders should go.

What to measure

Leads

Source-defined

Users created that day.

Defined as churn.leads

SUM(churn.leads)Trust: Translated

Show the SQL: Leads

Redshift SQLTranslated

SUM(churn.leads)

Translated to Redshift SQL from membership_activity.view.lookml L83-86. Not run against a warehouse.

Show the original LookML: membership_activity.view.lookml L83-86

dating_platform/membership_activity.view.lookmlL83-86 Show context

80    sql: ${TABLE}.site81  - dimension: source82    sql: ${TABLE}.source83  - measure: leads84    type: sum85    sql: ${TABLE}.leads86    detail: detail*87  - measure: downgrades88    type: sum89    sql: ${TABLE}.downgrades

dating-platform, membership_activity.view.lookml, lines 83-86

Upgrades

Source-defined

Upgrade transactions that day.

Defined as churn.upgrades

SUM(churn.upgrades)Trust: Translated

Show the SQL: Upgrades

Redshift SQLTranslated

SUM(churn.upgrades)

Translated to Redshift SQL from membership_activity.view.lookml L95-98. Not run against a warehouse.

Show the original LookML: membership_activity.view.lookml L95-98

dating_platform/membership_activity.view.lookmlL95-98 Show context

92    type: sum93    sql: ${TABLE}.renewals94    detail: detail*95  - measure: upgrades96    type: sum97    sql: ${TABLE}.upgrades98    detail: detail*99  sets:100    detail:101    - id

dating-platform, membership_activity.view.lookml, lines 95-98

Downgrades

Source-defined

Lapsed subscriptions with auto-renew off, dated by renewal date, and only for members with an upgrade transaction: members who never upgraded are not counted.

Defined as churn.downgrades

SUM(churn.downgrades)Trust: Translated

Show the SQL: Downgrades

Redshift SQLTranslated

SUM(churn.downgrades)

Translated to Redshift SQL from membership_activity.view.lookml L87-90. Not run against a warehouse.

Show the original LookML: membership_activity.view.lookml L87-90

dating_platform/membership_activity.view.lookmlL87-90 Show context

84    type: sum85    sql: ${TABLE}.leads86    detail: detail*87  - measure: downgrades88    type: sum89    sql: ${TABLE}.downgrades90    detail: detail*91  - measure: renewals92    type: sum93    sql: ${TABLE}.renewals

dating-platform, membership_activity.view.lookml, lines 87-90

Renewals

Source-defined

Renewal transactions that day.

Defined as churn.renewals

SUM(churn.renewals)Trust: Translated

Show the SQL: Renewals

Redshift SQLTranslated

SUM(churn.renewals)

Translated to Redshift SQL from membership_activity.view.lookml L91-94. Not run against a warehouse.

Show the original LookML: membership_activity.view.lookml L91-94

dating_platform/membership_activity.view.lookmlL91-94 Show context

88    type: sum89    sql: ${TABLE}.downgrades90    detail: detail*91  - measure: renewals92    type: sum93    sql: ${TABLE}.renewals94    detail: detail*95  - measure: upgrades96    type: sum97    sql: ${TABLE}.upgrades

dating-platform, membership_activity.view.lookml, lines 91-94

Active subscription

Source-defined

Active means the next renewal date is in the future. The status column may disagree.

Defined as subscriptions.currently_active

CASE WHEN subscriptions.next_renewal_on > GETDATE() THEN 'yes' ELSE 'no' ENDTrust: Translated

Show the SQL: Active subscription

Redshift SQLTranslated

CASE WHEN subscriptions.next_renewal_on > GETDATE() THEN 'yes' ELSE 'no' END

Translated to Redshift SQL from memberships.view.lookml L36-38. Not run against a warehouse.

Show the original LookML: memberships.view.lookml L36-38

dating_platform/memberships.view.lookmlL36-38 Show context

33  - dimension: trial_days34    type: int35    sql: ${TABLE}.trial_days36  - dimension: currently_active37    type: yesno38    sql: ${TABLE}.next_renewal_on > GETDATE()39  - dimension_group: updated40    type: time41    timeframes:

dating-platform, memberships.view.lookml, lines 36-38

Churn rate

Missing

The explore is labelled Churn Report but defines no rate. It needs active subscriptions as a denominator, which the view does not have.

The query

Movements by month

Suggested

Leads, upgrades, downgrades and renewals per month, read from the daily movements table. No dashboard tile reads this explore, so the field combination is ours.

Show the SQL: movements by month

Redshift SQLSuggested

SELECT  DATE_TRUNC('month', churn.calendar_date) AS month,  SUM(churn.leads) AS leads,  SUM(churn.upgrades) AS upgrades,  SUM(churn.downgrades) AS downgrades,  SUM(churn.renewals) AS renewalsFROM churn  -- derived table, defined in the sourceGROUP BY 1ORDER BY month

Suggested query. Fields translated to Redshift SQL from enterprise.model.lookml L5-7 and membership_activity.view.lookml L76-78, L83-98. Not run against a warehouse.

The data behind it

Inferred

Entities: user, subscription, user transaction
Grain: churn: one row per site, source and calendar day.
Time: day and month. Each movement is dated by a different column.
Key fields: churn.sitechurn.sourcechurn.daysubscriptions.auto_renewsubscriptions.next_renewalsubscriptions.status

What can change the answer 3

Caution: Downgrades fan out by upgrade count

Inferred

The downgrade branch joins each subscription to every upgrade transaction of the user.

What goes wrong: A member with three past upgrades counts as three downgrades.

Read the guide: Join fanout

Source: membership_activity.view.lookml L22-38

dating_platform/membership_activity.view.lookmlL22-38 Show context

19                 U.source,20                 trunc(U.created_at)21        UNION ALL22        SELECT    U.site,23                  U.source,24                  TRUNC(S.next_renewal_on) AS calendar_date,25                  0,26                  COUNT(1) AS downgrades,27                  0,28                  0 AS renewals29        FROM  subscriptions S30              JOIN users U ON S.user_id = U.id31              JOIN user_transactions T32              ON  S.user_id = T.user_id33                  AND T.type = 'upgrade'34        WHERE S.auto_renew = false35              AND S.next_renewal_on < GETDATE()36        GROUP BY U.site,37                 U.source,38                 trunc(S.next_renewal_on)39        UNION ALL40        SELECT  U.site,41                U.source,

dating-platform, membership_activity.view.lookml, lines 22-38

Caution: Downgrades are dated by renewal date

Inferred

A downgrade counts on next_renewal_on once that date has passed. It measures lapses, not the day a member turned off auto-renew.

Source: membership_activity.view.lookml L22-35

dating_platform/membership_activity.view.lookmlL22-35 Show context

19                 U.source,20                 trunc(U.created_at)21        UNION ALL22        SELECT    U.site,23                  U.source,24                  TRUNC(S.next_renewal_on) AS calendar_date,25                  0,26                  COUNT(1) AS downgrades,27                  0,28                  0 AS renewals29        FROM  subscriptions S30              JOIN users U ON S.user_id = U.id31              JOIN user_transactions T32              ON  S.user_id = T.user_id33                  AND T.type = 'upgrade'34        WHERE S.auto_renew = false35              AND S.next_renewal_on < GETDATE()36        GROUP BY U.site,37                 U.source,38                 trunc(S.next_renewal_on)

dating-platform, membership_activity.view.lookml, lines 22-35

Note: Two definitions of active

Inferred

currently_active uses the renewal date while status holds the system state. They can disagree for paused or refunded subscriptions.

Show the SQL: currently active

Redshift SQLTranslated

CASE WHEN subscriptions.next_renewal_on > GETDATE() THEN 'yes' ELSE 'no' END

Translated to Redshift SQL from memberships.view.lookml L36-38. Not run against a warehouse.

Show the original LookML: memberships.view.lookml L36-38

dating_platform/memberships.view.lookmlL36-38 Show context

33  - dimension: trial_days34    type: int35    sql: ${TABLE}.trial_days36  - dimension: currently_active37    type: yesno38    sql: ${TABLE}.next_renewal_on > GETDATE()39  - dimension_group: updated40    type: time41    timeframes:

dating-platform, memberships.view.lookml, lines 36-38

How the real implementation does it

churn

Source-defined

Leads, downgrades, upgrades and renewals are stacked with UNION ALL and summed per site, source and day.

Source: membership_activity.view.lookml L4-20

dating_platform/membership_activity.view.lookmlL4-20 Show context

1- view: churn2  derived_table:3    sql: |4      SELECT  site, source, calendar_date,5              SUM(upgrades) AS upgrades,6              SUM(downgrades) AS downgrades,7              SUM(leads) AS leads,8              SUM(renewals) AS renewals9      FROM  (10        SELECT  U.site,11                 U.source,12                 TRUNC(U.created_at) AS calendar_date,13                 COUNT(1) AS leads,14                 0 AS downgrades,15                 0 AS upgrades,16                 0 AS renewals17          FROM  users U18          GROUP BY U.site,19                 U.source,20                 trunc(U.created_at)21        UNION ALL22        SELECT    U.site,23                  U.source,

dating-platform, membership_activity.view.lookml, lines 4-20

churn

Source-defined

The downgrade branch finds subscriptions with auto-renew off whose renewal date has passed, joined to the member's upgrade transactions, so members who never upgraded drop out.

Source: membership_activity.view.lookml L22-38

dating_platform/membership_activity.view.lookmlL22-38 Show context

19                 U.source,20                 trunc(U.created_at)21        UNION ALL22        SELECT    U.site,23                  U.source,24                  TRUNC(S.next_renewal_on) AS calendar_date,25                  0,26                  COUNT(1) AS downgrades,27                  0,28                  0 AS renewals29        FROM  subscriptions S30              JOIN users U ON S.user_id = U.id31              JOIN user_transactions T32              ON  S.user_id = T.user_id33                  AND T.type = 'upgrade'34        WHERE S.auto_renew = false35              AND S.next_renewal_on < GETDATE()36        GROUP BY U.site,37                 U.source,38                 trunc(S.next_renewal_on)39        UNION ALL40        SELECT  U.site,41                U.source,

dating-platform, membership_activity.view.lookml, lines 22-38

subscriptions

Source-defined

Active is computed from the renewal date, not the status column.

Show the SQL: active subscription

Redshift SQLTranslated

CASE WHEN subscriptions.next_renewal_on > GETDATE() THEN 'yes' ELSE 'no' END

Translated to Redshift SQL from memberships.view.lookml L36-38. Not run against a warehouse.

Show the original LookML: memberships.view.lookml L36-38

dating_platform/memberships.view.lookmlL36-38 Show context

33  - dimension: trial_days34    type: int35    sql: ${TABLE}.trial_days36  - dimension: currently_active37    type: yesno38    sql: ${TABLE}.next_renewal_on > GETDATE()39  - dimension_group: updated40    type: time41    timeframes:

dating-platform, memberships.view.lookml, lines 36-38

Other ways to measure it

Count each lapse once

Suggested

Use EXISTS against upgrades instead of a join, so each lapsed subscription counts once.

Show the suggested SQL: churn downgrades branch

Suggested, not from source

SELECT U.site, U.source, TRUNC(S.next_renewal_on) AS calendar_date,       COUNT(DISTINCT S.id) AS downgradesFROM subscriptions SJOIN users U ON S.user_id = U.idWHERE S.auto_renew = false  AND S.next_renewal_on < GETDATE()  AND EXISTS (SELECT 1 FROM user_transactions T              WHERE T.user_id = S.user_id AND T.type = 'upgrade')GROUP BY 1, 2, 3

Add a daily active snapshot

Suggested

Store a daily count of active subscriptions, so churn rate has a denominator. Rebuilding history from today's renewal dates, as below, only approximates the past, because renewals overwrite them; start a daily snapshot and keep each day's result.

Show the suggested SQL: daily active snapshot

Suggested, not from source

-- calendar: one row per day, which you supplySELECT  D.calendar_date,  U.site,  U.source,  COUNT(DISTINCT S.id) AS active_subscriptionsFROM calendar DJOIN subscriptions S  ON S.created_at <= D.calendar_date AND S.next_renewal_on > D.calendar_dateJOIN users U ON S.user_id = U.idGROUP BY 1, 2, 3

Short answer

What to measure

Leads

Upgrades

Downgrades

Renewals

Active subscription

Churn rate

The query

Movements by month

The data behind it

What can change the answer 3

Caution: Downgrades fan out by upgrade count

Caution: Downgrades are dated by renewal date

Note: Two definitions of active

How the real implementation does it

churn

churn

subscriptions

Other ways to measure it

Count each lapse once

Add a daily active snapshot

Related questions