Snowflake Data Share

Reference for 2Checkout's Snowflake Data Share, including datasets, schemas, relationships, refresh schedules, and reporting guidance.

Overview

The 2Checkout Data Share in Snowflake gives you direct SQL access to your transactional, subscription, and customer data, refreshed automatically throughout the day. Query the data using standard SQL, or connect any BI tool that supports Snowflake — such as Sigma, Power BI, Tableau, or Looker Studio — to build your own reports and dashboards.

The share includes five datasets:

• ORDERS_EVENTS - event-sourced order lifecycle tracking (orders, refunds, chargebacks)
• SUBSCRIPTION_EVENTS - subscription state change history (renewals, churn, auto-renewal)
• CUSTOMER_EVENTS - customer profile change history
• AUTHORIZATIONS - payment authorization attempts, successful and declined
• EXCHANGE_RATES - daily currency exchange rates from October 2012 onwards

Availability and requirements

• The Data Share is available on request. Contact your 2Checkout account manager to have it enabled for your account.
• You need an active Snowflake account to consume the share.
• You only see data belonging to your own merchant account: access is enforced at row level by 2Checkout.

How the data is structured

All datasets are event-sourced: each row is a snapshot of an entity (order, subscription, customer, authorization attempt) at a specific point in time, recorded in the EVENT_DATE column. An entity therefore appears multiple times, once per state change. To obtain the current state, select the most recent event per entity key (for example, the latest EVENT_DATE per LICENCE_CODE).

Data freshness: ORDERS_EVENTS, SUBSCRIPTION_EVENTS, CUSTOMER_EVENTS, and EXCHANGE_RATES refresh approximately every 1.5 hours; AUTHORIZATIONS refreshes approximately every 10.5 hours.

Naming and type conventions

• Order identifier: REFNO in every dataset (CURRENT_REFNO / PREVIOUS_REFNO in SUBSCRIPTION_EVENTS).
• Merchant account: ACCOUNT_ID in every dataset.
• Country: COUNTRY_CODE (ISO 3-letter) in every dataset; ORDERS_EVENTS and AUTHORIZATIONS also carry a full-name COUNTRY column.
• Timestamps: EVENT_DATE and all other date columns are TIMESTAMP_TZ (timezone-aware).
• Statuses: SUBSCRIPTION_STATUS and CUSTOMER_STATUS, both uppercase enumerations.
• Test flags: TEST_ORDER (orders) and TEST_SUBSCRIPTION (subscriptions), both BOOLEAN.
• Monetary amounts are numeric (no casting needed before arithmetic).

ORDERS_EVENTS

Records every state change in the lifecycle of an order. Each row is a single event timestamped by EVENT_DATE. Use this dataset to calculate order volumes, revenue, average order value, refund rates, and chargeback rates.

Column	Type	Description	Possible values / notes
REFNO	VARCHAR	Order reference number (join key across datasets)
EVENT_TYPE	VARCHAR	Lifecycle stage of this event	AUTH, PENDING, PROCESSING, APPROVED, VENDOR_APPROVED, COMPLETE, SHOPPER_INVOICE, ORDER_SHIPPED, DELIVERED_ORDER_FOR_PARTNER, REFUND, REFUNDED_ORDER_FOR_PARTNER, CHARGEBACK_OPEN, CHARGEBACK_CLOSED, SUSPECT, INVALID, CANCELED, SEPA_REFUSAL
EVENT_DATE	TIMESTAMP_TZ	When this event occurred
ORDER_DATE	TIMESTAMP_TZ	When the order was originally placed
COMPLETE_DATE	TIMESTAMP_TZ	Order completion date	NULL until completed
ORDER_PRICE	NUMBER(38,2)	Total order price
CURRENCY	VARCHAR	Order currency	ISO 4217 (USD, EUR, GBP, ...)
ORDER_EXCHANGE_RATE	FLOAT	Exchange rate applied at order time
PAYABLE_AMOUNT	NUMBER(38,2)	Amount payable to you
PROFIT_GROSS	NUMBER(38,4)	Gross profit
COMMISSION	NUMBER(38,4)	Commission amount
AFFILIATE_ORDER_COMMISSION	NUMBER(38,4)	Affiliate commission
ORDER_DISCOUNT	NUMBER(38,2)	Discount amount applied
ORDER_DISCOUNT_CODE	VARCHAR	Discount coupon code
ORDER_DISCOUNT_NAME	VARCHAR	Discount promotion name
ORDER_DISCOUNT_PRICE	NUMBER(38,2)	Discount price impact	Negative value - amount deducted from the order price
ORDER_DISCOUNT_TAX	NUMBER(38,2)	Tax on discount	Negative value - amount deducted from taxes
SHIPPING	NUMBER(38,2)	Shipping cost
REFUND_DATE	TIMESTAMP_TZ	Date of refund (if applicable)
REFUND_REASON	VARCHAR	Reason for refund	Standard reasons (UNWANTED_RENEWAL, DUPLICATE_ORDER, CHARGEBACK, DST Refund, Manual taxes refund) plus your custom CUSTOM_REASON_* codes
REFUND_TOTAL	NUMBER(38,2)	Total refund amount
REFUND_TYPE	VARCHAR	Type of refund	TOTAL, PARTIAL
REFUND_PROCESSOR	VARCHAR	Who requested the refund	VENDOR, SHOPPER, 2CO; NULL when not applicable
REFUND_COMMENT	VARCHAR	Free-text refund comment
CHARGEBACK_OPEN_DATE	TIMESTAMP_TZ	Date the chargeback was opened
CHARGEBACK_OPEN_REASON	VARCHAR	Reason for chargeback	Fraud/Order not recognized; New/renewal order not recognized; Canceled recurring; Order not fulfilled/not delivered; Duplicate order; Agreed refund not processed; Product(s) not as described/unfunctional; Authorization problem; Information request; Unknown reason
CHARGEBACK_CLOSED_DATE	TIMESTAMP_TZ	Date the chargeback was resolved
CHARGEBACK_CLOSE_REASON	VARCHAR	Resolution outcome	WON, LOST, ACCEPTED, OPEN
CARD_TYPE	VARCHAR	Payment card type	Free text with mixed casing (Visa/visa, Mastercard/MasterCard, ...) - normalize casing before grouping
CARD_LAST_DIGITS	VARCHAR	Last digits of the card number
CARD_EXPIRATION_DATE	TIMESTAMP_TZ	Card expiration date	Represented as the last day of the expiry month
COUNTRY	VARCHAR	Buyer country, full name	e.g. "United States of America"
COUNTRY_CODE	VARCHAR	Buyer country, ISO 3-letter code	'N/A' when unknown
PARTNER_ID	NUMBER	Partner identifier
PARTNER_CODE	VARCHAR	Partner code
AFFILIATE_ID	NUMBER	Affiliate identifier
ORDER_FLOW	VARCHAR	Order flow type	REGULAR, PURCHASE ORDER, STORED CREDIT
ORDER_STATUS	VARCHAR	Current order status	AUTHRECEIVED, PENDING, COMPLETE, CANCELED, REFUND, REVERSED, PENDINGCASH, TEST, CASHED
ORDER_ORIGIN	VARCHAR	Origin channel	AVANGATE (automatic billing), WEB, MOBILE, API
APPROVE_STATUS	VARCHAR	Approval status	OK, WAITING, INVALIDDATA, FRAUD
SRC	VARCHAR	Traffic source
TEST_ORDER	BOOLEAN	Whether this is a test order	TRUE / FALSE
ACCOUNT_ID	NUMBER	Your merchant account ID
INVOICE	VARCHAR	Invoice number

SUBSCRIPTION_EVENTS

Captures every state transition of your subscriptions. Each row is a snapshot of a subscription at a specific EVENT_DATE, recording its status and renewal properties at that moment. Use this dataset to calculate renewal rates, churn, auto-renewal disable reasons, and subscription lifecycle metrics.

Column	Type	Description	Possible values / notes
EVENT_DATE	TIMESTAMP_TZ	When this state change occurred
LICENCE_CODE	VARCHAR	Unique subscription identifier
SUBSCRIPTION_STATUS	VARCHAR	Subscription state at event time	ACTIVE, DISABLED, EXPIRED, PAST_DUE, PENDING_ACTIVATION
IS_AUTORENEWING	BOOLEAN	Whether auto-renewal is enabled	TRUE / FALSE
IS_TRIAL	BOOLEAN	Whether this is a trial subscription	TRUE / FALSE
IS_LIFETIME	BOOLEAN	Whether this is a lifetime (non-expiring) subscription	TRUE / FALSE
SUBSCRIPTION_EXPIRATION_DATE	TIMESTAMP_TZ	Current subscription expiration date
PREVIOUS_SUBSCRIPTION_EXPIRATION_DATE	TIMESTAMP_TZ	Expiration date before the latest renewal or extension	NULL for new acquisitions
AUTORENEWAL_DISABLE_REASONS	VARIANT	JSON array of reasons auto-renewal was disabled	CHURN_REASON_* codes, e.g. HIGH_PRICE, DONT_NEED, PREFER_MANUAL, ALREADY_RENEWED, REFUNDED_ORDER, CREDIT_CARD_REMOVED, IRRECOVERABLE_CARD_ERROR, UNSPECIFIED; empty array while auto-renewing
AUTORENEWAL_DISABLE_DATE	TIMESTAMP_TZ	When auto-renewal was disabled	NULL while auto-renewing
AUTORENEWAL_DISABLE_SOURCE	VARCHAR	Channel through which auto-renewal was disabled	SYSTEM, MYACCOUNT, MYACCOUNT-CPC, CPANEL, GAP, CHURN_SOURCE_UNSPECIFIED, or empty
DISABLE_REASONS	VARIANT	JSON array of reasons the subscription was disabled	Same CHURN_REASON_* code set; empty array while enabled
DISABLE_DATE	TIMESTAMP_TZ	When the subscription was disabled	NULL while enabled
DISABLE_SOURCE	VARCHAR	Channel through which the subscription was disabled	SYSTEM, API, MYACCOUNT-COD, CPANEL, CHURN_SOURCE_UNSPECIFIED, or empty
RENEWAL_COUNTER	NUMBER	Number of paid billing cycles so far	0, 1, 2, ...
GRACE_PERIOD_DAYS	NUMBER	Days after expiration before hard cancellation	Typically 30
NEXT_RENEWAL_PRICE	NUMBER	Upcoming renewal price (net)
NEXT_RENEWAL_PRICE_CURRENCY	VARCHAR(3)	Currency of the renewal price	ISO 4217
INITIAL_PURCHASE_DATE	TIMESTAMP_TZ	Original purchase date
PRODUCT_CODE	VARCHAR	Your product code
PAYMENT_METHOD	VARCHAR	Stored payment method type	CCVISAMC, CCAMEX, DISCOVER, CCJCB, CCHIPERCARD, CCELO, CARTE_BLEUE, CC_APPLE_PAY, CC_INSTALLMENTS, PAYPAL, PAYPAL_ARMS_RS, DIRECT_DEBIT, DIRECT_EBANKING, IDEAL, BOLETO, FREE, FREE_ARMS_RS, FREE_ARMS_SP, CCVISAMC_ARMS_RS, CCAMEX_ARMS_RS
CUSTOMER_ID	NUMBER	Buyer/customer identifier	Joins to CUSTOMER_EVENTS.CUSTOMER_ID
ACCOUNT_ID	NUMBER	Your merchant account ID
CURRENT_REFNO	VARCHAR	Most recent order linked to this subscription	Joins to ORDERS_EVENTS.REFNO
CURRENT_PRODUCT_ID	NUMBER	Current product variant ID
PREVIOUS_REFNO	VARCHAR	Prior order reference	NULL for new acquisitions
PREVIOUS_PRODUCT_ID	NUMBER	Prior product variant ID
SUBSCRIPTION_PRODUCT_ID	NUMBER	Subscription-product link ID
PARTNER_ID	NUMBER	Partner identifier
TEST_SUBSCRIPTION	BOOLEAN	Whether this is a test subscription	TRUE / FALSE

CUSTOMER_EVENTS

Tracks customer (buyer) profile information over time. Each row is a timestamped snapshot of a customer's profile at EVENT_DATE, enabling historical lookups of contact and address data and customer-level analyses such as Customer Lifetime Value.

Column	Type	Description	Possible values / notes
EVENT_DATE	TIMESTAMP_TZ	When this profile snapshot was recorded
CUSTOMER_ID	NUMBER	Unique customer identifier
EXTERNAL_CUSTOMER_ID	VARCHAR	External customer ID assigned by you	Often NULL; GUID-style when present
FIRST_NAME	VARCHAR	Customer first name
LAST_NAME	VARCHAR	Customer last name
EMAIL	VARCHAR	Customer email address
PHONE	VARCHAR	Customer phone number
COMPANY	VARCHAR	Company name
CITY	VARCHAR	Billing city	May contain "-" placeholder
ZIP	VARCHAR	Billing postal code
STATE	VARCHAR	Billing state/region	Free text - full names and abbreviations both occur
COUNTRY_CODE	VARCHAR	Billing country, ISO 3-letter code	USA, GBR, CAN, DEU, ...
FISCAL_CODE	VARCHAR	Tax identification number
TAX_OFFICE	VARCHAR	Tax office reference
CUSTOMER_STATUS	VARCHAR	Customer account status	ACTIVE, INACTIVE, TRIAL
CREATION_DATE	TIMESTAMP_TZ	When the customer record was first created
FIRST_NAME_DELIVERY	VARCHAR	Delivery contact first name
LAST_NAME_DELIVERY	VARCHAR	Delivery contact last name
EMAIL_DELIVERY	VARCHAR	Delivery email
ADDRESS_DELIVERY	VARCHAR	Full delivery address
ACCOUNT_ID	NUMBER	Your merchant account ID

AUTHORIZATIONS

Records payment authorization attempts made through the payment gateway - both successful and declined charges. Use this dataset for approval-rate analysis, decline diagnostics, and payment-method performance. Only attempts linked to an order reference (REFNO) are included.

Column	Type	Description	Possible values / notes
ATTEMPT_ID	VARCHAR	Unique attempt identifier	Composite of REFNO and EVENT_DATE
EVENT_DATE	TIMESTAMP_TZ	When the authorization was recorded
ATTEMPT_DATE	TIMESTAMP_TZ	When the authorization was attempted
ACCOUNT_ID	NUMBER	Your merchant account ID
REFNO	VARCHAR	Order reference the attempt belongs to	Joins to ORDERS_EVENTS.REFNO
PAYMENT_METHOD	VARCHAR	Payment method category	CC, PAYPAL, DIRECT_DEBIT, BOLETO, IDEAL, WIRE, WEBMONEY, OTHER
CARD_TYPE	VARCHAR	Card brand	Free text with mixed casing - normalize before grouping, or prefer CARD_NETWORK
CARD_NETWORK	VARCHAR	Card network	VISA, MASTERCARD, AMERICAN EXPRESS, DISCOVER, JCB, MAESTRO (clean uppercase)
CARD_CATEGORY	VARCHAR	Card category	CREDIT, DEBIT, PREPAID, CHARGE CARD; older rows may contain brand names
CARD_TIER	VARCHAR	Card tier level	STANDARD, CLASSIC, GOLD, PLATINUM, WORLD, WORLD ELITE, INFINITE, SIGNATURE, BUSINESS, and more
PREPAID	VARCHAR	Whether the card is prepaid	YES, NO
SUBSCRIPTION_EXPIRATION_DATE_AFTER_SUCCESS	TIMESTAMP_TZ	Expiration date of the subscription renewal linked to this attempt (not card expiry)	NULL when the attempt is not linked to a subscription renewal (e.g. a first-time purchase)
TOTAL_PRICE	NUMBER(38,2)	Amount authorized	0.00 occurs (e.g. card verification attempts)
AVG_PRICE	NUMBER(38,2)	Average price per item
CURRENCY	VARCHAR	Authorization currency	ISO 4217
COUNTRY	VARCHAR	Buyer country, full name
COUNTRY_CODE	VARCHAR	Buyer country, ISO 3-letter code	'N/A' when unknown
ISSUER_COUNTRY	VARCHAR	Card issuer country, full name
ISSUER_COUNTRY_CODE	VARCHAR	Card issuer country, ISO 3-letter code	'N/A' when unknown
RESPONSE_TYPE	VARCHAR	Authorization outcome classification	SUCCESS; SOFT DECLINE (temporary - a retry may succeed); HARD DECLINE (permanent - retries will not succeed)

Card fields are card-payments only: CARD_TYPE, CARD_NETWORK, CARD_CATEGORY, CARD_TIER, and PREPAID are populated only when PAYMENT_METHOD = 'CC'. For other payment methods they are NULL. They can also be NULL on older historical rows.

EXCHANGE_RATES

A reference table with historical currency conversion rates from October 1, 2012 onwards. Join on date and currency pair to convert financial amounts (e.g. ORDER_PRICE, NEXT_RENEWAL_PRICE) into a common base currency.

Column	Type	Description	Possible values / notes
EXCHANGE_DATE	TIMESTAMP_TZ	Date the rate applies to	Daily, from 2012-10-01 to present
FROM_CURRENCY	VARCHAR(3)	Source currency code	ISO 4217, 30+ currencies
TO_CURRENCY	VARCHAR(3)	Target currency code	ISO 4217, 30+ currencies
EXCHANGE_RATE	FLOAT	Conversion multiplier (FROM x RATE = TO)

Relationships between datasets

• ORDERS_EVENTS.ACCOUNT_ID = SUBSCRIPTION_EVENTS.ACCOUNT_ID = CUSTOMER_EVENTS.ACCOUNT_ID = AUTHORIZATIONS.ACCOUNT_ID - merchant account link
• SUBSCRIPTION_EVENTS.CUSTOMER_ID = CUSTOMER_EVENTS.CUSTOMER_ID - buyer identity
• SUBSCRIPTION_EVENTS.CURRENT_REFNO / PREVIOUS_REFNO = ORDERS_EVENTS.REFNO - orders that created or renewed the subscription
• AUTHORIZATIONS.REFNO = ORDERS_EVENTS.REFNO - order behind the authorization attempt
• EXCHANGE_RATES joins on date + currency pair to any financial column

Usage tips

• Current state: datasets are event-sourced, so filter to the latest EVENT_DATE per entity key (QUALIFY ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY EVENT_DATE DESC) = 1).
• Currency normalization: join EXCHANGE_RATES on the transaction date and currency pair to convert amounts to your reporting currency.
• Exclude test data: filter TEST_ORDER = FALSE and TEST_SUBSCRIPTION = FALSE for production reporting.
• Approval rate: in AUTHORIZATIONS, approval rate = SUCCESS attempts / all attempts; distinguish SOFT DECLINE (retryable) from HARD DECLINE (not retryable) when analyzing recovery potential.
• Card brand grouping: prefer CARD_NETWORK (clean uppercase) over CARD_TYPE (mixed-case free text).

Need a custom dataset? Additional datasets can be evaluated on request - contact your 2Checkout account manager.