Which marketing channels actually produce pipeline, not traffic

Build me an app that finally answers which marketing channels produce pipeline, not just traffic. The main screen is a single table with one row per acquisition channel and campaign, traffic columns on the left, business outcome columns on the right, and derived efficiency columns that decide the ranking.

At the top of the screen is a date window picker, defaulting to the last 90 days. Everything on the page is scoped to that window. Resolve the Google Analytics property with List Account Summaries rather than asking anyone to paste a numeric property ID; if the credential can see more than one property, let the viewer choose once and remember the choice.

The traffic side of each row comes from Google Analytics Run Report (GA4) for the selected window, using the dimensions sessionDefaultChannelGroup, sessionSource, sessionMedium and sessionCampaignName against the metrics sessions, engagedSessions and keyEvents. Page through the results with offset and limit so large accounts are not truncated, and show sessions, engaged sessions and key events as plain numbers.

The business outcome side of the same row comes from HubSpot Search Deals, filtered to deals whose create date falls inside the same window, paging through the cursor until every deal is collected. Read each deal's original source properties (hs_analytics_source, hs_analytics_source_data_1 and hs_analytics_source_data_2) plus dealname, amount, dealstage, pipeline and closedate. Group the deals by original source and campaign, and show three columns per row: deals created, open pipeline value (the total amount of deals still open) and closed won revenue (the total amount of deals in a closed won stage).

Matching Google Analytics channel names to HubSpot source values is the fiddly part and no automatic join is reliable, so the app keeps an editable mapping table stored in a tab of a Google Sheet. Read it on load with Get Values, let people edit it inside the app on a Mapping screen, save edits back with Update Values, and add brand new rules with Append Values. Each mapping row pairs an analytics channel, source, medium or campaign pattern with one or more HubSpot source values. Never quietly drop a deal that does not match: collect everything unmatched into a clearly labelled unmatched bucket shown under the table, listing the raw source values, the deal count and the money involved, with a one click action on each unmatched value that appends a new mapping row so it lines up from then on.

The derived columns are the point of the whole thing. Compute deals per thousand sessions and revenue per thousand sessions for every row, and make the table sortable by any column so the channel with the most traffic stops automatically looking like the best channel. Add a flag on rows where traffic is high but pipeline is near zero: any row above a viewer adjustable session threshold whose deals created is zero or whose revenue per thousand sessions is far below the blended average gets a visible marker and can be filtered to on its own.

Whoever is looking should be able to hand the top of this screen to a CFO without editing it. Above the table put a plain summary strip for the selected window: total sessions, total deals created, total open pipeline, total closed won revenue, blended revenue per thousand sessions, and the best and worst channel by revenue per thousand sessions. Use plain labels and formatted currency, no jargon and no metric codes.

Every row has an Investigate this channel button that starts a background agent for that channel. The agent runs more Run Report (GA4) calls scoped to that channel: landing pages using the landingPage dimension with sessions and key events, a month by month trend over the last six months, and a device mix using deviceCategory. It then pulls that channel's specific deals from HubSpot with Search Deals using the mapped source values, and writes a short findings brief covering what the channel actually brings, where that traffic lands, which deals it generated, whether it is trending up or down, and one clear recommendation. The brief is stored in the app so the row shows the latest findings with its date and the full text opens in a panel. The agent also files the same brief as a HubSpot note using Create Note, associated with the largest deals that channel produced (the top three by amount), so sales sees the context on the record.

Add a Save snapshot action that appends the current table, dated, to a snapshot tab in the same Google Sheet with Append Values, so month over month comparisons build up over time.

Google Analytics is read only in this app, so every write goes to HubSpot notes or the Google Sheets mapping and snapshot tabs. Deal attribution must use HubSpot's original source properties, and the mapping table must stay user editable and obvious, because it is the honest answer to a join that cannot be automated.