How to Create an Income and Expense Report in Looker

Tracking your business's financial health starts with a clear view of your income against your expenses. While a spreadsheet can get you started, creating a dynamic, real-time income and expense report in a tool like Looker provides a much clearer picture. This guide will walk you through setting up a powerful financial report in Looker to monitor performance, spot trends, and make smarter decisions.

Why Build an Income and Expense Report in Looker?

You might be wondering why you should use a business intelligence tool for what sounds like a simple accounting task. Spreadsheets are familiar, but they have their limits. A report in Looker, on the other hand, offers several key advantages:

• A Single Source of Truth: Looker connects directly to your databases (like QuickBooks, Stripe data piped into a warehouse, or even structured Google Sheets). This means your report always reflects the latest transactions, eliminating the tedious and error-prone process of manually downloading and updating CSV files.

• Dynamic and Interactive Analysis: Instead of a static chart, you get an interactive dashboard. You can filter by date, drill down into specific expense categories, or see which income streams are performing best, all with a few clicks. Stakeholders can answer their own follow-up questions without needing you to create a new report.

• Automated & Shareable: Once built, your report updates automatically. You can schedule reports to be sent to your team or stakeholders daily, weekly, or monthly, ensuring everyone stays informed without any extra work from you.

The goal is to move from a weekly reporting scramble - downloading data, wrestling with pivot tables, and answering questions - to a system where insights are always current and accessible.

Step 1: Preparing Your Financial Data

Before jumping into Looker, the most critical step is ensuring your data is clean, organized, and ready for reporting. Looker is powerful, but it relies on a well-structured data model. If your data is a mess, your report will be, too.

Consolidate Your Data Sources

Your financial data likely lives in multiple places. It could be in:

• Accounting Software: QuickBooks, Xero, FreshBooks, etc.

• Payment Processors: Stripe, Braintree, PayPal.

• Spreadsheets: Google Sheets or Excel files for tracking miscellaneous expenses or specific project budgets.

To use this data in Looker, it needs to be accessible in a single database or data warehouse that Looker can connect to. This often involves setting up a data pipeline (using a tool like Fivetran or Stitch) to pull data from these sources into a central repository like BigQuery, Redshift, or Snowflake.

Structure Your "Transactions" Table

At its core, an income and expense report is based on a list of transactions. Ideally, you will have a single table in your database with a structure similar to this:

• transaction_date (A date or timestamp)

• description (A text description of the transaction)

• amount (The numerical value)

• category (e.g., "Software Subscription," "Client Payment," "Office Supplies")

• transaction_type (A field specifying 'Income' or 'Expense')

Having a clear transaction_type column is absolutely essential. If you don't have one, you can often create it based on logic. For example, amounts from Stripe might always be 'Income,' while transactions categorized as "Salaries" are 'Expense.' Cleaning and categorizing this data upstream is what enables powerful analysis in Looker.

Defining Logic with Your Data Team in LookML

Looker uses a modeling layer called LookML to define your data, metrics, calculations, and relationships. It's the "brain" behind your reports. You'll need to work with someone who understands LookML to ensure your financial data is properly set up. They will need to define:

• Dimensions: These are the fields you can group by, like Transaction Date, Category, or Type.

• Measures: These are aggregate calculations, like a sum or average. You will need a Total Amount to sum up the values of transactions.

A good LookML developer might also create custom measures to make reporting easier, like Total Income and Total Expenses:

Having these pre-built measures simplifies the report-building process for everyone on your team. You won't have to fiddle with complex filters every time, you can just select "Total Income" and "Total Expenses."

Step 2: Building Your Report in an "Explore"

Once your data is modeled, you can start building. In Looker, reports are built within a feature called an "Explore." An Explore is a starting point for a query, giving you access to a specific set of governed data (like your transactions).

1. Create a New Look or Dashboard

You can either create a single report (a "Look") or start building a "Dashboard," which is a collection of several reports or "tiles" in one place. For a comprehensive income and expense tracker, a dashboard is your best bet.

2. Select Your Financial Explore

Navigate to the "Explore" section and choose the Explore built on your financial transactions data. It might be named something like "Transactions," "Financials," or "Ledger."

3. Select Your Dimensions and Measures

With an empty Explore in front of you, you'll see a list of available Dimensions and Measures on the left-hand side. This is where you select the building blocks of your report.

• Select a Time Dimension: In the Dimensions list, find your date field (e.g., Transaction Date). Click on it and choose a time-frame to group by, such as Month or Quarter. This will form the x-axis of your chart.

• Select Your Financial Measures: In the Measures list, click on Total Income and Total Expenses (or the generic Total Amount measure if you don't have custom ones).

Click the Run button. Looker will now query your database and show you a data table with income and expenses summarized for each month.

4. Filter Your Report

Right now, your report is showing all data from all time. You'll want to add a filter to narrow the focus.

• Add a Date Filter: Find the "Filters" section. Select your date dimension (Transaction Date) and choose a time range, like "is in the past 12 complete months." This keeps your dashboard relevant.

• Filtering for Income vs. Expense (If Needed): If you don't have separate Total Income and Total Expenses measures, you'll need two separate queries. For the income visuals, filter for Transaction Type "is Income." For the expenses visuals, filter Transaction Type "is Expense."

5. Visualize Your Data

A table of numbers is useful, but a visualization tells a clearer story. Find the "Visualization" tab and experiment with charts. Here are a few great options:

• Column Chart: Choose the column chart option to plot your Total Income and Total Expenses series side-by-side for each month. This provides an easy comparison of what is coming in versus what is going out.

• Bar Chart: For drilling down into expense categories, a bar chart works well. Select the "Category" dimension and "Total Expenses" measure, and visualize them as a bar chart to see how your spending is allocated.

• Single Value Visualizations: Use these to display key numbers for the selected period, like "Total Income," "Total Expenses," and "Net Profit," in a single, glanceable view.

Once you're happy with a chart, you can click "Save as Look." You can add it to your newly created dashboard.

Step 3: Enhancing Your Dashboard

Now you have the building blocks, you can refine your dashboard to make it even more useful.

1. Add Dashboard-Level Filters

Rather than hard-coding a date filter into each report, you can add a dynamic filter on the dashboard itself. This allows anyone viewing the report to choose their own date range (like "This Quarter" or "Last 90 Days") without editing anything. Another common filter to add is "Category," allowing teams to narrow down to specific expense lines like "Software Spends" or "Marketing Spend."

2. Calculate Net Metrics

Sometimes you want to see metrics that aren't explicitly in your database. "On-the-fly Calculations" allow you to create metrics on the fly without needing to use LookML. A common one is a calculation for net profit:

This simple formula subtracts the "Total Expenses" column from the "Total Income" column to create a "Net Profit" column directly in your report table.

3. Schedule Dashboard Delivery

You don't want to have to log into Looker every day just to check your financials. Set up a schedule to have the dashboard emailed to yourself, and necessary stakeholders on a regular cadence - daily, weekly, or monthly - to keep everyone informed and aligned.

Final Thoughts

Creating an income and expense report in Looker transforms how you monitor business finances. It provides a dynamic, always-current view, replacing the manual, static nature of spreadsheets, and helping you spot trends, manage spending, and ultimately make more informed decisions. The process relies heavily on having clean, organized data and a well-defined LookML model, but once that's in place, building visual reports becomes a straightforward process of selecting dimensions and measures.

While tools like Looker are incredibly powerful, they can be complex and bring a steep learning curve that's a roadblock for many team members. At Graphed, we see a world where every team member is empowered to answer their own questions without needing to become a data expert. Learning LookML or navigating a complex interface isn't required. We built our product so you can connect data sources like QuickBooks and Stripe, then simply ask questions like, "Show me my income versus expenses over time by category for the last 120 days as a line chart." We automate the reporting drudgery that bogs down teams, turning hours of manual work into seconds and freeing up your time to actually act on the insights you uncover.