Has Purchased¶
Has Purchased returns a list of orders that meets the threshold that you define – for example, exactly 4, less than 5, more than 2, or between 2 and 10 – and occurred during your chosen date range. For example, return all customers who have purchased 3 (or more) times in the previous 2 years:

After you specify a value and date range you may apply filters to associate customers who have purchased with specific products, brands, channels, and stores. For example, return all customers who have purchased wool or cotton socks from your website.
How this attribute works¶
Has Purchased represents a common approach people use when they build segments: start with all orders, and then associate that list of orders to the products and brands your customers purchased.
Has Purchased 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 Has Purchased work?
Has Purchased is built from standard columns that are output by Amperity. The following example returns a list of customers who have purchased 3 (or more) times in the previous 2 years:

The SQL for Has Purchased works like this. It uses order ID from the Unified_Itemized_Transactions table as its starting point:
SELECT
amperity_id
,COUNT(DISTINCT order_id) AS purchase_count
FROM Unified_Itemized_Transactions
and finds all of the distinct orders that were made by all customers – the “purchase count” – and then returns all orders that match the date range:
WHERE order_datetime < DATE_TRUNC('day', CURRENT_TIMESTAMP - interval '2' year)
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))
From that list of customers who made a purchase within that date range, Has Purchased uses another SELECT statement to return only customers whose purchase count meets the threshold that you defined. For example, more than 2 purchases:
SELECT
*
FROM total_distinct_orders
WHERE purchase_count > 2
This creates a list of customers who made at least 3 purchases during a specific time window. From here you can apply additional filters for your products, stores, channels, and brands.
The SQL for Has Purchased 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 Has Purchased 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 Has Purchased attribute located under Transactional Behaviors:

After the attribute appears in your segment, set the purchase count and choose a date range:

After you specify a purchase count and 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, and “now” is the date and time on which the segment is run. For example: “today - 2 weeks” or “today - 14 days”.
Available conditions¶
The following table lists the conditions that are available to this attribute.
Note
Recommended conditions for this attribute are identified with “ Recommended” and conditions with more limited use cases are identified with “ Not recommended”.
Condition |
Description |
---|---|
between |
between returns a list of customers whose order threshold is in-between two specified values. |
exactly |
exactly returns a list of customers whose order threshold exactly matches specified value. |
less than |
less than returns a list of customers whose order threshold is less than the specified value. |
more than |
more than returns a list of customers whose order threshold is more than the specified value. |
Filter attributes¶
A filter attribute is a standard column that is output by Amperity. When a filter attribute is associated with a compound attribute, you may use each filter attribute to filter the results of the compound attribute on specific items in your product catalog, brands, channels, and stores.
The following filter attributes may be made available to this attribute, when the underlying data in your tenant is available from the Unified_Itemized_Transactions table:
Product category
Product description
Product subcategory
Product gender
Purchase brand
Purchase channel
Store ID
Important
The following table lists the semantic tag that generates the column that is the pre-requisite for each attribute filter. If the column is missing from the Unified_Itemized_Transactions table (or if it has a non-standard name) it will not be available to the attribute filter group:
Semantic tag |
Column name |
Filter name |
---|---|---|
pc/product-category |
product_category |
Product category |
pc/product-description |
product_description |
Product description |
pc/product-subcategory |
product_subcategory |
Product subcategory |
pc/product-gender |
product_gender |
Product gender |
txn-item/purchase-brand |
purchase_brand |
Purchase brand |
txn-item/purchase-channel |
purchase_channel |
Purchase channel |
txn-item/store-id |
store_id |
Store ID |