Nobody should have to remember to run the weekly report.
A Make.com scenario that pulls GA4 and HubSpot every Monday, writes the week to a historical log without duplicating it, and sends a rule-based executive digest — designed to keep Project 3's dashboard alive without anyone touching it, on free tiers only. Fully designed; not yet built.
The problem
Project 3 turned four systems into one executive dashboard — but a dashboard is only as current as the last time someone manually re-exported and re-pasted the data behind it. Project 4 named this limitation directly: HubSpot's free tier has no Sheets sync, so every update means re-exporting CSVs by hand, and nothing stops that habit from quietly lapsing. This project's brief was to remove the hands entirely: pull the same kind of marketing data on a schedule, log it so trends become visible instead of overwritten each time, and have the system flag a number that looks wrong — rather than wait for someone to notice the dashboard went stale.
What I set out to build
- One Make.com scenario, one weekly schedule, zero manual steps after setup — not a snapshot re-exported by hand.
- A historical log in Google Sheets that updates or skips a given week on re-run, and never creates a duplicate row.
- Week-over-week deltas computed once, in Sheets formulas, so the dashboard and the digest never disagree about a percentage.
- A rule-based executive digest — largest positive and negative deltas, no paid AI service — so a stale pipeline becomes an impossible-to-miss failure instead of a silent one.
- An operations budget worked out in credits against Make's actual free-tier cap, not assumed.
How the pieces fit together
The system runs as two layers that meet at one place: the Historical Performance Log. One layer pulls and writes; the other reads and reports.
Track A — Write layer (Make.com)
One scheduled scenario reads GA4 and HubSpot through their APIs, checks the log for an existing row, and writes or updates it — never appends blindly.
Track B — Read layer (Sheets + Looker Studio)
WoW formulas and digest-ranking logic live in the Sheet, so Looker Studio and the weekly email read the same numbers instead of each recalculating them separately.
Five layers, each with one job:
Stack
The build, phase by phase
Phases 0–2 — designed in full, not yet executed
The first three phases are written click-by-click: confirming GA4 and HubSpot access, designing the Sheet schema, and proving all three Make connections before any real logic gets built. None of it has run in my own accounts yet — this project starts from a complete plan rather than a blank canvas.
Make's three test modules (GA4, HubSpot CRM, Google Sheets) each returning real data on "Run once," before being deleted and rebuilt as the real scenario.
GA4 — pulled live, not snapshotted
Unlike Project 4's one-time GA4 export, this pipeline reads GA4's Data API directly every week through Make's "Generate a Report" module, filtered to sessionDefaultChannelGroup so each channel gets its own log row. The trade-off Project 4 avoided by snapshotting — a live connection that can silently break — is handled here with Phase 9's planned failure notifications instead of a human checking in.
The GA4 "Generate a Report" module configured with Property ID, date-range logic, and the channel-group dimension, returning a real weekly bundle.
HubSpot — read through Make's API, no CSV step
Project 4's dashboard depends on someone remembering to export and re-import HubSpot CSVs by hand — a limitation it names directly. This pipeline reads the same lifecyclestage and deal data through Make's HubSpot CRM search modules on the same weekly schedule as GA4, removing that manual step entirely.
The HubSpot "Search for Contacts" and "Search for Deals" modules, filtered to the current week, returning real counts.
The idempotency key — decided before any code
Each log row carries a Row_Key formula — the week's date joined to its channel on the GA4 tab, the date alone on the HubSpot tab — so Make can check what already exists before writing. Re-running the same week updates or skips it; it never duplicates. Wide format (one row per week per channel) was chosen over long format specifically because it keeps that check to one lookup per tab per run, not one per metric.
The log after two consecutive runs for the same week: the row count unchanged, Last_Updated_UTC changed.
The operations budget — a worked estimate, not a guess
Before writing a single module, I built a credit table against Make's documented pricing: roughly 19–27 credits per run, 82–117 a month in steady state, against the free plan's 1,000-credit cap. The one honest gap: Make's own documentation doesn't say definitively whether its Iterator module bills per row in addition to the module that processes each row — the estimate covers both cases rather than quoting the flattering one.
What's left
Phases 3–11 — the GA4 pull, the HubSpot pull, idempotent writes, WoW formulas and digest-ranking logic, the Looker Studio connection, digest delivery, scheduling and error handling, testing, and portfolio evidence capture — are roadmapped with time estimates per phase, not yet executed.
What Phase 10 will check
This hasn't been run yet, so this section is the test plan, not test results. These are the checks Phase 10 specifies, with blanks where the real numbers go once it executes.
- 1No duplicate rows. Run the scenario twice against the same week; confirm the log gains the expected rows once, not twice. [result: pending]
- 2Empty week. Run it against a week GA4 or HubSpot returns nothing for; confirm the scenario skips cleanly rather than writing blank or zero rows that would corrupt the WoW formulas. [result: pending]
- 3Broken connection. Revoke one connection mid-build; confirm Phase 9's failure notification fires instead of the scenario failing silently. [result: pending]
- 4WoW arithmetic. Spot-check one channel's week-over-week delta by hand against the Sheet's formula output. [result: pending]
What I'd tell a hiring manager directly
Decisions and reversals
The instructive moments so far were all in the design, not a debugging session — because there's nothing running yet to debug.
- Almost defaulted the GA4 source to the demo account, reasoning a real but near-empty portfolio site would make for thin channel-breakdown rows — reversed that once the actual property turned out to have enough weekly traffic to carry a real comparison.
- Initially planned to pull both the current and prior week fresh from GA4's API every run, per a literal reading of the brief — then realized the historical log already holds last week's numbers after the first run, so comparing against the stored row instead roughly halves the GA4 credits spent per week.
- Assumed Looker Studio had a native, free HubSpot connector; confirmed it doesn't — which is the actual reason HubSpot data routes through the same Sheet as GA4 rather than a second, paid connector.
Open at the time of writing
Three things are unresolved because the build hasn't started: whether Make's Iterator module bills credits per row in addition to the write module it feeds (the budget above covers both cases, but I don't yet know which is real); how Phase 7 should connect this Sheet to Project 3's dashboard without disturbing its existing live-GA4 data source; and whether the GA4 demo account would in fact have worked as a Make data source — the real property had enough traffic that the fallback was never tested.
Limitations
- Nothing here has run yet. Every number on this page — the ops budget, the schema, the phase count — is a design artifact, not a measurement.
- Weekly cadence only; between runs, the only fresher view is Looker Studio's optional live GA4 connector, not this pipeline.
- Idempotency is scoped to one week at a time; a schema change to the log after go-live would still need a manual backfill pass.
- Built against demo/mock HubSpot data, same as Project 4 — the digest's "wins" and "concerns" will reflect data variety, not real marketing performance, until this runs against a live business.
What I'd build next
- Execute Phases 3–11: the GA4 and HubSpot pulls, idempotent writes, WoW formulas, the Looker Studio connection, digest delivery, scheduling and error handling, and testing.
- Backfill 8–12 weeks of history so the dashboard and digest have a real trend to show, not a single data point.
- Replace the estimated credit table in this page with Make's actual execution-history numbers after the first few live runs.