# How Rittman Analytics Does Financial Analytics using Looker, Fivetran and Google BigQuery URL: https://rittmananalytics.com/blog/2025/09/07/how-rittman-analytics-does-financial-analytics-using-looker-fivetran-and-google-bigquery Description: As you’d expect from a data analytics consultancy that runs its business on Google Cloud, Google BigQuery and Looker, we use these services to provide the financial reports and dashboards… Author: Mark Rittman Published: 2025-09-07T22:37:02+01:00 Modified: 2025-09-07T22:37:02+01:00 --- ![How Rittman Analytics Does Financial Analytics using Looker, Fivetran and Google BigQuery](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fdtdtemp8.wpcomstaging.com%2Fwp-content%2Fuploads%2F2025%2F09%2F0.1669793402786709.png%3Ffit%3D5116%252C3680%26ssl%3D1) # As you’d expect from a data analytics consultancy that runs its business on [Google Cloud, Google BigQuery and Looker](https://www.rittmananalytics.com/partners/googlecloud), we use these services to provide the financial reports and dashboards our finance team needs to understand the trends and direction in our revenue, costs and profitability. In this blog I’ll explain how we source and integrate the data we need from [Xero](https://www.xero.com/uk/), our accounting system and other finance sources, how we’ve built a Looker dashboard for financial analytics and how we’re opening-up this trusted finance data more widely in the company via Google Sheets integration and Looker’s new Conversational Analytics feature. ## Sourcing and Transforming our Xero Finance Data Using [Fivetran](https://www.rittmananalytics.com/partners/fivetran) we extract our accounting data from Xero into Google BigQuery and then transform it, along with timesheet and invoice data and our budgets held in Google Sheets into a finance data mart within our corporate data warehouse. A key part of the logic for these transformations is an adapted version of the [Fivetran Xero dbt package](https://hub.getdbt.com/fivetran/xero/latest/) that generates a `profit_and_loss_report_fact` table that is the source of data for most of our finance reporting each month. Press enter or click to view image in full size Looking at the contents of this [dbt model](https://github.com/fivetran/dbt_xero/blob/main/models/xero__profit_and_loss_report.sql) and table you can see that while its structure suits the needs of a P&L report, if we wanted to create a calculation where, for example, `gross_profit` = `Revenue — Cost of Delivery` we’d have to use quite complicated SQL or Looker table calculations in-order to turn this `net_amount` measure into measures useful for reporting or calculations. Press enter or click to view image in full size So to make this table’s output easier to work with in Looker, we then use LookML [measure filters](https://cloud.google.com/looker/docs/reference/param-field-filters) to create derivations of this `amount` measure for top-level P&L category-level measures such as `Revenue`, `Cost of Delivery`, `Overheads` and `Taxation`. measure: revenue { group\_label: "P&L Actuals" type: sum description: "Sales revenue from consulting projects and other services" value\_format\_name: gbp sql: ${TABLE}.amount;; filters: \[account\_report\_category: "Revenue"\] } In addition we also then use the new [period-over-period measure type](https://cloud.google.com/looker/docs/period-over-period) in LookML to create comparison measures for month-ago, quarter-ago and so on. measure: revenue\_prior\_month { group\_label: "P&L Actuals Prior Month" type: period\_over\_period label: "Revenue Prior Month" description: "Sales Revenue prior month (i.e. previous month)" based\_on: revenue based\_on\_time: period\_month period: month value\_format\_name: gbp sql: ${amount\_base} ;; } Together with our budget data we now have a Looker explore that can be used to provide reporting on the financial results of the company. In the screenshot below the P&L Base Measures folder contains the base-level `Amount` and `Budget` measures we’ll need for creating our P&L report later on, and the derived `Revenue` and other measures will be used when reporting on revenue trends or for comparison or calculations we need to use elsewhere in our dashboard. Press enter or click to view image in full size ## Our Financial Analytics Looker Dashboard The screenshot below shows (with mocked-up numbers) the Financial Analytics dashboard we’re currently using at Rittman Analytics. The key visualisations and reports are highlighted and explained after the screenshot. Press enter or click to view image in full size ### 1\. Headline financial KPIs These are the top-level set of finance KPIs that communicate our YTD performance — “are we currently on-track to hit our revenue number, and are we doing so with a decent net margin?” and are calculated using the `Revenue`, `Cost of Delivery` and other measures we derived from the `net_amount` P&L report measure previously, with additional calculations in the explore queries to calculate % to target as provided by our budgets data source. Press enter or click to view image in full size ### 2\. Variance against budget for our top-level P&L report categories This table-format visualization provides actuals against budget for each top-level (`Revenue`, `Cost of Delivery`etc) P&L report category with the % variance against budget that each actual number represents —answering questions such as *“are our costs increasingly linearly with revenue, at a slower rate than revenue* (which is good) *or at a faster rate* (which is bad)?” Press enter or click to view image in full size ### 3\. Profit & Loss Report This is the report within the dashboard and tells us, by month and YTD, what our `Revenue`, direct (`Cost of Delivery`) and indirect (`Overheads`) costs were along with our provision for corporation tax ( `Taxation`), shareholder distributions ( `Dividends`) and our retained earnings that were left after all these costs (`Total` in the report). Press enter or click to view image in full size P&L reports (or income statements in the US and some other countries) have a particular finance report layout that accountants expect to see, and to create ours we use: - The [**Report Table** custom visualization](https://marketplace.looker.com/marketplace/detail/viz-report_table), which provides additional controls that let’s us specify the column for which we create subtotals in the report, how we display negative values and so on - a **Pivot** of the month dimension in the report together with **Row Totals** and **Totals**, in order to give us one column per month with YTD and retained earnings totals at the bottom of each month column Press enter or click to view image in full size - Sorting of the `account_report_category` and other P&L account dimensions by corresponding `_order`hidden dimensions, so that our report categories such as Revenue, Cost of Delivery, Overheads, and Retained Earnings always appear in the correct order in the report. dimension: account\_report\_category { label: "Account Category" hidden: no order\_by\_field: account\_report\_category\_order type: string description: "Top-level P&L account grouping; Revenue, Cost of Delivery, Overheads, Dividends and Retained Earnings" sql: ${TABLE}.account\_report\_category ;; } ### 4\. Revenue Pipeline & Cumulative Revenue YTD Apart from the detailed P&L report, we also include a few summary visualisations that help us understand trends in our finance numbers. For example, the Revenue Pipeline visualization (as explained in more detail in [this earlier blog](https://rittmananalytics.com/blog/2024/9/23/how-rittman-analytics-analyzes-its-historical-revenue-amp-sales-pipeline-using-google-bigquery-fivetran-stitch-andnbsplooker)) tells me how much of a backlog of agreed and in-negotiation project we had at the end of each month, a good indication of whether we should start hiring or whether in-fact we need to step-up our sales and marketing activity. Press enter or click to view image in full size The cumulative revenue visualization plots this year’s revenue growth curve against previous years, showing me visually whether we’re having a good or bad year compared to the last five. ### **5\. Monthly Average, Min and Max Cash Balance** [*“Revenue is vanity but cash is king”*](https://www.doholis-lambert.com/blog/revenue-is-vanity-profit-is-sanity-but-cash-is-king) goes the old saying in business, and its easy to think that billing = cash in bank whereas it’s often 30, 60 or more days between completing a piece of client work and getting money in the bank, and all the while your team need paying in the meantime. Press enter or click to view image in full size By monitoring the average, highest and lowest balance in our company bank account each month and keeping an eye on when the average might be trending down rather than up, and by understanding the working capital we need to make sure is available in our account each month as bills get paid and money comes in, we can avoid the awkward situation where everyone’s been busy but there’s no money to pay the quarterly VAT bill. ### 6\. Forecast Revenue for the next six months Predicting what our revenue will look-like over the coming months is important as we’ll need to base decisions over hiring, expansion or cutting costs over those numbers. Contracts running over six or twelve months make this task easier but the nature of consulting work is shorter engagements that may not even be in our pipeline at this point. Press enter or click to view image in full size We covered the [forecasting ability within Looker](https://rittmananalytics.com/adding-forecasting-to-your-looker-report-and-dashboards-833670e569d9) on the blog a few years ago and while [this type of forecasting approach](https://cloud.google.com/looker/docs/forecasting-in-visualizations#arima_algorithm) is never going to know about a big contract that ends this month or new ones just about to be signed, it takes a statistical approach that isn’t too far-off the mark as long as you provide it with enough historical data and enable the seasonality feature if your revenue is, well seasonal. For example, the forecast in the screenshot above predicts a fairly-spiky growth in sales when we include two years worth of actual data and ask Looker to forecast forward a further six months, whereas the screenshot below takes three years of data and factors-in seasonality. Press enter or click to view image in full size Which type of forecast is correct for your business will come down to how much your past performance is an indicator of your future, whether your sales are seasonal and so on. In-practice we combine this type of forecast with our schedule of future invoicing from booked work so at least the immediate future is more grounded in facts, not statistical forecasting. ## Google Sheets, Conversational Analytics and Other Routes to Consuming Looker Financials Data The big advantage of putting all your financial analytics data sources and reporting into Looker is the single version of the truth it gives your team; revenue is defined once and consistently across your organisation and you have point-and-click reporting tools that make it easy to find and share insights from your data. ### Looker data as an input into your spreadsheet models But in some cases you’ll have a need to combine this trusted Looker data with spreadsheet models business users’ own and control, and so [Connected Sheets for Looker](https://support.google.com/docs/answer/12822123?hl=en-GB) brings that data, refreshable on-demand or scheduled is a great way to ensure that at least the starting data in a user’s spreadsheet model is correct. Press enter or click to view image in full size ### Providing a Conversational UI for your trusted financial data The appeal of [Looker’s new Conversational Analytics feature](https://www.rittmananalytics.com/blog/2025/5/11/introducing-conversational-analytics-data-agents-and-code-interpreter-in-looker-and-lookernbspstudio) is that it opens-up the trusted data in your Looker semantic model to users who don’t have the time or capability to use Looker’s point-and-click data analysis tools. The challenge though is providing Conversation Analytics with sufficient context on your business and its way of reporting numbers so that it doesn’t just make this up for itself and provide the wrong results back to the user. In my [previous article on this feature](https://www.rittmananalytics.com/blog/2025/5/11/introducing-conversational-analytics-data-agents-and-code-interpreter-in-looker-and-lookernbspstudio) I explained how I used a LookML export of a dashboard to provide this much-needed context, and for our financials data the approach I’ve used is the same: 1. Create a LookML export of the key dashboard for a dataset, in our case the Financial Analytics dashboard we’ve just gone through, and pass it to Gemini with a prompt to turn it into instructions for a Conversational Analytics Agent 2. Review those instructions and fine-tune them with details about the currency to report in, output formats and so on 3. Paste those instructions into the definition of your Conversational Analytics Agent, where you’ll also select the dataset that the agent will use and the persona it will adopt, and 4. Your responses from the Conversational Analytics agent should then be informed by the knowledge and calculation rules that were derived from the LookML version of your financial analytics dashboard Press enter or click to view image in full size ## Interested? Find Out More! Rittman Analytics is a boutique data analytics consultancy that helps ambitious, digital-native businesses scale-up their approach to data, analytics and [generative AI](https://www.rittmananalytics.com/services/genai). We’re authorised delivery partners for [Google Cloud](https://www.rittmananalytics.com/partners/looker) along with [Oracle](https://www.rittmananalytics.com/partners/oracle), [Segment](https://www.rittmananalytics.com/partners/segment), [Cube](https://www.rittmananalytics.com/partners/cube), [Dagster](https://www.rittmananalytics.com/partners/dagster), [Preset](https://www.rittmananalytics.com/partners/preset), [dbt Labs](https://www.rittmananalytics.com/partners/dbt-labs) and [Fivetran](https://www.rittmananalytics.com/partners/fivetran) and are experts at helping you design an analytics solution that’s right for your organisation’s needs, use-cases and budget and working with you and your data team to successfully implement it. If you’re looking for some help and assistance with your AI initiative or would just like to talk shop and share ideas and thoughts on what’s going on in your organisation and the wider data analytics world, [contact us now](https://calendly.com/markrittman/initial-discovery-call-with-mark-rittman?utm_source=medium.com&utm_medium=blog&utm_campaign=customer_segmentation) to organise a 100%-free, no-obligation call — we’d love to hear from you!