DataBull Search
dating_platform/memberships.view.lookml 13 dims1 measure 3 warnings Repo files

LookML view · dating-platform

The subscriptions view: renewal dates, auto-renew and two definitions of active

At a glance

Does
Reads one row per subscription from public.subscriptions: status, auto_renew, trial_days, renewal period and unit, payment profile and source, with a count measure.Trust: Source-defined
Why
The membership record behind revenue and churn analysis: who is subscribed, on what terms, and when they renew.Trust: Inferred
Breaks
3 warnings. Top: currently_active ignores status
Grain
One row per subscription. id is the primary key and user_id is hidden.Trust: Source-defined
Source
dating_platform/memberships.view.lookml 58 lines
Source: memberships.view.lookml L1-10
dating_platform/memberships.view.lookmlL1-10
- view: subscriptions  sql_table_name: public.subscriptions  fields:  - dimension: id    primary_key: true    type: int    sql: ${TABLE}.id  - dimension: auto_renew    type: yesno    sql: ${TABLE}.auto_renew  - dimension_group: created    type: time    timeframes:

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

Shape

Trust: Source-defined

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

Table: public.subscriptions

Dimensions 13
NameTypeSQLUsed byTags
idL4int${TABLE}.id-primary key
auto_renewL8yesno${TABLE}.auto_renew-
createdtime, date, week, monthL11time group${TABLE}.created_at-
next_renewalL19time group${TABLE}.next_renewal_on-
payment_profile_typeL22string${TABLE}.payment_profile_type-
period_unitL24string${TABLE}.period_unit-
renewal_periodL26int${TABLE}.renewal_period-
sourceL29string${TABLE}.source-
statusL31string${TABLE}.status-
trial_daysL33int${TABLE}.trial_days-
currently_activeL36yesno${TABLE}.next_renewal_on > GETDATE()-derived
updatedtime, date, week, monthL39time group${TABLE}.updated_at-
user_idL47int${TABLE}.user_id-hidden

Measures 1
NameTypeSQLUsed byTags
countL51countCOUNT(*) of rows-

Relationships

Trust: Source-defined
Relationships of subscriptions
Relationships of subscriptions. Text version follows the diagram.This viewUsed by: explores, views, dashboardsas subscriptions, fk transactions.subscription_id transactions explore in enterprise
  • This Block
  • No relationship declared
Text version
  • subscriptions is joined into transactions: as subscriptions, fk transactions.subscription_id (no relationship declared)

Worth noticing

next_renewal is a date-only group

Trust: Source-defined

next_renewal is a dimension_group with type: date and no timeframes, unlike the type: time groups beside it.

Source: memberships.view.lookml L19-21
dating_platform/memberships.view.lookmlL19-21
    - week    - month    sql: ${TABLE}.created_at  - dimension_group: next_renewal    type: date    sql: ${TABLE}.next_renewal_on  - dimension: payment_profile_type    sql: ${TABLE}.payment_profile_type  - dimension: period_unit

dating-platform, memberships.view.lookml, lines 19-21

Joined for keys only

Trust: Source-defined

The enterprise transactions explore joins subscriptions on transactions.subscription_id with fields: [].

Source: enterprise.model.lookml L14-16
dating_platform/enterprise.model.lookmlL14-16
  joins:  - join: users    sql_foreign_key: transactions.user_id  - join: subscriptions    sql_foreign_key: transactions.subscription_id    fields: []  - join: user_score    sql_foreign_key: users.id    fields: []

dating-platform, enterprise.model.lookml, lines 14-16

What can change the answer 3

Caution: currently_active ignores status

Trust: Source-defined

currently_active is true when next_renewal_on is after today. It does not read status, so a cancelled subscription with time left counts as active.

What goes wrong: Active subscriber counts differ depending on whether a report uses status or currently_active.

Read the guide: Filters that change metric meaning

Source: memberships.view.lookml L36-38
dating_platform/memberships.view.lookmlL36-38
  - dimension: trial_days    type: int    sql: ${TABLE}.trial_days  - dimension: currently_active    type: yesno    sql: ${TABLE}.next_renewal_on > GETDATE()  - dimension_group: updated    type: time    timeframes:

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

Note: Downgrades in churn depend on these columns

Trust: Source-defined

churn counts a downgrade from auto_renew = false and a past next_renewal_on, reading the same table outside this view.

Source: memberships.view.lookml L8-10
dating_platform/memberships.view.lookmlL8-10
    primary_key: true    type: int    sql: ${TABLE}.id  - dimension: auto_renew    type: yesno    sql: ${TABLE}.auto_renew  - dimension_group: created    type: time    timeframes:

dating-platform, memberships.view.lookml, lines 8-10

Note: The drill set reaches a missing view

Trust: Source-defined

The detail set lists users.id, and no users view is in the repo.

Read the guide: Missing tables and stale references

Source: memberships.view.lookml L54-58
dating_platform/memberships.view.lookmlL54-58
  - measure: count    type: count    detail: detail*  sets:    detail:    - id    - users.id    - next_renewal

dating-platform, memberships.view.lookml, lines 54-58

Related

Guides built on this file

Guide Modeling upgrades, downgrades and renewals as daily movements How a real subscription product stacks leads, upgrades, downgrades and renewals per day, how it defines active, and the fanout hidden in its downgrades. RetentionRevenue Guide Filters that quietly change what a metric means Why two reports disagree on one metric: hidden default filters, tile filters and unwired dashboard filters, with a checklist and real examples. Best practicesAcquisitionRetention Guide Catching missing tables and stale references before a dashboard breaks Models can name tables, fields and explores that do not exist, and nothing fails until a chart runs. Real examples, and how to check yours before release. Best practices

Related source files

Source file churn Unions four counts per site, source and calendar day: new users as leads, downgrades, upgrades and renewals... view3 warnings Source file enterprise Declares six explores on project_connection: churn, transactions, messages, propositions, date_ideas and te... model4 warnings Source file transactions Builds web orders from transaction events: order id, affiliation, total, tax, city, state, country and the ... view3 warnings Source file user_score Joined with it in the transactions explore view3 warnings

Source model

Source model Inside a dating platform LookML project: sessions, revenue, subscriptions and messaging Product, revenue and customer analytics 17 views20 joins