DataBull Search
4 pitfalls

Retention and engagement

Measuring reply rates on proposals and messages

Do members' date proposals get responses, of which kind, and which templates work best?

Short answer

Trust: Inferred

Define a reply rate as items with at least one reply over all items sent, and decide up front which senders and message types count. This project stores response counts per proposal but reports averages that skip proposals with no response, its declared response rate cannot run, and two different spam rules decide which replies count.

Decision it supports: Which proposal templates and message types to promote.

What to measure

Proposals

Trust: Source-defined

Distinct proposals.

Defined as propositions.count

COUNT(DISTINCT propositions.id)Trust: Translated

Show the SQL: Proposals
Redshift SQLTrust: Translated
COUNT(DISTINCT propositions.id)

Translated to Redshift SQL from proposals.view.lookml L156-159. Not run against a warehouse.

Show the original LookML: proposals.view.lookml L156-159
dating_platform/proposals.view.lookmlL156-159
    filters:      message_response_type: Messages::Intrigue    detail: detail*  - measure: count    type: count_distinct    sql: ${TABLE}.id    detail: detail*  sets:    detail:    - id

dating-platform, proposals.view.lookml, lines 156-159

Responses per proposal

Trust: Source-defined

Average responses among proposals that got at least one, despite the name. Proposals with no response are NULL and AVG skips them.

Defined as propositions.response_count

AVG(CAST(propositions.response_num AS FLOAT))Trust: Translated

Show the SQL: Responses per proposal
Redshift SQLTrust: Translated
AVG(CAST(propositions.response_num AS FLOAT))

Translated to Redshift SQL from proposals.view.lookml L138-141. Not run against a warehouse.

Show the original LookML: proposals.view.lookml L138-141
dating_platform/proposals.view.lookmlL138-141
    type: avg    decimals: 2    sql: ${TABLE}.date_response_num  - measure: response_count    type: number    decimals: 2    sql: AVG(CAST(${TABLE}.response_num AS FLOAT))  - measure: intrigue_response_count    type: sum    sql: ${intrigue_response_num}

dating-platform, proposals.view.lookml, lines 138-141

Date responses per proposal

Trust: Source-defined

Average date responses among proposals that got at least one.

Defined as propositions.date_response_count

AVG(propositions.date_response_num)Trust: Translated

Show the SQL: Date responses per proposal
Redshift SQLTrust: Translated
AVG(propositions.date_response_num)

Translated to Redshift SQL from proposals.view.lookml L134-137. Not run against a warehouse.

Show the original LookML: proposals.view.lookml L134-137
dating_platform/proposals.view.lookmlL134-137
    hidden: true  - dimension: intrigue_response_num    hidden: true  - measure: date_response_count    type: avg    decimals: 2    sql: ${TABLE}.date_response_num  - measure: response_count    type: number    decimals: 2

dating-platform, proposals.view.lookml, lines 134-137

Messages

Trust: Source-defined

Messages, after the explore's default filters.

Defined as messages.count

COUNT(DISTINCT messages.id)Trust: Translated

Show the SQL: Messages
Redshift SQLTrust: Translated
COUNT(DISTINCT messages.id)

Translated to Redshift SQL from communications.view.lookml L19-22, L74-76. Not run against a warehouse.

Show the original LookML: communications.view.lookml L74-76
dating_platform/communications.view.lookmlL74-76
    sql: |      CASE        WHEN ${TABLE}.message_value  - measure: count    type: count    detail: detail*  - measure: avg_message_value    type: avg    sql: ${message_value}

dating-platform, communications.view.lookml, lines 74-76

Response rate

Trust: Inferred

response_rate is declared as a dimension over measures, so it cannot be queried. A rate needs proposals with a response over all proposals.

Reply share

Trust: Suggested

has_reply on messages could give a reply share. No measure defines it.

The query

Responses by template

Trust: Suggested

Proposals and average responses per responding proposal for the 20 most used templates. The explore keeps only published proposals unless you filter status yourself. No dashboard tile asks this, so the field combination is ours.

Show the SQL: responses by template
Redshift SQLTrust: Suggested
SELECT  propositions.template_name AS template_name,  COUNT(DISTINCT propositions.id) AS count,  AVG(CAST(propositions.response_num AS FLOAT)) AS response_count,  AVG(propositions.date_response_num) AS date_response_countFROM propositions  -- derived table, defined in the sourceWHERE propositions.status = 'published'  -- conditionally_filter: applies unless propositions.status is filteredGROUP BY 1ORDER BY count DESCLIMIT 20

Suggested query. Fields translated to Redshift SQL from enterprise.model.lookml L36-48 and proposals.view.lookml L110-111, L127-129, L134-141, L156-159. Not run against a warehouse.

The data behind it

Trust: Inferred
Entities
proposition, message, template, member
Grain
Propositions: one row per proposition with pre-aggregated response counts. Messages: one row per message.
Time
Proposition created and proposed dates; message created.
Key fields
propositions.template_namepropositions.categorypropositions.statusmessages.typemessages.has_replymessages.is_conversation_starter

What can change the answer 4

Caution: Counts that are averages

Trust: Source-defined

response_count is AVG(response_num) and date_response_count is type: avg. A proposal with no response has a NULL count, which AVG skips.

What goes wrong: Response count by template shows average responses among proposals that got any, so it can never be zero and cannot be added up across templates.

Show the SQL: response count
Redshift SQLTrust: Translated
AVG(CAST(propositions.response_num AS FLOAT))

Translated to Redshift SQL from proposals.view.lookml L138-141. Not run against a warehouse.

Show the original LookML: proposals.view.lookml L138-141
dating_platform/proposals.view.lookmlL138-141
    type: avg    decimals: 2    sql: ${TABLE}.date_response_num  - measure: response_count    type: number    decimals: 2    sql: AVG(CAST(${TABLE}.response_num AS FLOAT))  - measure: intrigue_response_count    type: sum    sql: ${intrigue_response_num}

dating-platform, proposals.view.lookml, lines 138-141

Caution: response_rate cannot run

Trust: Source-defined

It is a dimension that references the measures response_count and count. Looker rejects aggregates inside a dimension.

Source: proposals.view.lookml L146-149
dating_platform/proposals.view.lookmlL146-149
    type: sum    sql: ${intrigue_response_num}    detail: detail*  - dimension: response_rate    type: number    decimals: 2    sql: 100.0 * ${response_count} /${count}  - measure: count_intrigue_responses    type: count_distinct    sql: messages.id

dating-platform, proposals.view.lookml, lines 146-149

Caution: Two spam rules

Trust: Inferred

The response CTE excludes senders with U.spammer; the messages explore filters is_spammer on sender and recipient. They may not agree.

Source: proposals.view.lookml L4-14
dating_platform/proposals.view.lookmlL4-14
- view: propositions  derived_table:    sql: |      WITH proposition_responses AS (        SELECT  proposition_id,                type,                COUNT(1) AS response_count        FROM    messages M                JOIN users U                ON  M.sender_id = U.id        WHERE   M.state = 'sent'                AND U.spammer = false        GROUP BY proposition_id, type        )      SELECT  P.id,              P.user_id,              P.category,

dating-platform, proposals.view.lookml, lines 4-14

Note: Messages default to last week

Trust: Source-defined

Every message count in the explore starts as last week's count.

Read the guide: Filters that change metric meaning

Source: enterprise.model.lookml L23-26
dating_platform/enterprise.model.lookmlL23-26
- explore: messages  view: messages  always_filter:    messages.type: -Messages::Welcome    recipient.is_spammer: false    sender.is_spammer: false    messages.created_date: last week  joins:  - join: sender    from: users

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

How the real implementation does it

propositions

Trust: Source-defined

A CTE counts sent, non-spam responses per proposition and message type.

Source: proposals.view.lookml L4-14
dating_platform/proposals.view.lookmlL4-14
- view: propositions  derived_table:    sql: |      WITH proposition_responses AS (        SELECT  proposition_id,                type,                COUNT(1) AS response_count        FROM    messages M                JOIN users U                ON  M.sender_id = U.id        WHERE   M.state = 'sent'                AND U.spammer = false        GROUP BY proposition_id, type        )      SELECT  P.id,              P.user_id,              P.category,

dating-platform, proposals.view.lookml, lines 4-14

propositions

Trust: Source-defined

Response counts are averages over the proposals that got at least one response: the rest are NULL and AVG skips them.

Show the SQL: responses per proposal
Redshift SQLTrust: Translated
AVG(CAST(propositions.response_num AS FLOAT))

Translated to Redshift SQL from proposals.view.lookml L138-141. Not run against a warehouse.

Show the original LookML: proposals.view.lookml L138-141
dating_platform/proposals.view.lookmlL138-141
    type: avg    decimals: 2    sql: ${TABLE}.date_response_num  - measure: response_count    type: number    decimals: 2    sql: AVG(CAST(${TABLE}.response_num AS FLOAT))  - measure: intrigue_response_count    type: sum    sql: ${intrigue_response_num}

dating-platform, proposals.view.lookml, lines 138-141

proposition_templates

Trust: Source-defined

Template names fall back to a prettified slug, the same expression the proposals table repeats.

Show the SQL: template name
Redshift SQLTrust: Translated
COALESCE(proposition_templates.name, INITCAP(REPLACE(proposition_templates.slug, '-', ' ')))

Translated to Redshift SQL from proposal_templates.view.lookml L77-79. Not run against a warehouse.

Show the original LookML: proposal_templates.view.lookml L77-79
dating_platform/proposal_templates.view.lookmlL77-79
  - dimension: meta_title    hidden: true    sql: ${TABLE}.meta_title  - dimension: name    type: string    sql: COALESCE(${TABLE}.name, INITCAP(REPLACE(${TABLE}.slug, '-', ' ')))  - dimension: official    type: yesno    sql: ${TABLE}.official

dating-platform, proposal_templates.view.lookml, lines 77-79

enterprise

Trust: Source-defined

The messages explore always hides welcome messages and spammers.

Source: enterprise.model.lookml L23-26
dating_platform/enterprise.model.lookmlL23-26
- explore: messages  view: messages  always_filter:    messages.type: -Messages::Welcome    recipient.is_spammer: false    sender.is_spammer: false    messages.created_date: last week  joins:  - join: sender    from: users

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

Other ways to measure it

Define a real response rate

Trust: Suggested

Count proposals with any response over all proposals, per template. The response count is rebuilt from messages with the same rule the source uses, because the raw proposals table does not hold it.

Show the suggested SQL: proposal response rate
Suggested, not from source
WITH responses AS (  SELECT M.proposition_id, COUNT(1) AS response_num  FROM messages M  JOIN users U ON M.sender_id = U.id  WHERE M.state = 'sent' AND U.spammer = false  GROUP BY M.proposition_id)SELECT  P.proposition_template_id,  COUNT(DISTINCT P.id) AS proposals,  COUNT(DISTINCT CASE WHEN R.response_num > 0 THEN P.id END) AS proposals_with_response,  100.0 * COUNT(DISTINCT CASE WHEN R.response_num > 0 THEN P.id END)    / NULLIF(COUNT(DISTINCT P.id), 0) AS response_rate_pctFROM propositions PLEFT JOIN responses R ON R.proposition_id = P.idWHERE P.status = 'published'GROUP BY P.proposition_template_id

Reply share by message type

Trust: Suggested

Use the has_reply flag the messages table already has. Apply the same spam and welcome-message rules as everything else you report.

Show the suggested SQL: reply share by message type
Suggested, not from source
SELECT  M.type,  COUNT(*) AS messages,  SUM(CASE WHEN M.has_reply THEN 1 ELSE 0 END) AS replied,  1.0 * SUM(CASE WHEN M.has_reply THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS reply_shareFROM messages MJOIN users S ON S.id = M.sender_idJOIN users R ON R.id = M.recipient_idWHERE M.state = 'sent'  AND (M.type <> 'Messages::Welcome' OR M.type IS NULL)  AND S.spammer = false  AND R.spammer = falseGROUP BY M.type

Name averages as averages

Trust: Suggested

Rename response_count to avg_responses_per_proposition so charts say what they show.