Weekly LinkedIn Ads pipeline ROI report in Slack

Every Monday at 8am, build a LinkedIn Ads pipeline ROI report that ties our ad spend to actual HubSpot revenue, and post it to Slack. The trigger is a cron schedule (LinkedIn Ads doesn't have a poll trigger and this report is inherently a weekly cadence).

Step one: call LinkedIn Ads Get Ad Analytics for the last 30 days pivoted by campaign, and pull spend, impressions, clicks, and lead form submissions for each campaign.

Step two: call HubSpot Search Deals filtered to deals that were created or moved to closed-won in the same 30-day window, where the original source, lead source, or utm_source property indicates LinkedIn. For each matching deal, read the first-touch campaign property or utm_campaign property so we know which LinkedIn campaign drove it.

Step three: match HubSpot deals back to LinkedIn campaigns. Prefer exact match on utm_campaign or campaign ID where present; fall back to fuzzy match on campaign name when only the name is available. Then compute per campaign: spend, attributed open pipeline value, closed-won revenue, deal count, cost per deal, and payback ratio (closed-won revenue divided by spend).

Step four: send a Slack message (Send a Message) that summarizes: the top three campaigns funding pipeline, the bottom three campaigns spending without producing deals, any campaigns that have crossed a configurable spend threshold with zero attributed deals, and a short paragraph recommending where to shift budget.

Two important rules. First, call out attribution gaps explicitly: list the count and total value of HubSpot deals that have LinkedIn as their source but no campaign ID, rather than silently dropping them. Second, never recommend pausing a campaign that has less than two weeks of spend history.

Configurable inputs the workflow should expose: the Slack channel to post to, the reporting window (default 30 days), the HubSpot deal properties that identify LinkedIn as the source and that carry the campaign name or ID, the spend threshold that flags a campaign as burning budget with no deals, and the minimum spend history before a pause recommendation is allowed.