Connecting Xero to a destination like BigQuery offers several benefits. You can back up your Xero data and have quicker access to your consolidated records whenever you need them. However, this is not the only reason for connecting Xero to BigQuery. Let’s discuss how you can achieve this connection.
Methods to send your Xero data to Google BigQuery
- Coupler.io – This is a reporting automation and analytics platform that can help power reports with your Xero financial data. Coupler.io pulls data from over 60 sources into data warehouses (BigQuery, PostgreSQL, etc), spreadsheets (Xero to Google Sheets and Microsoft Excel), and BI tools (Power BI, Looker Studio, etc). You can also specify a schedule and automate frequent imports from Xero to BigQuery rather than set up the importer every time.
- Manual export – The native export in Xero allows you to download data as a CSV file. Xero Reports can be exported as PDF, Excel, or Google Sheets. All you need to do is find the section for the desired data and download it. Then, you can proceed to BigQuery to upload the file.
- Custom API integration – Another option is to use a script to extract data from Xero’s API. It’s a complex process that involves getting an access token and preparing a script to pull data from Xero and loading it to Google BigQuery.
How to connect Xero to Google BigQuery with Coupler.io?
With Coupler.io, you can export from two Xero source apps – Xero and Xero Reports. The Xero source provides access to every raw transaction in your account.
To connect Xero to BigQuery, click Proceed in the preconfigured form below.
If you’re new, you’ll need to create a Coupler.io account for free (no credit card is needed). Then, you can log in and integrate your Xero financial data by following the steps below:
Step 1. Collect Xero data
Add your account, select the Xero tenant, then pick a data entity and report type.
Step 2. Preview and transform
Check the data and make any required changes. You can:
- Hide and edit columns
- Filter and sort data
- Add new columns using formulas
You can also use Coupler.io to merge data from multiple Xero accounts or combine it with related information from other business apps such as CRMs, marketing platforms, etc.
Step 3. Load and schedule
Follow the instructions to load data into BigQuery. Go to IAM & Admin in your BigQuery, create a service account, and create a json key. Once the file has been saved to your device, you’ll return to the Coupler.io importer and upload it. Then, specify a new or existing dataset name and a table name to pull your data into. You may allow it to autodetect table schema or you can enter a preferred table schema to use.
The Set up a schedule button allows you to create a custom schedule for automatic data imports. First, you need to turn on Automatic data refresh. Then choose the interval, days of the week, specific time, and the timezone.
Make sure to hit Save and Run to send data from Xero to BigQuery. Congrats, your finance function automation is enabled.
What data can you export from Xero?
These are the data types you can export from the Xero source app:
| Data entity | Description |
|---|---|
| Accounts | Organization |
| Bank Transactions | All bank transactions such as debits, credits, and transfers |
| Bank transfers | Details of fund transfers between different bank accounts |
| Branding themes | Customized templates for email and document designs |
| Contact groups | Groups for categorizing different contacts |
| Contacts | All contacts and their details |
| Credit Notes | Documents issued to reduce any amounts owed by customers |
| Currencies | List of all currencies used in financial transactions |
| Employees | Information about those employed by the organization |
| Expense Claims | Records of employee expense claims, and the specific details |
| Invoices | All invoices issued to customers |
| Items | List of products and services available |
| Journals | A summary of all entries and transactions |
| Linked Transactions | Connected transactions, e.g. bills and orders |
| Manual Journals | Journal entries created manually |
| Organisation | Profile and details about the company |
| Payments | Record of all payments made or received |
| Prepayments | Payments made in advance of receiving any goods and services |
| Purchase orders | Orders placed for goods bought |
| Receipts | Records of money gotten from customers |
| Repeating invoices | Invoices sent to customers on a scheduled basis |
| Tax rates | All tax rates applied to various transactions |
| Tracking categories | Categories used to track and analyze financial performance |
| Users | All account users with their roles and permissions |
Can you automate exports of reports from Xero to BigQuery using Coupler.io?
Xero reports contain aggregated data from your account e.g. account-level summaries and monthly or bi-weekly totals. Coupler.io enables you to export these separately. For this, click Proceed in the preset form below:
Then connect your Xero account, choose the report type, transform, and load into the destination. The setup is very similar to the one described in the previous section.
Here are the report types to pick from:
| Report type | Description |
|---|---|
| Aged Payables by Contact | A snapshot of your financial standing including, assets, liabilities, and equity at a specific date |
| Aged Receivables by Contact | Overdue customer invoices and the time they have been pending |
| Balance Sheet | A snapshot of your financial standing including, assets, liabilities and equity at a specific date |
| Bank Statement | Records of bank transactions and their matching Xero entries |
| Bank Summary | An overview of bank balances and activity over a selected period |
| Budget Summary | Displays all budgets in your Xero account |
| Executive Summary | All key financial metrics, e.g. income, expenses, balance, profit, cash flow, etc. |
| Profit and Loss | A comprehensive report of income, expenses, and profit |
| Trial Balance | Summary of account balances to ensure total debits equal total credits |
Manually export Xero to BigQuery
You can download Xero data and reports from your account as CSV or Google Sheets files and then upload them to BigQuery. Check out the steps below:
Step 1: Export Xero data
- Navigate to the preferred category e.g. Invoices.
- Click Export to download your data.
It will save the CSV file to your device.
How to export Xero Reports
- Navigate to Accounting > Reports.
- Select the exact report you need e.g. Account summary.
- Choose the account, date range, and other necessary options.
Make sure to click Update to save and preview your report.
- At the bottom-right of the page, you can see the Export button.
To further load data to BigQuery, choose either Excel or Google Sheets.
Step 2: Upload data to BigQuery
- Add a new data source in BigQuery using the plus symbol.
- A sidebar will open up. Select the Local file source.
- Upload the source file (CSV or XLSX), specify the destination, and add other settings.
When you’re done, click Create table. This will load your data into the dataset and table.
What do you need to perform the custom integration with API?
If you have solid technical skills and access to the Xero API, you can make a Xero to Google BigQuery data integration. First, you’ll generate the endpoint and the access token. The next step is to use a custom script in Google Apps Script or another programming language to extract data from your Xero account. Here are the steps to do this:
The Xero API
- Navigate to developer.xero.com and log in with your Xero details.
You need to create a New app to proceed.
- Go to Explorer and click Connect your Xero organisation.
Make sure to Allow Access to the organization.
- Select the right API, Endpoint, and Operation to generate the link.
The Xero Accounting API has endpoints for all Xero data and one for Reports. If you choose a data entity, the next step is to select the operation, e.g. Get Purchase Orders. As for the Reports endpoint, you’ll pick the report type under Operation. E.g. Get Report Bank Summary. Your endpoint URL will be generated, and you can copy it immediately.
Access token
- Go to My Apps > Configuration to see your connection info.
This shows the Redirect URL, Client id, and Client secret.
- Go to the Xero Authorization server using a URL such as:
https://login.xero.com/identity/connect/authorize?response_type=code&client_id=YOURCLIENTID&redirect_uri=YOURREDIRECTURI&scope=openid profile email accounting.transactions&state=123
Replace the values for client id, redirect uri, scope, and state (optional). The Xero server will redirect back and add a temporary code to the redirect uri.
- Make a POST request to Xero’s token endpoint:
POST https://identity.xero.com/connect/token
The header:
authorization: "Basic " + base64encode(client_id + ":" + client_secret) Content-Type: application/x-www-form-urlencoded
Request body:
grant_type=authorization_code &code=xxxxxx &redirect_uri=https://myapp.com/redirect
Replace with values for client id, client secret, and code.
The response will contain an access token, expiry time, and a refresh token. You’ll add the access token to the authorization header when making the API request. The token expires after 30 minutes, but the refresh token will help you get a new one. Check out the Xero Authorization Code Flow for a detailed breakdown of the entire process.
Once you have the access token, you can create a script to extract data from the Xero API and send it directly to BigQuery.
Bonus step: Use dashboard templates for quick Xero analytics
If you want to turn your Xero financial data into insights, you’ll need to create a report or dashboard in a spreadsheet app or a BI platform. Moreover, creating reports from scratch is unnecessary since there are ready-to-use report templates. Here are some examples of reports designed in Looker Studio and Google Sheets that can help analyze Xero data.
Financial dashboard for Xero
The Xero financial dashboard makes it easy to monitor your company’s financial health by tracking revenue, expenses, cash flow, and balance sheet insights in real-time. It helps small business owners, accountants, and finance teams analyze key financial trends without manually sorting through spreadsheets. With clear visual reports, you can assess profitability, manage liquidity, and make data-driven financial decisions easily.
Here are the insights to expect:
- Revenue, expenses, and net income tracking for last month, month-to-date, and year-to-date
- Cash flow trends to monitor liquidity and financial stability
- Balance sheet overview with assets, liabilities, and equity insights
- Profit and loss comparison to understand spending patterns
- Net cash changes to identify financial risks and opportunities
You can use the dashboard in Coupler.io, which offers the AI insights feature designed to help you quickly make sense of your data. It’s also available as a template for third-party tools. In this case, follow the instructions in the Readme tab to connect your Xero account and automatically import data into Looker Studio or Google Sheets. The Coupler.io importer keeps your dashboard updated with scheduled refreshes, ensuring real-time financial tracking.
Xero financial dashboard
Get comprehensive financial insights from your Xero account with key performance metrics, balance sheet analysis, and cash flow projections. Quickly assess your business health and make informed financial decisions.
Xero financial dashboard in Looker Studio
Analyze your financial performance with a unified dashboard featuring key reports from Xero like cash flow, profit and loss, balance sheet, and others.
Xero financial dashboard in Google Sheets
Get instant visibility into your company’s financial health through key performance reports from Xero such as cash flow, income statement, and balance sheet.
Revenue dashboard for Xero
The revenue dashboard for Xero focuses on your business’s revenue, income, and expenses. With a detailed breakdown of financial health, you can analyze revenue patterns over the last 12 months and identify seasonal trends or unexpected fluctuations. It also highlights your top-performing customers and products, so that you can understand where the majority of your income is coming from.
Here are the insights to expect:
- Monthly revenue trends and fluctuations
- Contribution of top customers to total revenue
- Performance of key products by revenue generated
- Profit and loss summary to evaluate net earnings
- Detailed view of revenue sources for better financial planning
The dashboard is available in Coupler.io and as a template in Looker Studio and Google Sheets. Follow the instructions on the Readme tab to connect your Xero account using the built-in connector and populate the dashboard with your data.
Revenue dashboard for Xero
Analyze revenue streams, customer spending patterns, and product performance with data automatically synced from Xero. Gain actionable insights into sales trends, top-performing products, and high-value customers to drive growth strategies.
Revenue dashboard for Xero in Looker Studio
Get an overview of your profit and loss, products, and customers through a set of reports collected from Xero.
Revenue dashboard for Xero in Google Sheets
Track your revenue, costs, and customer insights with a comprehensive dashboard connected to your Xero account.
Accounts receivable dashboard
This dashboard offers a detailed analysis of accounts receivable turnover and provides a clear view of invoices and unpaid client balances. It includes a breakdown of paid, unpaid, and overdue invoices to monitor patterns in payment behaviors. It helps you stay on top of your cash flow and make crucial decisions to improve business revenue.
Here are the insights to expect:
- Total unpaid invoices
- Total overdue balance
- Aging analysis of overdue invoices
- Top customers with outstanding invoices
- Accounts Receivable summary
- Invoices paid by customer
- Complete list of customer invoices
Follow the instructions in the Readme to copy the dashboard and connect your Xero account. The Coupler.io importer will automatically update the report with your data. The template is available in Google Sheets and Looker Studio.
Accounts receivable dashboard for Xero in Looker Studio
See which customers accumulate unpaid and overdue invoices, analyze AR aging, and track changes over time.
Accounts receivable dashboard for Xero in Google Sheets
Track outstanding customer payments, monitor aging receivables, and analyze the correlation between paid and unpaid invoices to improve cash flow management.
Accounts payable dashboard
This dashboard ensures that you keep track of all the bills received from vendors and suppliers. It allows you to view the total amount due and provides details of unpaid bills and overdue bills. You can review paid bills and payment history over a period. It also shows a list of vendor bills so that you can categorize and determine the order of settlement.
Here are the insights to expect:
- Total amount due
- Total overdue balance
- Aging of overdue bills by days
- Top 10 unpaid bills by vendor
- Accounts payable aging (summary & unpaid bills)
- Current month bank summary
- Detailed summary of vendor bills
On the Readme, you’ll find instructions to copy and connect Xero to the Google Sheets or Looker Studio template. All you have to do is set up the importer and enable automatic data refresh.
Accounts payable dashboard for Xero in Looker Studio
Track outstanding, overdue, and paid bills to optimize payment schedules, manage vendor relationships, and ensure smooth cash flow.
Accounts payable dashboard for Xero in Google Sheets
Monitor vendor payment deadlines, track aging bills, and analyze payment history to optimize cash flow management and maintain healthy supplier relationships.
What method is best for the Xero to BigQuery data integration?
One major purpose of a data warehouse like BigQuery is to store large datasets from various sources in one central location. It allows you to retrieve data anytime, run complex SQL queries, compare historical data, and connect to BI platforms for comprehensive analysis.
The manual method is suitable for one-time imports and for sharing data with others. It’s not convenient for report building because you’d need to repeat the workflow every time.
If you’re quite technical or you have access to IT staff, you may use the custom integration with API to connect Xero to BigQuery.
The best option is to use a no-code tool like Coupler.io to pull data so you can create reports from scratch or use any of the report templates. In addition to Xero, there are several other connectors that can help build more data pipelines. Coupler.io is a platform that offers everything a small business needs for effective data analysis and reporting.
Automate Xero reporting with Coupler.io
Get started for free