Tiers on a measure

Assuming the derived table from this model on learn.looker.com

- view: user_order_facts

  derived_table:

    ## highlight

    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 DATE_TRUNC('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

    ## endhighlight

    

  fields:

  - dimension: user_id

    primary_key: true

    hidden: true

  

  - 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}

    

We can use the lifetime number of orders tier, and count the number of users in each tier.

Suppose we want a 30 day count (the number of orders in the last 30 days)? We would add another computed value in the derived table query.

    ...

    , COUNT(DISTINCT 

      CASE WHEN orders.created_at + interval 30 day >  CURRENT_TIMESTAMP

      THEN order_items.order_id

      ELSE NULL

      END) as orders_30_days

    ...

then the dimensions

  - dimension: orders_30_days

    type: int

  - dimension: orders_30_days_tiered

    type: tier

    style: integer

    tiers: [0,1,2,3,5,10]

    sql: ${orders_30_days}

You could also do this dynamically using parameters, but it makes for a confusing enduser experience. Adding an attribute on user, number of 30 day orders, its probably the best bet.

@srivera There is a really nice Looker block that describes how to do this:

[Analytic Block] User Loyalty and Other User Attributes