Home / Connectors / QuickBooks
Connector

QuickBooks connector: datasets and metric definitionsBeta

Bring in the profit and loss and balance sheet by month, the general ledger, and open invoices and bills. Matches QuickBooks reports. Updates daily.

5 datasets12 metric definitionsRead-onlyRefreshes daily

When you connect QuickBooks, Quiriz imports the datasets below and adds the metric definitions to Company Context, the one place your company’s definitions live. Every answer that uses one of these metrics uses the same definition, in the app, in Slack and in the Excel, Sheets, PowerPoint, Slides, Word and Docs add-ins. An admin can edit any of them.

DatasetsMetric definitionsWorks withQuestionsFAQ

Datasets

DatasetWhat it holds
[QuickBooks] Profit and lossOne row per account per month for this fiscal year and the two before it, matching the QuickBooks Profit and Loss report. amount is as QuickBooks shows it; profit_effect is + for income, - for costs, so SUM(profit_effect) is net income. pl_level: 1 income, 2 cost of goods sold, 3 expenses, 4 other income/expenses. basis names the QuickBooks report basis (Accrual or Cash), the company's own setting.
11 columnsmonth, fiscal_year, account_id, account_name, account_path, account_type, section, pl_level, basis, amount, profit_effect
[QuickBooks] Balance sheetOne row per account per month-end (today for the current month) for this fiscal year and the two before it, matching the QuickBooks Balance Sheet. Balances are points in time: never add month-ends together. is_cash = bank accounts; is_latest = the newest month-end. basis names the QuickBooks report basis (Accrual or Cash), the company's own setting.
11 columnsmonth_end, fiscal_year, account_id, account_name, account_path, account_type, section, is_cash, is_latest, basis, balance
[QuickBooks] LedgerOne row per general ledger posting line, for this fiscal year and the two before it (it may start later when the ledger line cap is reached: check MIN(txn_date)). Double entry: every transaction appears once per account it touches (an invoice has an Income line and an A/R line, both with the customer filled), so never sum amount across sections. Revenue by customer = SUM(profit_effect) WHERE pl_level = 1, grouped by customer. Spend by vendor = SUM(amount) WHERE pl_level IN (2, 3), grouped by vendor. customer is filled only on customer transaction types (invoice, sales receipt, credit memo, payment, refund) and vendor only on vendor types (bill, bill payment, expense, check, vendor credit, credit card credit, purchase order); journal entries and deposits leave both blank, so totals by customer can be below Revenue. For income and expense accounts, SUM(profit_effect) by month equals the Profit and loss dataset. basis names the QuickBooks report basis (Accrual or Cash), the company's own setting.
21 columnstxn_date, month, fiscal_year, txn_id, txn_type, doc_num, account_id, account_name, account_path, account_type, section, pl_level, name, customer, vendor, class, location, memo, basis, amount, profit_effect
[QuickBooks] Open invoicesEvery invoice with a balance still open today, from the QuickBooks A/R Aging Detail. A snapshot of today: no date filter applies. days_past_due is 0 until the due date.
9 columnscustomer, txn_type, doc_num, txn_date, due_date, days_past_due, aging_bucket, amount, open_balance
[QuickBooks] Open billsEvery bill with a balance still open today, from the QuickBooks A/P Aging Detail. A snapshot of today: no date filter applies.
9 columnsvendor, txn_type, doc_num, txn_date, due_date, days_past_due, aging_bucket, amount, open_balance

Metric definitions

Each metric is a total over the period you ask about. A metric built from other metrics, such as a rate, divides the totals for the whole period; it does not average daily rates. Dated by is the date column that decides which period a row belongs to.

[QuickBooks] Profit and loss

MetricHow it is calculatedDated byNotes
RevenueSum of amount, where pl_level = 1monthIncome accounts, by month, on the company's QuickBooks report basis (basis column). These are the books; Shopify's Net sales, when connected, is the store's own figure.
Cost of goods soldSum of amount, where pl_level = 2monthCost of goods sold accounts, by month.
Gross profitSum of profit_effect, where pl_level <= 2monthRevenue minus cost of goods sold, by month.
Gross marginGross profit ÷ RevenuemonthGross profit divided by revenue, as a decimal (0.42 = 42%).
Operating expensesSum of amount, where pl_level = 3monthExpense accounts, by month (not cost of goods sold, not other expenses).
Operating incomeSum of profit_effect, where pl_level <= 3monthGross profit minus operating expenses, by month.
Net incomeSum of profit_effectmonthEvery income and expense account, by month: QuickBooks' net income.
Net marginNet income ÷ RevenuemonthNet income divided by revenue, as a decimal.

[QuickBooks] Balance sheet

MetricHow it is calculatedDated byNotes
Cash balanceSum of balance, where is_cash = 1 AND is_latest = 1Today’s snapshotBank account balances at the newest month-end (today, for the current month). For cash over time, group [QuickBooks] Balance sheet by month_end; never add month-ends together.

[QuickBooks] Open invoices

MetricHow it is calculatedDated byNotes
Open receivablesSum of open_balanceToday’s snapshotWhat customers owe today, from the open invoices list.
Overdue receivablesSum of open_balance, where days_past_due > 0Today’s snapshotOpen invoice balances past their due date today.

[QuickBooks] Open bills

MetricHow it is calculatedDated byNotes
Open payablesSum of open_balanceToday’s snapshotWhat the company owes vendors today, from the open bills list.

Works with other connectors

Questions to ask

Ask QuickBooks in plain words

Try the questions on sample data with no signup, then connect QuickBooks read-only and ask your own.

Try Quiriz free →

Frequently asked questions

Does Quiriz change anything in QuickBooks?
No. The QuickBooks connector is read-only. Quiriz reads the data into datasets and never writes back to QuickBooks.
How often does the QuickBooks data refresh?
Once a day. Each dataset shows when it was last updated, and answers use the latest refresh.
Can I change how a metric is calculated?
Yes. The definitions are added to Company Context, where an admin can edit the formula, the filter or the date it uses. A daily refresh never overwrites a definition someone has changed.
Can I combine QuickBooks with other sources?
Yes. An admin can also define a metric from metrics on different datasets. See cross-source metrics.

All connectors: Quiriz connectors. Definitions on this page are generated from the product’s own connector code.