Track Historical Monthly Opportunity Pipeline Using Reporting Snapshot

Author: Rishabh Dubey

Requirement : The management wants to check how their opportunity pipeline is moving every month. They want to check on the 1st of every month how many opportunities are open and how many have been closed and what their stages are. The management also wants to keep this historical data  for reporting purposes.

Solution : This can not be done using Standard salesforce Reports. A reporting snapshot needs to be created that will run every month on the first day to store the data.

Reporting Snapshot : A Reporting Snapshot lets you report on historical data, and captures and stores data at a specific scheduled time over a period of time.

It is very common that the business users use Salesforce Reports to view their organization data frequently, but when they run the report, it would display only the most recent data available at the time of running the report. If the users need to analyze the data changes over a period of time—last few months, weeks, days, etc., Salesforce provides a very useful feature to accomplish it, and the feature is called Reporting Snapshot.

Steps to create Historical Reporting Solutions

1. Create a source Report
2. Create target Object
3. Create a Reporting Snapshot
4. Create Report on target object record

1. Create a Source Report

For our requirement the source report needs to be created on the Opportunity object that will show the current pipeline.

1. Go to reports tab
2. Click on new report and select opportunity Report type
3. Set the filters as follows

4. Column should be as follows

5. Save the report in the Public folder.

2. Create Target object to store the historical data

The Target Object is used to store the data generated by the report. The data will be stored at the time of the scheduling. It should have at least one field to map with the Source Report. The fields are created with the same data type as the report’s columns. It’s better to create the object and name it clearly as being a Snapshot Object such as “Opportunity Pipeline Snapshot’.

We can generate Reports and Dashboards from the Target Object

1. Go to object manager, click on create new custom object.
2. Name the object “Opportunity Pipeline Snapshot” and select Allow reports checkbox
3. Click on save
4.  Now we will need to create custom fields for all the columns of our source report in our target object.

Report Column	New Field Name	New Field Data Type
Opportunity Owner	Opportunity Owner	Text
Account Name	Account Name	Text
Opportunity Name	Opportunity Name	Text
Stage	Stage	Text
Amount	Amount	Currency
Closed	Closed	Checkbox

3. Create and Define Reporting Snapshot

1. Go to setup type reporting snapshots
2. Click on “Reporting Snapshots”
3. Click on New Reporting Snapshot
4. Name = “Monthly opportunity Pipeline”
5. Running user = Add yourself
6. Source Report = Opportunity Source Report
7. Target object = opportunity Pipeline Snapshot

8. Click on save and edit mappings
9. Mapping should be as follow

Account Name	Account Name (Text(255))
Amount	Amount (Currency(18, 0))
Closed	Closed (Checkbox)
Opportunity Name	Opportunity Name (Text(255))
Opportunity owner	Opportunity Owner (Text(255))
Reporting Snapshot Name	Opportunity Pipeline Snapshot Name (Text(80))
Stage	Stage (Text(255))

10. Click on save

11. Schedule the frequency for the snapshot

12. Select Monthly on day 1 of every month\
13. Start date should be today and end date can be after 5 year.\
14. Click on save

The Snapshot will run at 1 am on every first day of the month and you will see records created on the target object.

4. Create report on Source object

Once the snapshots run on the scheduled time you will be able to see records created on the Target objects.

The report needs to be created on this target object

1. Go to reports tab click on new
2. Select Record Type – “Opportunity Pipeline Snapshot”
3. Filter should be All records and all time.
4. Group the report by created date and group date by calendar Month.
5. Add stage as other grouping and add Amount as Column

6. Click on Add chart select “Stacked column”
7. On X-axis select created date, On Y-axis select Sum of account and stack by stage.

The Report will show only one bar for the first month. But as soon as the snapshot runs for the coming months, there will be additional bars for corresponding months.

Pros and Cons of Reporting Snapshot 

Pros

1. Measure the effectiveness of business for a period of time.
2. Get the historic trend data and store the report’s data as records in an object.
3. View the data differently, by creating the views in the Target object.

Cons

1. Only 100 fields are allowed in the Target object.
2. 2000 records are allowed to load at a time.
3. Record deletion is protected.
4. Custom frequencies like quarterly and semi-annually can not be defined.