Identify Trends with Salesforce Reporting Snapshots
5 min
One of the best features of moving from spreadsheets to Salesforce CRM is the ability to get real-time updates to reports and dashboards. Everyone gets the same information and information is updated as it happens.
Sometimes, a Sales or Service team leader or executive wants to see how things are progressing over time. This is easy to do for things like Opportunities won or lost, or Cases being opened and closed. However, the real-time nature of things like a Sales Pipeline or Service Backlog mean that those reports are constantly changing.
- Here are a few scenarios that leaders may want to see:
- How did the Sales Pipeline changes week over week?
- How did the Service Team’s case backlog increase or decrease over time?
- How many overdue Tasks did each team member have as of Friday each week this quarter?
- What was the count of uncontacted Leads by Lead Source on the first of each month?
While standard Salesforce reports could provide the answer to these questions at a given moment in time, they are not structured to capture changes. That is where Reporting Snapshots come in.
This article will detail how to set up Snapshots and best practices for using them.
What is a Reporting Snapshot?
Snapshots take the value(s) on a report at a given moment in time, and save those values to a record on what is called the “target object”. That target object is set up specifically to keep track of report values at a given time. By creating these records, we can then run a report off of the records in the target object, which will show us the trend of the report over time.
Step One – Create the Source Report
This is often the easiest step, because we typically already have the source report built. This is the report that we are taking the Snapshot from. A few key things to keep in mind...
- Keep this report simple. The snapshot will grab Grand Totals and Subtotals. It’s not a best practice to capture details from every record that comprises the report.
- For example, let’s say I want to measure each Sales Rep’s weighted pipeline. I will have subtotals by each grouping (in this case the report should be grouped by Opportunity Owner), and subtotals for the full team.
- Ensure that the right totals are turned on for the column you want to summarize and capture. Perhaps for the Pipeline report, I want to know the following fields by Rep:
- Fields by Rep
- Opportunity Record Count
- Total Weighted Pipeline
- Total Unweighted Pipeline
- Average Opportunity Amount Size
- In that scenario, I would want to make sure I had those columns either marked as “Sum”, “Average”, or both.
There is a maximum of 2000 records created from a single snapshot. So when creating the Source Report, make sure that the structure is not going to exceed this amount. For example, let’s say that I want to capture an open task count for every account in the system each week. If I have 50,000+ accounts, this will end up creating a problem. Look for ways to group or summarize the accounts (perhaps by Industry or Lead/Account Source) rather than capturing the value on every single account record.
Step Two – Create the Target Object
This is the place where we are going to record the value of our Source Report. First, give the object a concise name that clearly explains what it is. Since you may set up multiple Snapshots over time, it is not a best practice to call the object “Snapshot” or something generic like that. Consider a name like “Case Backlog Trend” or “Pipeline Snapshot”, which tells users what the object is capturing.
The fields on the target object have to be of the same or a compatible type as the Source Report. So for example, if you have a currency field on the report, the field on the target object also needs to be currency or a number field. Each value on the report is going to map to a single field on the target object.
Another consideration when creating the target object is the nature of the report you are going to create. Think about how you will be grouping or filtering the report on the back end as you determine the fields you want to capture. And don’t forget to check the box next “Allow Reporting”.
Step Three – Create the Snapshot (Map the Fields)
At this point, we are ready to create the actual Snapshot. First, we will give it a name and description. Then we will identify the Source Report and Target Object established earlier. Next, we’ll map the fields.
We have the object to map Grand Totals or Summary Totals. Use Grand Totals if you are looking to just get the tally for the full team, regardless of groupings. Summary Totals are useful if the report is breaking down the Snapshot by User, Lead Source, Team, etc. Not every field will be mapped from the Source Report, and not every field on the Target Object has to have a value.
As a good practice, create a field on the Target Object that allows you to map the name of the Snapshot directly onto the record. There may be a scenario where you want to create multiple Snapshots that use the same Target Object. If you map the Snapshot name to a field on the target object, you can use that later to filter or group the resultant report.
Step Four – Create the Snapshot (Schedule the Run)
The snapshot needs to be schedule to run at a specific time and schedule. This can be every day, every week day, only Thursdays, or for example Mondays and Wednesdays. There are time slots available for the different days of the week, so if you have multiple snapshots set up, you can stagger the times that they are running (and mass creating records).
As a best practice think try to schedule these for non-work hours. This will prevent changes happening right before or right after the snapshot is run. For example, let’s say we are capturing the pipeline by rep, and running it on Friday afternoon at 4:00 p.m. It’s possible that the Sales reps go in and make changes before the weekend starts. To avoid the dreaded response of “those numbers aren’t right!” from the Sales team, run the snapshot off-hours when the pipeline is locked in.
Also, consider whether you want to run the snapshot on specific days of the week or every day. The benefit to running it only on certain days of the week is that you will have more times available for other asynchronous reports and snapshots to run. The plus side of running every day is that you can pull in different dates depending on different needs. Let’s say that initially, the VP of Sales wants to see the pipeline as of Sunday each week (i.e. the pipeline going into the start of the week). Later, he or she switches it up and wants to see the pipeline on the 1st and 15th of the month. If we are running the snapshot daily, we will have the flexibility to pull in different sets of Target Object records (filtered by Day of Week or Day of Month), without having to adjust the snapshot schedule.
Just below the schedule on the snapshot set up page, we can see the actual runs. It shows us what time the snapshot actually ran (versus the expected time) and whether or not it was successful.
Step Five – Create a Report from the Target Object Records
Now we put the snapshots to work. Since we selected “Allow Reporting” when creating the Target Object, we will by default have a report type for our new object. That is the baseline of the report we will be creating. Just like a report on Leads, Cases, or Opportunities, we establish the filter criteria of which records we want to pull in.
As a side note, it can be very useful to add a couple of formula fields to the Target Object upon creation or later on before building reports. Specifically, a Day of Week field and Day of Month field are very helpful. This can be based on the record creation date or the date the report was run (which will typically be the same date anyway). These formula fields are very helpful when setting up the filters on the report. Even if we run the snapshot daily, we can use these to pull in a weekly or monthly record that will display a snapshot of the trends over time.
Other Considerations
Once we have the source report, target object, and snapshot set up, we really need to make sure that the source report is not edited in a way that will create errors for us. Even if the base report is something you use all the time, consider saving the actual source report in a folder with a title like “Snapshot Source Reports – DO NOT EDIT OR DELETE”.
Also, sometimes we are asked to set up a snapshot for something moving forward when a member of our team was tracking the information manually on a spreadsheet. The automated snapshot will only run moving forward. However, since the Target Object is a custom object like any other, we have the ability to manually create past records if required. This is a little extra work to manually set up the records for the older data, but leadership will appreciate the continuity of data and will grow to appreciate each of the snapshot’s automation very quickly!
