Tiers on a measure

  • 18 November 2015
  • 3 replies

I know looker has a Dimension type of tier but can I create a tier for a measure.

Use Case: I need to count the transactions for a buyer and determine which tier that buyer is in.

Name     | Trucks Purchased
Company1 | 30-40
Company2 | 20-30

Or create a histogram of the numbe of companies in each range (‘Trucks Purchased’)

3 replies

Userlevel 6

Assuming the derived table from this model on

- view: user_order_facts
## highlight
sql: |
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
GROUP BY user_id
sortkeys: [user_id]
sql_trigger_value: SELECT MAX(id) FROM orders
## endhighlight

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

CASE WHEN orders.created_at + interval 30 day > CURRENT_TIMESTAMP
THEN order_items.order_id
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.

Userlevel 6

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

[Analytic Block] User Loyalty and Other User Attributes

Hi @lloydtabb - Are you basically saying that he will need to create a derived table first to aggregate each company’s “Trucks Purchased”, then bring them in as dimensions so that he can tier them?

If that’s the case, how would you be able to make this dynamic? e.g. Be able to view the # of Trucks Purchased at varying timeframes.