Weekly contractor gross margin brief posted to Slack

Every Monday at 7am, write me a gross margin brief on last week's contractor book, using Timesheet Portal as the source, a Google Sheets tracker as the running history, and Slack as the place the brief lands.

Let me set three things before the first run: the target gross margin percentage we hold placements to, the Google Sheets spreadsheet to use as the margin tracker, and the Slack channel to post into. Treat the target margin as a setting I control rather than a number baked into the workflow, because our pricing changes.

Start by resolving the report fields. Timesheet Portal report endpoints take a reportFields array of numeric field ids, so call List Report Fields first and look up the ids for the fields I need: client, cost centre, contractor or worker, placement or job, billable hours, pay cost and charge value. Do not guess these ids, and do not hardcode them from a previous run, because they vary by account and edition.

Then call Get Time and Cost Report for last week, meaning the Monday to Sunday that just finished, passing the resolved field ids. Set useIsoDateFormat to true on the report settings so dates come back as yyyy-MM-dd rather than a locale format. Page through the results until a page returns fewer records than the last one or comes back empty.

The report returns codes rather than readable names, so call List Clients and List Cost Centres and use them to map client codes and cost centre codes to real names. Everything downstream, in the sheet and in the Slack brief, should use the human names.

Now compute margin. For each client, and separately for each placement, total the charge value and the pay cost, then work out gross margin as charge value minus pay cost, and margin percentage as that gap divided by charge value. Keep billable hours alongside so I can tell whether a change came from volume or from pricing. Skip anything with zero charge value rather than reporting an undefined or negative percentage.

Append one row per client per week to the tracker using Append Values, so history accumulates instead of being overwritten. Each row should carry the week start date, week end date, client name, cost centre, billable hours, pay cost, charge value, gross margin and margin percentage. If the sheet is empty on the first run, write a header row first. Never overwrite or clear earlier rows.

Read the prior weeks back out of the same spreadsheet with Get Values and use them as the comparison base. The sheet is deliberately the memory layer here: Timesheet Portal's report endpoints are capped at roughly 24 requests per hour and the API is meant for synchronisation rather than as a live backing store, so pull last week only from Timesheet Portal and get all earlier weeks from the sheet. Compare against the immediately preceding week for the headline movement, and against the last four to six weeks where that helps separate a real trend from a one week blip.

Then post a written brief to the Slack channel with Send a Message. Lead with the single thing worth acting on this week, stated in one line before anything else. Follow it with about five bullets covering the clients whose margin percentage moved most and in which direction, any placement now billing below our target margin, and the individual contractors driving the biggest swings. Close with a small table of the top clients showing hours, charge, pay, margin percentage and the change in percentage points against the prior week.

Always name the specific clients, placements and contractors rather than talking in aggregates. "Northwind Logistics fell 6.1 points, driven by two weekend placements" is useful; "margin softened across the portfolio" is not. Where a swing has an obvious cause visible in the data, such as overtime hours at an uplifted pay rate with no matching charge uplift, say so. If a client had no billable hours last week, leave them out of the comparison rather than reporting a total collapse. Keep the whole thing short enough to read in the Monday meeting, and format it as Slack mrkdwn, using single asterisks for bold.