Payment Attempts dataset

This page outlines the definition, type, and utility of each ShopifyQL column including numeric values, aggregates, and dimensional attributes.

Notes about this dataset

Anchor link to section titled "Notes about this dataset"

This dataset is available for Plus merchants only.
The grain of this dataset is one row for each payment_attempt_group.
A payment attempt group is defined as a sequence of payment attempts that meet all of the following criteria:
- Attempts were part of the same checkout
- Attempts used the same card, if card data is available
- Attempts were for the same amount of money, in the same currency
- Attempts used the same payment method
- Each attempt in the group was made no more than 30 minutes after the previous attempt
- The attempts were all failures, or ended in a success. If two sequential successes occur with all the same attributes, then they're treated as separate groups.
Attempts are grouped to account for retried payments, allowing users to determine whether a customer eventually succeeded in making the purchase that they intended to make. This approach is based on the assumption that if a payment attempt group ends in a success, then the preceding failed attempts didn't result in a loss of revenue.

Numeric values

Anchor link to section titled "Numeric values"

Numeric values are the base numbers in the dataset. They’re typically aggregated using ShopifyQL functions like sum.

Name	Type	Definition
`successful_attempts`	number	The number of payment attempts in this group that were successful
`failed_attempts`	number	The number of payment attempts in this group that were not successful
`customer_currency_amount`	price	The amount of the attempted payment, in the currency used by the customer
`shop_currency_amount`	price	The amount of the attempted payment, in the store's default currency

Dimensional attributes

Anchor link to section titled "Dimensional attributes"

Name	Type	Definition
`payment_attempt_group`	string	A unique identifier for the group of payment attempts described by each row
`order_id`	number	A unique idenfitier for the related order, if one exists
`card_bin_country`	string	The country associated with the card bin
`card_bin`	string	The first several digits of the card number used
`card_brand`	string	The brand of the card that was used
`card_wallet`	string	The card wallet that was used, if any
`payment_method`	string	The payment method that was used
`funding_source`	string	The source of funding behind the card used, such as `debit` or `credit`
`customer_currency`	string	The currency used by the customer
`shop_currency`	string	The default store currency
`first_attempted`	timestamp	The time at which the earliest payment attempt in the group occurred
`last_attempted`	timestamp	The time at which the latest payment attempt in the group occurred
`in_person_payment`	Boolean	Whether the payment was made in person, for example, using a point of sale terminal

Aggregates

Anchor link to section titled "Aggregates"

Aggregates are predefined calculations of numeric values, to replicate metrics that are available throughout Shopify. Aggregates can be grouped or filtered by any of the dimensional attributes.

Name	Type	Definition
authorization_rate	percent	The rate of successful payment authorization, adjusted for retries. Defined as `sum(successful_attempts) / count()`. To learn more about how retries are accounted for, refer to the dataset notes.
raw_authorization_rate	percent	The rate of successful payment authorization, not adjusted for retries. Defined as `sum(successful_attempts) / sum(successful_attempts + failed_attempts)`.

Sample queries

Anchor link to section titled "Sample queries"

Show payment authorization rate by month

Anchor link to section titled "Show payment authorization rate by month"

Show payment authorization rate by payment_method

Anchor link to section titled "Show payment authorization rate by payment_method"