Opportunity Win/Loss Analysis Extractor

Overview

A scheduled Power Automate cloud flow that extracts closed-won and closed-lost opportunities from Salesforce on the 1st of every month, appends each opportunity as a row in a SharePoint-hosted Excel workbook, and emails a summary scorecard (win count, loss count, total won/lost amount, win rate %) to a leadership distribution list.

The flow is designed as a FlowLibs reference pattern demonstrating:

- Recurrence trigger on a monthly calendar schedule - Dynamic SOQL construction from an environment variable - Aggregation inside a Foreach using conditional IncrementVariable actions - Connector-to-connector bridge (Salesforce -> Excel Online Business -> Outlook) - Full parameterization via Dataverse environment variables for ALM portability

Use Case

Revenue Operations and Sales Leadership teams typically manually export Salesforce Opportunity reports at month-end for pipeline reviews. This flow automates the extraction and scorecarding so leadership gets a month-over-month Excel record plus an at-a-glance HTML summary email with zero manual effort. The Excel workbook becomes a historical ledger that Power BI can point at for trend analysis.

The flow is ideal for teams that:

• Revenue Operations teams automating month-end Salesforce opportunity reporting
• Sales leadership needing recurring win/loss scorecards without manual exports
• Finance and executives tracking month-over-month win rate and closed-revenue trends
• Teams building a historical Excel ledger to feed Power BI trend analysis

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Change the schedule

Edit the Monthly_Win_Loss_Extract Recurrence trigger to change monthDays, hours, or timeZone. The default runs on the 1st of each month at 06:00 ET so downstream consumers have fresh data before the business day starts.

Change the lookback window

Update the flowlibs_WinLossLookbackMonths env variable (no flow edit required). Set to 3 for a rolling quarterly report or 12 for an annual comparison.

Change recipients

Update the flowlibs_LeadershipEmailRecipients env variable with a new comma-separated address list.

Change the Excel destination

Update the four workbook env variables (flowlibs_SharePointSiteURL, flowlibs_WinLossExcelDriveId, flowlibs_WinLossExcelFilePath, flowlibs_WinLossExcelTableName). The table columns must remain Id, Name, StageName, Owner, Amount, CloseDate, Type, LeadSource, or the Add_Opportunity_Row_To_Excel parameters must be adjusted to match.

Add fields to SOQL

Extend the SELECT clause in Build_SOQL_Query and add corresponding item/* parameters to Add_Opportunity_Row_To_Excel. Remember to add the matching column to the Excel table first.

Change win stage definition

Edit the equals expression in Determine_Won_Or_Lost if your org uses a stage name other than 'Closed Won' for wins.

Tune throughput

Foreach is set to concurrency.repetitions = 1 for deterministic ordering and Excel safety. Increase to 8-16 if your workbook can tolerate parallel row inserts and runtime is a concern.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

concat('SELECT Id, Name, StageName, Owner.Name, Amount, CloseDate, Type, LeadSource FROM Opportunity WHERE IsClosed = TRUE AND CloseDate = LAST_N_MONTHS:', variables('varLookbackMonths'), ' ORDER BY CloseDate DESC')

if(equals(items('For_Each_Opportunity')?['Amount'], null), 0, float(items('For_Each_Opportunity')?['Amount']))

if(equals(add(variables('varWonCount'), variables('varLostCount')), 0), '0.0', string(mul(div(float(variables('varWonCount')), float(add(variables('varWonCount'), variables('varLostCount')))), 100)))

items('For_Each_Opportunity')?['Owner']?['Name']