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:
Properties | Type | Description |
---|---|---|
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:
Properties | Type | Description |
---|---|---|
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:
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 the customer that a payment is attributed to:
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. |
Sales channel properties
The following properties show information about the sales channel that processed a payment:
Properties | Type | Description |
---|---|---|
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. |
Properties | Type | Description |
---|---|---|
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. |
Properties | Type | Description |
---|---|---|
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. |