First Order

First Order returns a list of customers who made their first order during your chosen date range. For example, return all customers whose first order was during the previous 12 months:

Customers who bought a blue shirt within the previous 12 months.

After you specify a date range you may apply filters to associate first purchases with specific products, brands, channels, and stores. For example, return all customers who purchased a blue shirt from your website.

How this attribute works

First Order represents a common approach people use when they build segments: find all of my customers who made their first order in the past N days, months, or years, and then associate that list of customers to your products and brands.

First 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 to your marketing, which simplifies the number of steps required to associate a list of customers to your products, stores, channels, and brands.

How does the SQL for First Order work?

First Order is built from standard columns that are output by Amperity. The following example returns a list of customers whose first order was a blue shirt that they purchased online within the previous 12 months:

Customers who bought a blue shirt within the previous 12 months.

The SQL for First Order works like this. It uses order datetime from the Unified_Itemized_Transactions table as its starting point:

SELECT
  amperity_id
  ,order_datetime
  ,RANK() OVER (PARTITION BY amperity_id ORDER BY order_datetime, order_id) AS order_rank
FROM Unified_Itemized_Transactions

and then uses the RANK() function to identify which of those orders were a customer’s first order, and then returns all items that match two conditions: order rank and a date range:

WHERE order_rank = 1
AND order_datetime < DATE_TRUNC('day', CURRENT_TIMESTAMP - interval '12' month)

In this example, only transactions that occurred within the previous 12 months and are a customer’s first order are returned.

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_category = 'shirt'
AND product_subcategory = 'blue'
AND purchase_channel = 'online'

and they are added to the SELECT statement, like this:

SELECT
  amperity_id
  ,order_datetime
  product_category = 'shirt'
  product_subcategory = 'blue'
  purchase_channel = 'online'
  ,RANK() OVER (PARTITION BY amperity_id ORDER BY order_datetime, order_id) AS order_rank
FROM Unified_Itemized_Transactions

Why are these attributes added to the WHERE statement and the SELECT statement? They are added to the SELECT statement to ensure that the correct ranking is applied to products, purchases, and stores before you apply product, purchase, and store filters to your segment.

The SQL for First 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 First 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 First Order attribute located under Transactional Behaviors:

Use the first order attribute to find first orders by date range.

After the attribute appears in your segment, choose a date range:

Customers who bought a blue shirt within the previous 12 months.

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

About relative dates

Use relative dates to define ranges of dates and times that are relative to the date and time at which a segment is run. For example, a rolling window that returns results starting from 90 days ago would use a relative date of “today - 90 days”.

A relative date is specified using the following pattern:

<today or now> <+/-> <#> <days, weeks, months, or years>

The 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

  • today - 3 months

  • today - 1 year

-or-

  • now - 2 weeks

  • now - 6 months

  • now - 24 hours

Use non-relative dates to pick specific dates and specific ranges of dates and times.

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 after

Recommended

was after returns a list of customers whose first purchases were after the specified time window.

was before

Recommended

was before returns a list of customers whose first purchases were before the specified time window.

was between

Recommended

was between returns a list of customers whose first purchases were between two specified time windows.

was not between

Not recommended

was not between returns a list of customers whose first purchases were not between two specified time windows.

was not on

Not recommended

was not on returns a list of customers whose first purchases were not on the specified time.

was on

was on returns a list of customers whose first purchases were on the specified time.

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