Question

Tiers on a measure

  • 18 November 2015
  • 3 replies
  • 1028 views

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
Badge

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

Userlevel 6
Badge

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.

Reply