I’m just after a bit of help with a Breww QL query -
I am trying to create a dynamic customer list using an average value within a period, but can’t seem to figure it out.
Using a combination of Number of Orders, Average Spend & Last Order narrows the range, but still picks up outliers. This is the part of the query that I’m struggling with:
or (type != "Distributor" and (average_order_value >= "X" and number_of_invoiced_orders >= "X" and last_order_date > "X months ago"))
Ideally we are looking for say 10 orders within 12 months timeframe - with the above, you will still pick up outliers such as a customer who may meet the 10 orders minimum requirement, but if the last one was in January 22, they would still be picked up. Customers such as this are already being picked up through different filtering lists we have, so would be good to be able to exclude them somehow.
If anyone has any thoughts, it would be much appreciated
I found this similar request, and saw that an Activity variable now exists in Breww QL, so was wondering if this had been implemented & I was missing something related?
Unfortunately, what you’re trying to achieve isn’t really possible right now, sorry! The limitation is that each of the variables that you can filter on are evaluated independently. For example, number_of_invoiced_orders and average_order_value are both for all orders from this customer ever, and not just for orders that match another part of the filter (i.e. the last_order_date > "X months ago" bit).
Following the BrewwQL expansion request, last_activity_date has been added (this is just for CRM activities, orders are not included in this), but there has been no progress on a total invoice value in a period yet as that’s far, far more complicated (I don’t think Matt realised this in his reply on that other thread, to be honest!). I’ll update that thread too, to avoid any confusion.
We have some plans to further improve the Raw Data Explorer to add grouping (e.g. by customer) and aggregate functions (sums, counts, averages, etc). If you could then filter on the result of an aggregate function, that could get you to the list that you’re looking for. I realise there are a few extra steps to get this into an automatically updating customer list, but it would be a step in the right direction.
I’m not that good with Excel, to be honest, but I believe it would be possible to get the list your looking for by exporting invoice data from the Raw Data Explorer and then grouping by the customer and filtering on the number of invoices orders and last order date in Excel.
