Starting in Looker 21.14, users can create ad hoc custom bins for numeric type dimensions without needing to use logical functions in Looker expressions or needing to develop type: tier LookML fields when the Custom Fields Labs feature is enabled.

How to Create Tiers for a Measure

In Looker, we can easily apply tiering to fields using a type: tier dimension. However, there are times when we may also want to tier values of a measure (defining cohorts by a count value, or comparing a set of minimum or maximum dates for first versus last order). Currently, type: tier can only be applied to dimensions, or unaggregated fields. To create tiers on a measure, we will need to transform a measure into a dimension (essentially, "dimensionalize" it). This is because SQL, and by extension Looker, doesn't allow grouping by the results of an aggregate; we can only group by dimensions.

To achieve this, we will create a derived table. Once the aggregate function (measure) is selected as a column in the derived table, we can define it as a dimension. The following example will walk you through the steps to implement this solution.

Step-by-Step Implementation

First, we will need to create a derived table. In Looker, there are two options: a SQL-based derived table, or a LookML-based native derived table.

For this example, the end goal is to create tiers on a count of user orders.

Option 1: Create a SQL-based Derived Table:

Start by setting up the appropriate fields in an Explore. For our example, we would select user_id, lifetime_items, and lifetime_orders (which is the measure we want to tier).
Select the SQL tab next to Results in the data table, then Open in SQL Runner from the lower right-hand side of the SQL interface.
Once in the SQL Runner, click Run to run the query, and then choose Add to Project from the gear menu to add the generated SQL to a new view file in the desired project.
Now that the derived table is in a view file, create a dimension of type: tier based on the desired derived table field. For example, below we've created a dimension of type: tier based on the lifetime_orders COUNT field, which we call lifetime_orders_tiered below:

    view: user_order_facts {
      derived_table: {
        sql: SELECT
            orders.user_id as user_id
            , COUNT(*) as lifetime_items
            , COUNT(DISTINCT order_items.order_id) as lifetime_orders
            , MIN(NULLIF(orders.created_at,0)) as first_order
            , MAX(NULLIF(orders.created_at,0)) as latest_order
            , COUNT(DISTINCT timestamp('month', NULLIF(orders.created_at,0)))
                as number_of_distinct_months_with_orders
            , SUM(order_items.sale_price) as lifetime_revenue
          FROM order_items
          LEFT JOIN orders ON order_items.order_id=orders.id
          GROUP BY user_id ;;
        sortkeys: [user_id]
        sql_trigger_value: SELECT MAX(id) FROM orders ;;
      }

      dimension: user_id {
        primary_key: yes
        hidden: yes
      }

      dimension: lifetime_items {
        type: number
        sql: COALESCE(${TABLE}.lifetime_items,0) ;;
      }

      dimension: lifetime_orders {
        type: number
        sql: COALESCE(${TABLE}.lifetime_orders,0) ;;
      }

      dimension: lifetime_orders_tiered {
        type: tier
        style: integer
        tiers: [0,1,2,3,5,10]
        sql: ${lifetime_orders} ;;
      }
    }

Option 2: Create a Native Derived Table

Native derived tables (NDTs) offer good governance because they interact with your existing model, and are very useful after a brief learning curve. You can learn more about NDTs in Looker on the Creating Native Derived Tables documentation page.

To use a native derived table to tier your measure:

Start by setting up your results in an Explore. Building off of our example above, we would select user_id, lifetime_items, and lifetime_orders (which is the measure we want to tier).
Click the Explore's gear menu and select Get Derived Table LookML. For instances using versions 7.8 and greater, select Get LookML, and then Derived Table. Looker then displays the LookML to create the corresponding NDT.
Copy the LookML. For a screenshot of this step, see the Creating Native Derived Tables documentation page.
Paste the LookML into a view file in your project. Don't forget to remove any row limit clause that may appear in the derived table LookML. For instructions on accessing project files and creating a view file, see the Understanding Model and View Files documentation page.
Create a dimension of type: tier to group the desired measure into cohorts. Similarly to the SQL derived table example, we would create lifetime_orders_tiered based on the new lifetime_orders dimension:

      dimension: lifetime_orders {
        type: number
        sql: COALESCE(${TABLE}.lifetime_orders,0) ;;
      }

      dimension: lifetime_orders_tiered {
        type: tier
        style: integer
        tiers: [0,1,2,3,5,10]
        sql: ${lifetime_orders} ;;
      }
    }

Make the Derived Table Explorable:

Now that the dimension of type: tier has been created for the lifetime_orders field in either a SQL-based derived table or a native derived table (that we have called user_order_facts below), we can create an Explore or join the derived table view to an existing Explore, to make the table accessible by end users:

    Explore: user_order_facts {}

Now the lifetime_orders_tiered dimension can be selected in an Explore to create a count of the number of users in each tier:

Another Example

Additionally, suppose we want a 30-day count (the number of orders in the last 30 days). We could add another computed value in the derived table query above using a SQL CASE statement in a SQL-based derived table, or by creating a derived_column in a native derived table:

  , COUNT(DISTINCT
    CASE WHEN orders.created_at + interval 30 day > CURRENT_TIMESTAMP
    THEN order_items.order_id
    ELSE NULL
    END) as orders_30_days

The type: tier dimension field based on the resulting orders_30_days dimension will look like this:

dimension: orders_30_days {
  type: number
  sql: ${TABLE}.orders_30_days ;;
}

dimension: orders_30_days_tiered {
  type: tier
  style: integer
  tiers: [0,1,2,3,5,10]
  sql: ${orders_30_days};;
}

Using the methods we've shown here, you can use derived tables to dimensionalize measures and add tier type parameters to your analysis.

There is another great example that describes how to dimensionalize a measure with a derived table in the post on User Loyalty and Other User Attributes in the Looker Community.

Articles in this section

Tiers on a Measure