Most frequent order¶

Most Frequent Order returns the products that each of your customers ordered most frequently during your chosen date range and at the frequency you have defined. For example, return a list of customers whose most frequent purchase within the last 30 days was tacos:

Important

You must choose at least one product attribute – category, subcategory, description, or gender – and/or a store ID.

After you choose at least one product and specify a date range you may apply filters to associate your customers orders with specific products, brands, channels, and stores. For example, return a list of customers who most frequently ordered deluxe tacos in Goleta, CA.

How this attribute works¶

Most Frequent Order represents a common approach people use when they build segments: figure out what your customers purchased most often within a certain time window, and then associate that list of customers to your marketing campaigns.

Most Frequent Order is a compound attribute, which means that it’s built from a combination of attributes that already exist in your data, and then appears as a single attribute that you can choose from the Segment Editor.

With this attribute, you can focus less on SQL and more on finding answers that align to your marketing goals and strategies. Purchase behavior attributes simplify the number of steps that are required to associate a list of customers to your products, stores, channels, and brands.

How does the SQL for Most Frequent Order work?

Most Frequent Order is built from standard columns that are output by Amperity. The following example returns a list of customers whose most frequent purchase within the last 30 days was tacos:

The SQL for Most Frequent Order works like this. It uses a count of distinct order IDs from the Unified Itemized Transactions table as its starting point, and requires at least one product category, product subcategory, product gender, or store ID to be selected:

SELECT
  amperity_id
  ,product_category
  ,product_subcategory
  ,store_id
  ,RANK() OVER (PARTITION BY amperity_id ORDER BY COUNT(DISTINCT order_id) DESC) AS frequency_rank
FROM Unified_Itemized_Transactions

then uses the RANK() function to determine order frequency, then ranks them in descending order, and then returns all items that match the date range:

WHERE order_datetime < DATE_TRUNC('day', CURRENT_TIMESTAMP - interval '30' day)

Returns and cancellations are filtered out automatically, like this:

AND (is_return IS NULL OR (NOT is_return))
AND (is_cancellation IS NULL OR (NOT is_cancellation))

and then results are grouped by Amperity ID and any selected filter attributes:

GROUP BY amperity_id, product_category, product_subcategory, store_id

From that list of customers, Most Frequent Order uses another SELECT statement to return only customers who purchased most frequently the combination of filter attributes you provided. For example, more than 2 purchases:

SELECT DISTINCT
  amperity_id
FROM list_of_most_frequent_orders
WHERE frequency_rank = 1
AND product_category IN 'tacos'
AND product_subcategory LIKE 'deluxe'
AND store_id = '0007 (Goleta, CA)'

This creates a list of customers who most frequently ordered deluxe tacos from your restaurant in Goleta, CA.

The SQL for Most Frequent Order is more complex than what is described in the previous section. This is due to the way this attribute returns only a list of Amperity IDs, uses a series of common table expressions (CTEs), and takes advantage of workflows that Amperity does behind the scenes to pre-filter the product, purchase, and store attributes.

You can view the full SQL for Most Frequent Order from the Segment Editor. Start a new segment and add only this attribute (along with any required conditions and filter attributes), and then click the View SQL link at the top of the page.

Add to segments¶

To find all customers who made their first order within a specified date range, start with the Most Frequent Order attribute located under Transactional Behaviors:

Use the most frequent order attribute to find your customer's most frequent orders by date range.

After the attribute appears in your segment, select at least one of product category, product subcategory, product gender, or store ID, and then choose a date range:

After you specify a date range you may apply filters to associate these customers to specific products, brands, channels, and stores.

About relative dates¶

A relative date is determined at the time a segment is run, where today is the day on which the segment is run. For example: yesterday, 30 days ago, 14 days ago, or 1 year ago. The list of relative date values includes a series of common ranges, but you may also type in a more specific range, such as 2 months ago or 5 days ago.

Relative date values

Value	Description
Tomorrow	Starts at 12:00:00 AM of the day after the current day. For example, if the current day is “Thursday 01 / 12 / 2023” then tomorrow is “Friday 01 / 13 / 2023”.
Today	Starts at 12:00:00 AM (or at the current time) on the current day and continues for 24 hours. For example, if the current day is “Thursday 01 / 12 / 2023” then today is “Thursday 01 / 12 / 2023”.
Yesterday	Starts at 12:00:00 AM of the day before the current day. For example, if the current day is “Thursday 01 / 12 / 2023” then yesterday is “Wednesday 01 / 11 / 2023”.
N days ago	Starts at 12:00:00 AM of the day N days before the current day. For example, if the current day is “Thursday 01 / 12 / 2023”, then: 7 days ago is “Thursday 01 / 05 / 2023” 14 days ago is “Thursday 12 / 29 / 2022” 30 days ago is “Tuesday 12 / 13 / 2022” 60 days ago is “Sunday 11 / 13 / 2022” 90 days ago is “Friday 10 / 14 / 2022”
1 month ago	Starts at 12:00:00 AM of the same day of the month that is 1 month before the current month. For example, if the current day is “Thursday 01 / 12 / 2023” then 1 month ago is “Monday 12 / 12 / 2022”.
1 year ago	Starts at 12:00:00 AM of same day of the year that is 1 year before the current year. For example, if the current day is “Thursday 01 / 12 / 2023” then 1 year ago is “Wednesday 01 / 12 / 2022”.

Available conditions¶

The following table lists the conditions that are available to this attribute.

Note

Recommended conditions for this attribute are identified with “ More useful” and conditions with more limited use cases are identified with “ Less useful”.

Condition	Description
was after	More useful Returns a list of customers whose most frequent order was after the specified time window.
was before	More useful Returns a list of customers whose most frequent order was before the specified time window.
was between	More useful Returns a list of customers whose most frequent order was between two specified time windows.
was not between	Returns a list of customers whose most frequent order was not between two specified time windows.
was not on	Returns a list of customers whose most frequent order was not on the specified time.
was on	Returns a list of customers whose most frequent order was on the specified time.

Filter attributes¶

A filter attribute is a standard column that is output by Amperity and is available from the Unified Itemized Transactions table. When a filter attribute is associated with a purchase behavior attribute, you may use them to filter the results by specific items in your product catalog, such as by brand, by channel, by store, or by specific details about the items in your product catalog, such as color, SKU, and so on. The list of filter attributes that will be available for product catalogs depends on their availability within your Unified Itemized Transactions table.