This article contains affiliate links. AutomateWithMNK may earn a commission if you sign up through certain links, at no additional cost to you. This does not influence our editorial assessment.
A useful sales dashboard helps a team decide which opportunity to discuss and which data still needs attention. Coupler.io can supply the data flow, while the team defines the measures and decides what actions follow from them.
This documentary guide follows a fictional business, Beacon Installations, using HubSpot opportunities and a Google Sheets reading view. It builds a small dashboard for a weekly sales meeting, with explicit exclusions instead of a large collection of unexplained charts.
Disclosure: this article includes a Coupler.io affiliate link. We may earn a commission if you purchase through that link.
Documentary analysis dated 14 October 2026, without a product account test. Numerical examples are fictional and illustrate the method.
Choose the decisions before choosing the charts
Beacon asks three questions: what value remains in the open pipeline, which opportunities need attention and which sales closed during the selected period? These questions require different definitions. Current pipeline is a state; closed sales are events in a period.
Label the measures accordingly. An open proposal is not recognised revenue or a payment. A forecast close date is not evidence that a customer has committed. A chart cannot turn an incomplete record into a commercial fact.
Write the response expected from the meeting reader. For example, a missing amount should lead the opportunity owner to check the proposal, while an unassigned opportunity should be allocated. This connects the dashboard to work the team can actually perform, without assuming the reporting service decides the sales policy.
Keep one opportunity as the unit of value
The main dataset has one row per opportunity. Contacts, activities and line items may have different row counts. Joining every contact to each opportunity can repeat its amount, making a perfectly successful extraction produce an incorrect total.
Beacon keeps contact detail separate from its amount table. If a relationship is required for another view, it checks the number of distinct opportunity identifiers before and after the join. The field dictionary records what a row represents in each dataset.
Keep pipeline and stage identifiers available even when the reading view uses friendly names. Two pipelines may share a stage label while applying different qualification rules. Start with one agreed pipeline and document exclusions before combining teams. This protects the meaning of both the total and the exceptions list.
Use a small filled dataset to define the measures
| Opportunity | Stage | Amount | Included in |
|---|---|---|---|
| BI-601 | Qualified | 2 800 | Open pipeline |
| BI-602 | Proposal | 5 200 | Open pipeline |
| BI-603 | Won | 3 600 | Closed-sales view if date qualifies |
The open pipeline in this example is 8,000 GBP. The won opportunity is excluded from that amount. These numbers illustrate the inclusion rule rather than a customer result.
Load checked records before building the page
Create a HubSpot flow, authorise the correct portal and select Deals following the source documentation. Select the fields needed for the definitions, configure a dedicated Google Sheets tab according to the destination guide and run the first extraction manually.
Trace each sample opportunity back to its record. Check the value, currency, stage and owner before grouping. If you enrich the records with a separate owner table, verify the relationship rather than assuming matching display names are sufficient.
Keep imported rows in a raw-data area and dashboard calculations elsewhere. A later refresh should not erase a formula or a management comment inside the writing range. Stabilise the column selection and save the dictionary with the accepted dataset. That gives a replacement maintainer a concrete explanation of the dashboard’s base.
Calculate measures without concealing uncertainty
Add amounts only for opportunities included in the agreed open stages. Display the count of opportunities whose amounts are missing beside the pipeline total. The reader should see when the available total does not cover all relevant opportunities.
Closed sales require the agreed closed state and date rule. If the business needs cash received, introduce payment evidence and a separate definition. Do not rename an opportunity amount as cash merely because the management page needs a short heading.
A conversion rate needs an explicit cohort and denominator. Dividing this month’s won opportunities by today’s open pipeline does not create a historical success rate. When the necessary cohort data is unavailable, show separate counts and explain the limitation. A modest measure is preferable to a confident percentage whose components do not describe the same population.
Map the calculation columns in the workbook
To reproduce Beacon’s small example, reserve opportunities_raw!A:D for opportunity identifier, stage, numerical amount and currency. Put BI-601 to BI-603 in rows 2–4: Qualified / 2800 / GBP, Proposal / 5200 / GBP and Won / 3600 / GBP. This is a proposed destination layout. Map the source properties to these columns; the labels are not guaranteed CRM API field names.
Calculate the two pipeline components
In pipeline_view, put Stage in A1, Qualified in A2, Proposal in A3 and Amount GBP in B1. With English functions and comma separators, enter =SUMIFS(opportunities_raw!C2:C,opportunities_raw!B2:B,A2,opportunities_raw!D2:D,"GBP") in B2. Copy it into B3 so the A2 reference becomes A3. The expected values for these rows are 2800 and 5200; =SUM(B2:B3) gives 8000. Won opportunities and other currencies are excluded.
This uses Google’s documented SUMIFS syntax. Adjust function language and separators to the workbook’s settings. Missing amounts do not contribute to the total, so keep a visible list of included opportunities without a value beside the measure.
Insert a chart from the checked summary
Select pipeline_view!A1:B3 and choose Insert → Chart. Select a column chart and verify that column A provides the stage labels, column B the amounts and the first row the headings. Use the title “Open pipeline in GBP” and show the last accepted extraction time in the reading page. Google documents chart insertion and editing. After an unchanged second extraction, check that the bars remain 2800 and 5200. This is a Google Sheets dashboard fed by Coupler.io, not a claim of testing Coupler.io’s native dashboard builder.
Design the page around attention and ownership
Beacon uses a compact layout: defined totals, data exceptions and opportunities requiring discussion. The latest accepted extraction time appears at the top. Filters are visible, so readers can recognise a change in pipeline or period.
Use a stage chart for distribution, a trend only when dated observations are comparable and a table when the reader must identify an opportunity. Red highlighting should represent an explained rule, such as a missing owner, rather than an unexplained judgement about a representative.
Assign each exception to someone able to correct the source. An analyst should not silently fill an invented value into the reporting copy to make the dashboard complete. The next extraction may replace that correction, and the CRM would still contain the unresolved record.
Check the dashboard with events that change its meaning
| Event | Expected effect | Risk to investigate |
|---|---|---|
| BI-602 becomes won | Leaves open pipeline | Same amount counted twice |
| BI-601 loses its amount | Incomplete-value warning | Missing amount becomes zero |
| Two contacts linked to BI-602 | One opportunity amount | Relationship multiplies rows |
| USD opportunity added | Separate currency or documented conversion | Mixed-currency total |
| No recent extraction | Old information clearly dated | Fresh-looking but stale dashboard |
Keep update cadence separate from historical comparison
Set the cadence around the meeting and the source’s availability. The refresh settings documentation describes automatic operation; the selected plan determines the available intervals. A configured schedule does not prove that the latest run delivered useful new data.
A replaced dataset is the latest available state. A weekly movement chart needs dated observations held separately. If you append observations, define the observation identifier and check for repeated captures. Current records alone do not reconstruct every previous pipeline stage.
Save the measure definitions used for each comparison. Adding a new pipeline or including a previously excluded stage changes the method. Mark that change so an apparent improvement is not presented as sales progress when it is actually an expanded population.
Finish the meeting with actions in the sales system
The dashboard should lead to named actions: verify the proposal amount, assign an owner or confirm the next buyer discussion. Those actions remain in the CRM. The dashboard is a reading layer, not a second ungoverned sales record.
Our founder-led sales guide for EngageBay discusses how commercial records support decisions in a different operational setup. It does not establish that EngageBay has the same Coupler.io source as the HubSpot flow used here.
Before adding another metric, ask what the meeting reader will do differently after seeing it. If nobody can answer, leave it out of the first version. A smaller dashboard with traceable measures and a clear next owner is easier to maintain and more useful than a page of figures whose origins are uncertain.
FAQ : practical questions
Is open pipeline the same as revenue?
No. It represents possible sales under the chosen inclusion rules. Closed sales, recognised revenue and cash receipts need separate definitions and supporting data.
Why did the total rise after adding contacts?
Inspect rows per opportunity identifier. A one-to-many relationship can repeat an opportunity amount. Keep the detail separate or aggregate at the correct level before totalling.
Should I weight pipeline stages?
Only with a justified, visible method. This guide supplies no reliable stage probabilities. Start with unweighted values and counts that the team can reconcile.
Can the latest extract show historical movement?
Not by itself. Historical movement requires comparable dated observations. A current-state copy does not automatically preserve previous states or earlier measure definitions.
Choose your next step
Build the page around three known opportunities and check the amounts, exclusions and extraction date. Once those agree with the meeting’s questions, evaluate the flow and subscription needed to maintain it. The following affiliate link opens Coupler.io’s website.
Explore Coupler.io for your sales dashboard data
Official sources
Checked on 14 October 2026.