[Analytic Block] Repeat Purchase Patterns

[lloydtabb]

About This Block

Repeat business is the key to success for most ecommerce companies. Figuring out behavioural differences between repeat and non-repeat customers and making more of them repeat customers can change your business for the better. Information about who are and who are not repeat customers can help better drive initiatives to keep customers coming back to your business. For example we can look at repeat customers segmented out by certain properties such as geographic location, product mix, price points, marketing campaign, etc. With Looker’s powerful drill down functionality you can easily identify any patterns and call out potential issues affecting your customers return potential.

Ideal Data Types

Repeat purchase analysis fits in with any one sided or two sided ecommerce business. The model is driven by looking at purchase/conversion activity at a user level. For example, e-commerce stores will probably have tables for users, orders, order items, etc. The analysis can be as simple as tracking order items over time from the orders table or become more granular by joining in tables with other attributes.

If you work with event data from a homegrown tool, Google Analytics, or many other third-party tools, this pattern can be customized to suit your data structure. Please reach out to a Looker analyst for assistance with this!

Expected Output

Repeat vs Non-Repeat Revenue

At a high level we can look at overall aggregate purchase patters such as total repeat vs. non-repeat customers, or trend over time.

Hopefully we should see a larger percentage of or business coming from repeat buyers over time.

After examining aggregate patterns, you can drill down to cut-and-slice the information further, yielding further insights. Are first purchasers buying a different set of items than repeat purchasers? Are the majority of repeat customers coming back to buy the same items? Ideally you have a steady distribution of items being purchased, rather than a majority of purchases driven by a small set of items (unless they’re high value items, and this behavior aligns with your business needs).

Drilling in this way can also help you isolate and eradicate “poison products”, or those products that drive consumers away from returning. If consumers that buy only once are buying different things than repeat purchasers on their first purchase, there may be a problem with those products. For example we can drill down to see repeat purchase activity by category.

Repeat vs Non-Repeat Purchase Size

Often times, we see consumers spending less on their first purchase than subsequent purchases. In most cases, this is to be expected, and reflects favorable purchasing patterns. It suggests that customers are satisfied with your products so they’re returning to buy more. If this is the case, this analysis also provides an opportunity for growth. Recognizing which products are purchased by mostly returning customers suggests that it’s a “sticky” product, or one that compels customers to return. You’d benefit from strategically positioning this item on your webpage/storefront, offering deals or bundling products, or leveraging any number of sales optimization tactics.

Repeat vs Non-Repeat Customer Average Revenue by Category

Try it yourself!

This model uses sequencing transactions with window functions, compatible with PL/pgSQL (PostgreSQL, Redshift, Greenplum), Microsoft SQL Server (2012+) dialects, and assume your model contains an order_items, orders and users table.

First we will create a derived table to rank, or sequence the orders over each user to isolate first time orders using the RANK window function partitioning over each user.

- view: order_user_sequence
  derived_table:
    sql: |
      SELECT
        id AS order_id
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) as user_order_sequence_number
      FROM orders
  fields:
  - dimension: order_id
    primary_key: true
    sql: ${TABLE}.order_id

  - dimension: user_order_sequence_number
    type: int
    sql: ${TABLE}.user_order_sequence_number
    
  - dimension: is_first_purchase
    type: yesno
    sql: ${user_order_sequence_number} = 1

Next we’ll create an Explore in our model that joins in our user, order and order sequencing data.

- explore: order_items
  joins:
    - join: orders
      foreign_key: order_id
      
    - join: order_user_sequence
      foreign_key: order_id
      
    - join: users
      foreign_key: orders.user_id

    - join: inventory_items
      foreign_key: inventory_item_id
      
    - join: products
      foreign_key: inventory_items.product_id

Note we also join in any other views that we want to be able to segment our analysis by, such as products.