Query Tax data
Use Sigma or Data Pipeline to retrieve your Stripe Tax data.
Stripe Tax data includes various components that work together to provide a comprehensive view of your tax liability through automated calculations with Payment Links, Checkout, Subscriptions, Invoices, Custom integrations and as a platform. All tax-specific data is available in the Tax section of the schema, and the primary table is tax_
.
To explore tax data further, you can use additional tables that represent the components of a tax_
, such as tax_
, tax_
, and tax_
.
This diagram represents all components created during an automated tax calculation and how it relates to the original object that triggered calculation. Amounts in the examples below appear in minor currency units (for example, cents instead of dollars).
Tax transactions
Each row in the tax_
table represents a single tax_transaction object. Tax transactions record the assumed or reduced tax liability for a transaction or reversal. You can report on every tax transaction created with automated tax calculations.
This table is our recommended starting point for creating reports on your tax liability. You need to join on the other tax tables below for your tax liability amounts, but this table joins your data to other products like invoices and checkout sessions. Joining to non-tax tables by source_
and source_
provides the object that triggers the creation of the tax transaction.
Tax transactions have a one-to-one relationship with their original source object. The following example retrieves a list of reportable tax transactions with their original source of invoices, which you can then expand on with query billing data.
select tx.id as tax_transaction_id, tx.posted_at as tax_transaction_recognized_at, inv.id as invoice_id, inv.total as invoice_total from tax_transactions tx inner join invoices inv on inv.id = tx.source_id limit 5
tax_transaction_id | tax_transaction_recognized_at | invoice_id | invoice_total |
---|---|---|---|
tax_TveK6FlF1XQuRlN | 2024-06-18 16:40:16 +0000 | in_oYtahW8MBSE6JJv | 20,000 |
tax_Em130vWnoCwl8J3 | 2021-06-28 00:01:21 +0000 | in_vAN2tgyE2lkvK0a | 189 |
tax_uMIkoyvo42KfQGm | 2024-06-23 23:35:40 +0000 | in_hdhy6X6WJ5TuD6B | 219 |
tax_2fRfQdNV3TFPjVG | 2024-06-23 23:35:40 +0000 | in_GBSdI7yYo1BnkgQ | 10,475 |
tax_sjuu0BHENX0wIv5 | 2023-07-23 04:03:06 +0000 | in_ofQsBwBs4M1GUXB | 200 |
Tax transactions metadata
Tax transactions might have metadata set with your custom integrations. Each row in tax_
returns one key-value pair.
Tax transaction items
Tax line items and tax shipping costs make up the total amounts and tax liability for tax transactions. Tax line items and tax shipping costs are in their own tables, but they both contribute to amounts and total tax liability.
Tax line items
Tax line items represent line items that contribute to the sale of goods for the transaction. Each row in tax_
represents a single tax line item associated to the tax_
.
Tax shipping costs
Tax shipping costs represent shipping items that contribute to the shipment of goods for the transaction. Each row in tax_
represents a single shipping cost associated to the tax_
.
Note
These tables provide high level information based on each item. A single tax transaction can have multiple line items and one shipping cost which make up the amounts for a tax transaction.
amount: This is the gross amount of the line item. When tax_
is inclusive
, it includes tax liability amounts. When tax_
is exclusive
(the default), it excludes tax liability amounts.
amount_tax: This is the amount of tax liability for the line item.
currency: This is the presentment currency that defines amount
and amount_
. Summing across different currencies yields unexpected results.
The following example retrieves a list of all amounts and tax liability for a given tax_
.
with tax_amounts as ( select li.tax_transaction_id, li.id, li.amount, li.amount_tax, li.tax_behavior, li.currency, 'line_item' as item_type from tax_transaction_line_items li union all select sc.tax_transaction_id, sc.id, sc.amount, sc.amount_tax, sc.tax_behavior, sc.currency, 'shipping_cost' as item_type from tax_transaction_shipping_costs sc ) select id, amount, amount_tax, tax_behavior, currency, item_type from tax_amounts where tax_transaction_id = 'tax_d2e5470dC63u'
id | amount | amount_tax | tax_behavior | currency | item_type |
---|---|---|---|---|---|
tax_li_sprz3wqPSI4nwFu | 3,100 | 0 | exclusive | usd | line_item |
tax_li_FbMJPAyPFpMk0Zp | 1,999 | 190 | exclusive | usd | line_item |
tax_li_d2GgUzjp1KzeO2w | 3,500 | 304 | inclusive | usd | line_item |
tax_li_NV0WUE5vAXiZGZ4 | 4,242 | 242 | exclusive | usd | line_item |
tax_li_GuLdZsxrxJg9enb | 799 | 0 | exclusive | usd | shipping_cost |
Tax transaction line items metadata
Tax transaction line items might have metadata set with your custom integrations. Each row in tax_
returns one key-value pair.
Tax transaction jurisdiction details
Each row in the tax_
table represents a jurisdiction which makes up the liability for the tax transaction item (tax_
or tax_
).
Understanding jurisdiction details
This table provides more in-depth information based on each jurisdiction. A single tax transaction item can have multiple jurisdictions which make up the amounts for a tax transaction item.
Summing amount_
or amount_
across all jurisdiction details doesn’t always equal the tax transaction item’s amount
.
amount_taxable: This is the amount up to the tax transaction item’s amount
that is taxable. Multiple jurisdictions can have the same taxable amount.
amount_non_taxable: This is the amount up to the tax transaction item amount
that is non-taxable. Multiple jurisdictions can have the same non-taxable amount.
amount_tax: This is part of the tax transaction item amount_
. Summing this across all jurisdiction details equals the transaction item’s amount_
.
currency: This is the presentment currency that defines amount_
, amount_
and amount_
. Summing across different currencies yields unexpected results.
filing_currency: This is the filing currency used by the applicable tax authority which defines filing_
, filing_
and filing_
. Summing across different filing currencies yields unexpected results.
The following example retrieves all jurisdiction details for a given tax transaction item.
select jd.amount_taxable, jd.amount_non_taxable, jd.amount_tax, jd.taxability_reason, jd.jurisdiction_level, jd.jurisdiction_name, concat(jd.jurisdiction_country, '-', jd.jurisdiction_state) as jurisdiction_location from tax_transaction_jurisdiction_details jd where li.id = 'tax_li_52d37cdd6f7'
amount_taxable | amount_non_taxable | amount_tax | taxability_reason | jurisdiction_level | jurisdiction_name | jurisdiction_location |
---|---|---|---|---|---|---|
0 | 1,299 | 0 | not_subject_to_tax | country | United States | |
1,299 | 0 | 77 | standard_rated | state | California | US-CA |
1,299 | 0 | 16 | standard_rated | county | San Diego | US-CA |
1,299 | 0 | 7 | standard_rated | district | Transactions and Use Tax (CLVT) | US-CA |
1,299 | 0 | 7 | standard_rated | district | Transactions and Use Tax (CVGT) | US-CA |
1,299 | 0 | 7 | standard_rated | district | Regional Transportation Commission (SDCT) | US-CA |
Example queries
For additional examples, please reference the Tax section of query template library in Sigma sidebar.
Tax liability by month
This example summarizes the tax liability imposed on line items and shipping costs grouped by month and currency.
with tax_amounts as ( select li.tax_transaction_id, li.amount, li.amount_tax, li.tax_behavior, li.currency from tax_transaction_line_items li union all select sc.tax_transaction_id, sc.amount, sc.amount_tax, sc.tax_behavior, sc.currency from tax_transaction_shipping_costs sc ), tax_liability as ( select date_format(date_trunc('month', posted_at), '%Y-%m-%d') as month, currency as presentment_currency, sum( (
month | presentment_currency | total_sales_excluding_tax | total_tax |
---|---|---|---|
usd | 286,600 | 43,522 | |
eur | 30,898 | 56 | |
usd | 79,776 | 2,565 | |
eur | 55,434 | 3,954 |
Tax itemized jurisdiction details
This template itemizes tax transaction jurisdiction details for the previous month to produce a customizable version of the Tax itemized export. Review the comments throughout the template to learn more about how it is customizable for your needs.
with tax_amounts as ( select li.id, li.source_line_item_id, li.amount, li.amount_tax, li.tax_behavior, li.tax_code, li.currency, li.quantity_decimal from tax_transaction_line_items li union all select sc.id, -- Shipping costs do not have source line item IDs '' as source_line_item_id, sc.amount, sc.amount_tax, sc.tax_behavior, sc.tax_code, sc.currency, -- Shipping costs do not have a quantity '' as quantity_decimal from
source_id | source_type | presentment_currency | recognized_at | tax_date, | transaction_source_item_id | transaction_item_id | amount | amount_tax | tax_behavior | tax_code | jurisdiction_name | jurisdiction_level | … |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
in_N61KTnGHkiKnFgl | invoice | eur | il_IU4BLjG1uJzobNj | tax_il_efSefMcGgBg3NlQ | -199 | -20 | exclusive | txcd_99999999 | Ireland | country | … | ||
in_cDJ2ozDUDGwj36i | invoice | usd | il_dqwNEkxoX8SRJGV | tax_il_3xzFOhhOUjfkqFY | 10,000 | 1,000 | exclusive | txcd_99999999 | Rhode Island | state | … | ||
in_y587AkIIn62WtGt | invoice | usd | il_AgSsLEUWr1Hbk3m | tax_il_7D7Ha4xfGmYxGGv | 2,999 | 371 | exclusive | txcd_99999999 | Fire District 17 | district | … |