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.

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

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.

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

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.