How do you analyze FreshBooks data in Metabase?

{
  "mcpServers": {
    "freshbooks": {
      "command": "npx",
      "args": ["@goodsamsoftware/freshbooks-mcp"],
      "env": {
        "FRESHBOOKS_CLIENT_ID": "your-client-id",
        "FRESHBOOKS_CLIENT_SECRET": "your-client-secret",
        "FRESHBOOKS_REDIRECT_URI": "https://freshbooks.goodsamsoftware.com/callback"
      }
    }
  }
}

# Install the Metabase CLI
npm install -g @metabase/cli

# Log in (opens your browser; requires Metabase v62+)
mb auth login --url https://your-metabase.example.com

# Load a scoped FreshBooks export - creates a table and a model
mb upload csv --file freshbooks-export.csv --collection "Finance & Accounting"

# Refresh the same table later
mb upload replace <table-id> --file freshbooks-export.csv

Create a polished Metabase dashboard for FreshBooks finance analytics.
Work end to end: find existing modeled data first; if none exists, load a minimal
read-only snapshot or describe the warehouse sync required.

Goal: Connect FreshBooks to Metabase for invoice collections, client revenue, expenses, profitability, time tracking, and cash-flow dashboards.

Inspect before querying:
- Inspect actual tables, column names, currencies, status values, and timestamps.
- Do not assume the source schema matches this guide exactly.
- Reconcile source totals before publishing any finance KPI.

Important:
- Do not claim Metabase connects natively to FreshBooks; it reads a
  supported database or CLI-uploaded tables.
- Keep native amount, native currency, and reporting-currency amount separate.
- Distinguish authorization, posting, payment, settlement, and reconciliation
  states where the source exposes them.
- Use stable business keys and apply updates/deletes during incremental syncs.
- Exclude account numbers, personal data, and secrets from broad dashboards.
- Only build cards whose required fields and history are actually present.

Dashboard sections:
1. Finance overview: Invoiced vs. collected; Open AR by age; Revenue by client; Expense trend.
2. Operations: Invoices due this week; Unbilled time; Late-paying clients; Projects over budget.
3. Controls and reconciliation: Payment-to-invoice exceptions; Missing expense categories; Duplicate expenses; Unbilled completed work.
4. Planning and trends: Collection forecast; Client revenue concentration; Project margin trend; Expense budget variance.

Suggested models: modeled_freshbooks_invoices, modeled_freshbooks_payments, modeled_freshbooks_expenses, modeled_freshbooks_project_margin.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. State refresh time, reporting
currency, accounting basis, and any reconciliation caveats.

SELECT
  date_trunc('month', issue_date) AS invoice_month,
  SUM(invoice_total) AS invoiced,
  SUM(amount_paid) AS collected,
  ROUND(100.0 * SUM(amount_paid) / NULLIF(SUM(invoice_total), 0), 1) AS collection_rate_pct
FROM modeled_freshbooks_invoices
GROUP BY 1
ORDER BY 1;

Surface approved billable work that has not reached a customer invoice.

SELECT
  project_name,
  client_name,
  SUM(billable_hours * billing_rate) AS unbilled_value
FROM modeled_freshbooks_time_entries
WHERE is_billable = TRUE
  AND invoice_id IS NULL
  AND approval_status = 'approved'
GROUP BY 1, 2
ORDER BY unbilled_value DESC;