Total Value of Orders

Total Value of Orders adds together all of the revenue for all of the items that customers purchased across all orders that match the value and occurred during your chosen date range. For example, return all customers who spent more than $100 during the previous six months:

After you specify a value and date range you may apply filters to associate these customers to specific products, brands, channels, and stores. For example, return all customers who spent at least 100 dollars within the previous six months on chewing gum and purchased from your website.

How this attribute works

Total Value of Orders represents a common approach people use when they build segments: find my customers by spend across a time window, and then associate those customers to specific products and brands.

Total Value of Orders 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 Total Value of Orders work?

Total Value of Orders is built from standard columns that are output by Amperity. The following example returns a list of customers who purchased more than $100 during the previous six months:

The SQL for Total Value of Orders works like this. It uses the sum of item revenue from the Unified_Itemized_Transactions table as its starting point:

SELECT
  amperity_id
  ,SUM(item_revenue) AS purchase_value
FROM Unified_Itemized_Transactions

then returns all items that match two conditions: purchase value and a date range, and then groups them by Amperity ID:

WHERE order_datetime < DATE_TRUNC('day', CURRENT_TIMESTAMP - interval '6' month)
AND purchase_value > 100
GROUP BY amperity_id

In this example, only transactions that occurred within the previous 6 months are returned, grouped by Amperity ID and aggregated by item revenue, after which they are filtered to include only customers who purchased more than $100.

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))

You may then filter this list of customers more by applying any of the product, purchase, and store filters. When you select these filters, they are added to the WHERE statement, like this:

AND product_subcategory = 'chewing gum'
AND purchase_channel = 'online'

The SQL for Total Value of Orders 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 Total Value of Orders 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 the total values of orders for customers who made orders within a specified date range, start with the Total Value of Orders attribute located under Transactional Behaviors:

After the attribute appears in your segment, specify the value condition and choose a date range:

After you specify a value 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
was between	was between returns total values that are between two specified values.
was exactly	was exactly returns total values that match the specified value.
was less than	was less than returns total values that are less than the specified value.
was more than	was more than returns total values that are greater 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