DataBull Search
dating_platform/tests.view.lookml +1 12 dims2 measures 4 warnings Repo files

LookML view · dating-platform

Experiments and user buckets: A/B test metadata and assignment in LookML

At a glance

Does
experiments.name, start and end dates, experiments.num_buckets and experiments.winning_bucket describe each experiment. user_experiment_buckets assigns users to a bucket per experiment.Trust: Source-defined
Why
Feeds the tests explore, which compares user metrics across experiment buckets to pick a winner.Trust: Inferred
Breaks
4 warnings. Top: A field named users.bucket
Grain
experiments: one row per experiment, keyed by experiments.id. user_experiment_buckets: one row per user per experiment, with no primary key.Trust: Inferred
Source
dating_platform/tests.view.lookml, dating_platform/participant_groups.view.lookml 84 lines
Source: tests.view.lookml L1-10
dating_platform/tests.view.lookmlL1-10
- view: experiments  sql_table_name: public.experiments  fields:  - dimension: id    primary_key: true    type: int    sql: ${TABLE}.id  - dimension_group: created    type: time    timeframes:    - time    - date    - week

dating-platform, tests.view.lookml, lines 1-10

Shape

Trust: Source-defined

Every field as declared, with the dashboard tiles that read it. Computed from the source; nothing here is edited.

experiments tests.view.lookml

Table: public.experiments

Dimensions 9
NameTypeSQLUsed byTags
idL4int${TABLE}.id-primary key
createdtime, date, week, monthL8time group${TABLE}.created_at-
endtime, date, week, monthL16time group${TABLE}.end_date-
nameL24string${TABLE}.name-
num_bucketsL26int${TABLE}.num_buckets-
removedtime, date, week, monthL29time group${TABLE}.removed_at-
starttime, date, week, monthL37time group${TABLE}.start_date-
updatedtime, date, week, monthL45time group${TABLE}.updated_at-
winning_bucketL53int${TABLE}.winning_bucket-

Measures 1
NameTypeSQLUsed byTags
countL56countCOUNT(*) of rows-

user_experiment_buckets participant_groups.view.lookml

Table: public.user_experiment_buckets

Dimensions 3
NameTypeSQLUsed byTags
users.bucketL4int${TABLE}.bucket-
experiment_idL7int${TABLE}.experiment_id-
user_idL10int${TABLE}.user_id-hidden

Measures 1
NameTypeSQLUsed byTags
countL14countCOUNT(*) of rows-

Relationships

Trust: Source-defined
Relationships of experiments, user_experiment_buckets
Relationships of experiments, user_experiment_buckets. Text version follows the diagram.These viewsUsed by: explores, views, dashboardsas tests, fk user_experiment_buckets.experiment_idas user_experiment_buckets, on user_metrics.id = user_experime... tests explore in enterprise
  • This Block
  • No relationship declared
Text version
  • experiments is joined into tests: as tests, fk user_experiment_buckets.experiment_id (no relationship declared)
  • user_experiment_buckets is joined into tests: as user_experiment_buckets, on user_metrics.id = user_experime... (no relationship declared)

Worth noticing

Explored as tests

Trust: Source-defined

In the enterprise model the tests explore is based on user_metrics and joins experiments under the alias tests through user_experiment_buckets.experiment_id.

Source: enterprise.model.lookml L55-64
dating_platform/enterprise.model.lookmlL55-64
  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: []

dating-platform, enterprise.model.lookml, lines 55-64

winning_bucket is stored, not computed

Trust: Missing

experiments.winning_bucket is an integer column. How a winner is chosen, and on which metric, is not in the repo.

Source: tests.view.lookml L53-55
dating_platform/tests.view.lookmlL53-55
    - week    - month    sql: ${TABLE}.updated_at  - dimension: winning_bucket    type: int    sql: ${TABLE}.winning_bucket  - measure: count    type: count    detail: detail*

dating-platform, tests.view.lookml, lines 53-55

What can change the answer 4

Caution: A field named users.bucket

Trust: Inferred

user_experiment_buckets declares a dimension literally named users.bucket. A dot in a field name collides with the view.field reference syntax.

What goes wrong: References to the bucket can resolve to a users view instead, and users is not in the repo, so bucket comparisons may not be selectable.

Source: participant_groups.view.lookml L1-6
dating_platform/participant_groups.view.lookmlL1-6
- view: user_experiment_buckets  sql_table_name: public.user_experiment_buckets  fields:  - dimension: users.bucket    type: int    sql: ${TABLE}.bucket  - dimension: experiment_id    type: int    sql: ${TABLE}.experiment_id

dating-platform, participant_groups.view.lookml, lines 1-6

Caution: User metrics repeat once per experiment

Trust: Inferred

The tests explore joins buckets on user_metrics.id = user_experiment_buckets.user_id. A user in four experiments appears four times.

What goes wrong: Unless one experiment is filtered, user_metrics totals count each user once per experiment they were in.

Read the guide: Join fanout

Source: enterprise.model.lookml L59-61
dating_platform/enterprise.model.lookmlL59-61
  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

dating-platform, enterprise.model.lookml, lines 59-61

Note: No primary key on assignments

Trust: Source-defined

user_experiment_buckets has no primary_key, so Looker cannot deduplicate user_experiment_buckets.count when the view is fanned out.

Read the guide: Primary keys and counts

Source: participant_groups.view.lookml L10-16
dating_platform/participant_groups.view.lookmlL10-16
  - dimension: experiment_id    type: int    sql: ${TABLE}.experiment_id  - dimension: user_id    type: int    hidden: true    sql: ${TABLE}.user_id  - measure: count    type: count    detail: detail*  sets:    detail:    - users.id

dating-platform, participant_groups.view.lookml, lines 10-16

Note: The metrics being compared are missing

Trust: Missing

The tests explore reads user_metrics, which is not in the repo. What each bucket is compared on is unknown.

Read the guide: Missing tables and stale references

Source: enterprise.model.lookml L55-58
dating_platform/enterprise.model.lookmlL55-58
  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: []

dating-platform, enterprise.model.lookml, lines 55-58

Parser checks 2

Trust: Source-defined

Mechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.

Show all 2 checks
  • no-primary-key 1
    • participant_groups.view.lookml:1 user_experiment_buckets declares no primary_key.
  • dotted-name 1
    • participant_groups.view.lookml:4 user_experiment_buckets declares a field named "users.bucket" with a dot in it.

Related

Guides built on this file

Guide Preventing join fanout by declaring every relationship Why sums and counts grow after a join, how to check any model for fanout, and the SQL and LookML that keep totals honest, from real join mistakes. Best practicesRevenue Guide Checking the primary key before you trust a count A count is only as clear as the key behind it. How to state and test a table's grain, with real tables whose keys repeat, merge rows or are missing. Best practicesRevenueRetention Guide Catching missing tables and stale references before a dashboard breaks Models can name tables, fields and explores that do not exist, and nothing fails until a chart runs. Real examples, and how to check yours before release. Best practices

Related source files

Source file user_tenure_metrics Cross joins users with a numbers table for tenure 1, 3 and 7, then sums 30-day lifecycle metrics (photos, d... view3 warnings Source file enterprise Declares six explores on project_connection: churn, transactions, messages, propositions, date_ideas and te... model4 warnings Source file user_score Joined with it in the tests explore view3 warnings Source file messages Joined with it in the tests explore view3 warnings

Source model

Source model Inside a dating platform LookML project: sessions, revenue, subscriptions and messaging Product, revenue and customer analytics 17 views20 joins