How to Create a Website Dashboard in Google Sheets with AI

Creating a dynamic dashboard to track your website’s performance doesn’t require expensive software or complex coding skills. You can build a surprisingly powerful and automated dashboard right inside Google Sheets, using its built-in tools and a touch of AI to simplify the process. This article will walk you through exactly how to connect your website data and build a custom report that updates on its own.

Why Use Google Sheets for a Website Dashboard?

Before jumping into the setup, let's cover why Google Sheets is such a great choice. For many, the main benefit is that it's completely free and part of the Google ecosystem you likely already use. It's also incredibly flexible, allowing you to track the exact metrics that matter to you instead of being stuck with a pre-configured report. But the real power comes from its ability to automate data imports and use simple tools to build professional-looking visuals.

The traditional downside was the manual effort - endless copying and pasting from Google Analytics. But by connecting your data directly and using Sheets' AI-powered features, you can eliminate most of that tedious work and get straight to the insights.

Step 1: Get Your Google Analytics Data into Google Sheets

To build a website dashboard, you first need data. The best way to get live website traffic data into your spreadsheet is by connecting it directly to Google Analytics. This is simple to do with a free add-on from Google.

Install the “Google Analytics” Add-on

1. In your Google Sheet, navigate to Extensions > Add-ons > Get add-ons.

2. In the Google Workspace Marketplace search bar, type “Google Analytics” and hit Enter.

3. Find the official add-on by Google and click Install.

4. Follow the prompts to grant the necessary permissions for the add-on to access your Google account.

Once installed, you're ready to create your first report.

Configure Your Data Import

Now, let's tell the add-on which data to pull from your Google Analytics account.

1. Go back to Extensions > Google Analytics > Create a new report.

2. A sidebar will appear on the right. Give your report a name (e.g., "Monthly Traffic Report").

3. Select the correct Google Analytics Account, Property, and View for your website.

4. Choose your metrics and dimensions. These are the building blocks of your report. For a basic website dashboard, start with these:

   • Metrics: Users, Sessions, Pageviews, Bounce Rate, Session Duration

   • Dimensions: Date (to see trends over time), Source / Medium (to see where traffic comes from), Device Category (to see mobile vs. desktop)

5. Click Create Report. This will generate a new sheet called "Report Configuration." This sheet is where the add-on stores your instructions manually and it can look a bit intimidating, but for now, we only need to adjust a couple of settings.

6. In the "Report Configuration" sheet, find the “Start Date” and “End Date” fields. You can replace the specific dates with dynamic values like yesterday or 30daysAgo. Using 30daysAgo as the start and yesterday as the end gives you a rolling 30-day view of your data every time you update it.

Set Up an Automatic Refresh

This is the key to an automated dashboard. You can schedule your report to run automatically, so your data is always fresh without you needing to do a thing.

1. Go to Extensions > Google Analytics > Schedule reports.

2. Click the checkbox for “Enable reports to run automatically.”

3. Choose a schedule, such as every day or every week. This depends on how frequently you need up-to-date data.

4. Click Save. Now Sheets will automatically fetch the latest data from Google Analytics based on your chosen schedule.

Finally, run the report for the first time by going to Extensions > Google Analytics > Run reports. A new sheet will appear, containing all the raw data you requested.

Step 2: Structure Your Dashboard for Success

A smart way to organize your spreadsheet is to separate your raw data from your visual dashboard. You should have at least two tabs:

• Raw Data: This is the sheet created by the Google Analytics add-on. Rename it to something clear, like "GA Data." You should never edit the data in this tab directly. Think of it as your clean, untouchable data source.

• Dashboard: This is a clean, new sheet where you’ll build all your charts and tables. This tab will pull information from your "GA Data" sheet to create summaries and visualizations.

This structure keeps your workbook organized, prevents accidental errors, and makes it much easier to manage down the line.

Step 3: Build Your Dashboard with AI and Formulas

With your data flowing in, now comes the fun part: making it useful. This is where Google Sheets' formulas and built-in AI come in handy.

Level 1: The AI-Powered "Explore" Feature

This is the fastest, easiest way to build charts without writing a single formula. The “Explore” feature uses AI to analyze your data and lets you ask questions in plain English.

1. Navigate to your "GA Data" sheet

2. Click the Explore icon in the bottom-right corner of the screen (it looks like a small box with a plus sign and sparkle).

3. A panel will open up showing auto-generated insights and charts.

4. You can ask specific questions in the text box at the top, such as:

   • “Average sessions by device category”

   • “Line chart of users by date”

   • “Top 5 source / medium by sessions”

   Google Sheets will generate an answer, usually with a great-looking chart.

5. You can insert these charts directly onto your dashboard sheet by simply dragging it to a cell in that tab. This lets you build a simple dashboard in minutes.

Level 2: Essential Formulas for a Custom Dashboard

While the Explore feature is fantastic for quick insights, formulas give you complete control and precision. In your "Dashboard" tab, you'll use formulas to summarize the data from your "GA Data" tab. Here are a few must-know formulas:

SUMIFS: For Totaling Key Metrics

Use SUMIFS to add up numbers based on different criteria. For example, to get total sessions from organic search:

This formula tells Sheets to look in the 'GA Data' sheet, find all the rows where the channel (Column A) is "organic", and then sum up the corresponding sessions (Column B). (Note: column letters may vary depending on what order you choose in Report Configuration)

QUERY: The Most Powerful Function in Sheets

QUERY is like a mini programming language inside Sheets. It allows you to select, filter, and organize data in almost any way you can imagine. For example, to create a table showing your top 5 traffic sources with their sessions and average session duration:

This might look complex, but it's just telling Sheets which columns you’re interested in, showing the average session duration and sorting by which source of traffic drove the most amount of traffic. Once you learn the QUERY basics, a whole new level of dashboard possibilities opens up.

SPARKLINE: For Mini In-Cell Trendlines

Want a quick visual trend next to your key numbers? SPARKLINE creates tiny charts that fit inside a single cell. For example, to show a line chart of daily sessions over the last 30 days:

This gives you a quick, at-a-glance visualization of your daily traffic directly on your dashboard.

Step 4: Add Visualizations to Tell the Story

A good dashboard uses charts and graphs to make data understandable at a glance. Move beyond SPARKLINE to create a truly professional view of your user behavior. In your "Dashboard" tab, use the information from your summary data to add even more useful at-a-glance visualizations - think KPI scorecards and graphs:

1. Highlight your summary table that you’ve created on your “Dashboard” sheet that utilizes the QUERY formula from the last section.

2. In the menu, click Insert > Chart.

3. In the Chart Editor pane, pick the right chart for insights. Choose a chart based on the dimension from your summary query (line, column, pie chart, or geo chart).

   • Line charts are perfect for tracking stats over time, like daily user traffic spikes from marketing campaigns or virality.

   • Bar charts work well for comparing categorical data, letting you see the breakout between different traffic sources

   • Pie charts show the ratios within your dimensions. For example, you want to know which device your visitors are using at a high level.

   • Geo charts allow you to know your traffic spread across the world or narrow in on high-traffic regions without having to rely on raw data tables.

4. Click Save to update the panel and see the new line graph.

A Simple Dashboard Layout Example

Not sure where to start? Try this proven dashboard layout:

• Top Row: Add headline "Key Performance Indicators at a glance" (called 'scorecards') containing KPIs such as major key performance sessions and page views on the site and new visitors. These allow any stakeholder who needs to follow key statistics for meetings or checkups without needing to read graphs and tables.

• Middle Row: This works to create space where any line charts of trends you include give an understanding of website trends like your users over the last few days.

• Bottom Panel & Deep Insight: The chart section gives opportunities for data breakdowns that need further analysis on deeper questions through a mix of tables, Geo graphs, bar charts, etc. Example data breakdowns can focus on how specific pages perform, conversion rates based on various categories for segments over your users. This section opens up opportunities to ask important questions about how you want to view specific sections of website behavior, depending on its role inside the organization, such as eCommerce products.

Now all that’s left is formatting to fit brand guidelines if needed and adding context about how to use it!

Final Thoughts

By connecting Google Analytics directly to Sheets and using its automated and AI-enhanced features, you can build a living dashboard that empowers you to make smarter, data-informed decisions for your website without costly software or hours of work. This transforms Sheets from a static spreadsheet tool into a dynamic, personalized BI platform.

While building a powerful single-source dashboard for free in Google Sheets, managing your workflow as your business grows can become challenging. Many teams use multiple channels alongside website performance tools, like CRMs and ad networks - meaning manual connections and formulas can create unnecessary complexity. At Graphed, we created a single platform where you can connect your key data from your company sources to a central view. Graphed lets you have conversational data analytics where you get to build data visual reports in English, creating any answers without dealing with complicated tooling or making data requests to an analyst - you can turn setups over in less than a minute with our library of integration templates and focus entirely on acting based on the insights you uncover.