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.
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
| Dataset | What it holds |
|---|---|
| [QuickBooks] Profit and loss | One 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 sheet | One 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] Ledger | One 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 invoices | Every 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 bills | Every 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
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Revenue | Sum of amount, where pl_level = 1 | month | Income 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 sold | Sum of amount, where pl_level = 2 | month | Cost of goods sold accounts, by month. |
| Gross profit | Sum of profit_effect, where pl_level <= 2 | month | Revenue minus cost of goods sold, by month. |
| Gross margin | Gross profit ÷ Revenue | month | Gross profit divided by revenue, as a decimal (0.42 = 42%). |
| Operating expenses | Sum of amount, where pl_level = 3 | month | Expense accounts, by month (not cost of goods sold, not other expenses). |
| Operating income | Sum of profit_effect, where pl_level <= 3 | month | Gross profit minus operating expenses, by month. |
| Net income | Sum of profit_effect | month | Every income and expense account, by month: QuickBooks' net income. |
| Net margin | Net income ÷ Revenue | month | Net income divided by revenue, as a decimal. |
[QuickBooks] Balance sheet
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Cash balance | Sum of balance, where is_cash = 1 AND is_latest = 1 | Today’s snapshot | Bank 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
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Open receivables | Sum of open_balance | Today’s snapshot | What customers owe today, from the open invoices list. |
| Overdue receivables | Sum of open_balance, where days_past_due > 0 | Today’s snapshot | Open invoice balances past their due date today. |
[QuickBooks] Open bills
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Open payables | Sum of open_balance | Today’s snapshot | What the company owes vendors today, from the open bills list. |
Works with other connectors
- QuickBooks + Stripe: Burn multiple. Net burn from the books against net new ARR from billing. Read the guide.
- QuickBooks + Shopify: Books against the store. QuickBooks Revenue is the books; Shopify Net sales is the store’s own figure. Compare them by month to find what has not been booked. Define it as your own metric.
- Harvest + QuickBooks: Client profitability. Fees from the books, hours from Harvest, margin per client. Read the guide.
Questions to ask
- What was our gross margin by month this year?
- Which expense accounts grew the most compared with last year?
- How does net income year to date compare with the same period last year?
- How has our cash balance changed month by month?
- Who owes us the most, and how overdue is it?
- Which customers brought in the most revenue last quarter?
- Which vendors did we spend the most with this year?
Related guides
- Utilization, Realization and Project Margin from Harvest
- QuickBooks AI: Ask Your Books Questions in Plain English
- QuickBooks MCP: Three Ways to Ask Claude About Your Books, and What Each One Costs You
- QuickBooks Profit and Loss: Analyze It by Month
- QuickBooks AR Aging Report: Who Owes You
- Budget vs Actual Report: How to Read It, Calculate Variance and Write the Commentary
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?
How often does the QuickBooks data refresh?
Can I change how a metric is calculated?
Can I combine QuickBooks with other sources?
All connectors: Quiriz connectors. Definitions on this page are generated from the product’s own connector code.