Querying Issuing
Use Stripe Sigma to retrieve information about Issuing.
The Issuing objects represented within Sigma include Authorizations, Transactions, Cards, and Cardholders. Issuing-specific tables can be found within the Issuing section of the schema.
Issuing data for your connected accounts can be found within tables prefaced with connected_account_
, e.g.,connected_account_issuing_authorizations
. More information about using Connect with Sigma can be found in the Connect section of the documentation.
Authorizations
Whenever an issued card is used to make a purchase, an Authorization object is created. Each row within the issuing_authorizations
table represents data about this object. The same information can be retrieved through the API and is available in the Dashboard. Note that the request history field is not currently available in Sigma. Every authorization that has been created on your account is available in Sigma.
The card_id
column of this table stores the ID of the card that was used to make the purchase. Additional information about the card that initiated the authorization can be found by joining the this column with the issuing_cards
table.
To access the transactions associated with a particular authorization, you can join on the authorization_id
column in the issuing_transactions
table.
The following query computes counts of authorizations grouped by approval status.
select date_trunc('month', created) as month, count(case when approved then 1 end) as num_approved_authorizations, count(*) as total_num_authorizations from issuing_authorizations where date_trunc('month', created) between date_trunc('month', date_add('month', -13, date(data_load_time))) and date_trunc('month', date_add('month', -1, date(data_load_time))) group by 1 order by 1 desc, 2 limit 2
month | approved | num_authorizations |
---|---|---|
2021-02-01 | false | 506 |
2021-02-01 | true | 10,045 |
Transactions
Any use of an issued card that results in funds entering or leaving your Stripe account, such as a completed purchase or refund, is represented by an Issuing Transaction object. Information about these objects is stored within within the issuing_transactions
table. The same information can be retrieved through the API and is available in the Dashboard.
For additional details about the transaction, such as the fee, you can access the associated balance transaction. You can do this by joining the balance_transaction_id
column with the id
column of the balance_transactions
table. Balance transactions are not Issuing-specific objects. More information about working with balance transactions in Sigma can be found in the Transactions section of the documentation.
The authorization_id
column allows you to access the Authorization object associated with the Transaction by joining on the id
column of the issuing_authorizations
table. This can provide additional details about how the transaction was authorized. The authorization_id
column on an Issuing transaction can be empty in the event of force capture and for some instances of refunds.
You can also access both the card and cardholder involved in the transaction via the card_id
and cardholder_id
columns. Information about the card is stored in the issuing_cards
table, and information about the cardholder is stored in the issuing_cardholders
table. The Card and Cardholder objects can provide additional details about who initiated the transaction.
The following query returns information about the three most recent over captures. It joins the issuing_authorizations
table to determine if this transaction is an over capture by comparing the amounts of the two objects.
select date_format(it.created, '%Y-%m-%d') as day, it.id, ia.amount as authorized_amount, -1 * it.amount as captured_amount from issuing_transactions it join issuing_authorizations ia on it.authorization_id=ia.id where it.type='capture' and -1 * it.amount > ia.amount --- This checks if this transaction was overcaptured order by day desc limit 3
day | id | authorized_amount | captured_amount |
---|---|---|---|
2021-02-26 | ipi_S4pwkKxai3ZYfYf | 150 | 151 |
2021-02-26 | ipi_yvhw9349dGnzWMv | 0 | 1,000 |
2021-02-26 | ipi_Z6GdthYVWxNly2b | 1450 | 1050 |
One of the benefits of using Sigma with Issuing is the ability to aggregate data. The following example joins the balance_transactions
table and aggregates each of the types of fees for Issuing transactions by month.
select date_trunc('month', it.created) as month, fd.type as fee_type, sum(fd.amount) as net_fees, sum(it.amount) as net_amount from issuing_transactions it inner join balance_transactions bt on bt.id=it.balance_transaction_id inner join balance_transaction_fee_details fd on fd.balance_transaction_id=bt.id group by 1,2 order by month desc, fee_type
month | fee_type | net_fees | net_amount |
---|---|---|---|
2021-01-01 | stripe-fee | 590 | 10,000 |
2021-02-01 | stripe-fee | 59 | 1,000 |
2021-03-01 | stripe-fee | 590 | 10,000 |
Cards
The issuing_cards
table contains data about an individual Card object. The same information is available via the API and within the Dashboard. The spending controls field is not currently available in Sigma.
Every issued card has an associated Cardholder, which can be accessed by joining the issuing_cardholders
table on the cardholder_id
column.
The following example retrieves information about three cancelled cards and their cardholders.
select ic.id, ich.email, ic.cancellation_reason, ic.last4 from issuing_cards ic inner join issuing_cardholders ich on ic.cardholder_id=ich.id where cancellation_reason is not null limit 3
id | cancellation_reason | last4 | |
---|---|---|---|
ic_hZcxIF4Ne6QijNM | b.smith@example.com | stolen | 1234 |
ic_0wWFiDVm8YuN3Gp | c.jackson@example.com | lost | 1235 |
ic_W147lpoLQWyh9Uv | j.doe@example.com | stolen | 1236 |
Cardholders
Cardholder data is stored within the issuing_cardholders
table. The same information can be retrieved through the API or via the Dashboard. The spending controls field is not currently available in Sigma.
This table can be joined to other tables to provide information about the entity that initiated a transaction or owns an issued card.
The following example retrieves information the three most recently created active cardholders.
select date_format(created, '%Y-%m-%d') as day, id, email, type from issuing_cardholders where status='active' limit 3
day | id | type | |
---|---|---|---|
2021-02-26 | ich_Tg34h0FGQZzhtFd | j.smith@example.com | individual |
2021-02-26 | ich_t4AosylcPsXtESj | entity@example.com | business_entity |
2021-02-26 | ich_N7EBpAmjm4CIfC6 | j.doe@example.com | individual |
Metadata
Metadata for each Issuing object is stored in a separate table in Sigma. The names of these tables will be the name of the objectโs table with the addition of _metadata
to the end, e.g., issuing_transactions_metadata
. The metadata table will have a foreign key to the corresponding object in the primary table that you can use to join the two tables. For example, every row in the issuing_transactions_metadata
table has the column issuing_transaction_id
that references the id
column of a row in the issuing_transactions
table.
The following example creates a dictionary from the issuing_transactions
tableโs metadata table. It then uses it to access the value of the metadata key 'my_label'
for several transactions.
with transactions_metadata_dictionary as ( select issuing_transaction_id, map_agg(key, value) metadata_dictionary from issuing_transactions_metadata group by 1 ) select date_format(it.created, '%Y-%m-%d') as day, it.id, it.amount, metadata_dictionary['my_label'] as my_label_value from issuing_transactions it left join transactions_metadata_dictionary on it.id = transactions_metadata_dictionary.issuing_transaction_id where element_at(metadata_dictionary, 'my_label') is not null order by day desc limit 3
day | id | amount | my_label_value |
---|---|---|---|
2021-02-26 | ipi_76szYEfxlmZitiR | 2000 | true |
2021-02-26 | ipi_fxLdV3RN471Zhkd | 100 | true |
2021-02-26 | ipi_ZhMdAphkLmkm7RV | 10000 | false |