[Analytic Block] User Loyalty and Other User Attributes


(Elan Weiner) #1

##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…

Expected Output

Users by Lifetime Number of Orders

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

[Explore Data in Full Screen](https://blocks.looker.com/embed/explore/user_loyalty_and_user_attributes/users?show=data&query=ZqdxST4&vis=%7B%7D)

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

Different users spend different amounts of money in our store. The majority spend $20 to $500 with us.

Users Count and Total Historic Revenue by Lifetime Revenue Tiered.

The vast majority of our revenue comes from people that spend more than $100 with us and really, the big spenders are almost 1/2 of our revenue.

Revenue, last 12 months by User Lifetime Revenue Tiered

This pattern is pretty constant over the months. Again, notice that most of our revenue is coming from people that have spent more than $500 with us over a lifetime.

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


Tiers on a measure
KPI Queries Vs Funnel (Cohort) Queries
JOIN 2017 - Deep Dive - Customer Retention