{"section":"tutorials","requestedLocale":"en","requestedSlug":"payments","locale":"en","slug":"payments","path":"docs/en/tutorials/beta/vtex-data-pipeline-beta/payments.md","branch":"main","content":"The payment dataset is composed of two main tables: `payments_transitions` and `transaction_transitions`.\n\nThis section includes the following information:\n\n- [Characteristics of payment data](#data-characteristics)\n- [Table: payments_transitions](#table-payments_transitions)\n- [Table: transaction_transitions](#table-transaction_transitions)\n- [Table: transactions_interactions](#table-transactions_interactions)\n- [Table: authorizations_consolidated](#table-authorizations_consolidated)\n- [Table: transaction_consolidation](#table_transaction_consolidation)\n- [Analyses with payment data](#analyses-with-payment-data)\n- [Correlations with other data](#correlations-with-other-data)\n\n## Data characteristics\n\n|**Characteristic**|**Description**|\n| - | - |\n|**Data source**|The data in this set comes from the [VTEX Payments module](/en/docs/tracks/how-the-payments-module-works). It can be collected and organized to reflect the transactions and payments completed on the platform.|\n|**Availability**|Navigation data can only be accessed through the VTEX Admin.|\n|**History**|The data is retained for two years, from 2023 for clients who already use the VTEX platform. The data in the `transaction_interactions` table only has history since May 2024 and the data in `authorizations_consolidated` only since June 2023.|\n|**Minimum update interval**|One hour.|\n\n## Table: payments_transitions\n\nThe `payments_transitions` table stores detailed information about payments, including the provider and the payment method used in each transaction. The table fields are described below:\n\n|**Column name**|**Field type**|**Column description**|\n| :-: |:-: | - |\n|batch_id|character varying(13)|Control ID of the data ingestion batch.|\n|event_date_ti me|timestamp with time zone|Date and time of transition, including time zone information.|\n|account|character varying(128)|Account name, mapped to 'account_name' on VTEX License Manager.|\n|account_id|character varying(36)|Account identifier, mapped to 'id' on VTEX License Manager after removing hyphens.|\n|connector|character varying(1024)|Provider that processes the payment.|\n|delta_value|double precision|Time in milliseconds spent in the 'from_transition' transition.|\n|from_transiti on|character varying(64)|Payment status up to the date and time of the event.|\n|to_transition|character varying(64)|New transaction status after the date and time of the event.|\n|payment_id|character varying(32)|Unique payment identifier.|\n|payment_sys tem|character varying(1024)|Payment type, such as credit card, debit card, etc.|\n|transaction_i d|character varying(36)|Unique identifier of the transaction this payment belongs to.|\n|tid|character varying(1024)|External code received from the operator, representing an identification number for an ecommerce transaction.|\n|nsu|character varying(1024)|NSU (Número Sequencial Único) is a unique sequential number used in Brazil to identify a card sales transaction and is associated to each created invoice.|\n|currency_cod e|character varying(1024)|Currency code that is represented using three capital letters.|\n|value|integer|Payment amount multiplied by 100. For example, this amount will be 235 for a payment of $2.35.|\n\n## Table: transaction_transitions\n\nThe `transaction_transitions` table stores the transactions from completed orders.\nEach transaction has a set of payments and changes the status based on a predefined flow. \n\nThe table fields are described below:\n\n|**Column name**|**Column type**|**Column description**|\n| :-:| :-: | - |\n|batch_id|character varying(13)|Control ID of the data ingestion batch.|\n|event_date_tim e|timestamp with time zone|Date and time of transition, including time zone information.|\n|account|character varying(128)|Maps to an 'account_name' on VTEX License Manager.|\n|account_id|character varying(36)|Maps to an 'id' in the VTEX license management system after removing hyphens.|\n|delta_value|double precision|Time in milliseconds spent in the 'from_transition' transition.|\n|from_transition|character varying(64)|Transaction status up to the date and time of the event.|\n|to_transition|character varying(64)|New transaction status after the date and time of the event.|\n|transaction_id|character varying(36)|Unique identifier of the transaction.|\n\n## Table: transactions_interactions\n\nThis table tracks updates to transactions through some interactions. Below is a detailed description of each field:\n\n| **Column name** | **Column type** | **Column description** |\n|:---:|:---:|---|\n| key_id | character varying(35)\t | Interaction Id |\n| key_transaction_id | character varying(35) | Transaction Id |\n| payment_id | character varying(120) | Unique id for payment. Not Required. |\n| source | character varying(120) | It indicates which code called the interaction update or what that interaction represents; possible values can be function name or business rules. It doesn't have a pattern. Required. |\n| status | character varying(60) | Transaction status update. Required |\n| date | timestamp without time zone\t | Date-time when transaction was happened. Required. |\n| message | character varying(65535) | Detailed information about transaction update. Non structured field. Required. |\n| ticks | character varying(60) | Payment tick id. Required. |\n| batch_id | character varying(13) | Indicate datetime when job that ingested data happened. YYYY_MM_DD_hh. |\n| account | character varying(60) | Account name where the given transaction has taken place. Required. |\n\n## Table: authorizations_consolidated\n\nThis table consolidates details about payment authorizations, tracking key information about each transaction, including risk assessment, payment details, and ingestion control. Below is a description of each field:\n\n| **Column name** | **Column type** | **Column description** |\n|:---:|:---:|---|\n| account | character varying(63) | Account name where the given transaction has taken place (will always be the same as the merchant_name) |\n| merchant_name | character varying(63) | Merchant name where the given transaction has taken place (will always be the same as the merchant_name) |\n| transaction_start_date | timestamp with time zone | Date and time of authorization, including time zone information. |\n| connector | character varying(80) | Name of the connector that took part of the given transaction. |\n| payment_id | character varying(255) | Unique payment identifier. |\n| payment_system | character varying(255) | Payment type, such as credit card, debit card, etc. |\n| transaction_id | character varying(63) | Unique identifier of the transaction this payment belongs to. |\n| currency | character varying(4) | Currency code that is represented using three capital letters. |\n| payment_value | double precision | Payment amount multiplied by 100. For example, this amount will be 235 for a payment of $2.35. |\n| authorized | boolean | Defined whether the transaction was authorized or not (true or false) |\n| installments | integer | Number of installments of the given transaction |\n| recipients_count | integer | Number of recipients of the given transaction |\n| risk_level | character varying(100) | Risk level associated with that transaction |\n| return_code | character varying(255) | Return code, sent by the payment provider (normally indicates why a transaction was rejected) |\n| affiliation_id | character varying(37) | Unique identifier of the affiliation associated with this transaction. |\n| antifraud_affiliation_id | character varying(37) | Unique identifier of the antifraud affiliation associated with this transaction. |\n| ingestion_time | timestamp with time zone | Date and time when this row was included on the table |\n| batch_id | character varying(13) | Control ID of the data ingestion batch. |\n\n## Table: transaction_consolidation\n\nThis table contains payment transaction metadata, including detailed information about amounts, status, affiliates, payment connectors, fraud analysis, installments, and source data.  \n\n| **Column name** | **Column type** | **Column description** |\n|:---:|:---:|---|\n| id | character varying(65535)\t | Unique Transaction ID this Payment belongs to. |\n| transaction_value | double precision\t | Payment value with the decimal format. |\n| status_transaction | character varying(65535)\t | The last state of the Transaction |\n| reference_key | character varying(65535)\t | Internal identifier used for reconciliation or as a transaction reference. The reference_key is a field sent by Checkout to the Payments when requesting the creation of a transaction. |\n| merchant_name | character varying(65535)\t | Account name that includes: main account, sub accounts, child accounts, etc. |\n| account_name | character varying(16383)\t | Account name that includes: main account, sub accounts, child accounts, etc. |\n| main_account_name | character varying(16383)\t | Show the main account name. |\n| is_active | boolean | Indicates whether the transaction is authorized or not. |\n| is_operating | boolean | Indicates whether the account or connector is operational at the transaction moment. |\n| recipients_count | integer | Number of splits, sellers receiving the payment in case of a split, and total merchants receiving the payment including the main account. |\n| date | date | The last date of the transition. |\n| account_verification_only | boolean | Indicates whether the transaction was performed solely to verify the account or payment method, without a real monetary value. |\n| payment_id | character varying(65535)\t | Unique Payment ID. |\n| value_payment | double precision | Monetary value of the first payment associated with the transaction. |\n| status_payment | character varying(65535)\t | Final status of the first payment associated with the transaction (e.g., approved, canceled). |\n| currency_code | character varying(65535)\t | Uppercase three-letters currency code. |\n| connector_name | character varying(65535)\t | The connector is who processes the payment. |\n| affiliation_id | character varying(65535)\t | The affiliation_id represents a payment connector instance linked to a specific account. When configuring a store with a payment connector (e.g., CIELO), it becomes an affiliation, meaning the connector is instantiated for that account. |\n| affiliation_name | character varying(65535)\t | Descriptive name of the connector affiliation used for the first payment. |\n| is_legacy_connector | boolean | Indicate if this connector is a legacy or not. |\n| payment_system | integer | Payment code type i.e. 1, 2, 3, and etc. |\n| installments | integer | Number of installments. |\n| payment_system_name | character varying(65535)\t | Payment type i.e. credit cart, debit cart, etc. |\n| return_code | character varying(65535) | Anything that the payment connector returns to us. |\n| antifraud_affiliation_id | character varying(65535)\t | Unique identifier for the antifraud service instance associated with the first payment. |\n| antifraud_affiliation_name | character varying(65535)\t | The antifraud_affiliation_id represents the instance of an anti-fraud service linked to a specific account. When configuring an anti-fraud provider (e.g., Sherlock) for a store, it becomes an affiliation, meaning the service is instantiated for that account. |\n| antifraud_provider_name | character varying(65535)\t | Descriptive name of the antifraud service affiliation used in the first payment. |\n| antifraud_analysis_result | character varying(65535)\t | The result of the antifraud analysis. |\n| pan_type | character varying(65535)\t | Type of card used in the first payment (e.g., credit, debit, prepaid). |\n| payment_origin | character varying(65535)\t | Origin or channel through which the first payment was made (e.g., physical store, website, app). |\n| is_3ds_required | boolean | Indicates whether the first payment required 3D Secure protocol authentication. |\n| payment_id_2 | character varying(65535)\t | Unique Payment ID, for the second payments, when applicable. |\n| value_payment_2 | double precision | Monetary value of the second payment associated with the transaction (when applicable). |\n| status_payment_2 | character varying(65535)\t | Final status of the second payment associated with the transaction (e.g., approved, canceled). |\n| currency_code_2 | character varying(65535)\t | Uppercase three-letters currency code. |\n| connector_name_2 | character varying(65535)\t | The connector is who processes the second payment, if exists. |\n| affiliation_id_2 | character varying(65535)\t | The affiliation_id represents a payment connector used for the second payment instance linked to a specific account. When configuring a store with a payment connector (e.g., CIELO), it becomes an affiliation, meaning the connector is instantiated for that account. |\n| affiliation_name_2 | character varying(65535)\t | Descriptive name of the connector affiliation used for the second payment (when applicable). |\n| is_legacy_connector_2 | boolean | Indicate if this second connector used is a legacy or not. |\n| installments_2 | integer | Number of installments of the second payment, when applicable. |\n| payment_system_2 | integer | Numeric code of the payment method used in the second payment. |\n| payment_system_name_2 | character varying(65535)\t | Name of the payment method used in the second payment (e.g., credit card, Pix). |\n| return_code_2 | character varying(65535)\t | Return code provided by the connector regarding the second payment. |\n| antifraud_affiliation_id_2 | character varying(65535)\t | Unique identifier for the antifraud service instance associated with the second payment. |\n| antifraud_affiliation_name_2 | character varying(65535)\t | Descriptive name of the antifraud service affiliation used in the second payment (when applicable). |\n| antifraud_provider_name_2 | character varying(65535)\t | Name of the antifraud service provider used for the second payment (when applicable). |\n| antifraud_analysis_result_2 | character varying(65535)\t | Detailed result of the antifraud analysis for the second payment (e.g., approved, rejected, pending). |\n| pan_type_2 | character varying(65535)\t | Type of card used in the first payment (e.g., credit, debit, prepaid). (when applicable) |\n| payment_origin_2 | character varying(65535)\t | Origin or channel through which the second payment was made (e.g., physical store, website, app). |\n| is_3ds_required_2 | boolean | Indicates whether the second payment required 3D Secure protocol authentication. |\n| transaction_value_usd | double precision | This USD value is FX Neutral, meaning that is always considered the dollar of the day each payment was made. This conversion information is taken from the analytics_bi.currencies table, through the fromcurrency field. |\n| value_payment_usd | double precision | This USD value is FX Neutral, meaning that is always considered the dollar of the day each payment was made. This conversion information is taken from the analytics_bi.currencies table, through the fromcurrency field. |\n| value_payment_2_usd | double precision | This USD value is FX Neutral, meaning that is always considered the dollar of the day each payment was made. This conversion information is taken from the analytics_bi.currencies table, through the fromcurrency field. |\n| batch_id | character varying(13) | Data ingestion batch control ID. |\n\n## Analyses with payment data\n\nThe payment dataset can be used for the following analyses:\n\n- **Payment processor efficiency analysis:** Evaluate the performance of different payment providers by analyzing the average time spent on each transition to identify the most efficient and reliable ones.  \n- **Monitoring payment status transitions:** Track changes in payment status over time to understand patterns and identify potential issues in the payment flow.  \n- **Transaction volume analysis by account:** Determine the volume of transactions processed by each account to get insights about the demand for payment services on different accounts.  \n- **Distribution of payment type:** Analyze the use distribution of different payment systems to assess customer preferences for different payment methods. \n\n## Correlations with other data\n\nThe payment data of the VTEX platform has correlations with other datasets which give more insight into the purchase cycle:  \n\n- **Interaction with [order](/en/docs/tutorials/orders-data-pipeline-beta) data:** Analyzing payment data alongside orders offers insights into how payment methods influence order conversions and reveal trends in customer purchasing behavior.    \n- **Financial and accounting analyses:** Integrating payment data with financial analyses contributes to more efficient cash flow management and enables more accurate financial forecasts, helping improve the company's financial health. \n\n### Discover other datasets\n\n- [Catalog](/en/docs/tutorials/catalog-data-pipeline)\n- [Inventory](/en/docs/tutorials/inventory-data-pipeline-beta)\n- [Navigation](/en/docs/tutorials/navigation-data-pipeline) \n- [Orders](/en/docs/tutorials/orders-data-pipeline-beta)  \n- [Prices](/en/docs/tutorials/prices-data-pipeline-beta)  \n- [Promotion](/en/docs/tutorials/promotions)  \n- [Gift cards](/en/docs/tutorials/gift-card-data-pipeline)\n- [Bridge logs](/en/docs/tutorials/bridge-logs-data-pipeline)"}