One page an executive can read in thirty seconds, and a trail behind every number on it.

The problem

Projects 1 through 3 each answer one part of the same question. The tracking system knows where traffic and leads come from, the consent system knows who has agreed to what, and the HubSpot lifecycle engine knows where each contact sits and what each deal is worth. An executive asking "how is marketing doing?" would have to open GA4, HubSpot's contact list, HubSpot's deal board and a UTM spreadsheet, then reconcile them in their head. The brief was a single reporting layer a non-technical reader can scan at a glance, without any of the three upstream systems being rebuilt or paid for.

What I set out to build

• One page, four sections — Traffic & Attribution, Lifecycle, Consent & Compliance, Pipeline & Revenue — mapped one-to-one to the three earlier projects plus the deal data.
• A single Google Sheet as the model layer, so Looker Studio connects to one place and the logic behind every number can be inspected cell by cell.
• KPI formulas that find columns by header name rather than column letter, so a re-export with a different column order doesn't silently break a number.
• A reconciliation log that checks each headline figure against the system it came from before the dashboard is shared.
• A public link that shows aggregates only, while the Sheet holding contact-level data stays private.

How the pieces fit together

The system runs as two layers that meet at one point: the Blended_Master tab, where five source tabs become a single row of KPIs.

Five layers, each with one job:

Stack

What's actually running

Inputs — one export per source, columns chosen on purpose

HubSpot's free tier has no Sheets sync, so each source arrives as a CSV export. The export dialog uses "Properties and associations in your view," which means the columns in the table are the columns in the file — so the table is set up first, then exported. The dialog below shows the contacts export covering all 25 contacts in one file.

GA4 traffic — a snapshot, not a live connection

GA4's own Share → Download file → Export to Google Sheets produces a "Reports snapshot" covering 8 September to 5 October 2026. A snapshot was a deliberate choice over a live GA4 connector: a published dashboard has to keep working for a recruiter who has no access to the GA4 property. The export arrives as several stacked tables, and the first cleanup step is making the Session source/medium table the header row of the GA4_Traffic tab.

Consent — Project 2's properties, read as columns

Project 2's consent data lives in HubSpot contact properties in the Consent & Privacy group. Rather than rebuild anything, the dashboard reads those same properties through the contacts export and counts them: how many contacts have a cookie-consent status, how many have granted it, how many are unsubscribed.

The blend — one row, twelve KPIs, found by header name

Blended_Master holds the whole executive view as a single row. Every formula looks up its column by header text with INDEX and MATCH, so the numbers follow the data rather than the column letters. Rates are computed here, once, so the dashboard never recalculates them differently from the Sheet.

Looker Studio — six sources, one job each

Each Sheet tab is added as its own embedded Google Sheets data source. Blended_Master feeds the twelve scorecards; Lifecycle_Attribution, Consent_Status, GA4_Traffic, UTM_Attribution and Deals_Pipeline each feed one chart. All six report a Working status.

The dashboard

Four labelled sections run left to right: Traffic & Attribution, Lifecycle, Consent & Compliance, Pipeline & Revenue. Scorecards sit on top. Below them are five charts — sessions by source, leads by channel, pipeline amount by deal stage, contacts by lifecycle stage and cookie consent status.

Proving it, not asserting it

Every headline number was compared against the system it came from. Three checks agreed. Three did not — and those are listed in the next section rather than quietly corrected in the screenshots.

1 — Contacts: HubSpot to dashboard

The contacts export reports 25 rows in one file (see the export dialog above). The Total Contacts scorecard reads 25, and the lifecycle pie's slices — Lead 80%, Marketing Qualified Lead 8%, Opportunity 8%, Customer — account for those same 25 contacts, with the single Customer matching the Customers scorecard.

2 — Deals: HubSpot deal board to dashboard

The HubSpot deal board shows three deals, with Closed Won at $45,000 and Closed Lost at $0. The dashboard reads Total Deals 3 and Closed Won Amount 45,000. Total Pipeline Amount is 105,000: the $45,000 won deal plus the $60,000 Tanaka Logistics deal documented in Project 3, still at Appointment Scheduled.

3 — Key events: GA4 to dashboard

Key events across the seven source/medium rows in the GA4 snapshot — 46, 59, 37, 6, 8, 8 and 4 — add up to 168. The Total Key Events scorecard reads 168.

4 — What the consent chart shows

The cookie-consent pie shows 84% of contacts with no recorded status, 12% Granted and 4% Not Yet Presented. That is a finding in its own right: on this data set, most contacts have no cookie-consent status on record at all.

What I'd tell a hiring manager directly

Challenges

The instructive problems were all in the seams between systems, not in the dashboard itself. The GA4 export arrives as several tables stacked in one sheet, so a formula that sums a whole column also sums the second and third tables. The clearest example: the Sessions figure in Blended_Master is 117, while the GA4 snapshot's own total is 52 — the 52 from the first table, plus 13 from the active-users table, plus 52 again from a repeated sessions table. Separately, I assumed HubSpot's Original Source and its two Drill-Down fields could be added as columns; they are built-in, read-only fields, so a hand-made property of the same name just stays empty. Attribution therefore comes from Project 1's UTM data instead, which is richer than the "Offline Sources" value HubSpot would have assigned to imported test contacts. A smaller snag: the Deals page opens in board view, where Edit columns doesn't exist; it only appears in table view.

Limitations

• Refresh is manual. HubSpot's free tier has no Sheets sync, so every update means re-exporting CSVs and re-importing them; the GA4 figures are a snapshot covering 8 September to 5 October 2026.
• There are no row-level joins across systems. Lead Conversion Rate divides leads from the UTM dataset by GA4 sessions from a different window, so it is directional, not a true funnel rate.
• The upstream projects were built under different fictional company names — the GA4 property and the HubSpot deals don't belong to one company — so this shows a reporting layer, not one company's performance.
• The dashboard has no date control, because the Sheet holds snapshots rather than dated rows.
• The Sheet contains contact-level data, which is why it stays private and the public link relies on the owner's credentials. Anyone with the link can see the aggregates, and nothing else.
• This is an educational portfolio project, not a reporting system for a company with real stakeholders.

What I'd build next

• Clean the GA4_Traffic tab down to a single table and re-point the Sessions formula, then re-run the reconciliation until every check agrees.
• Fix the Consent Granted formula and set the rate scorecards to the Percent type.
• Add a shared campaign key so sessions and leads can be joined and conversion measured end to end.
• Add dated rows and a date-range control so trends over time become possible.
• Replace manual re-exports with a scheduled connector once a paid tier is justified.