BI data source
The Business Intelligence (BI) Data Source is a feature designed to provide an extensive view of transaction data within the IXOPAY platform. This functionality allows users to execute sophisticated queries and aggregations on their transaction data for analytical and reporting purposes.
IXOPAY platform Full Version
The BI data source is an optional feature which is not automatically available for all IXOPAY platform clients!
If you want to get access to all IXOPAY platform features you need to upgrade your plan. Please contact our customer success team at [email protected] or our sales team at [email protected] for more information.
Connectivity and authentication
You can access the BI Data Source through these endpoints:
- For production:
https://bds.ixopay.com/query/transactions - For sandbox:
https://sandbox.ixopay.com/query/transactions
Each request to the BI data source must be authenticated using BASIC Authentication credentials. Here, the username is the API Key, and the password is the API Key's password. You can refer to the API Key section of our user manual for more information on creating an API Key.
Querying the BI data source
The BI Data Source is built on ElasticSearch 7.10 service, providing robust querying and search operations. It enables you to perform powerful data analysis on the stored transaction information.
You can use a wide range of query and search operations as described in the ElasticSearch 7.10 documentation. Here are some useful resources:
For a more granular understanding of how to query the BI data source, please refer to the Example Queries and ElasticSearch Schema sections. These resources contain detailed examples and the specific ElasticSearch schema used by the IXOPAY platform, which will guide you on how to effectively use this feature.
Example queries
Below, you'll find a set of example queries you can execute on the BI Data Source to serve various use cases:
Example 1: Finding a specific transaction
Retrieve a particular transaction using its UUID:
curl --request GET \
--url 'https://bds.ixopay.com/query/transactions?q=uuid:12345678901234567890' \
--user '$BI_API_KEY:$BI_PASSWORD'
Example 2: Count transactions by status (in the last 24 hours)
Aggregate transactions based on their status within the past 24 hours:
curl --request POST \
--url 'https://bds.ixopay.com/query/Transactions' \
--header 'Content-Type: application/json; utf-8' \
--user '$BI_API_KEY:$BI_PASSWORD' \
--data '
{
"aggs": {
"status_aggregation": {
"terms": {
"field": "status",
"min_doc_count": 1,
"order": {
"_term": "asc"
},
"size": 500
}
}
},
"query": {
"bool": {
"filter": [
{
"range": {
"created_at": {
"format": "epoch_millis",
"gte": "now-24h",
"lte": "now"
}
}
}
]
}
},
"size": 0
}'
Example 3: Volume of transactions per method & currency (in the last month)
Determine the total volume of transactions per payment method and currency over the previous month:
curl --request POST \
--url 'https://bds.ixopay.com/query/Transactions' \
--header 'Content-Type: application/json; utf-8' \
--user '$BI_API_KEY:$BI_PASSWORD' \
--data '
{
"aggs": {
"method_aggregation": {
"terms": {
"field": "method",
"min_doc_count": 1,
"order": {
"_term": "asc"
},
"size": 100
},
"aggs": {
"currencies": {
"terms": {
"field": "currency",
"min_doc_count": 1,
"size": 100
},
"aggs": {
"volume": {
"sum": {
"field": "amount"
}
}
}
}
}
}
},
"query": {
"bool": {
"filter": [
{
"range": {
"created_at": {
"format": "epoch_millis",
"gte": "now-1M",
"lte": "now"
}
}
}
]
}
},
"size": 0
}'
These examples illustrate the power and flexibility of querying the BI Data Source on IXOPAY. Using these queries, you can retrieve, aggregate, and analyze your transaction data effectively.
ElasticSearch Schema
The BI Data Source uses a specific ElasticSearch schema. You can download the description here: bi-elements.txt.
ElasticSearch Schema
"tenant_guid": { "type": "keyword" }, // GUID of (Sub-)Tenant
"tenant_name": { "type": "keyword" }, // Name of (Sub-)Tenant
"master_tenant": { "type": "keyword" }, // GUID of Master Tenant (IXOPAY platform's direct customer)
"merchant_guid": { "type": "keyword" }, // GUID of Merchant
"merchant_name": { "type": "keyword" }, // Name of Merchant
"connector_guid": { "type": "keyword" }, // GUID of Connector
"connector_name": { "type": "keyword" }, // Name of Connector
"provider_guid": { "type": "keyword" }, // GUID of Provider
"provider_name": { "type": "keyword" }, // Name of Provider
"purchase_id": { "type": "text" }, // Transaction's Purchase-ID
"test_mode": { "type": "boolean" }, // Test-Mode enabled/disabled
//routing
"related_id": { "type": "keyword" }, // UUID of related transaction (e.g. preauthorization in case capture)
"broker_id": { "type": "keyword" }, // UUID of master transaction in case of routing
"meta_connector_guid": { "type": "keyword" }, // GUID of Meta-Connector
"meta_connector_name": { "type": "keyword" }, // Name of Meta-Connector
//base data
"adapter": { "type": "keyword" }, // Adapter Name
"method": { "type": "keyword" }, // Payment Method Name
"type": { "type": "keyword" }, // Transaction Type (debit, refund etc.)
"status": { "type": "keyword" }, // Current Status of Transaction
"first_error_code": { "type": "keyword" }, //code of first error (see errors element below)
"first_error_message": { "type": "keyword" }, //message of first error (see errors element below)
"first_adapter_error_code": { "type": "keyword" }, //adapter code of first error (see errors element below)
"first_adapter_error_message": { "type": "keyword" }, //adapter message of first error (see errors element below)
"api_call_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, //timestamp of the incoming API call
"received_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, //timestamp when we received the transaction (e.g. for Push transactions)
"created_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Creation Date
"modified_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Last modified date
"initiated_by": { "type": "keyword" }, // Who initiated the transaction (merchant, Virtual-Terminal, etc.) - possible values merchant, ixopay, provider, vt-merchant, vt-tenant, scheduler
"merchant_txid": { "type": "text" }, // Transaction ID from Merchant
"adapter_txid": { "type": "text" }, // Transaction ID from payment provider
"adapter_token": { "type": "text" }, // Payment Token from payment provider
"adapter_processor_txid": { "type": "text" }, // Optional Transaction ID from processor behind payment provider
"additional_id1": { "type": "text" }, // Additional ID 1 sent by Merchant
"additional_id2": { "type": "text" }, // Additional ID 2 sent by Merchant
"amount": { "type" : "scaled_float", "scaling_factor": 100 }, // Amount
"currency": { "type": "keyword" }, // Currency
"base_amount": { "type" : "scaled_float", "scaling_factor": 100 }, // Amount in Base Currency
"base_currency": { "type": "keyword" }, // Base Currency
"description": { "type": "text" }, // Description (sent by Merchant)
"statement_descriptor": { "type": "text" }, // Descriptor on customer's statement
"extra_data": { "type": "object" }, // Extra Data sent by Merchant
"result_extra_data": { "type": "object" }, // Extra Data returned by Provider
"callback_extra_data": { "type": "object" }, // Extra Data returned by Provider
"buyer_country": { "type": "keyword" }, // Country of customer (if provided by provider)
"buyer_country_geopoint": { "type": "geo_point" }, // Geo-Point for this country
"3d_secure": { "type": "keyword" }, // 3D-Secure flag (mandatory, optional, off)
"transaction_indicator": { "type": "keyword" }, // Transaction Indicator (Single, Recurring etc.)
"incoming_settlement_state": {"type": "keyword"}, // Current Settlement State from Provider to Tenant/Nerchant
"outgoing_settlement_state": {"type": "keyword"}, // Current Settlement State from Tenant to Merchant
//custom data
"return_data": { "type": "object" }, // Additional transaction data (varies by method)
//items sent by Merchant
"items": {
"type": "nested",
"properties": {
"identification": { "type": "text" },
"name": { "type": "text" },
"price": { "type" : "scaled_float", "scaling_factor": 100 },
"currency": { "type": "keyword" },
"quantity": { "type" : "scaled_float", "scaling_factor": 100 },
"description": { "type": "text" }
}
},
// Attributes computed by follow-up transaction
"is_recurring": { "type": "boolean" },
"registered_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_chargedback": { "type": "boolean" },
"chargedback_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_charged_reversed": { "type": "boolean" },
"chargeback_reversed_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_deregistered": { "type": "boolean" },
"deregistered_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_refunded": { "type": "boolean" },
"refunded_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_fully_refunded": { "type": "boolean" },
"is_captured": { "type": "boolean" },
"captured_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_voided": { "type": "boolean" },
"voided_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"is_with_register": { "type": "boolean" },
"attempt": { "type": "integer" }, // in case of Automatic Failover through Meta-Connector this identifies the attempt number
"is_in_manual_review": { "type": "boolean" }, //whether this transaction is currently in the manual review list
// Statistical data
"is_postback_delivered": { "type": "boolean" }, // is the postback successfully acknowledged by the Merchant's system
// Tags of the transaction
"tags": {
"type": "nested",
"properties": {
"name": { "type": "keyword" },
"createdAt": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
"createdBy": { "type": "keyword" }
}
},
// Fee data
"fees": {
"type": "nested",
"properties": {
"amount": { "type": "scaled_float", "scaling_factor": 1000 }, // Fee amount
"currency": { "type": "keyword" }, // Fee currency
"deducts_payout": { "type": "boolean" }, // Does this fee affects the payout amount
"description": { "type": "text" }, // Decription
"date": { "type": "date", "format": "yyyy-MM-dd" }, // Effective date for fees
"created_by": { "type": "keyword" }, // User-ID if manually created
"entity": { "type": "text", "fields": { "raw": { "type": "keyword" } } }, // Fee Entity Name
"entity_id": { "type": "keyword" }, // Fee Entity ID
"entity_type": { "type": "keyword" } // Type of Entity (Mapping to fix values: Provider side Mapping: normal, markup, interchange, scheme, conversion, gateway; Merchant side Mapping: merchant-normal, merchant-markup, merchant-interchange, merchant-scheme, merchant-conversion)
}
},
// Customer data
"customer": {
"type": "object",
"properties": {
"identification": { "type": "text" },
"first_name": { "type": "text" },
"last_name": { "type": "text" },
"birth_date": { "type": "text" },
"billing_address1": { "type": "text" },
"billing_address2": { "type": "text" },
"billing_city": { "type": "text" },
"billing_postcode": { "type": "text" },
"billing_state": { "type": "text" },
"billing_country": { "type": "text" },
"billing_country_geopoint": { "type": "geo_point" },
"billing_phone": { "type": "text" },
"shipping_firstname": { "type": "text" },
"shipping_lastname": { "type": "text" },
"shipping_company": { "type": "text" },
"shipping_address1": { "type": "text" },
"shipping_address2": { "type": "text" },
"shipping_city": { "type": "text" },
"shipping_postcode": { "type": "text" },
"shipping_state": { "type": "text" },
"shipping_country": { "type": "text" },
"shipping_country_geopoint": { "type": "geo_point" },
"shipping_phone": { "type": "text" },
"company": { "type": "text" },
"email": { "type": "text", "fields": { "raw": { "type": "text", "analyzer": "email"} } },
"email_verified": { "type": "boolean" },
"ip_address": { "type": "ip" },
"national_id": { "type": "text" },
"iban": { "type": "text" },
"bic": { "type": "text" },
"gender": { "type": "text" },
"extra_data": { "type": "object" }
}
},
// Creditcard information
"creditcard": {
"type": "object",
"properties": {
"type": { "type": "keyword" }, // Card Type
"card_holder": { "type": "text" }, // Card holder
"country": { "type": "keyword" }, // BIN Country of card
"expiry_month": { "type": "byte" },
"expiry_year": { "type": "short" },
"first_six_digits": { "type": "text" },
"last_four_digits": { "type": "text" },
"xid": { "type": "text" }, // XID of 3D-Secure Authentication process
"3ds_status": { "type": "keyword" }, // Authentication result of 3D-Secure process, one of: off, attempted, authenticated
"enrolled": { "type": "keyword" }, // Is card holder 3D-Secure enrolled
"eci": { "type": "keyword" }, // ECI Flag
"cavv": { "type": "text" }, // CAVV Result value of 3D-Secure Authentication
"merchant_fingerprint": { "type": "text" }, // Fingerprint of card (merchant-specific)
"global_fingerprint": { "type": "text" }, // Fingerprint of card (system wide)
"bin_brand": { "type": "keyword" }, // BIN Brand of card
"bin_country": { "type": "keyword" }, // BIN Country of card
"bin_country_geopoint": { "type": "geo_point" }, // Geopoint of BIN Country
"bin_type": { "type": "keyword" }, // BIN Type (Consumer, Business etc.)
"bin_level": { "type": "keyword" } // BIN Level (Standard, Gold, Platinum)
"3ds_version": { "type": "keyword"}, // 3D Secure version applied
"3ds_env": { "type": "keyword"}, // 3D Secure environment used (inhouse = IXOPAY platform's)
"3ds_dsTransId": { "type": "keyword"}, // Directory Server Transaction ID for 3D Secure 2.x authentication
"3ds_serverTransId": { "type": "keyword"}, // Directory Server Transaction ID for 3D Secure 2.x authentication
"3ds_challenged": { "type": "boolean"}, // Was the Challenge flow triggered?
"3ds_challenge_completed": { "type": "boolean"}, // was the challenge flow completed
"3ds_trans_status": { "type": "keyword"}, // 3DS Transaction Status
"3ds_method_fingerprinted": { "type": "boolean"}, // Was the Method flow triggered
"token_type": { "type": "keyword"}, // Type of token used (if any, e.g. ApplePay, GooglePay)
}
},
// Transaction errors
"errors": {
"type": "nested",
"properties": {
"message": { "type": "text" }, // Error Message of Gateway
"code": { "type": "text" }, // Error Code of Gateway
"adapter_message": { "type": "text" }, // Error Message from Provider
"adapter_code": { "type": "text" }, // Error Code from Provider
"created_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }
}
},
// Status history
"status_history": {
"type": "nested",
"properties": {
"status_field": { "type": "keyword" },
"from_status": { "type": "keyword" },
"to_status": { "type": "keyword" },
"created_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }
}
},
// Risk Evaluation data
"risk_data": {
"type": "object",
"properties": {
"risk_profile_id": { "type": "integer" }, // Executed Risk Profile
"risk_profile_name": { "type": "text", "fields": { "raw": { "type": "keyword" } } }, // Name of Risk Profile
"risk_check_performed_on": { "type": "keyword" }, // Date Time of Risk Check performance
"final_score": { "type": "integer" }, // Total Score
"final_action": { "type": "keyword" }, // Final action
"alert": { "type": "boolean" }, // Alert notification triggered
"3d_secure": { "type": "keyword" } // Resulted 3D-Secure Flagging
}
},
// Individual Risk Check results
"risk_checks": {
"type": "nested",
"properties": {
"check_type": { "type": "text", "fields": { "raw": { "type": "keyword" } } }, // Identifier of executed risk check
"rule_id": { "type": "integer" }, // ID of Risk Rule
"hit": { "type": "boolean" }, // Did this check hit?
"action": {"type": "keyword" }, // Executed action
"score_added": { "type": "integer" }, // Score added to total score
"alert": { "type": "boolean" }, // Alert notification triggered
"manual_review": { "type": "boolean" }, // Manual review triggered
"3d_secure": { "type": "keyword" } // Resulted 3D-Secure Flagging
}
},
// Post Processing Data
"invoiceable": { "type": "boolean" }, // is transaction invoiceable
"reconciliation_state": {"type": "keyword"}, // Reconciliation state (none, matched, mismatch)
"settlement_amount": { "type" : "scaled_float", "scaling_factor": 100 }, // Settled amount (from provider)
"settlement_currency": { "type": "keyword" }, // Settlement currency (from provider)
"settlement_date": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Settlement date (from provider)
"settlement_exchange_rate": { "type" : "scaled_float", "scaling_factor": 100 }, // Exchange rate for settlement amount (if applicable)
"outgoing_settlement_amount": { "type" : "scaled_float", "scaling_factor": 100 }, // Outgoing settlement amount
"outgoing_settlement_currency": { "type": "keyword" }, // Outgoing settlement Currency
"outgoing_settlement_date": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Outgoing settlement date
// Further Post-Processing Data
"postprocessing": {
"type": "object",
"properties": {
"reconciliation_conflicts": { // Exists if reconcilation conflicts occurred
"type": "nested",
"properties": {
"resolved": { "type": "boolean" }, // Is the conflict resolved
"resolution": { "type": "keyword" } // Resolution option
}
},
"provider_settlement": { // Exists if provider settlement batch was created
"type": "object",
"properties": {
"settlement_status": {"type": "keyword"}, // Status of provider settlement batch
"settlement_number": {"type": "text"}, // Settlement batch number from Provider
"payment_ref": {"type": "text"}, // Payment reference
"settlement_date":{ "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Date of Settlement
"funds_received_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" } // Date of Funds Received
}
},
"jobs": { // All Post-Processing jobs this transaction appears in
"type": "nested",
"properties": {
"job_type_id": {"type": "keyword" }, // ID of Job Type
"job_type_name": {"type": "keyword" }, // Name of Job Type
"created_by": {"type": "keyword" }, // If manually created, this contains the User-ID
"created_at": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Date/Time of Job creation
"status": {"type": "keyword" }, // Status of Job
"conflicted": { "type": "boolean" }, // Conflicted state of Job
"external_id": {"type": "text" }, // External ID of Job (usually assigned by IXOPAY platform customer's upstream systems)
"period_from": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, // Job Period From
"period_to": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" } // Job Period To
"job_data": { "type": "object" } // Additional Job Data
}
}
}
}
By leveraging the BI data source, you can unlock deeper insights into your transaction data, helping you make more informed decisions about your business strategies.
Please note that this article assumes a basic understanding of the ElasticSearch service. For beginners, we recommend familiarizing yourself with ElasticSearch before diving into the BI Data Source feature.