Best practice Acquisition, Retention
Filters that quietly change what a metric means
Why do two reports disagree on the same metric?
The rule
Trust: InferredTreat filters as part of a metric's definition. List every filter between the table and the number, build the ones that change the population into the metric itself, and label the rest where readers see them. A filter readers cannot see, or one that reaches only some charts, makes one metric name report different populations.
Why it matters
Trust: InferredA measure is only half of a metric. Explore filters, tile filters and dashboard defaults decide which rows it counts. When those filters are hidden, unwired or applied unevenly, one measure name reports different populations.
Readers compare tiles and screenshots, not SQL. A count that silently excludes spam or older weeks gets compared with one that does not, and the difference is read as a trend.
| Where | Filters in effect | What count means |
|---|---|---|
| messages explore | always_filter: last week, no welcome, no spam | Last week's real messages |
| Maps dashboard, Message Activity | date_range this year replaces the date; type and spam filters stay | This year's real messages |
| Raw messages table | none | Every message, including welcome and spam |
Trust: Inferred Derived from the filters in source; no numbers involved.
How to check your own model
Trust: Suggested- List every filter between the table and the number: default explore filters, chart filters and dashboard defaults.
- For each dashboard filter, check which charts actually use it. A filter nothing listens to is decoration.
- Put population-changing filters in the chart title, such as non-Mozilla user agents only.
- When a restriction is part of the definition, build it into the metric instead of a chart filter.
A safer shape
Build the population into the metric
Trust: SuggestedName the restricted count for what it is and compute it next to the unrestricted one. Nobody can drop the filter by accident, and the gap between the two numbers is visible.
Show the suggested SQL: messages with the population in the name
SELECT DATE_TRUNC('week', m.created_at) AS week, COUNT(*) AS all_messages, COUNT(CASE WHEN (m.type <> 'Messages::Welcome' OR m.type IS NULL) AND s.spammer = false AND r.spammer = false THEN 1 END) AS real_messagesFROM messages AS mLEFT JOIN users AS s ON s.id = m.sender_idLEFT JOIN users AS r ON r.id = m.recipient_idGROUP BY 1The same fix in LookML
Trust: SuggestedIn a LookML dashboard, wire the filter or remove it: each tile that should respect the country filter gets a listen binding, and tiles where it does not apply say so in their title.
Show the suggested LookML: default.dashboard.lookml
- dashboard: default filters: - name: country type: suggest_filter explore: other_browser_os_impressions dimension: other_browser_os_impressions.country default_value: US elements: - name: browser_os_breakdown title: Browser-OS Breakdown listen: country: other_browser_os_impressions.countryWhere it shows up in real projects 4
default (Unusual UAs): A default that reaches no tile
Trust: Source-definedThe country filter defaults to US. No tile has a listen binding, so the filter bar shows US while every tile counts all countries.
Source: default.dashboard.lookml L6-10
layout: tile tile_size: 100 filters: - name: country type: suggest_filter explore: other_browser_os_impressions dimension: other_browser_os_impressions.country default_value: US elements: - name: browser_os_breakdown title: Browser-OS Breakdownadvertising-marketing-small, default.dashboard.lookml, lines 6-10
default (Unusual UAs): Unusual is defined by a tile filter
Trust: Source-definedThe only definition of an unusual UA is a tile filter: not starting with Mozilla/5.0. Other tiles with unusual in the title do not apply it.
Source: default.dashboard.lookml L35-47
width: 10 y_axis_labels: - Count of Impressions - name: unusual_uas_table title: Unusual UAs type: table explore: other_browser_os_impressions dimensions: - other_browser_os_impressions.ua measures: - other_browser_os_impressions.count sorts: - other_browser_os_impressions.count filters: other_browser_os_impressions.ua: -Mozilla/5.0% limit: 50 - name: revenue_and_impressions_top_10_pids title: Revenue and Impressions for Top 10 PIDs type: looker_columnadvertising-marketing-small, default.dashboard.lookml, lines 35-47
enterprise: An always_filter on messages
Trust: Source-definedThe messages explore always excludes welcome messages and spammers and defaults to last week. Readers can change the date but cannot remove the filter.
Source: enterprise.model.lookml L23-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: usersdating-platform, enterprise.model.lookml, lines 23-26
The SQL this produces
Ask the explore for nothing but the message count and the SQL still carries four conditions: no welcome messages, no spam on either side, and last week only.
Show the SQL: a plain message count
SELECT COUNT(DISTINCT messages.id) AS countFROM source_schema.messages AS messagesLEFT JOIN users AS sender /* not in repo */ ON messages.sender_id = sender.idLEFT JOIN users AS recipient /* not in repo */ ON messages.recipient_id = recipient.idWHERE (messages.type <> 'Messages::Welcome' OR messages.type IS NULL) -- always_filter: the value can be changed, not removed AND NOT recipient.is_spammer AND NOT sender.is_spammer AND messages.created_at >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week' AND messages.created_at < DATE_TRUNC('week', CURRENT_DATE)- users is not in the repo, so recipient.is_spammer is rendered as a plain column and its definition cannot be checked.
- users is not in the repo, so sender.is_spammer is rendered as a plain column and its definition cannot be checked.
- Join sender uses a foreign key, but users is not in the repo, so the key is assumed to be id.
- Join recipient uses a foreign key, but users is not in the repo, so the key is assumed to be id.
- messages names its table with ${TABLE} as a schema; it is rendered as source_schema because the source does not name the schema.
- users is not in the repo, so its table and columns cannot be checked.
Suggested query. Fields translated to Redshift SQL from enterprise.model.lookml L20-35 and communications.view.lookml L8-15, L19-22, L50-51, L74-76. Not run against a warehouse.
traffic_pulse: Cohorts cut by a session date
Trust: InferredThe cohort table groups visitors by first-touch week, but the dashboard date filter applies to session start. Older cohorts only show visitors who came back in the window.
Source: traffic_overview.dashboard.lookml L263-283
y_axis_labels: null y_axis_min: null y_axis_max: null - name: cohort_analysis title: Cohort analysis type: table explore: sessions dimensions: - visitors.first_touch_week pivots: - sessions.start_week measures: - visitors.count listen: date: sessions.start_date referer_medium: sessions.referer_medium new_vs_returning: sessions.new_vs_returning_visitor country: sessions.geography_country landing_page: sessions.landing_page_path referer_host: sessions.referer_url_host app_id: sessions.app_id limit: 500 width: null height: null - name: session_count_and_engagement_level_by_referer_host title: Session count and events per sesion for top 10 referer hosts type: looker_columndating-platform, traffic_overview.dashboard.lookml, lines 263-283