Snapshotting is one of the most common advanced use cases we see here at SpringML. Clients need to know more than what their data says now—they need to know what it said a week/quarter/year ago, and how it’s changed over time.
I’ll cover some basics of snapshotting, how to set up Snapshots in CRM Analytics, and a few tips and tricks for a successful implementation of the Snapshot Analytics templated app.
When do I need Snapshots?
First, let’s give a quick intro to Snapshots. A snapshot is a point in time copy of your full source data. If we’re snapshotting a dataset, every time we run our snapshot dataflow, we append all rows from the source data into our snapshot dataset. Each time the snapshot runs, we add the new rows with the current date as the “SnapshotDate.”
Having full copies of your data over time allows you to “go back in time” to data “as of” a certain SnapshotDate. This allows you to capture changes to your data that aren’t normally saved, analyze that data, and look for trends in how your data changes over time.
Making full copies of your data can end up using a very large number of rows. Thankfully, with a row limit of 10 billion rows, CRM Analytics is up to the challenge.
When don’t I need Snapshots?
As a quick side-note, you don’t need snapshots if you capture every change in your data. Capturing changes allows you to reproduce historical snapshots, reconstructing the data at a certain period of time by looking at its most recent changes.
A great example of this is the OpportunityHistory object. The Sales Analytics prebuilt CRM Analytics App uses this object to build a really cool waterfall dashboard, comparing your pipeline between a start and end date, and categorizing the different ways (at the opportunity level) your pipeline changed between those two dates (expanded, moved in/out, won, lost, etc). The limitation to this solution (also mentioned in the documentation) is that “the dashboard supports only fields within the opportunity history table.” We most commonly see a requirement to track changes over time at a lower grain (Opportunity Line Items or Quote Lines).
Another new solution is the Change Analytics Template app, which allows you to analyze changes between two dates on any object which has field history tracking enabled. Notably, the Opportunity Line Item object does not allow field history tracking.
How do I set up snapshots?
CRM Analytics now has a templated app that provides a great foundation for this, Snapshot Analytics. This template sets up a dataflow and basic dataset, and is a great foundation for further tweaking.
Tips and Tricks
Add an IsMostRecentSnapshot field
This allows you to reliably get your most recent snapshot data, without relying on the SnapshotDate. For example, if you filter to SnapshotDate = “current day”, and your dataflow hasn’t run yet (or failed), then you won’t get any data.
Current dimension value lookups for historical snapshot data
For example, if your Sales Reps change regions, do you want to see what their region was as of the snapshot date, or do you want to filter to their current region?
If the latter, then you need to add an additional lookup every time your snapshot runs to grab the most recent data. You also need to slice off your “current” dimension fields near the beginning of your snapshot dataflow, so you can re-add them.
Start early and in Production
The best time to start snapshots is a year ago; the second-best time is today. You won’t have snapshot data until you start running your snapshot dataflow. Also, sandbox data won’t be relevant to users in production, so you need to get your process going in production as soon as possible.
Scheduling and Retention Logic
The Snapshot analytics app includes a few handy fields. The RemoveDataFlag allows you to run your snapshot multiple times per day, only keeping the most recent snapshot for that date. The SnapshotFilterFlag allows you to add logic to remove old snapshots (eg anything older than 1 year).
For snapshot retention, this depends on your data volume. For lower volume, you can run and keep snapshots daily. For larger volumes, we often take a hybrid approach, where we might keep snapshots daily for 60 days back, then keep weekly snapshots up to two years back.
You also want to make sure your snapshot runs after your source dataset updates. Remember to keep track of this over time, since dataflow run times tend to increase over time.
Conclusion
CRM Analytics row limit of 10 billion rows, and a number of out-of-the-box template apps, allow you to get going with a snapshotting solution quickly and easily. The best time to start your snapshots is now, so don’t wait! ! However, if you are looking for that specialized assistance, feel free to reach out to us. , SpringML would love to partner with you to help design a scalable, maintainable, and robust solution together.
