[Analytic Block] User Loyalty and Other User Attributes

About This Block

Loyalty facts are attributes computed over the lifetime of each user, which helps us segment and understand our customer base. Segmenting customers/users by their behavior helps us explore the most important characteristics that differentiate each user group, and how we can target them for the behavior changes we want. For example, what common threads can we find among users who bought one item but never returned – did they all buy the same item or types of items? Did they all come from a similar ad source? If so, you’d be compelled to make steps to drive customers away from those items, and probably change your advertising strategy. A myriad of questions are unlocked to help us better understanding of our customer behavior.


  • Who are my most frequent customers? Who are my most recent customers?
  • What products or events are driving the most customers to return?
  • Which customers bought once and never came back, and what drove that behavior?
  • Which customers yield the highest margins?
  • What is the lifetime revenue generated be each of my customers?
  • Do customers with different start months follow the same ordering pattern?

Ideal Data Types

This Block works great a large portion of companies in the e-commerce, consumer goods, marketing, healthcare, finance, and gaming spaces, among other verticals. The logic can be applied to any usage or transactional data where actions can be mapped to users. Sample usage and transactional data includes orders, order items, sessions, visits, postings, donations, events, etc…

Questions you can answer

Users by Lifetime Number of Orders

Number of users that have order 0, 1, 2, 3, etc times.

Users by Lifetime Number of Orders Tiered

Bucket the users by the number of times they have ordered.

Users Cohorted by Create Month by Lifetime Number of Orders Tiered

Users that first bought with us a while ago behave any differently?

User Distribution by Tiered Lifetime Revenue

Users Count and Total Historic Revenue by Lifetime Revenue Tiered.

Revenue, last 12 months by User Lifetime Revenue Tiered

Find customers that have spent more than $500 that haven’t bought recently

These customers are valuable, we can easily find them. Click on the counts to get their email addresses so you can target them.

Poison Products?

Are there products a first time buy is buying that is causing them never to buy from you again.

We are looking at products counting the number of users that purchased this as part of their first purchase. If you see something that is outweighted as first purchase here, maybe there is a problem.

Try It Out

For optimal query performance, it is best to compile these attributes in a persisted derived table (PDT) that can be joined to the users view in your LookML project. Since the attributes are based on historical user behavior, regenerating the PDT once to a few times per day is usually a fine cadence for pulling in the newest user and transaction data.

view: user_order_facts {

derived_table: {

sql: SELECT

user_id

, COUNT(DISTINCT order_id) AS lifetime_orders

, SUM(sale_price) AS lifetime_revenue

, MIN(NULLIF(created_at,0)) AS first_order

, MAX(NULLIF(created_at,0)) AS latest_order

, COUNT(DISTINCT DATE_TRUNC('month', NULLIF(created_at,0))) AS number_of_distinct_months_with_orders

FROM order_items

GROUP BY user_id

;;

}



dimension: user_id {

primary_key: yes

hidden: yes

sql: ${TABLE}.user_id ;;

}



dimension_group: first_order {

type: time

timeframes: [date, week, month, year]

sql: ${TABLE}.first_order ;;

}



dimension_group: latest_order {

type: time

timeframes: [date, week, month, year]

sql: ${TABLE}.latest_order ;;

}



dimension: days_as_customer {

description: "Days between first and latest order"

type: number

sql: DATEDIFF('day', ${TABLE}.first_order, ${TABLE}.latest_order)+1 ;;

}



dimension: days_as_customer_tiered {

type: tier

tiers: [0, 1, 7, 14, 21, 28, 30, 60, 90, 120]

sql: ${days_as_customer} ;;

style: integer

}



dimension: lifetime_orders {

type: number

sql: ${TABLE}.lifetime_orders ;;

}



dimension: repeat_customer {

description: "Lifetime Count of Orders > 1"

type: yesno

sql: ${lifetime_orders} > 1 ;;

}



dimension: lifetime_orders_tier {

type: tier

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

sql: ${lifetime_orders} ;;

style: integer

}



measure: average_lifetime_orders {

type: average

value_format_name: decimal_2

sql: ${lifetime_orders} ;;

}



dimension: distinct_months_with_orders {

type: number

sql: ${TABLE}.number_of_distinct_months_with_orders ;;

}



dimension: lifetime_revenue {

type: number

value_format_name: usd

sql: ${TABLE}.lifetime_revenue ;;

}



dimension: lifetime_revenue_tier {

type: tier

tiers: [0, 25, 50, 100, 200, 500, 1000]

sql: ${lifetime_revenue} ;;

style: integer

}



measure: average_lifetime_revenue {

type: average

value_format_name: usd

sql: ${lifetime_revenue} ;;

}

}



4 1 1,255
1 REPLY 1

Have anything for good 

Top Labels in this Space
Top Solution Authors