LookML view · dating-platform

The user_score view: lead score, expected LTV and lead value per user

At a glance

Does: Reads one row per user from public.user_score: score, score_pre_signup, demographic_quality, expected_ltv and value. Three measures average them.Trust: Source-defined
Why: Ranks users and leads by predicted worth, so acquisition and product teams can compare cohorts by expected value.Trust: Inferred
Breaks: 3 warnings. Top: Average expected LTV overstates value
Grain: One row per user. id is a hidden primary key that the model joins to user ids.Trust: Inferred
Source: dating_platform/customer_score.view.lookml 50 lines

Source: customer_score.view.lookml L1-10

dating_platform/customer_score.view.lookmlL1-10 Show context

1- view: user_score2  sql_table_name: public.user_score3  fields:4  - dimension: id5    primary_key: true6    type: int7    sql: ${TABLE}.id8    hidden: true9  - dimension: demographic_quality10    type: number11    sql: ${TABLE}.demographic_quality12    decimals: 413  - dimension: expected_ltv

dating-platform, customer_score.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.

Table: public.user_score

Dimensions 6

Name	Type	SQL	Used by	Tags
`id`L4	int	`${TABLE}.id`	-	primary keyhidden
`demographic_quality`L9	number	`${TABLE}.demographic_quality`	-
`expected_ltv`L13	number	`${TABLE}.expected_ltv_if_converted`	-
`score`L17	number	`${TABLE}.score`	-
`score_pre_signup`L21	number	`${TABLE}.score_pre_signup`	-
`value`L25	number	`${TABLE}.value_of_lead`	-

Measures 3

Name	Type	SQL	Used by	Tags
`avg_score`L29	avg	`${score}`	-	derived
`avg_expected_ltv`L34	avg	`${expected_ltv}`	-	derived
`avg_value`L39	avg	`${value}`	-	derived

Relationships

Source-defined

Relationships of user_score

This Block
No relationship declared

Text version

user_score is joined into transactions: as user_score, fk users.id (no relationship declared)
user_score is joined into tests: as user_score, fk user_metrics.id (no relationship declared)

Worth noticing

expected_ltv hides a condition

Source-defined

expected_ltv reads expected_ltv_if_converted. It is the lifetime value if the user converts, not a probability-weighted value.

Source: customer_score.view.lookml L13-16

dating_platform/customer_score.view.lookmlL13-16 Show context

10    type: number11    sql: ${TABLE}.demographic_quality12    decimals: 413  - dimension: expected_ltv14    type: number15    sql: ${TABLE}.expected_ltv_if_converted16    decimals: 217  - dimension: score18    type: number19    sql: ${TABLE}.score

dating-platform, customer_score.view.lookml, lines 13-16

Joined only for keys

Source-defined

The transactions and tests explores join user_score with fields: [], so none of its fields can be selected there.

Source: enterprise.model.lookml L17-19

dating_platform/enterprise.model.lookmlL17-19 Show context

14  - join: subscriptions15    sql_foreign_key: transactions.subscription_id16    fields: []17  - join: user_score18    sql_foreign_key: users.id19    fields: []20- explore: messages21  view: messages22  always_filter:

dating-platform, enterprise.model.lookml, lines 17-19

What can change the answer 3

Caution: Average expected LTV overstates value

Inferred

avg_expected_ltv averages a value that assumes conversion. Leads who never convert count at their full converted value.

What goes wrong: A cohort chart of average expected LTV reads like revenue per user when most users never pay.

Source: customer_score.view.lookml L34-38

dating_platform/customer_score.view.lookmlL34-38 Show context

31    sql: ${score}32    decimals: 433    detail: detail*34  - measure: avg_expected_ltv35    type: avg36    sql: ${expected_ltv}37    decimals: 238    detail: detail*39  - measure: avg_value40    type: avg41    sql: ${value}

dating-platform, customer_score.view.lookml, lines 34-38

Caution: The dashboard asks for a view named scores

Source-defined

The Average Expected LTV tile requests scores.avg_expected_ltv from a users explore. No view or join is named scores, and no model declares a users explore.

What goes wrong: The only dashboard use of this view cannot run.

Read the guide: Missing tables and stale references

Source: company_overview.dashboard.lookml L90-100

dating_platform/company_overview.dashboard.lookmlL90-100 Show context

87    width: null88    height: null89    legend_align: null90  - name: average_lifetime_value91    title: Average Expected LTV92    type: looker_line93    explore: users94    dimensions:95    - users.created_month96    measures:97    - scores.avg_expected_ltv98    listen:99      date_filter: users.created_date100    limit: 500101    width: null102    height: null103    legend_align: null

dating-platform, company_overview.dashboard.lookml, lines 90-100

Note: value is a lead-stage value

Source-defined

value reads value_of_lead. The short field name hides that it is a lead-stage value.

Source: customer_score.view.lookml L25-28

dating_platform/customer_score.view.lookmlL25-28 Show context

22    type: number23    sql: ${TABLE}.score_pre_signup24    decimals: 1025  - dimension: value26    type: number27    sql: ${TABLE}.value_of_lead28    decimals: 429  - measure: avg_score30    type: avg31    sql: ${score}

dating-platform, customer_score.view.lookml, lines 25-28

At a glance

Shape

Dimensions 6

Measures 3

Relationships

Worth noticing

expected_ltv hides a condition

Joined only for keys

What can change the answer 3

Caution: Average expected LTV overstates value

Caution: The dashboard asks for a view named scores

Note: value is a lead-stage value

Related

Guides built on this file

Related source files

Source model