Marketing scoreboard for JobNimbus lead sources and ad spend

Build me a marketing scoreboard app for my roofing company that the owner and the office manager open every week to see which lead sources actually pay for themselves. JobNimbus stores only a basic lead source field and cannot show revenue by channel, so the app has to do all of the joining itself: leads and sold work from JobNimbus, paid spend from Google Ads, and every other kind of spend from a spreadsheet tab I maintain.

The main tab is the channel scoreboard. Load leads using the JobNimbus List Contacts and List Jobs operations and group them by their lead source field. Follow each lead through to sold work with List Estimates, List Invoices and List Payments, matching those records back to the contact or job they belong to (records related to a record can be filtered by related.id, and JobNimbus dates are Unix epoch seconds). Count a lead as a sold job when it has an approved estimate or an invoice, and count revenue as payments received against those invoices. Show invoiced and collected as two separate figures so nobody confuses booked work with money in the bank.

Join that revenue against spend. Pull campaign level spend for the same period with the Google Ads Campaign Performance Report operation, and let me map campaigns to lead source names in a mapping screen the app saves, so a campaign named for a service line still rolls up under the right channel. Read offline spend from a Google Sheets tab using Get Values, where I log yard signs, door knocking, mailers and lead marketplaces by channel and month. Meta and the other ad platforms are not connected, which is exactly why the spreadsheet tab has to carry every non-Google channel.

For every channel show leads, sold jobs, spend, cost per lead, cost per sold job, revenue and return on spend, with the current month in one column and the prior month beside it plus the change between them. Make the table sortable and let me click a channel to see the underlying leads and jobs, so I can sanity check a number instead of taking it on faith. Roofing reference points are useful as an editable comparison line rather than a verdict: search leads around 124 dollars each on non branded terms and 44 on branded, Local Services Ads in the 75 to 150 range, Performance Max around 64, and a blended return around 2.5 times spend.

Two rules matter more than anything else in the math. First, roofing sales cycles run about a month, so attribute revenue to the month the lead came in rather than the month the job closed. A lead that arrived in February and closed in March belongs in February, next to the February spend that bought it. Second, do not rank channels on tiny samples. Any channel under a configurable minimum (default five leads in the period) gets labelled too small to judge and sits below the ranked channels, with its raw numbers still visible.

The second tab is a cleanup worklist of leads that arrived with no source attached, which in home services is roughly four out of every ten phone leads. List the unattributed contacts and jobs with whatever context helps identify them (name, created date, status, assigned rep, first activity), let the office manager pick a source from a dropdown populated by the valid lead sources in Retrieve Account Settings, and write it straight back with Update a Contact or Update a Job. Keep the row on screen with a saved confirmation, show how many are left, and let her filter by month and rep so she can work a batch in one sitting.

Add a Write this month's marketing review button that starts a background agent. The agent should compare every channel against the prior period and against my target cost per sold job (a setting I can edit in the app), take the too small to judge rule seriously instead of crowning a winner on three leads, and explain in plain language, no jargon, where to shift budget next month and what to stop paying for. It should also call out how much of the month's revenue sits in leads with no source, since that caps how much anyone should trust the ranking. Save the finished review inside the app so it stays readable in a history list of past reviews, and post it to our team channel with the Slack bot Send a Message operation, with a short summary in the message itself rather than a bare link.

Everything the app remembers should live in the app: the campaign to channel mapping, the target cost per sold job, the minimum lead count, the spreadsheet and Slack channel settings, and every saved review. The scoreboard should load fresh numbers when it is opened, since the whole point is that we look at it weekly and adjust rather than waiting for a monthly report.