Weekly lead source ROI report for Workiz and Google Ads

Every Monday at 7am, tell me which of my lead sources are actually making money instead of just making noise. The report covers the previous full week, Monday through Sunday.

Start with Google Ads. Use the Campaign Performance Report operation to pull campaign level clicks, impressions, cost, and conversions for that week. Cost comes back in micros, so divide by one million to get real currency amounts.

Then pull the same period out of Workiz. Use List Leads for every lead created during the week, and List Jobs for the jobs. Both list endpoints return at most 100 records per call, so page through with the offset until you have the whole week. Workiz has no standalone invoice endpoint, so take job value from the amount fields on the job records themselves.

Match the lead source values on the Workiz records against the Google Ads campaign names. Lead source naming is rarely an exact match, so match on close names and treat anything you cannot confidently map as its own unmapped source rather than forcing it into a campaign. When attributing a booked job, prefer the source field on the job record; if jobs do not carry one, match a job back to its lead on client name or phone number.

For every source, work out the number of leads, how many became booked jobs, total job value won, cost per booked job, and return on ad spend. Count a job as booked when its status shows it was scheduled, completed, or otherwise won, and exclude cancelled work. Sources with no matching Google Ads campaign, such as referrals or repeat customers, should still appear with their leads, booked jobs, and job value, leaving cost and return on ad spend blank.

Before writing anything, read the existing rows on the Google Sheets tracking tab with Get Values so you have the prior week's numbers for the same sources. Then append one row per source for the new week using Append Values, starting each row with the week start date, so the tab builds a week over week trend.

Finally, post a summary to Slack with Send a Message. Name the best and worst performing sources, give a concrete recommendation on where to shift budget, and flag any source whose cost per booked job rose more than 25 percent against the prior week's row. Keep it short enough to read on a phone.