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 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"