Weekly Salesforce pipeline snapshot to sales leadership

Build a deterministic code workflow that runs every Monday at 8:00 AM America/New_York and produces a weekly pipeline snapshot for sales leadership.

Trigger: cron, weekly, Monday 08:00 ET.

Step 1. Salesforce SOQL Query against the Opportunity object. Pull all open opportunities (IsClosed = false) with CloseDate within the next 90 days from today. Select Id, Name, Amount, StageName, OwnerId, Owner.Name, AccountId, Account.Name, CloseDate, and LastActivityDate. Order by CloseDate ascending.

Step 2. In code, transform the result set: (a) compute total Amount and opportunity count bucketed by StageName, (b) compute total Amount and opportunity count bucketed by Owner.Name, (c) flag any opportunity where LastActivityDate is null or older than 14 days from the run date as stale.

Step 3. Google Sheets Append Values. Append one row per opportunity to the configured pipeline log spreadsheet and tab. Columns in order: run_date (today, ISO date), opportunity_id, opportunity_name, account_name, owner_name, stage, amount, close_date, last_activity_date, is_stale (true/false). This builds a week-over-week history.

Step 4. Build a clean HTML email body. Include: a header with the run date, a By Stage table (stage, deal count, total amount), a By Owner table (owner, deal count, total amount), and a Stale Deals section listing every flagged opportunity with name, owner, stage, amount, close date, and last activity date. Format amounts as USD currency.

Step 5. Gmail Send a Message. Send the email to the sales leadership distribution list (configurable recipients) with subject "Weekly pipeline snapshot" and the HTML body from step 4.

Inputs to expose as workflow configuration: the Google Sheet ID and tab name for the pipeline log, the recipient list for the email, the lookback days for stale flagging (default 14), and the close date window in days (default 90).

Additional information

What does this prompt do?

Pulls every open Salesforce opportunity closing in the next 90 days, including amount, stage, owner, and last activity date.
Buckets the pipeline by stage and by owner so leadership can see coverage and ownership at a glance.
Appends a snapshot row per opportunity to a Google Sheets pipeline log, so historical trends accumulate week over week.
Emails sales leadership a clean HTML table of the totals and flags any deals with no activity in the last 14 days.

What do I need to use this?

A Salesforce login with access to the Opportunity object.
A Google account with edit access to the Google Sheet you want to use as the pipeline log.
A Gmail account that can send to your sales leadership distribution list.
The list of email addresses (or a group alias) that should receive the Monday snapshot.

How can I customize it?

Change the schedule. Run it daily, biweekly, or only on the first Monday of the month instead of every Monday at 8am ET.
Adjust the time window. Pull deals closing in the next 30 or 60 days instead of 90, or scope it to a single business unit.
Tune the stale deal threshold. Flag opportunities with no activity in 7, 21, or 30 days instead of 14.
Swap the recipients or the Google Sheet. Point the email at a different distribution list, or log to a different tab for each region or segment.

Frequently asked questions

Does this work with Salesforce Professional, Enterprise, or Unlimited editions?

Yes. As long as your Salesforce login has read access to the Opportunity object, this will work on any edition that exposes opportunities through the standard interface.

Will the historical pipeline log keep growing forever?

Yes. Each run appends a fresh snapshot row per opportunity tagged with the run date, so over time you build a week-by-week record of how pipeline shifted. You can archive or roll up older rows whenever you want.

Can I send the summary to Slack instead of email?

Yes. The bucketed totals are produced as structured data, so you can swap the Gmail step for a Slack message to a leadership channel, or do both.

How do I change which deals are flagged as stale?

The stale threshold is a single setting in the workflow. Change it from 14 days to whatever cadence your team expects reps to touch open opportunities.

What if an owner has no open pipeline that week?

Owners with zero qualifying opportunities are simply omitted from the by-owner section of the email. You can also choose to show them with a zero so gaps are visible.