Orders per customer in last twelve months - how to group frequency of purchases

Questions
1

I know that you can use frequency group to see the count of customers that are 1x, 2x, and 3x+ customers. I need to pull 3x, 4x, and 5x in the last twelve months. Any ideas on how to do this?
Eg:
In last 12 months
500 customers made 1x purchase
100 customers made 2x purchases
50 customers made 3x purchases
25 customers made 4x purchases
10 customers made 5x purchases

2

Hi @Jennifer_Fenton !

We don’t have a great way of doing this in the standard explores. The reason is that you’re wanting to filter an aggregate measure (customers) by the value of another aggregate measure (orders). You’d be able to do this for all time orders (i.e. show how many customers have 1x, 2x, etc orders since they became customers), but not for a dynamic timeframe like last 12 months.

If you or someone on your team has access to SQL Runner on your Looker instance, you could just navigate there (either just go to the /sql page on your looker instance or navigate to Develop > SQL Runner) and run the following query, which will give you exactly what you need:

WITH
order_count_by_customer AS ( 
  SELECT -- Get order count by customer
     customer_id
    ,COUNT(DISTINCT order_id) AS orders
  FROM drp.order_status
  WHERE 1=1
    AND order_date::DATE >= DATEADD(day,-365,current_date)
  GROUP BY 1
)
SELECT -- Get count of customers for each unique order count
   orders AS orders_in_past_year
  ,COUNT(DISTINCT customer_id) AS customers
FROM order_count_by_customer
GROUP BY 1
ORDER BY 1

If you want to change the time period, you would just change the -365 in AND order_date::DATE >= DATEADD(day,-365,current_date) to be whatever number of days you want to look back relative to the current date.

Sorry that this isn’t possible at the moment in our standard explores, but I hope this helps you get you the data you’re looking for.

3

Thank you! I appreciate getting the query. I may be able to figure that out from here.