How to Create a Small Business Dashboard in Looker with AI

Building a dashboard to track your business performance shouldn't feel like wrestling with a mountain of data and complex software. You just want clear answers to simple questions. This tutorial will walk you through creating a useful small business dashboard in Looker Studio (formerly Google Data Studio) by using AI as your helpful assistant to handle the tricky parts.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother with a Small Business Dashboard?

For a small business, time is your most valuable asset. A dashboard saves you time by automating the painful process of manual reporting. Instead of spending every Monday morning downloading CSVs from different platforms and pasting them into a spreadsheet, a dashboard gives you a live, at-a-glance view of your most important metrics.

Think of it as the cockpit for your business. It helps you answer vital questions instantly:

• Are the Facebook ads we launched last week actually generating sales on Shopify?
• Which blog posts are bringing the most new visitors to our website?
• How is our sales team's pipeline looking this quarter compared to last?
• Is our website traffic up or down this month, and where is it coming from?

A well-built dashboard turns scattered data into clear signals, helping you make smarter, faster decisions instead of relying on gut feelings.

Getting Started: Connecting Your Data in Looker Studio

First, a quick clarification: Google has two products named "Looker." There’s the enterprise-level platform, Looker, and the free, user-friendly tool, Looker Studio. For most small businesses, Looker Studio is the perfect starting point, and that's what we'll be using today.

The first step is to bring your data into one place. Let's connect two of the most popular data sources for small businesses: Google Analytics and Google Sheets.

Connecting Google Analytics 4

Your website analytics are a goldmine of information about your audience and their behavior. Connecting GA4 to Looker Studio is refreshingly simple.

1. Go to lookerstudio.google.com and click the Create button in the top left, then select Report.
2. Looker Studio will now ask you to add data to your report. In the search bar on the right, type "Google Analytics."
3. Select the Google Analytics connector and click AUTHORIZE to give Looker Studio permission to access your Google account.
4. Once authorized, you'll see a list of your GA accounts. Select the account and the specific GA4 property for your business (e.g., your Shopify store's website).
5. Click Add in the bottom right corner. A pop-up will confirm you're adding this data source, click Add to report.

That's it! Your report canvas is now linked to your live Google Analytics data.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Connecting Google Sheets

What about data that isn't in Google Analytics? Maybe you track your social media metrics, manual sales data, or project expenses in a simple spreadsheet. Google Sheets is the perfect hub for this.

1. In your new Looker Studio report, go to Resource > Manage added data sources.
2. Click Add a data source.
3. Search for and select the Google Sheets connector.
4. You'll now see a list of your recent spreadsheets. Select the one containing your data.
5. Make sure the options are correctly configured. Use the first row as headers, and include hidden and filtered cells if needed.
6. Click Add to connect your sheet to the report.

Now you have both your website data and your spreadsheet data available in the same report, ready to be visualized.

Using AI as Your Data Analysis Assistant

You’ve connected your data. You can now start adding charts like scorecards, tables, and time-series graphs by dragging and dropping fields. This is great for basic metrics like 'Total Users' or 'Revenue'.

But what happens when you need to answer a more specific business question? This is where the real power — and often frustration — of BI tools comes in. You need to create custom formulas and calculated fields, and unless you're an expert in Functions and RegEx, it can be a major headache.

This is where AI becomes your secret weapon. Instead of spending hours looking up tutorials, you can simply ask an AI chatbot like ChatGPT, Claude, or Gemini to write the formulas for you. You don't need to understand the syntax, you just need to describe the outcome you want in plain English.

Example 1: Grouping Your Own Marketing Channels

The standard channel groupings in Google Analytics are useful, but they might not reflect how your business operates. Maybe you want to group all paid advertising from Facebook, Instagram, and Google Ads into a single "Paid Media" category.

Without AI, you’d need to learn how CASE statements work. With AI, you just ask for it.

Step 1: Write a Simple Prompt Go to your AI tool of choice and type a prompt like this:

"Write a Looker Studio CASE statement for GA4. I want to group the field 'Session source / medium' into new categories. Create a bucket called 'Paid Social' that includes CPC traffic from Facebook and Instagram. Create another called 'Paid Search' for CPC traffic from Google and Bing. Everything else can be labeled 'Other'."

Step 2: Get Your Instant Formula The AI will likely spit out something like this:

CASE WHEN REGEXP_CONTAINS(Session source / medium, 'facebook.*cpc|instagram.*cpc') THEN 'Paid Social' WHEN REGEXP_CONTAINS(Session source / medium, 'google.*cpc|bing.*cpc') THEN 'Paid Search' ELSE 'Other' END

Step 3: Add the Formula to Looker Studio Now, just copy and paste that code into Looker Studio:

1. In your Looker Studio report, in the right-hand panel, click Add a field under your GA4 data source.
2. Give your new field a name, like "Custom Channel Grouping."
3. Paste the formula the AI gave you into the formula box. Looker Studio will give you a little green checkmark if the syntax is valid.
4. Click Save and then Done.

You now have a brand new dimension you can use in any chart! You can drag "Custom Channel Grouping" into a pie chart and pair it with a metric like "Conversions" to see which of your custom channels drives the best results.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Example 2: Extracting Information from Campaign Names

Smart marketers use consistent naming conventions for their campaigns to track performance (e.g., EM_2024_SpringPromo_DiscountA). The problem is that the full name is too long for a chart legend. What if you just wanted to see performance by the promotion itself ("SpringPromo")?

Traditionally, this requires a tricky bit of RegEx (Regular Expressions). Now, it's just another simple question.

Step 1: Write a Simple Prompt In Looker Studio, I have a campaign name field like 'EM_2024_SpringPromo_DiscountA'. Write a formula to extract just 'SpringPromo' from the middle.

Step 2: Get Your Instant Formula The AI will give you the exact REGEXP_EXTRACT formula you need:

REGEXP_EXTRACT(Campaign Name, '_(.*?)_')

Just like before, copy this, create a new calculated field in Looker Studio, and paste it in. In seconds, you've created a clean, readable dimension for your reports without needing to become a RegEx wizard.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Designing a Dashboard That's Actually Useful

Creating charts is one thing, creating a dashboard that provides genuine insight is another. A cluttered, confusing dashboard is just as bad as no dashboard at all. Follow these simple principles to make sure yours is clean, clear, and actionable.

1. Start with Questions, Not Data Don't just start building charts for every metric available. Begin by writing down the 3-5 most important questions you need answered daily or weekly. For an e-commerce store, this might be:

• What was our total revenue yesterday?
• Which marketing channels drove that revenue?
• What were our top-selling products?

Design your dashboard specifically to answer these questions at a glance.

2. Use a Logical Layout People naturally read from top to bottom and left to right. Place your most important, high-level KPIs (Key Performance Indicators) in scorecards at the top left. This could be your overall revenue, website sessions, or lead count. As you move down and to the right, provide more detailed breakdowns, like tables or bar charts showing the sources of those key metrics.

3. Choose the Right Chart for the Job

• Scorecards: Perfect for displaying a single, important number (e.g., "Total Sales this Month"). Use the comparison feature to show change over the previous period.
• Time-Series Charts: The best way to show a trend over time (e.g., "Website Traffic over the last 90 days").
• Bar/Column Charts: Ideal for comparing values across different categories (e.g., "Sales by Country").
• Tables: Use these when you need to show precise detail, but use them sparingly as they can be visually dense.

4. Add Controls for Interactivity The most valuable addition to any dashboard is a Date range control. Go to Insert > Date range control and place it at the top of your report. This allows anyone viewing the dashboard to easily change the time frame (e.g., This Week, Last Quarter, Year-to-Date) and see all the charts update instantly.

Final Thoughts

You can create a powerful dashboard for your small business without needing a data science degree. Free tools like Looker Studio provide the foundation, and leveraging AI assistants helps you get past the technical hurdles of crafting custom formulas, making valuable insights more accessible than ever.

At Graphed, we believe this process can be even simpler. While using AI to write formulas is a huge step up from learning code yourself, it still involves juggling different tools and troubleshooting syntax. We created a platform where you can connect your data sources - like Google Analytics, Shopify, Facebook Ads, and Salesforce - and then just describe the entire dashboard you want in plain English. Your prompt doesn't just create a formula, it builds the charts, organizes the layout, and powers a complete, interactive dashboard in about 30 seconds. To try it yourself, you can build your first AI-powered dashboard for free on Graphed.