Build Data Studio dashboards from QuickBooks cash flow
QuickBooks reports are snapshots, and a dashboard needs them in time. Load the Cash Flow, Profit and Loss and Balance Sheet reports into your data warehouse, keep every period, and chart them in Data Studio.
%20scale(1.25)%22%20fill%3D%22%23fff%22%20d%3D%22M12%200A12%2012%200%200%200%200%2012a12%2012%200%200%200%2012%2012%2012%2012%200%200%200%2012-12A12%2012%200%200%200%2012%200zm.642%204.1335c.9554%200%201.7296.776%201.7296%201.7332v9.0667h1.6c1.614%200%202.9275-1.3156%202.9275-2.933%200-1.6173-1.3136-2.9333-2.9276-2.9333h-.6654V7.3334h.6654c2.5722%200%204.6577%202.0897%204.6577%204.667%200%202.5774-2.0855%204.6666-4.6577%204.6666H12.642zM7.9837%207.333h3.3291v12.533c-.9555%200-1.73-.7759-1.73-1.7332V9.0662H7.9837c-1.6146%200-2.9277%201.316-2.9277%202.9334%200%201.6175%201.3131%202.9333%202.9277%202.9333h.6654v1.7332h-.6654c-2.5725%200-4.6577-2.0892-4.6577-4.6665%200-2.5771%202.0852-4.6666%204.6577-4.6666Z%22%2F%3E%0A%3C%2Fsvg%3E%0A)
QuickBooksConnectedA/P Aging Summar…A/R Aging Summar…AccountsBalance Sheet re…
Data StudioRealmReportReportDaily 09:00QuickBooks Endpoints
26 QuickBooks endpoints you can import. Each lands in its own table in your data warehouse.
A/P Aging Summary report
A/P Aging Summary as of report_date (window end): one row per vendor with aging buckets.
11 fieldsTime seriesA/R Aging Summary report
A/R Aging Summary as of report_date (window end): one row per customer with Current, 1-30, 31-60, 61-90, 91+ and Total columns.
11 fieldsTime seriesAccounts
Chart of accounts, active and inactive.
20 fieldsBalance Sheet report
Balance Sheet as of the run window end (Assets, Liabilities, Equity by account).
12 fieldsTime seriesBill payments
Payments against vendor bills; joins to Bill through Line[].LinkedTxn.
23 fieldsBills
Vendor bills (payables).
25 fieldsCash Flow report
Statement of Cash Flows for the run window.
12 fieldsTime seriesCompany info
One row per company: names, country, fiscal-year start.
15 fieldsCredit memos
Credits issued to customers (reduce receivables; negative revenue in reconciliations).
23 fieldsCustomers
Customers, including inactive ones and sub-customers, with open balance and a flattened billing address.
28 fieldsDeposits
Bank deposits, including the ones that group undeposited customer payments.
21 fieldsGeneral Ledger Detail report
General Ledger: every posting line per account for the run window, with opening balance and totals.
12 fieldsTime seriesInvoice changes (CDC, incl. deletes)
Change data capture for Invoice: every invoice created, updated or deleted since the run window start, full payload, deleted ones with status = Deleted.
9 fieldsTime seriesInvoices
Invoices with header amounts, customer and Line[] as JSON (SalesItemLineDetail.ItemRef, Qty, UnitPrice, Amount).
36 fieldsItems
Products and services with price, purchase cost and the three account links.
25 fieldsJournal entries
Manual and adjusting journal entries; the postings the transaction tables miss.
15 fieldsPayments
Customer payments.
21 fieldsPreferences
One row per company: home currency, multi-currency flag, fiscal-year and tracking settings.
17 fieldsProfit and Loss report
Profit and Loss per run window, one column per month (summarize_column_by=Month), rows nested by account group.
12 fieldsTime seriesPurchases
Expenses paid directly (cash, check, card) without a bill.
26 fieldsRefund receipts
Money refunded to customers.
26 fieldsSales by Customer report
Sales by Customer Summary for the run window.
12 fieldsTime seriesSales receipts
Sales paid at the time of sale (no receivable).
27 fieldsTransfers
Money moved between balance-sheet accounts.
16 fieldsVendor Expenses report
Expenses by Vendor for the run window.
12 fieldsTime seriesVendors
Vendors, including inactive ones, with open payable balance.
19 fields
Why QuickBooks reports do not go straight to a Data Studio dashboard
A dashboard on the QuickBooks account shows one report, run for one period, at the moment you look. To chart cash flow over time every snapshot has to be kept, and to read it beside the result and the balance sheet the three have to meet in a warehouse.
- A QuickBooks report is rebuilt each time you run it, so earlier runs are not kept
- The data analyst joins the three statements on the company and period
- Dashboard viewers read one definition of cash flow, not a screenshot per period
QuickBooksReport snapshotsData Studio
Data warehouseJoined by idStep 1: Load QuickBooks into your data warehouse first
Create a Data Mart with QuickBooks as the source and choose where the data lands. The QuickBooks connector is a custom connector built in OWOX Data Marts, and the data goes into any popular data warehouse.
- Three of the 26 endpoints are statements; each run keeps a dated snapshot
- Authorize QuickBooks Online with OAuth and give the company (realm) id
- Open the QuickBooks connector for your data warehouse below
QuickBooksData MartGoogle BigQuery
AWS Athena
Snowflake
AWS Redshift
Databricksquickbooks.report_cash_flowStep 2: Link QuickBooks financial statements to sales and ad spend
In your data warehouse the model joins Cash Flow report, Profit and Loss and Balance Sheet on realm_id, so cash flow can be read against the revenue and the spend that drove it.
- The three statements share the company and the period
- report_basis says Cash or Accrual, so views do not mix them
- Revenue and spend tables from other sources join by date
Step 3: Feed Data Studio dashboards with QuickBooks data
Add Data Studio as a destination of the QuickBooks Data Mart and build the on it. Fields such as start_period, end_period, report_basis and the nested report_rows come from your data warehouse.
- Cash by period reads the Data Mart, not the QuickBooks API
- Turn on Available in Data Studio for the published Data Mart, then add the OWOX connector in Data Studio
Data StudioCash Flow report
Google SheetsSame Data Mart
ClaudeSame Data MartStep 4: Use the same QuickBooks Data Mart in a sheet or an assistant
The Cash Flow Data Mart is not locked to one dashboard. Open the same snapshots in a spreadsheet or ask about them in an AI assistant, with the same definitions.
- The controller compares periods in a spreadsheet without a new export
- An assistant reads the same Data Mart, so cash flow agrees with the dashboard
Data StudioDashboardGoogle SheetsSame Data MartClaudeSame Data MartStep 5: Reuse QuickBooks cash flow snapshots
The same Data Mart works in OWOX, where your assistant for data can read it. Ask about any report, then get the result in Google Sheets or Excel in one click.
- Ask in which period cash flow turned negative, then export the answer
- report_basis is kept with every snapshot
Google SheetsOpen
ExcelOpen
- 0.41.0Dates from Google Sheets arrive as dates, not text
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.40.0Connecting a plan from Google Sheets takes fewer guesses
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.39.0Long Facebook Ads imports no longer fail on a corrupted response
Why OWOX for QuickBooks in Data Studio
A dashboard on the QuickBooks account shows what QuickBooks reports today. OWOX keeps every snapshot in your data warehouse so Data Studio can show how it moved.
- You own the data. QuickBooks lands in your own data warehouse, in tables you control.
- History you can chart. Each run stores the report with its period and basis, so trends exist after the fact.
- Three statements, one model. Cash Flow, Profit and Loss and Balance Sheet join on the company and the period.
- One place to fix. When Intuit changes its API, you fix one connector, not a pile of scripts.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.