Customers schema

The customers schema gives you access to insights about your customers, and makes it easy to segment your customers according to their purchase history. Insights include first-time versus returning customers, and automatic segmentation that separates your customers into promising, loyal, at-risk, and dormant.

Using SINCE and UNTIL will filter customers by the date of their first order. A query that filters with SINCE -1m UNTIL today will return all customers who placed their first order in the last month.

Tip

It can take up to 10 hours for information about new customers to show in your customer reports.

Example customers query - customers over time

  1. SHOW count(1) AS new_customer_count, sum(total_order_count) AS orders, sum(total_order_value) AS order_value
  2. OVER month(happened_at) AS month
  3. FROM customer_analytics
  4. SINCE -11m
  5. UNTIL today ORDER
  6. BY month ASC

Order properties

The following properties show information about your customers’ order histories:

Properties Type Description
Aggregate properties - used in the SHOW clause
returned_item_quantity number The number of items returned.
net_quantity number Equates to the number of items sold - the number of items returned.
ordered_item_quantity number The quantity of items that were ordered.
orders number The number of orders that were placed on a given date. Canceled, pending, and unpaid orders are included. Test and deleted orders are not included.
Non-aggregate properties - used in the BY clause
order_id number The unique numeric identifier for the order.
order_name number The order number.
financial_status caseless_string The payment status of the order.
sale_kind string Whether the sale transaction is an order or a return.
adjustment caseless_string Whether there is an adjustment written to account for a refund discrepancy, for example, where the value of restocked items doesn’t equal the value of refunded payments.
cancelled caseless_string Whether or not the order is canceled.

Customer properties

The following properties show information about your customers’ addresses, and whether they are first-time or returning customers:

Properties Type Description
Non-aggregate properties - used in the BY clause
customer_email string The email address entered by the customer.
customer_name string The first and last names entered by the customer.
customer_id number The unique numeric identifier for the customer.
customer_type string Shows First-time if this is the customer’s first order, and Returning if this is not the customer’s first order.
billing_country string The country from the customer’s billing address.
billing_region string The state or province from the customer’s billing address.
billing_company string The company from the customer’s billing address.
billing_city string The city from the customer’s billing address.
shipping_region string The state or province from the customer’s shipping address.
shipping_city string The city from the customer’s shipping address.
shipping_country string The country from the customer’s billing address.

Customer segments

The following properties show customers by their loyalty status:

Properties Type Description
Non-aggregate properties - used in the BY clause
loyal caseless_string Customers who have a high probability of returning, and have placed more orders than the average customer.
promising caseless_string Customers who have a high probability of returning and becoming a loyal customer.
dormant caseless_string Customers who have a very low probability of returning to make another purchase.
at_risk caseless_string Customers who are a repeat customer with a medium probability of returning, and have not placed an order in a while.
## Time properties The following properties group information by the date and time that customers placed their first order:
Properties Type Description
Non-aggregate properties - used in the BY or OVER clause
year year The year of a customer’s first order.
month month The month of a customer’s first order.
week week The week of a customer’s first order.
day day The day of a customer’s first order.
hour hour The hour of a customer’s first order.