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.
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…
Number of users that have order 0, 1, 2, 3, etc times.
Bucket the users by the number of times they have ordered.
Users that first bought with us a while ago behave any differently?
These customers are valuable, we can easily find them. Click on the counts to get their email addresses so you can target them.
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.
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} ;;
}
}
Have anything for good