Executive Marketing Performance Dashboard

An executive reporting layer that pulls the outputs of the first three projects — GA4 traffic and UTM attribution, HubSpot consent status, lifecycle stages and deals — into one Google Sheet and one Looker Studio dashboard. Twelve KPI scorecards and five charts, each tied back to a named source tab, built entirely on free tools and checked against the source systems instead of trusted on sight.

GA4 + HubSpot Exports

The actual data path this page describes — each hop below is backed by a real screenshot, including the places where the numbers disagreed with the source.

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.

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.

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.

Raw exports land untouched in their own tabs. One tab, Blended_Master, holds twelve named KPIs as header-matched formulas. A QA log tab records each check against GA4 and HubSpot.

Looker Studio reads the Sheet through six embedded data sources: Blended_Master drives all twelve scorecards, and each of the other five tabs drives exactly one chart.

Five layers, each with one job:

GA4 Traffic acquisition report (Project 1), the UTM Reporting Dataset (Project 1), HubSpot contact exports for lifecycle and consent (Projects 2 and 3), and the HubSpot deals export (Project 3).

GA4_Traffic, UTM_Attribution, Lifecycle_Attribution, Consent_Status and Deals_Pipeline — one tab per source, imported as-is so any number can be traced back.

Blended_Master turns the five tabs into one row of twelve KPIs. No row-level joins across systems, by design — GA4 sessions are anonymous and HubSpot contacts are not, so there is no honest shared key.

One Looker Studio report, four labelled sections, scorecards on top and charts below.

Public view link on the report; data sources use the owner's credentials so viewers never need access to the Sheet itself.

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.

The contacts export: All contacts, 25 rows in 1 file, CSV, properties limited to the columns in the view.

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.

The raw export: 52 sessions, 13 active users, and several tables stacked in one sheet.

GA4_Traffic after the header cleanup — the first table now starts in row 1; the later tables from the export are still below it.

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 consent properties from Project 2, all in HubSpot's Consent & Privacy group — the dashboard reuses them instead of duplicating them.

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.

Blended_Master, with the Total Sessions formula visible in the formula bar and all seven tabs along the bottom, including QA_Log.

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 six data sources: Blended_Master used by 12 charts, the other five by one chart each.

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.

The dashboard as built, in Looker Studio's View mode.

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.

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.

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.

The source of truth for check 2: three deals, $45,000 Closed Won, one Closed Lost at $0.

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.

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.

On business impact: this is demonstrated capability, not measured business impact. The figures come from small test data — 25 contacts, 3 deals and a 52-session GA4 snapshot — behind fictional companies. What's demonstrated is a reporting layer that, pointed at real exports, would give an executive one reconciled page instead of four tools.

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.

The first, scorecards-only build. Total Sessions and Consent Granted Rate already read 0 here, and the later full-page view still shows them that way.

Three disagreements between the dashboard and its sources are not yet resolved: Total Sessions reads 0 on the dashboard and 117 in Blended_Master, against 52 in GA4; Consent Granted Rate reads 0% on the scorecard while the consent pie shows 12% of contacts as Granted; and the two rate scorecards display raw decimals (0.04, 0.03) rather than percentages. The Sessions-by-Source chart is affected by the same stacked GA4 tables — its categories include the table headers. I'm listing them here because a dashboard that hides its own discrepancies is the exact problem this project exists to prevent.

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.

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.