How to Set Up Salesforce Reporting Snapshots – Freeze Data for Future Reference
Salesforce Reporting Snapshots is a little-known feature that is included in out-of-the-box Salesforce orgs and is easy to set up. Snapshots allow you to take a picture of a set of records and freeze the data on them for future reference.
The most common use case is Opportunity Pipeline Change over time, so that’s the example I’ll use today.
However, you can also use Snapshot to capture other data like:
- Case object volume-per-rep, change over time
- API Calls trending, longer than the last 7 days
- The number of Leads owned by users, over time
There are some drawbacks of using snapshots, mainly that you can only use a Snapshot to capture up to 2,000 records at once. But, if you have a small org, or you always have less than 2,000 records, they are a great option (especially if you do not have the budget for a heavy-duty data analytics tool!)
How to Set Up Opportunity Pipeline Reporting Snapshots
1. Create a Report Folder and Report. This report should be of the data you want to capture (no more, no less.) I always recommend including ID values as well, because any record with a text value for a name could potentially change and you need a unique reference to be able to watch a single record change over time. We also want to save the report in a special folder that nobody has access to.
For our Opportunity Snapshot, we want to see it trending in the pipeline over time. It’s possible in the future that we will get questions like, “Who owns this Opportunity? How did this specific rep’s pipeline change over time? What Account was this for?”, so we want to make sure to include any identifiable information in our snapshot. We also want to only report on the records that need a Snapshot – Opportunities that are already Won, or Lost probably aren’t going to change and don’t count towards our pipeline, so we’ll exclude those.
Once our report is prepared, we can save it in our Snapshot Folder. This is our pipeline as it stands today and we will use it to take a “picture” of the pipeline, going forward.
2. Create a Custom Object to hold the results of the Snapshot. The Custom Object is what will actually hold the data that we’ve frozen. This is the custom object that we will report on in the future, to see the change over time. Our new custom object needs to have a field for every column on our report above. When you create the custom object, make sure you check the “Allow Reports” box as well! Nobody but an admin should be looking at the Snapshot records themselves (only the reports) so I always leave “Allow Search” as FALSE and do not create a Custom Tab.
We’ll also need to add Custom Fields to hold the data from the report. I like to use Text Area fields for all the Names, Picklist and any other text values. The reason for that is that I don’t want to have to return here in the future and update any Picklist to match any future changes to Stage, or to Type. I also don’t want these snapshots connected to related objects, so I don’t use any Lookup fields here. I do use Date and Currency fields, though, because I still want to be able to group by Dates on my future reports and I also want to sum up any Amount values on the reports.
3. Set up the Snapshot. Navigate to Setup -> Reporting Snapshots and click “New Reporting Snapshot” Give your Snapshot an appropriate name and set the running user. Note: If the running user ever becomes inactive, you’ll want to update it, otherwise the Snapshot will stop running. Select the Source Report (the report you created) and the Target Object (the custom object you created.) Then click “Save & Edit Field Mappings”
Map the fields from the Source Report to the fields in the Custom Object. Click Save.
4. Schedule the Snapshot. Create a schedule that runs according to the frequency at which you want to see your data change. A more frequent schedule will give you more detail, but will create more records. A less frequent schedule gives you a broader view of change over time and creates less records. For this example, we’ll set our snapshot up to run on a weekly basis, because we want that level of granularity. In the Schedule Reporting Snapshot section, click “Edit” and set up your schedule. You can also opt to receive an email, when the snapshot runs. You’ll be required to choose an End Date – if you want to run this report indefinitely, just choose a date very far in the future.
5. Report on the results. You won’t actually have any data to report on until after the next scheduled run, so you can wait until then to set up a report or create an empty one now. In this example, I’ll have waited a few weeks so we can work with a report that has data. Generate a new report for the Custom Object you made.
Set up your report like you would set up any normal Salesforce report. For Opportunity Pipeline Snapshot, typically we’re grouping by the “Created Date” (the date the Snapshot was created) and we might also group by Stage under that.
If you have a single Opportunity (or Type) you are interested in (like, “New Business”) just set your report filters as needed. This report is showing a very small pipeline growth. However, if you filter it to just see “New Business”, you see a different picture with huge growth in the New Business pipeline!
Now that you have all this setup, you can let it run quietly in the background. The longer your Snapshot runs, the better picture you will have of data changing over time. It’s an easy (and free) way to give good analytics to your Sales team and see what your pipeline or records looked like on a given date in the past.
I hope you found this helpful. Do let us know in the comments, any other ideas that you may have on how to use Snapshots!
Jaime de Viajes BIDtravel
Awesome, very clear explanation
I was wondering, would you consider export the report to XLS to create the custom object from spreadsheet to save some time?
Also, what about capturing fields from report formulas? I have tried but they dont show up as origin available fields
Hi Jaime, Are you talking about creating the initial custom object? I just do it directly in Salesforce, it’s not many fields. Snapshots don’t allow for formula fields, there’s an idea on the idea exchange: https://trailblazer.salesforce.com/ideaView?id=08730000000DgAIAA0
If you really need the formula field (and all the fields you’re referencing are in your snapshot, you could just re-create the formula field directly on your snapshot custom object. Or you can create automation to populate the formula in a text/currency field, and that can be referenced in the snapshot as well.
Jaime de Viajes BIDtravel
Hi, not sure if you mention it, but just in case. If using a summary report instead of a tabular report, some report formulas will be available to add, but not other fields like the Id´s as you did in your example. Will keep on testing this tool anyway. Maybe because of the limitation of the “preferred start time” when scheduling, I will have to go for a solution using apex (schedulable class).
Thanks – really useful to know this technique when tracking over time.
Hi! – This was really helpful, thank you for putting this article together!
One question, and I’m not sure this is the venue for asking this, but when creating the pipeline report (like the one in the example), is there a way to only show the Total Amount for the most recent snapshot?
In the example, the Total Amount reflects the value of all the snapshots combined, which is what I have in my report, too. That could be very misleading to anyone viewing the report. Just thought I would ask.
Thank you for this very article. I was feeling a bit bewildered about snapshots but these instructions helped me feel more confident.
Thank you for posting this! It was a great step-by-step list of what to do. It didn’t take long to set up and my snaps start tomorrow. 🙂
This was INCREDIBLY helpful!!! Thank you so much for sharing this!
Hi, this was very helpful, thank you! Instead of creating a schedule to run daily/weekly, can I have a snapshot captured every time I run a report?
Context – I have a job that creates data and every other time the job runs, it overrides existing records.
So I want to capture the results of each run separately in a snapshot object. It should be real-time and not a schedule. Thoughts? Suggestions?