Overview

Google Sheets Canvas can turn one spreadsheet tab into an interactive visual experience. In this workflow, the Copper CRM for Google Sheets add-on supplies the Opportunity data, and Gemini uses the starter prompt to build a dashboard with global filters, KPIs, monthly performance, forecast analysis, loss insights, and record-level detail.

What you will build

A current-year executive sales dashboard that can be filtered by Year, Owner, and Opportunity Status while remaining a reporting-only layer over the Copper export.

Before you start

You will need:

A Copper account and permission to export the Opportunity records you want to analyze.
The Copper CRM for Google Sheets add-on installed in Google Sheets.
Access to Gemini and Canvas in Google Sheets. Canvas is available in Sheets on the web for eligible accounts; availability can depend on your Workspace plan and administrator settings.

How the process works

Step	Outcome
1. Import Copper data	The add-on creates a source tab containing the Opportunity records and fields you select.
2. Start a Canvas	Canvas uses the active Copper tab as its single data source.
3. Paste the prompt	Gemini receives the business rules, calculations, filters, layout, and governance requirements.
4. Review and refine	You validate field mappings and totals, then use focused follow-up prompts for corrections or design changes.
5. Refresh	New Copper exports or refreshes update the source; you recheck mappings after structural changes.

Step 1: Connect Copper data with the Google Sheets add-on

Open a new Google Sheet in Google Drive.
Open Extensions (or Add-ons, depending on your Sheets menu) and launch the Copper CRM for Google Sheets add-on. If prompted, sign in and authorize access to the Copper account you want to report on.
Select Import data, then choose Opportunities as the record type.
Choose the Copper pipeline to analyze. Optionally select a Copper filter to reduce the record set; ensure the import includes the history needed for every dashboard year you plan to select.
Select Import Data and wait for the new tab to populate. Larger exports can take several minutes; keep each import to 20,000 rows or fewer.
Confirm the export includes Opportunity Name, Owner, Company, Pipeline, Stage, Status, Value, Created Date, Close Date, Loss Reason, and Currency when relevant.
Confirm row 2 contains readable headers, row 3 onward contains records, date and value columns are populated, and the statuses and stages match your Copper setup.
Rename the tab only if necessary, then leave the tab structure and both header rows intact.

Choose the import scope carefully

Canvas uses one tab. Include all fields and all relevant Opportunity records in this export before you generate the dashboard. If you later need a missing field, re-import it and ask Gemini to review the field mappings.

Step 2: Create a Canvas from the Copper export

Select the Copper Opportunity export tab so it is the active sheet.
Start Canvas from Tools > Create canvas, Insert > Create a canvas, or the Canvas option in the sheet’s bottom bar. The exact entry point can vary by account rollout.
Paste the complete starter prompt in the next section into Gemini.
Before submitting, replace the example stage probabilities with values that match your Copper pipeline if you already know them.
Submit the prompt. Review Gemini’s assumptions and mappings before accepting the dashboard as final.

Why the prompt defines business logic

Canvas can calculate and visualize directly from its source tab. The prompt names the required metrics and logic so Gemini applies consistent definitions. If your reporting process requires auditable cell-by-cell formulas, build a separate reporting table in Sheets first, then create Canvas from that table.

Suggested starter prompt

Copy the entire prompt below. It includes the source rules, dashboard specification, read-only guardrails, and a final check for assumptions or missing data.

Role & Objective: Act as a sales operations analyst. Design an executive-level Sales Analytics Dashboard in Google Sheets powered strictly by a raw Copper CRM data export tab. The dashboard must focus on the current year by default and function exclusively as a read-only reporting and visualization layer—do not include features or inputs that allow users to create, edit, or delete underlying records within the dashboard canvas.

1. Global Dashboard Controls & Filtering (Top Banner):
* Year Dropdown Selector: Allows selecting the active analysis year (defaults to the current year). All KPIs, tables, and charts must dynamically filter based on Close Date within the selected year.
* Owner Dropdown Selector: Allows filtering the entire dashboard by a specific Opportunity Owner or selecting "All Owners" for full team visibility.

2. Executive KPI Summary Cards (Top Section):
* Display 6 key performance indicator blocks reflecting the selected Year and Owner:
* Total Won Revenue (YTD / Selected Year)
* Active Pipeline Value (Unweighted)
* Weighted Forecasted Pipeline: Open pipeline adjusted by stage win probabilities, calculated as ∑ (Opportunity Value × Stage Probability).
* Win Rate (%): Formulated as Win Rate = Won Deals / (Won + Lost + Abandoned).
* Average Sales Cycle Length: Average duration in days where Cycle Length = Close Date - Created Date for closed-won deals.
* Average Deal Size (ACV): Average monetary value across all Won opportunities.

3. Monthly Performance, Goal Tracking & Visuals (Middle Section - Primary):
* Full 12-Month Performance Table: Explicitly include all 12 calendar months (Jan - Dec) for the selected year. If a month has no sales or activity, display $0 / 0 deals rather than omitting the row.
* Inputs & Columns: Month Name, User-Editable Monthly Goal ($), Actual Won Revenue, Open Pipeline Value (Forecasted/Weighted), Total Projected Revenue (Won + Open Weighted), and Goal Attainment % with conditional formatting (green/red).
* Overall Annual Target: Include a dedicated input cell for the Total Annual Revenue Goal (with YTD vs. Annual Target Attainment %).
* Monthly Revenue Bar Chart: Include a bar chart (or clustered column chart) displaying Open Pipeline Value vs. Closed-Won Revenue for each of the 12 months side-by-side so seasonal throughput and open pipeline coverage are visually clear.

4. Stage Probability & Owner Breakdown (Middle Section - Secondary):
* Stage Forecast Table: Group open deals for the selected year/owner by Opportunity Stage with mapped probabilities (e.g., Discovery = 20%, Demo = 40%, Proposal = 60%, Negotiation = 80%). Columns: Stage Name, Assigned Probability %, Deal Count, Unweighted Open Pipeline Value, and Weighted Forecasted Revenue.
* Owner Breakdown Table: Grouped by Opportunity Owner, displaying Active Deal Count, Open Pipeline Value, Weighted Forecasted Revenue, and Closed-Won Revenue.

5. Pipeline Risk & Loss Analysis:
* Loss Reason Report Table: Group all Lost and Abandoned opportunities in the selected period by the "Loss Reason" column. Display Total Lost Revenue, Lost Deal Count, and Percentage Share of overall lost pipeline.

6. Interactive Master Opportunity Table (Bottom Section):
* Full opportunity summary displaying: Opportunity Name, Owner, Company, Stage, Stage Probability (%), Unweighted Value, Weighted Forecasted Value, Created Date, Close Date, Status, and Loss Reason.
* Include an interactive Data Validation dropdown filter above the table to view deals by Status (*All, Won, Open, Lost, Abandoned*).

7. Data Governance, Mechanics & Formatting:
* Data Integrity Constraint: The entire dashboard must be strictly read-only regarding underlying records. Do not build inline editing, row creation, or row deletion mechanisms into the canvas; all output must derive dynamically from the primary Copper export source sheet.
* Utilize dynamic formulas (`SUMIFS`, `COUNTIFS`, `AVERAGEIFS`, `FILTER`, `QUERY`) referencing the raw Copper export tab and linking directly to the Year and Owner selectors so updates auto-recalculate when new source data is pasted.
* Create a small lookup key table for Stage Probabilities so stage weighting defaults can be adjusted without editing core formulas.
* Format currency as `$#,##0`, dates as `YYYY-MM-DD`, percentages as `0.0%`, and days as whole numbers.

What each part of the prompt creates

Prompt section	What Gemini should create
Global controls	Year and Owner dropdowns filter the entire dashboard. Year defaults to the current year; Owner defaults to All Owners.
Executive KPI cards	Six cards show won revenue, unweighted open pipeline, weighted forecast, win rate, average sales-cycle length, and average won deal size for the selected filters.
Monthly performance	All 12 months remain visible, including zero-activity months. Monthly goals, won revenue, weighted open pipeline, projected revenue, and attainment sit alongside an annual target summary.
Revenue chart	A side-by-side monthly chart compares closed-won revenue with open weighted pipeline, making seasonality and pipeline coverage easier to scan.
Stage forecast	Open deals are grouped by stage and multiplied by configurable stage probabilities to create a realistic weighted forecast.
Owner breakdown	Managers can compare active deal count, open pipeline, weighted forecast, and closed-won revenue by Opportunity Owner.
Loss analysis	Lost and Abandoned deals are grouped by Loss Reason, including lost value, deal count, and share of the total lost pipeline.
Master table	A record-level Opportunity summary includes stage probability and weighted value, with a Status dropdown for All, Won, Open, Lost, or Abandoned.
Governance and formatting	The Canvas reports from the Copper source without record-management controls. Currency, date, percentage, and duration formats stay consistent.

How the calculations work

Metric	Definition
Weighted forecast	For each Open deal: Opportunity Value × Stage Probability. The KPI is the sum of those weighted values.
Win rate	Won deal count ÷ (Won + Lost + Abandoned deal count). Open deals are excluded from the denominator.
Sales cycle	Average number of days from Created Date to Close Date for Won deals only.
Average deal size	Average Opportunity Value across Won deals for the active filters.
Projected revenue	Actual Won Revenue + Weighted Open Pipeline for the month.
Goal attainment	Actual Won Revenue ÷ Monthly Goal. If no goal is set, the dashboard should not invent one.

Step 3: Validate the generated Canvas

AI-generated reporting should be checked before it is used in forecasts or executive reviews.

Review Gemini’s source-field mapping and confirm it used row 2 as the readable headers.
Change the Year and Owner selectors and confirm every KPI, table, and chart updates to the same scope.
Compare Total Won Revenue against Copper filtered by the same Close Date year, Owner, and Won status.
Confirm January through December always appear and zero-activity months display zero rather than disappearing.
Verify every open stage has an explicit probability; spot-check several weighted values manually.
Confirm win rate excludes Open deals and uses Won, Lost, and Abandoned in the denominator.
Test the master-table Status filter and confirm it also respects the selected Year and Owner.
Check that no dashboard control can create, edit, or delete an underlying Copper record.
If multiple currencies appear, confirm totals are separated or filtered by currency.

Refine the dashboard with follow-up prompts

“Map the source column named Monetary Value to Opportunity Value and recalculate all currency metrics.”
“Set stage probabilities to Discovery 20%, Demo 40%, Proposal 60%, and Negotiation 80%.”
“Set the annual target for 2026 to $2,500,000 and use the following monthly goals: [list amounts].”
“Explain why the Won Revenue KPI differs from the sum of the monthly table, including every filter used.”
“Increase the contrast of the goal-attainment indicators and keep green/red meaning accessible with text labels.”
“Highlight open deals more than 30 days past their Close Date without changing the Copper source.”

Refresh and share your dashboard

Refresh the Copper export through the add-on whenever the report needs current Opportunity data. Canvas should recalculate from the source, but you should revalidate after field names, stages, statuses, or the export scope changes. Canvas uses the spreadsheet’s sharing permissions, so share the file only with people authorized to view the Copper data. Give view access to dashboard consumers when possible and reserve edit access for report owners.

Troubleshooting

Issue	What to do
Create canvas is missing	Use Sheets on the web and confirm your Workspace edition, Gemini access, administrator settings, language, and smart features meet Google’s current requirements.
Canvas will not generate	Confirm the file is stored in Drive and reduce an unusually large import with a Copper filter. Then retry from the active Copper tab.
A KPI is wrong	Ask Gemini to list its field mapping and filter logic. Check Status, Value, Created Date, Close Date, Year, and Owner first.
Some months are absent	Repeat that all 12 calendar months must remain visible and zero-activity months must show $0 and 0 deals.
Weighted values are zero	Add the exact missing stage names and probabilities, then ask Gemini to recalculate all weighted metrics.

Related resources

Using Copper’s Google Sheets add-on

Google Help: Create and use a Sheets canvas

Need help?

Contact Copper Support through the in-app chat or email support@copper.com.

Build a Copper Sales Analytics Dashboard with Google Sheets Canvas