Salesforce Opportunity Reporting / Trend Analysis

1. Background:

a. There is a formula field in Opportunity called PeriodYYYYMM. It will display a value for the Opportunity Period in the format YYYYMM. i.e. for an Opp created in April 2025, it's 202505.

formula:
TEXT(YEAR( DateValue(CreatedDate))) & LPAD(TEXT(Month ( DateValue(CreatedDate) )),2,'0')

b. Won Opportunities: Status__c='Won' and Probability__c <> 'Pipeline'

c. “All” Opportunities: Probability__c <> 'Pipeline'

d. Oppty value is in Amount

e. Other Opportunity fields of interest are picklists:

Market_Cycle__c sample value: MED
Center__c sample value: Fremont
Product_Group__c sample value: ETC

2. Goal is to be able to have a report which will summarize data by Reporting Period for the trailing twelve periods. Also to see this trend over time.

As I write this, today is April 21 2025, the month of April has not finished (the period of 202504 is not yet complete) so can't be reported on yet. The most recent reportable period is 202503.

Example of 12 month reporting periods:

Reporting Period 202504 (April 2025) would be 202402 to 202503. i.e. summarize data for the 12 months prior to April.

Reporting Period 202503 (March 2025) would be 202401 to 202502. i.e. summarize data for the 12 months prior to March.

3. For each Reporting Period, we want to see:

Count of Total Opportunities (CountTotal)
Count of Won Opportunities (CountWon)
CountWon / CountTotal (CaptureRateCount)
Sum Amount
Sum Won Amount
Sum Won Amount / Sum Amount (CaptureRateAmount)
Sum Amount * CaptureRateCount (PipelineWeightedbyCaptureRateCount)
Sum Amount * CaptureRateAmount (PipelineWeightedbyCaptureRateAmount)

… then be able to see graphs of how, for example, Capture Rate or Weighted Pipeline changed over Reporting Periods.

Example:


Reporting Period 202504 202503
CountTotal 200 202
CountWon 20 22
CaptureRateCount 10.00% 10.89%
etc...

Remember each Reporting Period (i.e. the 200 value) was summed from 12 months of data.

4. Have the ability to drill-down and filter reports/charts on Opportunity Fields: Market_Cycle__c, Center__c, Product_Group__c which are all picklist fields. i.e. See that Capture Rate chart or Weighted Pipeline chart for just Market_Cycle__c = “MED”

4. My idea was to create an object to store this summary information for each period then have a flow to run on a schedule to loop through the Opportunities from the last 12 Reporting Periods and update the data in this object. Then report off this new object.

I started building this but then got confused on how to structure the data to be able to do the drill down’s (#4).

Note that Opportunities can be won for past periods, i.e. an Opportunity created in November 2024 might close today. So, I’d see the flow running daily for all 12 completed periods and re-totalling.

I’m open to other ways of achieving the result. Please let me know what you think.