How to build QuickBooks dashboards in Metabase

QuickBooks Online is the accounting system of record for invoices, payments, expenses, and your chart of accounts. Metabase is where you turn that financial data into shared, trustworthy dashboards. Because Metabase reads from SQL databases, the reliable way to connect them is a small pipeline: sync QuickBooks into a database or warehouse on a schedule, then point Metabase at it. This guide walks through that path end to end — including a free option with no paid connector.

How do you connect QuickBooks to Metabase?

Metabase connects to SQL databases and warehouses — not to SaaS APIs directly, and there's no native QuickBooks connector. So connecting QuickBooks to Metabase means one thing: run a small pipeline that copies QuickBooks data into a database on a schedule, then connect Metabase to that database. Once the data lands, the models, metrics, and SQL later in this guide all work.

The good news: this doesn't require a paid tool. Use a managed connector if you want zero maintenance, or a free, code-based sync you host yourself — both are covered in Build the pipeline below, and in more depth in our guide to building a data pipeline.

What can you analyze from QuickBooks data in Metabase?

• Invoiced revenue — by month, product/service, and customer
• Accounts receivable — outstanding balance and aging buckets
• Days sales outstanding (DSO) — how fast you collect
• Cash flow — payments received vs. bills and expenses paid
• Profitability — gross margin by customer and product
• Expenses — spend by category and vendor over time
• Customer concentration — top customers by revenue and balance

Which QuickBooks dashboards should you build in Metabase?

How do you build the QuickBooks → Metabase pipeline?

For dashboards that need history and reliability, land QuickBooks data in a database first, then connect Metabase to that database.

Connector options

• dlt (free, code) — write a Python pipeline against the QuickBooks Online Accounting API for full control.
• QuickBooks Online API (free, raw) — the source of truth; query entities and reports and sync on a schedule.
• Airbyte — has a QuickBooks source covering invoices, payments, customers, items, bills, and more. Free if you self-host the open-source version; paid on Airbyte Cloud.
• Fivetran (paid, managed) — offers a QuickBooks connector with a maintained schema and incremental syncs.

Notes

• Land raw tables first, then build clean models on top.
• QuickBooks amounts are decimals in the major currency unit — don't divide by 100.
• Decide your revenue basis (accrual from invoices vs. cash from payments) and apply it consistently.
• AR aging is an as-of calculation — model unpaid invoice balances against a reference date.

Can you generate a QuickBooks dashboard with AI?

Yes — and once QuickBooks data is synced into a database, this is the fastest way to a strong first draft. First give an AI assistant a way to read your Metabase schema and create questions and dashboards, then paste the prompt below. It builds the dashboard from your database tables and tells the agent to skip metrics the schema can't support instead of faking them.

Two ways to let an assistant query and build in Metabase

Both connect to a Metabase instance that's already pointed at your synced database — the pipeline above moves the data; these just let the assistant read and write Metabase. Pick whichever fits your setup:

Set up the Metabase MCP server

Enable it under Admin → AI → MCP, then point your client at the endpoint:

# Metabase built-in MCP (replace with your instance URL)
claude mcp add --transport http metabase https://your-metabase.example.com/api/metabase-mcp

{
  "mcpServers": {
    "metabase": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://your-metabase.example.com/api/metabase-mcp"]
    }
  }
}

Clients with native remote support can use a "url" field instead of the mcp-remote bridge. Confirm the current endpoint in the Metabase MCP docs.

Set up the Metabase CLI

Install it globally, then authenticate once (the binary is mb):

# Install the CLI (the binary is `mb`)
npm install -g @metabase/cli

# Authenticate once — opens your browser on Metabase v62+, or use an API key
mb auth login --url https://your-metabase.example.com
mb auth status

On Metabase v62+ mb auth login opens your browser; older servers fall back to an API key. A terminal-based assistant can then inspect your schema (mb db schemas, mb table get --include fields) and create content (mb card create, mb dashboard create) against the synced tables.

Prompt: build the QuickBooks Revenue & AR dashboard

With MCP or the CLI connected, paste this into your assistant to generate the dashboard:

Create a polished Metabase dashboard for QuickBooks Online financial analytics
using the available QuickBooks tables in this database.

Goal: Help founders and finance leaders understand invoiced revenue, accounts
receivable, cash flow, and profitability from QuickBooks Online data.

First, inspect the schema and identify the available QuickBooks tables. Do not
assume exact table names. Map the available raw tables into these analytical
concepts where possible: Customers, Invoices, Invoice line items, Payments,
Items (products/services), Bills, Vendors, Expenses/Purchases, and the Chart of
Accounts.

Important:
- Build the dashboard from durable database/warehouse tables.
- QuickBooks amounts are decimals in the major currency unit — do not divide by 100.
- Recognize revenue from invoices (or from the accounting method the business uses);
  do not double-count payments applied to invoices as extra revenue.
- Compute AR as unpaid invoice balances as of a date, and DSO from AR and revenue.
- Report in the company's home currency; if multi-currency, convert with a
  documented rate or caveat the mix.
- Do not claim Metabase connects natively to QuickBooks unless that is explicitly
  true in this environment.

Dashboard title: QuickBooks Revenue & AR Overview

Sections:
1. Executive summary (KPI cards): Invoiced revenue this month; AR balance; DSO;
   Overdue AR; Payments received this month; Gross margin (only if cost data exists).
2. Revenue & sales: Invoiced revenue by month; Revenue by product/service; Revenue
   by customer; Average invoice value.
3. AR & collections: AR aging buckets; DSO by month; Overdue invoices by customer;
   Collection rate.
4. Cash flow: Payments received vs. bills paid by month; Net cash movement;
   Expenses by category; Upcoming bills.
5. Profitability: Gross margin by month; Profit by product/service; Top customers
   by revenue and margin.

Filters: Customer, Product/service, Account, Class/Location (if used), Date range.

Before finalizing, create or recommend reusable Metabase models:
modeled_qbo_customers, modeled_qbo_invoices, modeled_qbo_invoice_lines,
modeled_qbo_payments, and modeled_qbo_ar_aging (an as-of AR balance model).

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric that
cannot be calculated from the available schema. Reconcile totals against
QuickBooks' P&L and AR aging reports. Keep it practical, dense, and
executive-readable. Avoid vanity metrics.

How should you model QuickBooks data in Metabase?

Core tables

Modeling advice

• Build a modeled_qbo_ar_aging model: unpaid invoice balances bucketed by days past due_date as of today.
• Pick one revenue basis (accrual vs. cash) and reuse the definition everywhere.
• Don't add payments to invoiced revenue — payments settle invoices, they aren't additional revenue.
• Map the chart of accounts so expenses roll up into readable categories.
• Reconcile modeled revenue and AR against QuickBooks' own reports.

Which QuickBooks metrics should you track in Metabase?

What SQL powers QuickBooks dashboards in Metabase?

These assume the modeled tables above (PostgreSQL dialect, amounts in major currency units). Adjust identifiers to match your warehouse.

SELECT
  date_trunc('month', i.txn_date) AS month,
  ROUND(SUM(i.total_amt), 2)      AS invoiced_revenue,
  COUNT(*)                         AS invoices,
  ROUND(AVG(i.total_amt), 2)       AS avg_invoice_value
FROM invoices i
WHERE i.txn_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

SELECT
  CASE
    WHEN CURRENT_DATE - i.due_date <= 0  THEN 'Current'
    WHEN CURRENT_DATE - i.due_date <= 30 THEN '1-30'
    WHEN CURRENT_DATE - i.due_date <= 60 THEN '31-60'
    WHEN CURRENT_DATE - i.due_date <= 90 THEN '61-90'
    ELSE '90+'
  END                          AS aging_bucket,
  COUNT(*)                     AS open_invoices,
  ROUND(SUM(i.balance), 2)     AS ar_outstanding
FROM invoices i
WHERE i.balance > 0
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - i.due_date);

SELECT
  c.display_name             AS customer,
  ROUND(SUM(i.total_amt), 2) AS revenue,
  ROUND(SUM(i.balance), 2)   AS open_balance
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.txn_date >= date_trunc('year', CURRENT_DATE)
GROUP BY c.display_name
ORDER BY revenue DESC
LIMIT 20;

What are common mistakes when analyzing QuickBooks in Metabase?

Related

Related analytics

Related integrations

FAQ