This guide for Episode 9 of Zero to AI brings together the episode narrative, the free tool stack, step-by-step setup instructions, Gemini prompts, and listener homework.
Why AI-Powered Dashboards Matter
Most dashboards fail because they show numbers but do not tell you what to do next. When you are reinventing your work or trying to grow sales, you do not need more data. You need clarity.
AI-powered dashboards give you that clarity without needing to be a data analyst. They help you:
- see your sales pipeline clearly
- spot bottlenecks before they hurt revenue
- understand which sources actually bring in money
- forecast what might happen next
- decide what to focus on this week
In this guide, you will build a simple but powerful sales dashboard using free tools:
- Google Sheets for data
- Google Looker Studio — the free dashboard tool used in this episode
- Gemini as your AI sales analyst
The Zero to AI Free Dashboard Stack
We will use this combination because it is powerful, free, and realistic for beginners.
Core stack
- Google Sheets: stores your sales data and lets you use Gemini to clean and calculate
- Google Looker Studio: turns your sheet into a live, interactive dashboard
- Gemini in Google Sheets: writes formulas, cleans data, categorises leads, and spots patterns
- Gemini reading your dashboard and data: explains what is happening in plain language
Optional extras
- Microsoft Power BI Free + Copilot: useful if you already work in the Microsoft ecosystem
- Polymer Search: upload a CSV and get instant exploratory dashboards
- ChatGPT Free + CSV upload: a fallback option for manual insight generation
For the main walkthrough, we will stay with Google Sheets + Looker Studio + Gemini.
What This Sales Dashboard Will Track
We will focus on sales so the value is concrete. Your dashboard will show:
- how many leads you have
- which stage each lead is in: New Lead, Qualified, Proposal Sent, Negotiation, Closed Won, Closed Lost
- total pipeline value
- weighted pipeline value
- where deals are getting stuck
- which sources bring in real revenue
You can later adapt the same structure for content, productivity, or career dashboards.
Step 1: Set Up Google Sheets
These steps are beginner-friendly. You have two options:
- Option A: manually create the sheet and type a few rows yourself
- Option B: copy the dummy data and upload or paste it into Google Sheets
1.1 Create your spreadsheet
- Open your browser and go to sheets.google.com
- Click Blank to create a new spreadsheet
- Rename it to something like Sales Dashboard – Zero to AI or My Sales Pipeline
1.2 Add your columns
In Row 1, add these headers:
- A1: Date
- B1: Lead Name
- C1: Source
- D1: Stage
- E1: Deal Value
- F1: Probability %
- G1: Next Action
- H1: Next Action Date
- I1: Notes
1.3 Add your data
Option A: Use the dummy sales data file
- Download the dummy sales CSV if it is provided alongside this guide, or copy the CSV block into a text file and save it as sales_data.csv
- In Google Sheets, click File > Import
- Choose Upload and select your CSV file
- When asked how to import, choose Replace data at selected cell with A1 selected, or Insert new sheet
- Click Import data
Your sheet will now be populated automatically.
Option B: Type a few rows yourself
If you prefer, start by typing a few rows manually under the headers.
Example row:
- Date: 2025-01-03
- Lead Name: Alpha Plumbing
- Source: Referral
- Stage: Qualified
- Deal Value: 3500
- Probability %: 50
- Next Action: Send proposal
- Next Action Date: 2025-01-05
- Notes: Warm lead, timeline flexible
1.4 Open Gemini in Sheets
On the right side of Google Sheets, look for the Gemini icon or panel.
- Click it to open the Gemini side panel
- If you do not see it, make sure you are logged into a Google account that has Gemini access
1.5 Let Gemini help with structure
Click any cell in your sheet, then type this into the Gemini panel:
Review my sales data. Tell me if my column headers are clear and suggest one extra column I should add to better understand pipeline health.
If Gemini suggests a column like Weighted Value or Lead Quality, add a new header in Row 1.
For example:
- J1: Weighted Value
Then ask:
Write a formula that multiplies Deal Value by Probability % as a decimal to create a Weighted Value for row 2. Explain where to paste it.
Paste the formula into J2, then drag it down the column.
1.6 Optional: Ask Gemini to classify lead quality
Add another header such as:
- K1: Lead Quality
Then ask Gemini:
Create rules to classify each lead as Hot, Warm, or Cold based on Stage, Probability %, and Notes. Tell me exactly how to implement this in column K.
Follow the instructions and fill the column.
You now have a structured dataset ready for a dashboard.
Step 2: Connect Sheets to Looker Studio
2.1 Open Looker Studio
- Go to lookerstudio.google.com
- Sign in with the same Google account you used for Sheets
- Click Blank report
2.2 Add your Google Sheet as a data source
- In the data source panel, click Google Sheets
- Find and select your spreadsheet
- Choose the correct worksheet
- Make sure Use first row as headers is turned on
- Click Add
- When prompted, click Add to report
Your sales data is now connected to Looker Studio.
Step 3: Build a Simple Sales Dashboard
You will now build a clean, minimal dashboard with just a few powerful views.
3.1 Add a date filter
- In the top menu, click Insert > Date range control
- Place it near the top of the report
This lets you switch between the last 7 days, 30 days, quarter, and so on.
3.2 Add key scorecards
Add three scorecards:
- Total Pipeline Value using Deal Value
- Total Weighted Pipeline using Weighted Value
- Deals Closed Won using a count of Lead Name filtered by Closed Won
3.3 Add a pipeline bar chart
- Click Insert > Bar chart
- Set Dimension to Stage
- Set Metric to Deal Value
This shows how much value sits at each stage of your pipeline.
3.4 Add a time series chart
- Click Insert > Time series chart
- Set Dimension to Date
- Set Metric to Deal Value or Closed Won
This shows how your pipeline or revenue changes over time.
3.5 Add a source performance chart
- Insert a Pie chart or Bar chart
- Set Dimension to Source
- Set Metric to Weighted Value or Deal Value
This shows which sources bring in the most valuable leads.
3.6 Add a lead quality filter
If you created a Lead Quality column:
- Click Insert > Drop-down list
- Set the control field to Lead Quality
Now you can filter the dashboard to show only Hot, Warm, or Cold leads.
You now have a fully working sales dashboard.
Step 4: Use Gemini as Your AI Sales Analyst
Looker Studio handles the visuals. Gemini reads the data and tells you what it means.
4.1 Analyse the data in Sheets
- Go back to your Google Sheet
- Click into any cell
- Press Ctrl + A or Cmd + A to select all the data
- Open the Gemini panel
4.2 Ask for insights
Use prompts like these:
Insight scan
Analyse this sales data and give me seven clear insights about what is improving, what is declining, and which sources are strongest.
Pipeline bottleneck
Tell me which pipeline stage is the biggest bottleneck and what action would unblock revenue fastest.
Conversion analysis
Calculate the conversion rate between each stage and identify where I am losing the most leads.
Prediction
Based on the last thirty to sixty days, predict likely closed revenue for next month and explain your reasoning.
Weekly action plan
Write a simple action plan I should follow this week based on this data. Focus on three actions only.
The 10 Most Powerful Gemini Prompts for Google Sheets and Looker Studio
Use these exactly as written. Copy and paste them into the Gemini sidebar in Google Sheets or the Explore with Gemini panel in Looker Studio.
5.1 Data-cleaning prompts for Google Sheets
Prompt:
Clean and standardise every phone number in column D to New Zealand format like +64 21 123 4567 and overwrite the cells directly.
What it does:
Turns different phone formats into one consistent NZ format.
Why it works well:
Gemini recognises NZ phone number patterns.
Prompt:
From the Address column, extract only the suburb or city into a new column called “Suburb”.
What it does:
Pulls suburb or city from full addresses.
Why it works well:
It recognises common NZ addressing patterns.
Prompt:
Flag any daily sales in column F that are more than 2 standard deviations away from that store’s average in a new column called “Outlier?” with Yes/No.
What it does:
Finds unusual spikes or crashes automatically.
Why it works well:
It uses proper statistics without you needing to know the maths.
Prompt:
Create a new column “Profit per Customer” = (Sales – Cost) ÷ Customers. If Customers = 0 or blank, return 0.
What it does:
Writes a safe formula for a useful business metric.
Why it works well:
It prevents divide-by-zero errors.
5.2 Instant insight prompts for Google Sheets
Prompt:
Which store or location is most negatively affected on rainy days?
What Gemini returns:
A table plus explanation.
Example:
Wellington sales drop 28% on rainy days versus only 11% in Auckland.
Prompt:
Predict December 2025 sales for each store using the last 12 months of data.
What Gemini returns:
A forecast table plus explanation.
Example:
Auckland $16,200, Wellington $11,800, Christchurch $14,900.
Prompt:
Why did muffin sales crash in September?
What Gemini returns:
Root-cause text plus chart.
Example:
A 45% drop, with 62% correlated to rainy days reducing foot traffic.
Prompt:
What are my top 3 quickest opportunities to make more money right now?
What Gemini returns:
A ranked list by dollar impact.
Example:
- Raise prices on coffee 8% (+$3,400/month)
- Open 1 extra hour on weekends (+$2,100)
- Reduce muffin wastage in Christchurch (+$900)
Prompt:
Show me monthly profit per customer as a line chart with trendline.
What Gemini returns:
A line chart directly in the sheet.
5.3 Dashboard-building prompts for Looker Studio
Prompt:
Show me a map of average profit per customer by suburb.
What it creates:
An NZ map with coloured pins or regions.
Pro tip:
Works even better if you already have a Suburb column.
Prompt:
Create a forecast chart for total sales for the next 3 months with confidence band.
What it creates:
A line chart extending into the future with uncertainty shading.
Pro tip:
Great for showing likely future performance.
Prompt:
Find anomalies in the data and explain them in plain English.
What it creates:
Highlights unusual days or rows and explains them clearly.
Prompt:
Add a scorecard for profit per hour the café is open.
What it creates:
A scorecard plus calculated field.
Pro tip:
Use this as a primary KPI.
Prompt:
Show me sales by day of week as a bar chart and highlight weekends.
What it creates:
A day-by-day bar chart so you can spot weekend performance quickly.
5.4 Bonus one-click prompts
- Give me a one-paragraph executive summary of this dataset as if I’m showing my business partner tomorrow.
- List the 5 riskiest things that could hurt profit next month.
- Create a calculated field for Waste % = (Wasted Units ÷ Units Sold) × 100.
- Make a heatmap of sales by hour of day and day of week.
- Export the top 10 insights as a ready-to-copy bullet list for my team meeting.
5.5 How to get the best results from Gemini every time
- Be specific about column names
- Use the exact header text from your sheet
- Ask it to overwrite the cells directly when cleaning
- If the first answer is not right, reply with make it simpler or add a chart
- In Looker Studio, start prompts with an action verb such as Show me, Create, Add, or Find
Step 5: ChatGPT Analysis in 5 Minutes
6.1 Open ChatGPT
- Go to chat.openai.com
- Sign in or create an account
- Click New chat
6.2 Upload your data
- Click the paperclip icon next to the text box
- Click Upload file
- Select your CSV file
- Click Open
- Wait for it to upload
6.3 Ask for insights
Use these prompts one at a time:
First prompt
Analyse this sales data and give me the 5 most important insights.
Second prompt
What patterns do you see in this data?
Third prompt
Are there any unusual trends or anomalies I should investigate?
Fourth prompt
Based on this data, what 3 questions should I be asking about my business?
6.4 Get formulas for Google Sheets
Ask:
Write me a Google Sheets formula that categorises sales as High (over $500), Medium ($200 to $500), or Low (under $200).
Then:
- Copy the formula ChatGPT gives you
- Go back to your Google Sheet
- Paste it into a cell
Done.
Homework: Build Your First Sales Dashboard
To make this real, follow this simple homework:
- Decide on one focus area: sales for your business, freelance pipeline, or job search leads
- Create the Google Sheet with the columns listed above, or import the dummy data
- Connect the sheet to Looker Studio
- Build the simple dashboard with scorecards, a pipeline bar chart, time series, and source chart
- Use Gemini to analyse the data and write a short weekly summary
- Choose one action you will take next week based on what you saw
The goal is not to build a perfect dashboard. The goal is to experience what it feels like when your numbers start telling you a clear story.
Closing Thoughts
AI-powered dashboards are not about becoming more obsessed with data. They are about reducing noise so you can see what matters.
When you combine:
- a simple sheet
- a clear dashboard
- an AI that reads the story
you get a decision engine, not just a report.
Start small. One sheet. One dashboard. One weekly insight. Then build from there. Have this scaled and built properly for your organisation
Next one is Episode 10 — create your own AI assistant
