LookML view · dating-platform

The user_tenure_metrics view: activity at day 1, 3 and 7 in one derived table

At a glance

Does: Cross joins users with a numbers table for tenure 1, 3 and 7, then sums 30-day lifecycle metrics (photos, dates posted, views, messages, replies) up to each tenure.Trust: Source-defined
Why: Early-engagement benchmarks: what a user has done by day 1, 3 and 7, for activation analysis and for comparing experiment buckets.Trust: Inferred
Breaks: 3 warnings. Top: The primary key is not unique
Grain: One row per user per tenure, so three rows per user. The declared primary key is id alone, which is not unique.Trust: Source-defined
Source: dating_platform/customer_loyalty_metrics.view.lookml 85 lines

Source: customer_loyalty_metrics.view.lookml L1-10

dating_platform/customer_loyalty_metrics.view.lookmlL1-10 Show context

1- view: user_tenure_metrics2  derived_table:3    sql: |4      SELECT  users.id,5              table_of_numbers.i AS tenure,6              SUM(COALESCE(M.photo_uploads,0)) AS photo_uploads,7              MAX(COALESCE(M.has_conversion,0)) AS has_conversion,8              SUM(COALESCE(M.unique_dates_posted,0)) AS unique_dates_posted,9              SUM(COALESCE(M.dates_posted,0)) AS dates_posted,10              SUM(COALESCE(M.profile_views,0)) AS profile_views,11              SUM(COALESCE(M.quick_views,0)) AS quick_views,12              SUM(COALESCE(M.sent_messages,0)) AS sent_messages,13              SUM(COALESCE(M.sent_messages_from_speed_date,0)) AS messages_sent_from_speed_date,

dating-platform, customer_loyalty_metrics.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 24 hour. Reads warehouse tables directly.

Dimensions 15

Name	Type	SQL	Used by	Tags
`id`L36	string	`${TABLE}.id`	-	primary keyhidden
`tenure`L40	int	`${TABLE}.tenure`	-
`photo_uploads_by_tenure`L43	number	`${TABLE}.photo_uploads`	-	hidden
`unique_dates_posted_by_tenure`L47	number	`${TABLE}.unique_dates_posted_by_tenure (default)`	-	implicit sql
`dates_posted_by_tenure`L49	number	`${TABLE}.dates_posted`	-
`has_conversion_by_tenure`L52	yesno	`${TABLE}.has_conversion`	-
`questions_answered_by_tenure`L55	number	`${TABLE}.questions_answered`	-
`messages_sent_by_tenure`L58	number	`${TABLE}.sent_messages`	-
`replies_received_by_tenure`L61	number	`${TABLE}.replies_received`	-
`intrigues_sent_from_speed_date_by_tenure`L64	number	`${TABLE}.intrigues_sent_from_speed_date`	-
`messages_sent_from_speed_date_by_tenure_by_tenure`L67	number	`${TABLE}.messages_sent_from_speed_date`	-
`profile_views_by_tenure`L70	number	`${TABLE}.profile_views`	-
`quick_views_by_tenure`L73	number	`${TABLE}.quick_views`	-
`messages_received_from_date_stream_by_tenure`L76	int	`${TABLE}.messages_received_from_date_stream_by_tenure...` `${TABLE}.messages_received_from_date_stream_by_tenure (default)`	-	hiddenimplicit sql
`date_responses_receieved_by_tenure`L79	int	`${TABLE}.date_responses_receieved_by_tenure (default)`	-	hiddenimplicit sql

Relationships

Source-defined

Relationships of user_tenure_metrics

This Block
No relationship declared

Text version

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

Worth noticing

Tenure 1 only includes day 0

Inferred

Metrics join on M.lifecycle_day < table_of_numbers.i. If lifecycle days start at 0, tenure 1 sums day 0 only and tenure 7 sums days 0 to 6.

Source: customer_loyalty_metrics.view.lookml L20-26

dating_platform/customer_loyalty_metrics.view.lookmlL20-26 Show context

17              SUM(COALESCe(M.date_responses_receieved, 0)) AS date_responses_receieved,18              SUM(COALESCE(M.messages_received_from_date_stream, 0)) AS messages_received_from_date_stream,19              SUM(COALESCE(M.impressions, 0)) AS impressions20      FROM    users21              JOIN table_of_numbers22              ON  table_of_numbers.i <= DATEDIFF(DAY, users.created_at, GETDATE())23              LEFT JOIN ${TABLE}.user_lifecycle_metrics_30_days M24              ON  M.id = users.id25                  AND M.lifecycle_day < table_of_numbers.i26      WHERE   table_of_numbers.i IN (1,3,7)27      GROUP BY  users.id,28                table_of_numbers.i29      ORDER BY users.id DESC

dating-platform, customer_loyalty_metrics.view.lookml, lines 20-26

There are no measures

Source-defined

Every field is a dimension. To total or average activity by tenure, a reader has to add measures or export rows.

Source: customer_loyalty_metrics.view.lookml L40-51

dating_platform/customer_loyalty_metrics.view.lookmlL40-51 Show context

37    primary_key: true38    hidden: true39    sql: ${TABLE}.id40  - dimension: tenure41    type: int42    sql: ${TABLE}.tenure43  - dimension: photo_uploads_by_tenure44    type: number45    sql: ${TABLE}.photo_uploads46    hidden: true47  - dimension: unique_dates_posted_by_tenure48    type: number49  - dimension: dates_posted_by_tenure50    type: number51    sql: ${TABLE}.dates_posted52  - dimension: has_conversion_by_tenure53    type: yesno54    sql: ${TABLE}.has_conversion

dating-platform, customer_loyalty_metrics.view.lookml, lines 40-51

What can change the answer 3

Caution: The primary key is not unique

Source-defined

id is the primary key, but each user appears three times, once per tenure. Symmetric aggregates rely on that key being unique.

What goes wrong: Any measure added to this view, or a join to it, can triple counts per user.

Read the guide: Primary keys and counts

Source: customer_loyalty_metrics.view.lookml L36-42

dating_platform/customer_loyalty_metrics.view.lookmlL36-42 Show context

33    - id34    - tenure35  fields:36  - dimension: id37    primary_key: true38    hidden: true39    sql: ${TABLE}.id40  - dimension: tenure41    type: int42    sql: ${TABLE}.tenure43  - dimension: photo_uploads_by_tenure44    type: number45    sql: ${TABLE}.photo_uploads

dating-platform, customer_loyalty_metrics.view.lookml, lines 36-42

Caution: Three fields read columns the derived table does not produce

Source-defined

unique_dates_posted_by_tenure, messages_received_from_date_stream_by_tenure and date_responses_receieved_by_tenure have no sql, so they default to ${TABLE}.<field name>. The derived table outputs unique_dates_posted and the other columns without the _by_tenure suffix.

What goes wrong: Selecting any of them fails with an unknown column error.

Read the guide: Missing tables and stale references

Source: customer_loyalty_metrics.view.lookml L76-81

dating_platform/customer_loyalty_metrics.view.lookmlL76-81 Show context

73  - dimension: quick_views_by_tenure74    type: number75    sql: ${TABLE}.quick_views76  - dimension: messages_received_from_date_stream_by_tenure77    type: int78    hidden: true79  - dimension: date_responses_receieved_by_tenure80    type: int81    hidden: true82  sets:83    detail:84    - id

dating-platform, customer_loyalty_metrics.view.lookml, lines 76-81

Note: A doubled suffix and a misspelling

Source-defined

messages_sent_from_speed_date_by_tenure_by_tenure repeats _by_tenure, and receieved is misspelled in both the SQL and the field name.

Source: customer_loyalty_metrics.view.lookml L67-69

dating_platform/customer_loyalty_metrics.view.lookmlL67-69 Show context

64  - dimension: intrigues_sent_from_speed_date_by_tenure65    type: number66    sql: ${TABLE}.intrigues_sent_from_speed_date67  - dimension: messages_sent_from_speed_date_by_tenure_by_tenure68    type: number69    sql: ${TABLE}.messages_sent_from_speed_date70  - dimension: profile_views_by_tenure71    type: number72    sql: ${TABLE}.profile_views

dating-platform, customer_loyalty_metrics.view.lookml, lines 67-69

At a glance

Shape

Dimensions 15

Relationships

Worth noticing

Tenure 1 only includes day 0

There are no measures

What can change the answer 3

Caution: The primary key is not unique

Caution: Three fields read columns the derived table does not produce

Note: A doubled suffix and a misspelling

Related

Guides built on this file

Related source files

Source model