How to Identify Customers with Reduced Purchase Frequency Using Acumatica Inquiry (for Dashboard datescource)

Question

Our management wants to display a metric on the Dashboard that identifies customers whose purchase frequency has dropped over the last month, compared to their regular historical buying pattern.

For example:

Customer A normally places 3 orders per week, but only placed 1 order weekly recently
Customer B normally orders once every two weeks, but has made no purchase in the past two weeks

We need to flag these customers for sales team follow‑up to prevent customer churn. Could you advise how to build this logic via Acumatica Inquiry?

ray20 · Accepted Answer

​@dgranger70 Thanks for your great idea! I have successfully implemented the requirement with a pure no-code nested Generic Inquiry approach (no custom fields, no report export, no import scenario). I would like to share my working solution for reference:Step 1: Create Sub GI 1 (Recent 30 Days Order Count)Query the SOOrder table, filter valid orders from the last 30 days, group by CustomerID, and calculate the total order count as Count30.Step 2: Create Sub GI 2 (Recent 180 Days Order Count)Query the same SOOrder table, filter valid orders from the last 180 days, group by CustomerID, and calculate the total order count as Count180.Step 3: Master GI for Final CalculationJoin the above two sub GIs on CustomerID, then calculate the frequency ratio:Ratio = Count180 / Count30I encountered a small issue here: the calculation result is automatically rounded to an integer, and I cannot keep 2 decimal places in the GI formula.Business Logic & Dashboard FilterThe core logic is to compare the recent purchase activity with the customer’s historical average frequency:For example, if a customer placed 60 orders in 180 days, the average 30-day frequency is 10 orders. If they only placed 7 orders in the latest 30 days, the ratio is 8, which is much higher than our threshold.Finally, I embedded this master GI into the Dashboard and set a filter to only display customers with Ratio > 7. These customers are identified as having a significant drop in purchase frequency and require sales follow-up to prevent churn.This method is fully dynamic, real-time, and zero-maintenance.

dgranger70 · Answer

You should be able to achieve this with a custom report, import scenario, and generic inquiry. I would start by making the custom report pulling from the SOOrder table and accepting a date range (1/1/2025-12/31/2025) as parameters. groupHeader1 would list the customer and detailSection1 would look like this and list all order dates within the specified date range:

Customer	Order Dates	Time Between Orders
AACUSTOMER	5/20/2026	16
AACUSTOMER	5/4/2026	14
AACUSTOMER	4/20/2026	etc...

The Time Between Orders column would use the DateDiff() and Previous() functions to get the number of days between order. =DateDiff(‘d’, [SOOrder.OrderDate], Previous([SOOrder.OrderDate]). The result should increment a variable ($TotalDays). In the footer, you would average the Time Between Orders column for the customer ($TotalDays/$LnNbr).

I would edit the excel layout for the report so you only export the customerID and their average.

Customer	Average Order Frequency
AACUSTOMER	15
ABARTENDE	24

You could then create an attribute for the customer record (ORDERFREQUENCY) and use an import scenario to update all customers at once. Then you would build a generic inquiry that lists the customerID, their order frequency, their most recent order date, and the days since their last order. You could then build a dashboard widget that would highlight customers outside of a specified range.

Once that is set up, you would only have to run the report and import scenario when you want to update customer order frequency.

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded