Weekly contractor gross margin report for staffing agencies

PromptCreate

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.

Example output

*Contractor margin brief: 28 Jul to 3 Aug* Act on this first: Northwind Logistics has fallen to 14.2% margin, below your 20% target, and it is rate driven rather than volume driven. • Northwind Logistics fell 6.1 points to 14.2%. Danny Osei and Priya Raman both billed weekend hours at an uplifted pay rate with no matching charge uplift. • Halcyon Care rose 3.4 points to 27.8% after the Mercer placement moved onto the new charge rate agreed in June. • Two placements are now billing below target: Northwind / D. Osei at 11.9% and Northwind / P. Raman at 15.6%. Both are worth a rate conversation this week. • Brightline Group held steady at 24.1% across 6 contractors, no action needed. • Total billable hours were 1,284, up 42 on the prior week, so the margin drop is pricing rather than lost volume. Client | Hours | Charge | Pay | Margin | Change Northwind Logistics | 412 | 18,540 | 15,907 | 14.2% | -6.1 Halcyon Care | 388 | 16,296 | 11,766 | 27.8% | +3.4 Brightline Group | 484 | 21,296 | 16,163 | 24.1% | +0.2

What does this prompt do?

Pulls last week's billable hours, pay cost and charge value for every contractor placement from your Timesheet Portal account.
Works out gross margin by client and by placement, then adds one row per client to a running spreadsheet so history builds up week after week.
Reads the previous weeks back out of that spreadsheet and compares them, so you see movement rather than a single snapshot.
Posts a short written brief to Slack naming the specific clients, placements and contractors behind the biggest swings, led by the one thing worth acting on.

What do I need to use this?

A Timesheet Portal account, with an administrator login that can access reporting
A Google account and a spreadsheet to use as your margin tracker
A Slack workspace and the channel where the brief should be posted
The target gross margin percentage you hold placements to, so the brief knows what counts as underperforming

How can I customize it?

Change the day and time. Monday at 7am suits a Monday management meeting, but a Friday afternoon wrap up works just as well.
Set your own target margin percentage. Anything billing below it gets called out by name in the brief.
Choose the channel and how much detail you want, from a short headline for leadership to a fuller placement level breakdown for the delivery team.
Narrow the scope to specific clients, branches or cost centres if you only want to watch part of the book.

FAQs

Do I need to build the tracker spreadsheet first?

Just create an empty spreadsheet and point the workflow at it. It adds one row per client per week, so the sheet fills itself in over time. The first run gives you a baseline and the comparisons get richer from the second week onward.

Why does it keep history in a spreadsheet instead of just asking Timesheet Portal each time?

Timesheet Portal limits how often reports can be run, so refetching months of history every week is not practical. Keeping a running sheet means the comparison is instant, you own the record, and you can chart or pivot it yourself whenever you want.

Can I change what counts as a bad margin?

Yes. The target margin is a setting you choose rather than a fixed number, so you can set it to whatever your business runs to and change it whenever your pricing does.

Will it work if we track work by branch, division or cost centre?

Yes. It reads your client and cost centre lists so the brief refers to real names rather than internal codes, and you can group or filter the reporting around them.

What happens for a client with no billable hours last week?

They are simply left out of that week's comparison rather than being reported as a total collapse in margin. The brief focuses on placements that actually had activity.

Do we need a paid Slack plan?

No. The brief posts as a normal message into whichever channel you pick, which works on any Slack plan.

Related templates

Auto-fix your calendar when a flight slips, and flag what's at risk

When your flight moves, your calendar times get corrected automatically and you get a Slack note naming the meetings you're about to miss.

Agentic Task

Trace phishing emails to the sending IP and report abuse

Every 15 minutes, forwarded phishing reports get traced back to the server that really sent them, with a verdict in Slack and the worst senders reported.

Agentic Task

Weekly Amazon S3 bucket security audit posted to Slack

Every Monday, check every S3 bucket for public exposure, missing encryption and weak backup settings, then get the risks ranked in Slack.

Agentic Task

Turn procurement portal tenders into CRM deals each morning

Every weekday at 7am, sign in to the tender portals you track, filter new notices against your bid criteria, and open a deal for the ones worth chasing.