Monthly ServiceM8 job margin and underquoting report

On the first business day of each month, review job profitability across the ServiceM8 jobs I completed in the month that just ended, and tell me where I am systematically underquoting. I care about the pattern far more than any single job.

Start with List Jobs in ServiceM8 and narrow to jobs that were finished inside the previous calendar month. ServiceM8 deletes are soft, so records with active set to 0 still come back over the API. Filter those out everywhere, on jobs, material lines and labour records alike, or the numbers will be wrong. Job lists are cursor paginated, so page through until you have the whole month rather than stopping at the first batch.

For each job, build the real margin rather than trusting the invoice total. Call List Job Materials for the job. Each material line carries both a charge out price and a cost price, so sum the charge out side for revenue and the cost side for what the materials actually cost me. Then call List Job Admin Activities for the same job to get the labour time actually logged against it. Convert those logged hours into a labour cost using an hourly cost rate I can set at the top of the workflow, defaulting to 75 per hour if I have not set one. Job margin is revenue minus material cost minus labour cost, and I want it as both a currency figure and a percentage.

Group the results two ways. Use List Categories to resolve each job's category so I can see margin by job type, and use List Staff Members to resolve the staff member who ran the job so I can see margin by person. Roll up average and median margin for each category and each staff member, and always note how many jobs sit behind each number, so I know which groupings are meaningful and which rest on one or two jobs.

Append one row per job to my Google Sheets margin tracker using Append Values, so margin can be trended month over month. Each row should carry the month, job number, client, job category, staff member, revenue charged, material cost, labour hours logged, labour cost, margin in currency, margin percentage, and a flag for any job where materials were logged but no labour was recorded at all. Always append rather than overwrite, so history builds up in the same sheet.

Then post a narrative summary to Slack with Send a Message. Lead with the overall picture for the month: total revenue, total cost, blended margin, and how that compares with the previous months already sitting in the tracker. Then give me the three things I actually care about. First, the job categories that consistently run over the hours I charged for, judged across the whole category rather than on a single bad job. Second, the jobs where materials were logged but no labour was recorded at all, because those jobs look artificially profitable and their real cost is unknown. Third, one concrete suggested price adjustment for the worst performing category, expressed as the percentage increase or the extra quoted hours needed to bring that category back to a healthy margin, with the arithmetic shown so I can sanity check it.

Keep the Slack message short enough to read on a phone. Call out patterns and name the categories and people involved rather than listing every job, since the per job detail already lives in the spreadsheet. If a month has too few completed jobs to say anything meaningful, say so plainly instead of inventing a trend.