Payments schema

You can see information about the payments that you've received, including payment totals, the payment methods that your customers use, and details about the order that each payment is attributed to.

Using SINCE and UNTIL will filter payments and refunds by the date of the transaction. A query that filters with SINCE -1m UNTIL today will return all payments and refunds that took place in the last month.

Example payments query - payments by method

SHOW orders, gross_payments, refunded_payments, gift_card_payments, net_payments, total_payments BY payment_method, digital_wallet
FROM payments
SINCE -7d
UNTIL -0d
ORDER BY net_payments DESC

Payments properties

The following properties show information about the dollar value totals that are included in a payment:

PropertiesTypeDescription
Aggregate properties - used in the SHOW clause
captured_payments price The sum of transaction_amount for captured payments.
cash_change_returns price The sum of transaction_amount for change payments.
cash_payments price The sum of the transaction_amount for sale payments (non-credit card payments).
gross_payments price The sum of the transaction_amount for transactions of type sale (non-credit card), change, and capture.
gift_card_payments price The sum of transaction_amount for gift card payments.
net_cash_payments price The sum of transaction_amount for sale transactions - the sum of change transactions.
net_payments price The sum of the net transaction_amount for transactions of type sale, change, capture, and refund, and not including the purchase of gift cards.
refunded_payments price The sum of transaction_amount for refunded payments.
transaction_amount price The amount of a payment or refund.
total_payments price The sum of (sale - change + capture - refund) payments.

Transaction properties

The following properties show information about the payment methods that customers use:

PropertiesTypeDescription
Aggregate properties - used in the SHOW clause
transactions number The total number of transactions.
Non-aggregate properties - used in the BY clause
payment_method string The payment method that the customer used.
credit_card_type string The type of credit card that the customer used.
digital_wallet string Which digital wallet the customer used, if applicable.
transaction_kind string Whether the transaction is a sale, refund, or change.

Order properties

The following properties show information about the order that a payment is attributed to:

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 the customer that a payment is attributed to:

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

Sales channel properties

The following properties show information about the sales channel that processed a payment:

PropertiesTypeDescription
Non-aggregate properties - used in the BY clause
channel_provider_name string The sales channel that the order was placed through.
pos_location_name string The name of the point of sale location where the sale was made.

Employee properties

If a payment was from a POS sale or a draft order, then the following property shows information about the employee who made the sale:

PropertiesTypeDescription
Non-aggregate properties - used in the BY clause
employee_id number The unique numeric identifier for the employee who created the order.
employee_name string The name of the employee who created the order.

Time properties

The following properties show information about the date and time that a sale was made:

PropertiesTypeDescription
Non-aggregate properties - used in the BY or OVER clause
year year The year of a payment or refund.
month month The month of a payment or refund.
week week The week of a payment or refund.
day day The day of a payment or refund.
hour hour The hour of a payment or refund.