LookML view · dating-platform

The messages view: message value, reply flags and the filters every query inherits

At a glance

Does: Reads one row per message with sender, recipient, thread, proposition, type, state and reply flags, plus a numeric message_value. Measures: count and avg_message_value.Trust: Source-defined
Why: Supports messaging quality analysis: how often conversations start, how often messages get replies, and how valuable messages are by sender age and subscription.Trust: Inferred
Breaks: 3 warnings. Top: message_value_with_24hr is unfinished SQL
Grain: One row per message. id is the primary key.Trust: Source-defined
Source: dating_platform/communications.view.lookml 84 lines

Source: communications.view.lookml L1-10

dating_platform/communications.view.lookmlL1-10 Show context

1- view: messages2  sql_table_name: ${TABLE}.messages3  fields:4  - dimension: original_message_id5    hidden: true6    type: int7    sql: ${TABLE}.original_message_id8  - dimension_group: created9    type: time10    timeframes:11    - time12    - date13    - week

dating-platform, communications.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: ${TABLE}.messages

Dimensions 19

Name	Type	SQL	Used by	Tags
`original_message_id`L4	int	`${TABLE}.original_message_id`	-	hidden
`created`time, date, week, monthL8	time group	`${TABLE}.created_at`	Message Activity
`has_reply`L16	yesno	`${TABLE}.has_reply`	-
`id`L19	int	`${TABLE}.id`	-	primary key
`is_conversation_starter`L23	yesno	`${TABLE}.is_conversation_starter`	-
`proposition_id`L26	int	`${TABLE}.proposition_id`	-
`recipient_account_age`L29	int	`${TABLE}.recipient_account_age`	-
`sender_account_age`L32	int	`${TABLE}.sender_account_age (default)`	-	implicit sql
`recipient_id`L34	int	`${TABLE}.recipient_id`	-
`sender_id`L37	int	`${TABLE}.sender_id`	-
`sender_subscribed`L40	yesno	`${TABLE}.sender_subscribed`	-
`source`L43	string	`${TABLE}.source`	-
`state`L45	string	`${TABLE}.state`	-
`thread_id`L47	int	`${TABLE}.thread_id`	-
`type`L50	string	`${TABLE}.type`	-
`updated`time, date, week, monthL52	time group	`${TABLE}.updated_at`	-
`is_sent_within_24hr`L60	yesno	`(${sender_account_age}/60) < 24`	-	hiddenderived
`message_value`L64	number	`${TABLE}.message_value`	-
`message_value_with_24hr`L68	number	`CASE WHEN ${TABLE}.message_value`	-	derived

Measures 2

Name	Type	SQL	Used by	Tags
`count`L74	count	`COUNT(*) of rows`	Message Activity
`avg_message_value`L77	avg	`${message_value}`	-	derived

Relationships

Source-defined

Relationships of messages

This Block
No relationship declared

Text version

messages is the base view of messages: base view
messages is joined into propositions: as messages, on messages.proposition_id = propo... (no relationship declared)
messages is joined into tests: as messages, on messages.sender_id = user_metri... (no relationship declared)
messages is read by maps_dashboard: 1 tile

Filters and exclusions that change meaning

Every explore query starts filtered

Source-defined

The messages explore in the enterprise model always excludes welcome messages and spammer senders and recipients, and starts at created_date: last week.

Source: enterprise.model.lookml L23-26

dating_platform/enterprise.model.lookmlL23-26 Show context

20- explore: messages21  view: messages22  always_filter:23    messages.type: -Messages::Welcome24    recipient.is_spammer: false25    sender.is_spammer: false26    messages.created_date: last week27  joins:28  - join: sender29    from: users

dating-platform, enterprise.model.lookml, lines 23-26

Worth noticing

Account age is assumed to be in minutes

Inferred

is_sent_within_24hr divides sender_account_age by 60 and compares it to 24. That means 24 hours only if the column is stored in minutes. The field is hidden.

Source: communications.view.lookml L60-63

dating_platform/communications.view.lookmlL60-63 Show context

57    - week58    - month59    sql: ${TABLE}.updated_at60  - dimension: is_sent_within_24hr61    type: yesno62    sql: (${sender_account_age}/60) < 2463    hidden: true64  - dimension: message_value65    type: number66    decimals: 10

dating-platform, communications.view.lookml, lines 60-63

sender_account_age has no sql

Source-defined

sender_account_age declares only type: int, so Looker reads the default column ${TABLE}.sender_account_age. Its neighbour recipient_account_age spells the column out.

Source: communications.view.lookml L29-33

dating_platform/communications.view.lookmlL29-33 Show context

26  - dimension: proposition_id27    type: int28    sql: ${TABLE}.proposition_id29  - dimension: recipient_account_age30    type: int31    sql: ${TABLE}.recipient_account_age32  - dimension: sender_account_age33    type: int34  - dimension: recipient_id35    type: int36    sql: ${TABLE}.recipient_id

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

The table name uses the TABLE placeholder

Inferred

sql_table_name: ${TABLE}.messages puts ${TABLE} inside the definition of the table itself. It resolves only if something substitutes a schema there.

Source: communications.view.lookml L1-3

dating_platform/communications.view.lookmlL1-3 Show context

1- view: messages2  sql_table_name: ${TABLE}.messages3  fields:4  - dimension: original_message_id5    hidden: true6    type: int

dating-platform, communications.view.lookml, lines 1-3

What can change the answer 3

Blocker: message_value_with_24hr is unfinished SQL

Source-defined

The dimension's SQL is CASE WHEN ${TABLE}.message_value with no condition, THEN or END.

What goes wrong: Selecting the field breaks the whole query, including every other field in the same look.

Source: communications.view.lookml L68-73

dating_platform/communications.view.lookmlL68-73 Show context

65    type: number66    decimals: 1067    sql: ${TABLE}.message_value68  - dimension: message_value_with_24hr69    type: number70    decimals: 1071    sql: |72      CASE73        WHEN ${TABLE}.message_value74  - measure: count75    type: count76    detail: detail*

dating-platform, communications.view.lookml, lines 68-73

Caution: Default counts cover last week only

Source-defined

Because of the always_filter, count in the messages explore counts last week's non-welcome, non-spam messages unless the reader changes each filter value.

What goes wrong: Message volume from a fresh explore looks like weekly volume, and comparing it with monthly targets understates activity.

Read the guide: Filters that change metric meaning

Source: enterprise.model.lookml L23-26

dating_platform/enterprise.model.lookmlL23-26 Show context

20- explore: messages21  view: messages22  always_filter:23    messages.type: -Messages::Welcome24    recipient.is_spammer: false25    sender.is_spammer: false26    messages.created_date: last week27  joins:28  - join: sender29    from: users

dating-platform, enterprise.model.lookml, lines 23-26

Note: Sender and recipient joins expose sets that are not in the repo

Source-defined

The explore joins users twice, as sender and recipient, and limits each to a messages set that would live in the missing users view.

Read the guide: Missing tables and stale references

Source: enterprise.model.lookml L27-35

dating_platform/enterprise.model.lookmlL27-35 Show context

24    recipient.is_spammer: false25    sender.is_spammer: false26    messages.created_date: last week27  joins:28  - join: sender29    from: users30    sql_foreign_key: sender_id31    fields: sender.messages32  - join: recipient33    from: users34    sql_foreign_key: recipient_id35    fields: recipient.messages36- explore: propositions37  view: propositions38  conditionally_filter:

dating-platform, enterprise.model.lookml, lines 27-35

At a glance

Shape

Dimensions 19

Measures 2

Relationships

Filters and exclusions that change meaning

Every explore query starts filtered

Worth noticing

Account age is assumed to be in minutes

sender_account_age has no sql

The table name uses the TABLE placeholder

What can change the answer 3

Blocker: message_value_with_24hr is unfinished SQL

Caution: Default counts cover last week only

Note: Sender and recipient joins expose sets that are not in the repo

Related

Guides built on this file

Related source files

Source model