QuickBooks Data Connector User Guide

QuickBooks Data Connector User Guide

Info
QuickBooks is a powerful accounting software that helps businesses manage their finances, track expenses, and generate financial reports. This guide will walk you through the process of integrating QuickBooks with Power My Analytics, allowing you to automate your financial data collection and create insightful reports. By following these steps, you'll be able to streamline your financial reporting and gain valuable insights into your business performance.

Prerequisites

Before you begin, ensure you have:
  1. Login credentials for your Intuit account (email, user ID, or phone number)

Adding QuickBooks as a Data Source

  1. Log in to your Power My Analytics hub.
  2. Navigate to Sources from the main menu.
  3. Click on + Add New Source.
  4. Select QuickBooks from the gallery of available sources.
  5. Sign in to your QuickBooks account to authenticate.


Creating a Data Studio Report

Follow these steps to create a Data Studio report using your QuickBooks data:
  1. After adding QuickBooks as a data source, go to Reports > Data Studio in your PMA hub
  2. Select QuickBooks under Create Data Studio Report (this will open the official Data Studio application in a new window)
  3. Data Studio Configuration:
    1. Step 1:
      1. Name your data source (e.g., "QuickBooks - Your Organization Name")
      2. Choose your Power My Analytics hub from the dropdown
      3. Optional: Leave "Use report template for new reports" checked to use the PMA demo template
      4. Click Next
    2. Step 2:
      1. User Account: Select your QuickBooks account (for real data) or "Sample Account" (for sample data)
      2. Click Next
    3. Step 3:
      1. Choose "Accounting Method" for reports
      2. Optional: Allow "Accounting Method" modification in reports
      3. Choose "Invoice dates based on" for reports
      4. Optional: Allow "Invoice dates based on" modification in reports
      5. Click Connect (top right) to complete the data source configuration
    4. Step 4:
      1. Click Create Report (top right)
For a quick start, use our pre-built QuickBooks demo template.

Key Reports and Fields

QuickBooks data in Power My Analytics offers a wide range of metrics and dimensions. Here are some popular metrics you can use in your reports:
  • Key Metrics: Net Income, Balance, Invoices 91+ Days Late, Invoices 61-90 Days Late, Invoices 31-60 Days Late, Amount
  • Balance Sheet Metrics: Assets, Liabilities
  • Cash Flow Metrics: Operating Activities, Net Income, Total Adjustments
  • Accounts Receivable Metrics: 1-30 days, 31-60 days, 61-90 days, 91+ days, Total, Current
  • Profit and Loss Metrics: Total Income, COGS, Expenses, Other Expense, Total Expenses, Net Income, Amount, Balance, Amount (profit loss detail), Income Amount, Expenses Amount
  • Invoice Metrics: Amount, Open Balance, Qty, Rate
  • Account Metrics: Balance
For a complete list of available QuickBooks fields, refer to the Schema Explorer.

Currency Handling

Power My Analytics automatically uses the home currency from your connected QuickBooks account for all monetary fields (Income Amount, Expenses Amount, COGS Amount, Other Expense Amount, Other Income Amount, and Net Income). For example, if your QuickBooks account is configured in GBP, your reports in Data Studio will display amounts as GBP.

Note for accounts connected before April 14, 2026: If you connected your QuickBooks source before April 14, 2026, your reports may still display amounts in USD even when your QuickBooks account uses another currency. To pick up the correct currency, either:

  • Disconnect and reconnect the QuickBooks source in your PMA hub (Sources > Action > Remove Source, then Sources > + Add new source), or
  • Contact PMA support and we can update the currency on our end.

If you need to display QuickBooks data alongside another currency in the same report, see How to Convert a Currency to Another Currency. Power My Analytics does not perform currency conversion automatically.

Accounting Method (Cash vs. Accrual)

Power My Analytics reports your QuickBooks data on a cash basis by default. If your business keeps its books on an accrual basis (or you simply want to see accrual figures), you can switch the accounting method in the QuickBooks connector configuration in Data Studio.

To choose your accounting method:

  1. In your PMA hub, go to Reports > Data Studio and select QuickBooks to configure a new data source in Data Studio. For an existing data source, choose Edit Connection in Data Studio.
  2. In the connector configuration screen (the same screen where you set your invoice date option), locate the accounting method option.
  3. Select Cash or Accrual.
  4. Click Connect (top right) to apply your selection.

Your selection applies to QuickBooks report-based figures, including Profit and Loss, Balance Sheet, Cash Flow, A/R Aging, and Profit and Loss Detail. When you switch to Accrual, your reported numbers update to match QuickBooks' accrual reporting, so they may differ from the cash-basis figures you saw before.

NotesNote: The default is Cash, so your existing reports will not change unless you switch to Accrual. If you already have a QuickBooks data source, you'll see the accounting method option the next time you open Edit Connection in Data Studio.

Limitations and Important Considerations

  1. Cached Data Requests with Unique Metrics and Date Dimension: When a unique metric is paired with the date dimension, this will result in a request for cached data instead of a request for live data. For more information, please see Understanding Cached and Live Data Requests.
  2. Backfilling: An initial backfill of 24 months will be performed when you first connect your QuickBooks account as a data source.
  3. Nightly Data Sync window: Each nightly Data Sync re-reads the past 59 days of your Profit Loss, Profit Loss Detail, Cash Flow, and Balance tables, and the past 7 days of your Invoice List, A R Aging Summary, and Account List tables. See Late-Entered Transactions and Your Nightly Data Sync below.
  4. A R Aging Summary backfills: The A R Aging Summary table can be backfilled for up to 62 days at a time, because QuickBooks limits how many requests can be made per minute. If you choose a longer range, that one table is skipped while the rest of your backfill completes.

Unavailable Data

  1. Realtime data
  2. Custom fields

Late-Entered Transactions and Your Nightly Data Sync

Bookkeepers often enter or adjust transactions in QuickBooks days or weeks after the transaction date. Here's how Power My Analytics keeps your reports in step with those changes.

What's picked up automatically

  1. Profit Loss, Profit Loss Detail, Cash Flow, and Balance: Every night, your Data Sync re-reads the past 59 days and replaces those figures with QuickBooks' current numbers. Transactions entered, edited, or deleted up to 59 days after the transaction date show up in your reports after the next nightly Data Sync, with no backfill needed.
  2. Invoice List: New and edited invoices are picked up based on when they were created or last updated in QuickBooks, so late invoices are captured automatically.
  3. A R Aging Summary and Account List: These tables record your current figures each night, so there's nothing older to catch up on.

When you need a backfill

If your team changes transactions dated more than 59 days ago (for example, correcting a closed month or running a year-end cleanup), run a backfill covering those dates once the changes are made:
  1. In your PMA hub, go to Sources.
  2. Open the Actions menu (three dots) next to your QuickBooks source.
  3. Select Backfill data range, then choose a range that reaches back to the oldest date you changed (use Custom for a specific range).
The backfill replaces the stored figures for those dates with QuickBooks' current numbers.
Notes
Note: The 59-day window is the same for every QuickBooks source and can't be changed, and backfills can't be scheduled to run automatically. If your books are regularly updated more than 59 days after the transaction date, compare a recent period against QuickBooks from time to time and run a backfill when needed.

"Resolving" after a long backfill

If you backfill more than 62 days, your QuickBooks source may show Resolving afterward. That's expected: the A R Aging Summary table can't be backfilled more than 62 days at a time, but the rest of your backfill completes normally and A R Aging Summary keeps syncing every night. No action is needed.

Tips and Tricks

  1. Avoid Mix and Match errors by checking the Schema Explorer before you create charts and filters. Make sure all metrics added to a chart are contained within the same table space.
  2. If you are using a language other than English, some filters may not work properly. Contact us using the link below for help with those filters.
  3. Pay close attention to Invoices dates based on in the connector configuration screen. Your selection will make a difference in your reports.

Troubleshooting

See our article Basic Troubleshooting for information on issues that may affect the QuickBooks connector.
  1. Transactions missing from older dates: See Late-Entered Transactions and Your Nightly Data Sync above.
  2. Your source shows Reconnect, or you see an "Access Token Expired" error: Reconnect the source from Sources (click Reconnect, or open the Actions menu and select Reconnect source). This happens when the QuickBooks password changes or when QuickBooks ends the authorization for security reasons.