How to Create an Income and Expense Report in Looker with AI

Tracking your business's income and expenses feels like it should be simple, but it rarely is. Between multiple revenue streams, various subscriptions, and ad spend, your financial picture is scattered. While Looker Studio (formerly Google Data Studio) is a fantastic tool for pulling it all together, the setup can be daunting. This guide will walk you through building a clean, automated income and expense report in Looker, showing you how new AI features can dramatically simplify the process.

Why Bother with an Income and Expense Report?

Before jumping into the setup, it's worth remembering why this report is so valuable. Manually checking bank statements or exporting CSVs from Stripe gives you disconnected numbers, not answers. A proper income and expense report, also called a Profit & Loss (P&L) statement, provides clarity at a glance.

• Measure Profitability: See exactly how much money you're making (or losing) in real-time.

• Spot Trends: Is revenue growing month-over-month? Did a particular service cause a spike in expenses last quarter? A visual report makes trends easy to see.

• Identify Spending Leaks: By categorizing expenses, you can immediately spot where your money is going. That "Miscellaneous Software" category might be larger than you think.

• Make Confident Decisions: A clear financial picture fuels better decisions about hiring, marketing budgets, and pricing.

In short, it’s the financial dashboard for your business, turning raw data into an actionable roadmap.

Step 1: Get Your Financial Data in Order

Any great report starts with well-organized data. Looker Studio is powerful, but it can’t make sense of a chaotic spreadsheet. Before you create a single chart, you need to structure your datasource cleanly.

The Golden Rule: Tidy Data

Your data needs to be structured in a simple, consistent format. This usually means a table where:

• Each row represents a single transaction (one piece of income or one expense).

• Each column represents a piece of information about that transaction (like the date, amount, or category).

For most small businesses, a Google Sheet is the perfect place to start. It connects directly to Looker Studio for a seamless, free integration.

Example: Structuring Your Data in a Google Sheet

Create a Google Sheet with headers like these. This simple format is all you need to build a powerful report.

Your Google Sheet structure might look like this:

The magic key here is the "Type" column. Having a single column to differentiate between "Income" and "Expense" makes creating calculations in Looker Studio much, much easier down the road.

Connecting to Other Tools

Of course, you might not be manually putting data into a Google Sheet. You might have income data in Stripe, expense data in QuickBooks, and ad-spend data in Facebook Ads. Getting all this data into one place for Looker is the trickiest part of the process and often where people get stuck. You typically need third-party connectors or a more advanced data pipeline to aggregate this information before Looker can even see it.

Step 2: Building Your Report in Looker Studio (The Manual Way)

Once you have a clean data source (like the Google Sheet above), you can start building. Here's a traditional, click-based workflow to give you an appreciation for the process.

1. Connect Your Data Source

In Looker Studio, create a new blank report. You'll be prompted to connect to data. Select "Google Sheets," find the file you created, and click "Add." Just like that, your data is linked.

2. Add Your High-Level Numbers (Scorecards)

Scorecards are perfect for displaying single, important metrics.

• Go to Insert > Scorecard and place it on your report.

• By default, it will probably show "Record Count." In the properties panel on the right, change the Metric to "Amount."

• This now shows your total amount, both income and expenses. To fix this, create a filter. Under the Setup tab, click "Add a filter," name it "Income Only," and set the rule to Include > Type > Equal to (=) > Income.

• Now, copy this scorecard, paste it, and change its filter to only show expenses (Include > Type > Equal to (=) > Expense). Label them clearly as "Total Revenue" and "Total Expenses."

3. Calculate Your Profit with a Calculated Field

Now for the most important number: Net Profit. This requires creating what's called a "calculated field."

Go to your data source settings (Resource > Manage added data sources > Edit) and click "Add a Field." Give your new field a name, like "Net Profit," and enter this formula:

This little bit of code tells Looker: "Find all the rows marked 'Income,' add up their amounts, and then subtract the sum of all rows marked 'Expense.’" Now you can add a third scorecard to your report using "Net Profit" as its metric.

4. Visualize Your Spending and Earning Habits

Numerical scorecards are great, but charts tell a story. Add some essentials:

• Expenses by Category: Insert a Pie Chart or Bar Chart. Set the Dimension to "Category" and the Metric to "Amount." Apply your "Expense" filter to it so it only shows outgoing money. This immediately highlights where you're spending the most.

• Income vs. Expenses Over Time: Insert a Time series chart. Set the drill-down Dimension to "Date." For metrics, add the total "Income" amount and total "Expense" amount so you can see them trend on the same graph as separate lines.

5. Let People Explore the Data

To make the report truly useful, add controls. Go to Add a control > Date range control and place it at the top. Anyone viewing the report can now select a time period — like "Last Quarter" or "This Month" — and all the charts will update automatically.

Step 3: What if AI Could Do the Hard Work?

You can see how the manual process involves clicking, dragging, filtering, and writing simple formulas. It’s logical, but it takes time and practice. This is where AI assistants, like Gemini in Looker Studio, change the game.

Instead of building charts piece by piece, you can now use natural language to generate them for you.

Asking Questions, Getting Charts

With AI assistance enabled, you can literally describe the chart you want to see. You can think of it as a creation assistant that skips a lot of the setup menu clicking for you.

For example, instead of manually adding a bar chart, selecting the dimension, metric, and filters, you could just type something like:

“Show me a bar chart of total expenses by category for last month”

It will analyze your request, look at your connected data, identify the "Category" and "Amount" columns, apply a date filter for "last month," apply a filter for "Expense" rows, and generate the chart for you. You can then add this finished chart directly to your report. This approach dramatically reduces the learning curve and speeds up the process.

Don't Stop at the First Answer

Once you have a chart, you can continue refining it with follow-up questions. You might ask things like:

• "Change this to a pie chart."

• "Sort this from highest to lowest."

• "Only show the top 5 expenses."

This conversational approach to building reports makes data exploration more intuitive. You're no longer hunting around for the right setting in a menu, you're just asking for what you want to see. This lets you drill down much faster, turning a question like "Why were expenses high last month?" into an actionable insight in seconds.

Final Thoughts

An income and expense report gives you the financial visibility needed to run your business effectively. With a tool like Looker Studio, you can transform a simple spreadsheet into a professional, real-time dashboard. While building it manually is totally achievable, AI features are making this kind of powerful analysis accessible to everyone, regardless of their technical background.

Instead of just helping build a single chart, we designed Graphed to handle the entire reporting workflow. We directly connect to your financial tools — like Stripe, QuickBooks, and Shopify — alongside your marketing platforms. This eliminates the toughest step of collecting and cleaning scattered data. Then, you can ask a simple question in plain English like, “Create a dashboard showing my monthly profit and loss, breaking out my top 5 expense categories and comparing it to my revenue from Facebook Ads and Google Ads.” We build the entire interactive, real-time-updated dashboard for you in about 30 seconds.