LookML view · dating-platform

The churn view: leads, upgrades, downgrades and renewals per day in one UNION

At a glance

Does: Unions four counts per site, source and calendar day: new users as leads, downgrades, upgrades and renewals. Exposes day, month, site, source and four sums.Trust: Source-defined
Why: A subscription movement report. The enterprise model labels its explore Churn Report, for tracking upgrades against downgrades by acquisition source.Trust: Inferred
Breaks: 3 warnings. Top: Downgrades multiply by upgrade transactions
Grain: One row per site, source and calendar day after the outer GROUP BY. No primary key is declared.Trust: Source-defined
Source: dating_platform/membership_activity.view.lookml 115 lines

Source: membership_activity.view.lookml L1-10

dating_platform/membership_activity.view.lookmlL1-10 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,

dating-platform, membership_activity.view.lookml, lines 1-10

Shape

Source-defined

Every field as declared, with the dashboard tiles that read it. Computed from the source; nothing here is edited.

Derived table: persists for 1 hour. Reads warehouse tables directly.

Dimensions 4

Name	Type	SQL	Used by
`day`L73	date	`${TABLE}.calendar_date`	-
`month`L76	date_month	`${TABLE}.calendar_date`	-
`site`L79	string	`${TABLE}.site`	-
`source`L81	string	`${TABLE}.source`	-

Measures 4

Name	Type	SQL	Used by
`leads`L83	sum	`${TABLE}.leads`	-
`downgrades`L87	sum	`${TABLE}.downgrades`	-
`renewals`L91	sum	`${TABLE}.renewals`	-
`upgrades`L95	sum	`${TABLE}.upgrades`	-

Relationships

Source-defined

No joins, no upstream views and no dashboard tiles reference this view. It is an island in the model.

Relationships of churn

This Block

Text version

churn is the base view of churn: base view

Filters and exclusions that change meaning

A downgrade is an auto-renew-off subscription past its renewal date

Source-defined

The downgrade branch counts subscriptions with auto_renew = false and next_renewal_on before today, dated on the renewal day. Cancellations with time left are not counted yet.

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

Worth noticing

Each event type has its own date

Source-defined

Leads use the user created_at, upgrades and renewals use the transaction created_at, and downgrades use next_renewal_on. One day mixes three clocks.

Source: membership_activity.view.lookml L9-27

dating_platform/membership_activity.view.lookmlL9-27 Show context

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,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.id

dating-platform, membership_activity.view.lookml, lines 9-27

Sets copied from a transactions view

Source-defined

The detail and users sets list amount, gross_revenue, credit_card_type and other fields that churn does not declare.

Source: membership_activity.view.lookml L99-115

dating_platform/membership_activity.view.lookmlL99-115 Show context

96    type: sum97    sql: ${TABLE}.upgrades98    detail: detail*99  sets:100    detail:101    - id102    - users.id103    - amount104    - transaction_type105    users:106    - id107    - transaction_type108    - type109    - payment_type110    - gross_revenue111    - amount112    - net_revenue113    - refunds114    - user_id115    - credit_card_type

dating-platform, membership_activity.view.lookml, lines 99-115

What can change the answer 3

Blocker: Downgrades multiply by upgrade transactions

Source-defined

The downgrade branch joins user_transactions on user_id with T.type = 'upgrade'. A user with three upgrade transactions adds three downgrades for one subscription.

What goes wrong: downgrades overstates churn for the most engaged upgraders, and net movement looks worse than it is.

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: No churn rate is defined

Source-defined

The explore is labelled Churn Report, but the view only sums counts. Any rate has to be built by hand, and the source defines no denominator.

What goes wrong: Two analysts can publish different churn rates from the same explore.

Source: membership_activity.view.lookml L83-98

dating_platform/membership_activity.view.lookmlL83-98 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}.downgrades90    detail: detail*91  - measure: renewals92    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 83-98

Note: Reads tables, not views

Source-defined

The derived table selects from users, subscriptions and user_transactions directly. users and user_transactions are referenced as LookML views elsewhere but are not in the repo.

Read the guide: Missing tables and stale references

Source: membership_activity.view.lookml L29-33

dating_platform/membership_activity.view.lookmlL29-33 Show context

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,

dating-platform, membership_activity.view.lookml, lines 29-33

Parser checks 14

Source-defined

Mechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.

Show all 14 checks

no-primary-key 1
- membership_activity.view.lookml:1 churn declares no primary_key.
set-member-missing 13
- membership_activity.view.lookml:100 Set churn.detail lists "id", which is not a field or set in this view.
- membership_activity.view.lookml:100 Set churn.detail lists "amount", which is not a field or set in this view.
- membership_activity.view.lookml:100 Set churn.detail lists "transaction_type", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "id", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "transaction_type", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "type", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "payment_type", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "gross_revenue", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "amount", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "net_revenue", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "refunds", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "user_id", which is not a field or set in this view.
- membership_activity.view.lookml:105 Set churn.users lists "credit_card_type", which is not a field or set in this view.

At a glance

Shape

Dimensions 4

Measures 4

Relationships

Filters and exclusions that change meaning

A downgrade is an auto-renew-off subscription past its renewal date

Worth noticing

Each event type has its own date

Sets copied from a transactions view

What can change the answer 3

Blocker: Downgrades multiply by upgrade transactions

Caution: No churn rate is defined

Note: Reads tables, not views

Parser checks 14

Related

Guides built on this file

Related source files

Source model