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.

###Example customers query - customers over time

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

Order properties

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

PropertiesTypeDescription
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:

PropertiesTypeDescription
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:
PropertiesTypeDescription
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.

On this page