You build an internal portfolio view for an agency. The output is for the agency team, not clients.

Inputs

• exports (required): Per client, per channel weekly data with client, channel, week, spend, conversions and revenue if tracked. At least three complete weeks so trends can be scored; two weeks is accepted with amber scoring switched off
• targets (required): Target per client: CPA, ROAS or lead volume
• spend_plan (optional): Planned weekly spend per client, used for the pacing check
• expected_channels (optional): Which channels each client runs, used to spot missing exports
• account_owners (optional): Who manages each client

Tools

• Google Sheets: rollup output (optional)
• Looker Studio: source data (optional)

Steps

1. Normalize all exports to one table: client, channel, week, spend, conversions, revenue if any. Use the export-normalizer skill.
2. Compute per client for each week: CPA (spend / conversions), ROAS (revenue / spend) or lead count, whichever the target uses; week over week change; and, when spend_plan is given, spend versus plan.
3. Score each client on its latest week:
4. Off target = (actual minus target) / target, signed so that worse is positive (higher CPA, lower ROAS, fewer leads).
5. Pacing gap = (actual spend minus planned spend) / planned spend, absolute value. Only scored when spend_plan is given; otherwise say pacing was not checked.
6. Red: off target by more than 20 percent, or pacing gap above 15 percent.
7. Amber: not red, and the target metric got worse in each of the last two week over week comparisons (needs three weeks of data). With only two weeks, amber is not scored; say so in Data gaps.
8. Green: everything else.
9. Thresholds are configurable; state the ones used.
10. Sort red first, then amber, then green, with the one metric that triggered the status (if both triggers fire, name both).
11. Draft a one line action prompt per red and amber client for its owner, or for "Account owner" when account_owners is missing.
12. List data gaps: clients missing a target; clients missing a channel listed in expected_channels; weeks missing. When expected_channels is not given, list the channels seen per client and ask whether any are missing.

Rules for this job

• Internal only; never format for client distribution.
• Do not compare clients publicly or share one client's data with another.

Missing inputs

• Before starting, check every required input listed above. If one is missing, ask for it in a single short message that lists exactly what you need and why. Do not start the work on guessed inputs.
• If an optional input is missing, continue, state the default you used, and point out what would improve with it.

Truthfulness

• Use only facts from the user's inputs, files you were given, and pages or tools you actually opened in this session. Quote sources where you can.
• Never invent numbers, quotes, customer names, sources, rankings or results. When something is an estimate, label it as an estimate and show how you got it.
• If a tool call or page load fails, say so plainly and continue with what you have.

Approval points

• Approve before posting the rollup to a shared team channel.
• Never send, post, publish, buy, delete or change anything in a live account or on a public page without the user's explicit approval for that specific action.

Working without connectors

Ask for one CSV per client per channel, or a combined export, with client name, channel, week, spend, conversions and revenue for at least the last three weeks, plus the target per client and, if available, planned weekly spend and the channels each client runs.

Output format

Return these sections in this order, with these headings: 1. Portfolio table with status 2. Red and amber details 3. Data gaps End with a short list titled "Needs your decision" when any approval point applies. Leave it out otherwise.

• Approve before posting the rollup to a shared team channel.

Weekly portfolio rollup (internal, agency team only)

Inputs used: the fictional test data you pasted. Three weeks per client (two weeks ago, last week, this week), targets for all four clients, weekly spend plan, and expected channels. No account owners were given, so action prompts are addressed to "Account owner".

Thresholds used (defaults): red if off target by more than 20 percent, or spend pacing gap above 15 percent; amber if not red and the target metric got worse in each of the last two week over week comparisons; green otherwise. Definitions: CPA = spend / conversions; ROAS = revenue / spend. Off target = (actual minus target) / target, signed so worse is positive. Pacing gap = (actual spend minus planned spend) / planned spend. Currency assumed USD as written; no conversion applied.

1. Portfolio table with status

CPA and ROAS figures rounded to the cent or two decimals.

2. Red and amber details

Client A (Red), Google Ads, trigger: CPA - CPA went $45.00, $47.50, then $66.67: under target for two weeks, then 33.3 percent over. - In the data: conversions held at 40 then fell to 30 this week, while spend rose from $1,900 to $2,000, on plan. - Note: Client A should also run Email (per expected channels), but no Email export was provided, so this view is Google Ads only. - Action prompt for the Account owner: "Client A CPA hit $66.67 vs $50 target as conversions fell 40 to 30 on planned spend. Check conversion tracking and search terms today, and send the missing Email export."

Client B (Red), Meta Ads, trigger: ROAS - ROAS went 4.40, 4.20, then 3.00, 25 percent below target. It has declined two weeks in a row; the last drop is the large one. - In the data: spend flat at $5,000 all three weeks; revenue $22,000, $21,000, then $15,000. - Action prompt for the Account owner: "Client B ROAS is 3.0 vs 4.0 target after revenue fell to $15,000 on flat $5,000 spend. Check purchase tracking and creative performance before changing budget."

Client C (Red), Google Ads, trigger: spend pacing - CPA is fine: $109.09, 9.1 percent better than the $120 target. - Spend is the issue: $1,200 this week against a $1,000 plan, 20 percent over (above the 15 percent limit). Spend has risen each week: $1,000, $1,100, $1,200. - Action prompt for the Account owner: "Client C spent $1,200 vs $1,000 plan (+20%) though CPA is on target. Confirm whether the budget increase was agreed; if not, bring spend back to plan."

Client D (Amber), Google Ads, trigger: CPA trend - CPA is still 11.5 percent better than the $80 target, but it worsened in both week over week comparisons: $60.00, $66.67, $70.83. - In the data: spend rose $1,500, $1,600, $1,700 while conversions went 25, 24, 24. - Action prompt for the Account owner: "Client D CPA has risen two weeks running ($60 to $70.83) as spend grew and conversions stayed flat. Still under the $80 target; review where the extra spend went before it crosses."

3. Data gaps

• Client A, Email: listed in expected channels, but no Email export was provided. Client A's status is based on Google Ads only.
• Account owners: not provided, so action prompts are not assigned. Send owners per client to route them.
• No other gaps: all four clients have a target, a spend plan and three weeks of data for their listed paid channel.

Needs your decision

• Approve before I post this rollup to a shared team channel. Nothing has been posted.

Happy to build the portfolio view. Before I start I need two things, because each client is scored red, amber or green against its target using real numbers, and I will not guess either:

1. Exports per client, per channel: weekly data with client name, channel (paid, organic, email), week, spend, conversions, and revenue if you track it. Please send at least the last three complete weeks; with only two weeks I can still flag red clients, but I cannot score amber (two weeks of worsening in a row). One CSV per client per channel, or one combined export, both work.
2. Targets per client: the KPI each account is judged on and its target, for example CPA $50, ROAS 4.0, or a weekly lead count.

Optional, but they make the view sharper: - Spend plan per client (planned weekly spend), so I can flag pacing that is more than 15 percent off plan. Without it, pacing is not checked. - Expected channels per client, so I can tell you which exports are missing. - Account owners, so each red or amber client's action prompt goes to the right person.

Can you paste or upload the exports and targets?