Salesforce Revenue Reporting System

Operational Outcome

Reduced a manual, spreadsheet-driven reporting process to a repeatable workflow requiring only a refresh and snapshot selection. The reporting process became faster, more consistent, and significantly easier to audit.

Decision Outcome

Sales leadership gained a reliable week-over-week view of pipeline evolution rather than relying on point-in-time CRM reports or manual comparisons. The reporting output consistently highlighted:

• New opportunities
• Won deals
• Lost opportunities
• Pipeline movement
• Value changes
• Stage progression

Data Outcome

Perhaps the most valuable result was the creation of a historical analytical dataset. Instead of generating reports that disappeared after each meeting, every weekly extraction became part of a structured archive that could be reused across multiple analytical workflows. The same dataset later supported:

• Forecast validation
• Revenue planning
• KPI reporting
• Historical trend analysis

Those downstream uses are outside the scope of this demonstration but were an important objective of the original solution.

This section focuses on implementation details for readers interested in the engineering behind the solution: Salesforce extraction with Power Query, snapshot architecture, historical comparison logic, movement classification, and VBA automation. The business value of the project came from the reporting architecture itself rather than any individual technology, Power Query, VBA, and Excel were implementation choices selected because they integrated naturally with the client's existing processes while requiring virtually no change management for end users.

Salesforce extraction (Power Query M): connects to the Opportunity object, flattens the Owner lookup, and selects twelve fields, including three custom fields, before renaming every header to a human-readable label:

Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48, CreateNavigationProperties=true]),
Opportunity1 = Source{[Name="Opportunity"]}[Data],
ExpandOwner = Table.ExpandRecordColumn(Opportunity1, "Owner", {"Name"}, {"Owner.Name"}),
SelectCols = Table.SelectColumns(ExpandOwner, {
    "Id", "Name", "CloseDate", "StageName", "Amount", "LeadSource",
    "Owner.Name", "CreatedDate", "Loss_Reason__c",
    "Original_Lead_ID__c", "Probability", "Notes_Status__c"
}, MissingField.UseNull),
RenameCols = Table.RenameColumns(SelectCols, {
    {"Id", "Opportunity ID"}, {"Name", "Opportunity Name"},
    {"CloseDate", "Close Date"}, {"StageName", "Stage"},
    {"LeadSource", "Lead Source"}, {"Owner.Name", "Opportunity Owner"},
    {"CreatedDate", "Created Date"}, {"Loss_Reason__c", "Loss Reason"},
    {"Original_Lead_ID__c", "Original Lead ID"},
    {"Probability", "Probability (%)"}, {"Notes_Status__c", "Notes/Status"}
})

MissingField.UseNull means a renamed or deleted Salesforce field won't break the weekly refresh. It returns a null column instead of an error, the safer failure mode for an unattended process, though it still means someone has to monitor for silent schema drift. The final rename step matters beyond readability: its output headers are the exact contract Opps_Analysis.xlsx expects when it reads the CSV back in. Change a header here and the downstream file's type-casting step stops finding that column.

Self-locating snapshot folder (portfolio extension): rather than a folder path hardcoded as text, the Control sheet derives its own location from the workbook's file path:

=LEFT(CELL("filename",A1), FIND("[",CELL("filename",A1))-1) & "Data\"

This replaces a genuine limitation in an earlier version: two independently hardcoded, unlinked paths that both had to be updated by hand if the shared folder ever moved.

Comparison join (original): Delta_Comparison starts from a left-outer join of the current snapshot against the prior one on Opportunity ID, so every row from the newer file is kept even when nothing matches in the older one:

Source = Table.NestedJoin(Report1, {"Opportunity ID"}, Report2, {"Opportunity ID"}, "Previous", JoinKind.LeftOuter),
ExpandedPrevious = Table.ExpandTableColumn(Source, "Previous", {"Stage", "Amount"}, {"Previous.Stage", "Previous.Amount"})

A row with no match, meaning the opportunity doesn't exist in the older snapshot, comes back with Previous.Stageand Previous.Amount both null. That null check is what the stage bucketing below builds on.

Stage bucketing and movement classification (portfolio extension): the core of the rebuilt comparison logic, ported from a manual stage-checkup formula into the query itself:

StageCategory = (s as nullable text) as nullable text =>
    if s = null then null
    else if List.Contains({"Closed - Won", "Closed Won - 100%"}, s) then "Won"
    else if List.Contains({"Closed - Lost", "Closed Lost - 0%", "Postponed for Future"}, s) then "Lost"
    else if s = "Closed - Won (Cancelled)" then "Cancelled"
    else if s = "Closed - Internal" then "Internal"
    else "Open"

Every matched opportunity is bucketed on both the current and prior snapshot, then classified into New Sale, Lost Opp, Changed, Open, or no movement. Only rows with a real movement or dollar change survive the final filter. That's the direct fix for a flaw in an earlier version of the query: it kept only rows with no match in the prior snapshot, meaning brand-new opportunities, and discarded every stage or amount change on a continuing deal even though the join above made that comparison possible.

Weekly snapshot automation (VBA): one macro, triggered by a single button, that copies the live Salesforce table to a dated CSV and then refreshes the connection for next week:

folderPath = ThisWorkbook.Path & "\Data\"

If Dir(folderPath, vbDirectory) = "" Then
    MkDir folderPath
End If

filePath = folderPath & "Opps_" & Format(Date, "YYYY-MM-DD") & ".csv"
snapshotSheet.Copy
ActiveWorkbook.SaveAs Filename:=filePath, FileFormat:=xlCSVUTF8
ActiveWorkbook.Close SaveChanges:=False
ThisWorkbook.RefreshAll

The path is built from ThisWorkbook.Path, not typed in, so the macro still finds its own Data folder if the whole project is ever moved, and the folder gets created automatically the first time the macro runs against a fresh copy. The snapshot is written before the refresh, so each CSV always reflects exactly what was on screen at the moment of the click. It's a deliberate, auditable behavior, even though it means clicking the button doesn't pull live, real-time data.

This case study reflects a real client engagement. To preserve confidentiality, all names, amounts, dates, and records shown throughout this case study have been anonymized or synthetically generated, using a reduced record count built to demonstrate the same mechanics at a shareable scale.

The underlying reporting architecture, comparison methodology, and workflow accurately represent the production solution while protecting confidential client information.