Source model LookML, Redshift SQL
A larger project with two halves. The event_tracker model wires Snowplow-style web events into sessions, visitors and transactions. The enterprise model covers product data: messages, date proposals, subscriptions, churn, scores and experiments. Seven views it depends on, including users, are not in the repo. Trust: Source-defined
SQL on these pages is Redshift. Inferred from :: casts, BTRIM and GETDATE in the source. Trust: Inferred
17 views3 models13 explores4 dashboards20 joins25 tiles381 fields89 measures
Each lane is an explore: base view left, joins right with their keys. 7 referenced views are not in the repo and are drawn dashed. Lane headings name the dashboards that query them.
sessions.countsessions.bounce_ratesessions.events_per_sessionvisitors.countSnowplow-shaped derived tables roll raw events into sessions and visitors, with landing page, referer, campaign, geo and device per session.
transactions.transactions_valuetransaction_items.sales_valuetransaction_items.average_order_valueEcommerce events are split into an order-header view and an item view, joined by order id in the event tracker model.
churn.leadschurn.upgradeschurn.downgradeschurn.renewalssubscriptions.countA daily movement table counts leads, upgrades, downgrades and renewals. Subscription rows carry renewal dates, trial days and auto-renew.
messages.countpropositions.response_countpropositions.countMembers post date proposals and receive typed responses. Response counts are pre-aggregated per proposition in a derived table.
user_score.avg_scoreuser_score.avg_expected_ltvexperiments.countLead scores, early lifecycle activity and experiment buckets hang off a user_metrics view that is not in the repo.
Every measure in the repo, as declared. Tile counts show which ones a dashboard actually uses.
| Measure | View | Type | SQL | Tiles |
|---|---|---|---|---|
count | events | count | COUNT(*) of rows | 0 |
page_pings_count | events | count | COUNT(*) of rows | 0 |
page_views_count | events | count | COUNT(*) of rows | 0 |
transactions_count | events | count_distinct | ${transaction_order_id} | 0 |
distinct_pages_viewed_count | events | count_distinct | ${page_url_path} | 0 |
sessions_count | events | count_distinct | ${session_id} | 0 |
page_pings_per_session | events | number |
| 0 |
page_pings_per_visitor | events | number |
| 0 |
transactions_per_session | events | number |
| 0 |
transactions_per_visitor | events | number |
| 0 |
visitors_count | events | count_distinct | ${user_id} | 0 |
events_per_session | events | number | ${count}/NULLIF(${sessions_count},0)::REAL | 0 |
events_per_visitor | events | number | ${count}/NULLIF(${visitors_count},0)::REAL | 0 |
page_views_per_sessions | events | number | ${page_views_count}/NULLIF(${sessions_count},0)::REAL | 0 |
page_views_per_visitor | events | number | ${page_views_count}/NULLIF(${visitors_count},0)::REAL | 0 |
landing_page_views_count | events | count | COUNT(*) of rows | 0 |
internal_page_views_count | events | count | COUNT(*) of rows | 0 |
approx_user_usage_in_minutes | events | number |
| 0 |
approx_usage_per_visitor_in_seconds | events | number |
| 0 |
approx_usage_per_visit_in_seconds | events | number |
| 0 |
transactions_value | events | sum | ${transaction_value} | 0 |
count | messages | count | COUNT(*) of rows | 1 |
avg_message_value | messages | avg | ${message_value} | 0 |
avg_score | user_score | avg | ${score} | 0 |
avg_expected_ltv | user_score | avg | ${expected_ltv} | 0 |
avg_value | user_score | avg | ${value} | 0 |
transactions_count | transactions | count_distinct | ${order_id} | 0 |
transactions_value | transactions | sum | ${transaction_value} | 0 |
count | visitors | count_distinct | ${user_id} | 1 |
bounced_visitor_count | visitors | count_distinct | ${user_id} | 0 |
bounce_rate | visitors | number | ${bounced_visitor_count}/NULLIF(${count},0)::REAL | 0 |
events_count | visitors | sum | ${TABLE}.number_of_events | 0 |
events_per_visitor | visitors | number | ${events_count}/NULLIF(${count},0)::REAL | 0 |
sessions_count | visitors | sum | ${TABLE}.number_of_sessions | 0 |
sessions_per_visitor | visitors | number | ${sessions_count}/NULLIF(${count},0)::REAL | 0 |
landing_page_count | visitors | count_distinct | ${landing_page_url} | 0 |
campaign_medium_count | visitors | count_distinct | ${campaign_medium} | 0 |
campaign_source_count | visitors | count_distinct | ${campaign_source} | 0 |
campaign_term_count | visitors | count_distinct | ${campaign_term} | 0 |
campaign_count | visitors | count_distinct | ${campaign_name} | 0 |
referer_medium_count | visitors | count_distinct | ${referer_medium} | 0 |
referer_source_count | visitors | count_distinct | ${referer_source} | 0 |
referer_term_count | visitors | count_distinct | ${referer_term} | 0 |
leads | churn | sum | ${TABLE}.leads | 0 |
downgrades | churn | sum | ${TABLE}.downgrades | 0 |
renewals | churn | sum | ${TABLE}.renewals | 0 |
upgrades | churn | sum | ${TABLE}.upgrades | 0 |
count | subscriptions | count | COUNT(*) of rows | 0 |
count | user_experiment_buckets | count | COUNT(*) of rows | 0 |
count | proposition_templates | count | COUNT(*) of rows | 0 |
date_response_count | propositions | avg | ${TABLE}.date_response_num | 0 |
response_count | propositions | number | AVG(CAST(${TABLE}.response_num AS FLOAT)) | 0 |
intrigue_response_count | propositions | sum | ${intrigue_response_num} | 0 |
count_intrigue_responses | propositions | count_distinct | messages.id | 0 |
count | propositions | count_distinct | ${TABLE}.id | 0 |
distinct_skus_bought | transaction_items | count_distinct | ${sku} | 0 |
items_bought_count | transaction_items | sum | ${quantity} | 0 |
sales_value | transaction_items | sum | ${price} | 0 |
orders_count | transaction_items | count_distinct | ${order_id} | 0 |
items_per_order | transaction_items | number | ${items_bought_count} / NULLIF(${orders_count},0)::REAL | 0 |
average_order_value | transaction_items | number | ${sales_value} / NULLIF(${orders_count},0)::REAL | 0 |
count | transaction_items | count | COUNT(*) of rows | 0 |
count | bookings | count | COUNT(*) of rows | 0 |
count | experiments | count | COUNT(*) of rows | 0 |
count | sessions | count_distinct | ${session_id} | 11 |
visitors_count | sessions | count_distinct | ${user_id} | 0 |
bounced_sessions_count | sessions | count_distinct | ${session_id} | 0 |
bounce_rate | sessions | number | ${bounced_sessions_count}/NULLIF(${count},0)::REAL | 2 |
sessions_from_new_visitors_count | sessions | count_distinct | ${session_id} | 0 |
sessions_from_returning_visitor_count | sessions | number | ${count} - ${sessions_from_new_visitors_count} | 0 |
new_visitors_count_over_total_visitors_count | sessions | number | ${sessions_from_new_visitors_count}/NULLIF(${count},0)::REAL | 0 |
returning_visitors_count_over_total_visitors_count | sessions | number |
| 0 |
events_count | sessions | sum | ${TABLE}.number_of_events | 0 |
events_per_session | sessions | number | ${events_count}/NULLIF(${count},0)::REAL | 3 |
events_per_visitor | sessions | number | ${events_count}/NULLIF(${visitors_count},0)::REAL | 0 |
country_count | sessions | count_distinct | ${geography_country} | 0 |
region_count | sessions | count_distinct | ${geography_region} | 0 |
city_count | sessions | count_distinct | ${geography_city} | 0 |
zip_code_count | sessions | count_distinct | ${geography_zipcode} | 0 |
campaign_medium_count | sessions | count_distinct | ${campaign_medium} | 0 |
campaign_source_count | sessions | count_distinct | ${campaign_source} | 0 |
campaign_term_count | sessions | count_distinct | ${campaign_term} | 0 |
campaign_count | sessions | count_distinct | ${campaign_name} | 0 |
referer_medium_count | sessions | count_distinct | ${referer_medium} | 0 |
referer_source_count | sessions | count_distinct | ${referer_source} | 0 |
referer_term_count | sessions | count_distinct | ${referer_term} | 0 |
device_count | sessions | count_distinct | ${device_type} | 0 |
operating_system_count | sessions | count_distinct | ${operating_system} | 0 |
browser_count | sessions | count_distinct | ${browser} | 0 |
Every source file, classified. Combined files share one page; inventory-only files have a reason.
activities.view.lookml
How this LookML events view models page views, pings, transactions and usage time4 warnings
communications.view.lookml
The messages view: message value, reply flags and the filters every query inherits3 warnings
customer_loyalty_metrics.view.lookml
The user_tenure_metrics view: activity at day 1, 3 and 7 in one derived table3 warnings
customer_score.view.lookml
The user_score view: lead score, expected LTV and lead value per user3 warnings
deals.view.lookml
Declares offers: id, title, price, non_member_price and quantity. It is only reached through the bookings explore, so it is covered on the bookings page.
event_transactions.view.lookml
How this LookML transactions view models web orders from tracked events3 warnings
guests.view.lookml
How this LookML visitors view models first-touch attribution and lifetime engagement4 warnings
membership_activity.view.lookml
The churn view: leads, upgrades, downgrades and renewals per day in one UNION3 warnings
memberships.view.lookml
The subscriptions view: renewal dates, auto-renew and two definitions of active3 warnings
participant_groups.view.lookml
Experiments and user buckets: A/B test metadata and assignment in LookML4 warnings
proposal_templates.view.lookml
The proposition_templates view: date idea templates with a fallback name rule3 warnings
proposals.view.lookml
The propositions view: date proposals with response counts built in a derived table3 warnings
purchase_items.view.lookml
How this LookML order-item view models SKUs, quantity and average order value3 warnings
reservations.view.lookml
The bookings view: partner date bookings with a drill set into missing views3 warnings
tests.view.lookml
Experiments and user buckets: A/B test metadata and assignment in LookML4 warnings
user_sessions.view.lookml
How this LookML sessions view models acquisition, engagement and new vs returning traffic4 warnings
enterprise.model.lookml
The enterprise LookML model: six explores, 14 joins and no declared relationships4 warnings
event_tracker.model.lookml
How this LookML model wires events, sessions, visitors and web orders3 warnings
partners.model.lookml
One explore, bookings, with joins to users and offers. Its graph and its join_type misuse are shown on the bookings page and the repo map.
company_overview.dashboard.lookml
Three business dashboards: revenue, conversion and map tiles built on missing explores4 warnings
conversion_metrics.dashboard.lookml
Three business dashboards: revenue, conversion and map tiles built on missing explores4 warnings
geographics.dashboard.lookml
Three business dashboards: revenue, conversion and map tiles built on missing explores4 warnings
traffic_overview.dashboard.lookml
Traffic pulse dashboard: twelve session tiles and seven fully wired filters3 warnings
users, user_metrics, user_transactions and four session building blocks are referenced but never declared. Three explores and most dashboard tiles depend on them.
What goes wrong: Explores like transactions and tests cannot be validated from this repo, and the business dashboards cannot run as written.
Read the guide: Missing tables and stale references
enterprise.model.lookml L8-19- explore: churn label: Churn Report view: churn- explore: transactions view: transactions from: user_transactions joins: - join: users sql_foreign_key: transactions.user_id - join: subscriptions sql_foreign_key: transactions.subscription_id fields: [] - join: user_score sql_foreign_key: users.id fields: []- explore: messages view: messages always_filter:dating-platform, enterprise.model.lookml, lines 8-19
All 20 joins in this repo rely on Looker's default many_to_one. Several are one-to-many in practice, such as messages per proposition and tenure rows per user.
What goes wrong: Measures on the base view can be multiplied by the join without any warning in the explore.
enterprise.model.lookml L55-67 joins: - join: propositions sql_on: propositions.proposition_template_id = date_ideas.id- explore: tests view: user_metrics label: Test Metrics joins: - join: user_experiment_buckets sql_on: user_metrics.id = user_experiment_buckets.user_id fields: [] - join: tests from: experiments sql_foreign_key: user_experiment_buckets.experiment_id - join: user_tenure_metrics sql_foreign_key: user_metrics.id fields: [] - join: users from: users sql_foreign_key: user_metrics.iddating-platform, enterprise.model.lookml, lines 55-67
The messages explore always filters to last week, non-welcome, non-spam messages. The propositions explore defaults to published ones.
What goes wrong: A message count read from the explore is a last-week count unless the reader notices the filter.
Read the guide: Filters that change metric meaning
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
transactions is an explore in both enterprise (over the missing user_transactions) and event_tracker (over web ecommerce events). Dashboards are included by both models.
Read the guide: Missing tables and stale references
event_tracker.model.lookml L18-22 - join: visitors sql_on: sessions.domain_userid = visitors.domain_userid- explore: visitors- explore: transactions- explore: transaction_items joins: - join: transactions sql_foreign_key: transaction_items.ti_orderid- explore: session_appsdating-platform, event_tracker.model.lookml, lines 18-22
activities.view.lookml declares events, guests.view.lookml declares visitors, and user_sessions.view.lookml declares two views. Search by view name, not file name.
Mechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.
activities.view.lookml:64 events.timestamp is declared as both dimension and dimension_group.guests.view.lookml:43 visitors.first_touch is declared as both dimension and dimension_group.proposal_templates.view.lookml:43 proposition_templates.featured is declared as both dimension and dimension_group.user_sessions.view.lookml:122 sessions.start is declared as both dimension and dimension_group.activities.view.lookml:214 events.sessions_count drills into set "detail", which is not declared.guests.view.lookml:162 visitors.sessions_count drills into set "details", which is not declared.activities.view.lookml:296 Set events.visit_detail lists "domain_session_index", which is not a field or set in this view.activities.view.lookml:322 Set events.transaction_detail lists "domain_session_index", which is not a field or set in this view.event_transactions.view.lookml:69 Set transactions.transaction_detail lists "domain_userid", which is not a field or set in this view.event_transactions.view.lookml:69 Set transactions.transaction_detail lists "domain_sessionidx", which is not a field or set in this view.guests.view.lookml:239 Set visitors.individual_detail lists "referer_host", which is not a field or set in this view.guests.view.lookml:239 Set visitors.individual_detail lists "number_of_events", which is not a field or set in this view.membership_activity.view.lookml:100 Set churn.detail lists "id", which is not a field or set in this view.membership_activity.view.lookml:100 Set churn.detail lists "amount", which is not a field or set in this view.membership_activity.view.lookml:100 Set churn.detail lists "transaction_type", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "id", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "transaction_type", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "type", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "payment_type", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "gross_revenue", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "amount", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "net_revenue", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "refunds", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "user_id", which is not a field or set in this view.membership_activity.view.lookml:105 Set churn.users lists "credit_card_type", which is not a field or set in this view.user_sessions.view.lookml:431 Set sessions.detail lists "sessions_from_returning_visitors_count", which is not a field or set in this view.user_sessions.view.lookml:431 Set sessions.detail lists "zipe_code_count", which is not a field or set in this view.company_overview.dashboard.lookml:10 Tile total_revenue reads transactions.total_revenue, transactions.created_date from user_transactions, which is not in the repo.company_overview.dashboard.lookml:21 Tile transactions reads transactions.count, transactions.created_date from user_transactions, which is not in the repo.company_overview.dashboard.lookml:32 Tile transactions_by_type reads transactions.created_month, transactions.count, transactions.type, transactions.created_date from user_transactions, which is not in the repo.company_overview.dashboard.lookml:116 Tile revenue_by_type reads transactions.created_month, transactions.total_revenue, transactions.type, transactions.created_date from user_transactions, which is not in the repo.conversion_metrics.dashboard.lookml:21 Tile daily_revenue reads transactions.created_date, transactions.gross_revenue from user_transactions, which is not in the repo.conversion_metrics.dashboard.lookml:21 Tile daily_revenue reads users.orientation from users, which is not in the repo.conversion_metrics.dashboard.lookml:83 Tile revenue_by_month reads transactions.created_month, transactions.net_revenue from user_transactions, which is not in the repo.enterprise.model.lookml:8 Explore transactions is based on user_transactions, which is not in the repo.enterprise.model.lookml:12 Join transactions.users targets users, which is not in the repo.enterprise.model.lookml:28 Join messages.sender targets users, which is not in the repo.enterprise.model.lookml:32 Join messages.recipient targets users, which is not in the repo.enterprise.model.lookml:43 Join propositions.users targets users, which is not in the repo.enterprise.model.lookml:55 Explore tests is based on user_metrics, which is not in the repo.enterprise.model.lookml:68 Join tests.users targets users, which is not in the repo.geographics.dashboard.lookml:35 Tile map reads sender.zipcode from users, which is not in the repo.partners.model.lookml:8 Join bookings.users targets users, which is not in the repo.user_sessions.view.lookml:15 sessions builds on ${sessions_basic.SQL_TABLE_NAME}; no view named sessions_basic is in the repo.user_sessions.view.lookml:15 sessions builds on ${sessions_geo.SQL_TABLE_NAME}; no view named sessions_geo is in the repo.user_sessions.view.lookml:15 sessions builds on ${sessions_last_page.SQL_TABLE_NAME}; no view named sessions_last_page is in the repo.user_sessions.view.lookml:15 sessions builds on ${sessions_technology.SQL_TABLE_NAME}; no view named sessions_technology is in the repo.company_overview.dashboard.lookml:62 Tile users_map queries explore users, which no model in the repo declares.company_overview.dashboard.lookml:74 Tile users_by_orientation queries explore users, which no model in the repo declares.company_overview.dashboard.lookml:90 Tile average_lifetime_value queries explore users, which no model in the repo declares.conversion_metrics.dashboard.lookml:53 Tile gap_revenue queries explore transaction_ledger, which no model in the repo declares.geographics.dashboard.lookml:16 Tile map queries explore users, which no model in the repo declares.geographics.dashboard.lookml:52 Tile another_map queries explore users, which no model in the repo declares.conversion_metrics.dashboard.lookml:21 Tile daily_revenue reads acquisition.channel; explore transactions has no view or join named acquisition.enterprise.model.lookml:8 Explore transactions is declared in 2 models (enterprise, event_tracker).event_tracker.model.lookml:18 Explore transactions is declared in 2 models (enterprise, event_tracker).enterprise.model.lookml:12 Join transactions.users declares no relationship.enterprise.model.lookml:14 Join transactions.subscriptions declares no relationship.enterprise.model.lookml:17 Join transactions.user_score declares no relationship.enterprise.model.lookml:28 Join messages.sender declares no relationship.enterprise.model.lookml:32 Join messages.recipient declares no relationship.enterprise.model.lookml:43 Join propositions.users declares no relationship.enterprise.model.lookml:46 Join propositions.messages declares no relationship.enterprise.model.lookml:53 Join date_ideas.propositions declares no relationship.enterprise.model.lookml:59 Join tests.user_experiment_buckets declares no relationship.enterprise.model.lookml:62 Join tests.tests declares no relationship.enterprise.model.lookml:65 Join tests.user_tenure_metrics declares no relationship.enterprise.model.lookml:68 Join tests.users declares no relationship.enterprise.model.lookml:72 Join tests.user_score declares no relationship.enterprise.model.lookml:75 Join tests.messages declares no relationship.event_tracker.model.lookml:7 Join events.sessions declares no relationship.event_tracker.model.lookml:11 Join events.visitors declares no relationship.event_tracker.model.lookml:15 Join sessions.visitors declares no relationship.event_tracker.model.lookml:21 Join transaction_items.transactions declares no relationship.partners.model.lookml:8 Join bookings.users declares no relationship.partners.model.lookml:10 Join bookings.offers declares no relationship.geographics.dashboard.lookml:35 Tile name "map" is used more than once in maps_dashboard.guests.view.lookml:1 visitors declares no primary_key.membership_activity.view.lookml:1 churn declares no primary_key.participant_groups.view.lookml:1 user_experiment_buckets declares no primary_key.purchase_items.view.lookml:1 transaction_items declares no primary_key.user_sessions.view.lookml:1 session_apps declares no primary_key.user_sessions.view.lookml:13 sessions declares no primary_key.guests.view.lookml:51 visitors.last_touch uses type: time on a dimension instead of a dimension_group.guests.view.lookml:145 visitors.bounced_visitor_count uses "filter:" (singular), which is not the measure filters parameter.participant_groups.view.lookml:4 user_experiment_buckets declares a field named "users.bucket" with a dot in it.partners.model.lookml:10 Join bookings.offers puts "one_to_one" in join_type, where a join type such as left_outer belongs.proposals.view.lookml:146 Dimension propositions.response_rate references a measure./ open UpDown move Enter go Esc close