Lessons of Enterprise Scale Analytics, Part 5
ETL orchestration is a delicate and dynamic balance
To finish out our series on Lessons of Enterprise Scale Analytics, I want to chat this month about the aspect of Analytics at Enterprise scale that I’ve found to be the most powerful: enrichment in the data layer. Specifically, I’m talking about transforms and aggregations that we apply to our data not when it’s at rest and not when it’s being visualized, but as we’re preparing it to drive Dashboards and predictive models. In CRM Analytics, this is almost everything we do in Recipes besides I/O, joins, and appends.
Most analytics and Business Intelligence platforms incentivize us to allocate as much of our processing and enrichment to the data layer as possible for a number of reasons.
Batch transforms are efficient.
Enriched data promotes re-usability.
Performing heavy lifting in the data simplifies our visualizations.
In fact, this consideration - what values to compute within Dashboard queries themselves vs what values to compute in the data layer - is a weekly conversation for all of my clients. Let’s get into some of the details.
Modularity
In a greenfield or smaller-scale build, I could pack all of my data processing into a single script or Recipe. However, as we’ve seen in previous blogs, complexity only moves one direction, and that single Recipe will inevitably become too complicated to maintain, expand, or troubleshoot. For example, if I’m performing Activities or Interactions analysis, I can potentially have internal Attendees based on User records alongside external Attendees based on Contact records. Were I to include both of those inputs raw in an Activities Recipe, I would find myself repeating a lot of field derivation and rollup calculations (specifically aggregates and summaries) in parallel, not to mention performing enrichments like joining in goal numbers that might apply to targets outside of an Activities context.
In contrast, if I separate my User & Contact processing out to its own script or Recipe, perform my transforms and aggregations there, and then output to an intermediate structure, such as a Staged Dataset in CRM Analytics, every subsequent target - not just Activities - can reuse those enrichments. This gives us consistency across downstream tables or Datasets and consolidates our processing logic in one place for easy maintenance and troubleshooting.This functionality ties in well with the ever-present consideration of update frequency.
Freshness
Business Intelligence is not operational reporting. We shouldn’t be shooting for real-time numbers in our trending or historical analysis - ideally we want to see our BI and operational analytics side-by-side for a best sense of both the proverbial forest and the trees. Thus I always start the discussion of update frequency for BI at once daily. Realistically it’s possible to increase that to hourly at scale, but those refreshes are always expensive and limited, so the key to success becomes knowing where and how to tune the update frequency of your Recipes or scripts in relation to your data model (more on the data model in my previous entry).
In the example above where I’m performing Activities analysis with Internal (Users) and External (Contact) Attendees, my Users are hopefully logging lots of Activities during the day. In this case it’s worth it to consider processing our Activities-grain script or Recipe more frequently. However, the User and Contact records themselves don’t change or get updated nearly as often, so a daily refresh cadence for those grains saves us processing cycles and/or script runs with minimal concern about the data getting stale.
Runtime Performance
When we first start building Datasets in an Analytics implementation, we can pretty much build what we need on demand. The challenges arise once we start duplicating processing or outputs, as those are the costs that will get us to our governor limits more quickly and begin inflating our overall processing run times. As much as we try to balance the modularity and freshness noted above, over time our scripts will run long, potentially even long enough to cause chronic staleness in Dashboard data or to require manual queue-clearing and rescheduling. So sometimes we’ll want to split a script into parts exclusively for the performance, at the cost of more overall script runs or related API calls.There’s not an overall best practice we can turn to here - we have to know the data model and where we can compromise.
From my perspective, pushing the data layer as far as is practically possible is the most powerful lever we have available as Analytics developers. Although most guides out there focus on the visualization and adoption side of Analytics, being able to really lean into dynamic visualizations and have them apply at both record levels and rollup levels requires data that’s been processed. Enriching our data gives us categories custom built for Analytics, pre-scaled values that we can see next to their raw counterparts, and rollups that otherwise might take custom code for a single table - all the sorts of values that make visualizations interesting, and point quantitatively to the business dynamics in our numbers.
Concluding our series on Lessons of Enterprise Scale Analytics
Over 5 entries this year we’ve introduced the following lessons:
Complexity Only Moves One Direction. Know what’s coming and prep for it.
Grain is everything. Keep your detail level clear, especially your measure values
The Viz is only a fraction of the analysis. Watch the hidden aspects of Analytics very closely.
You have to understand the data model intuitively. Understand the whole picture to maximize your efficiency
ETL orchestration is a delicate and dynamic balance. Push the data layer as hard as you can.
The common thread here is that there are significant factors that influence your Analytics implementation on a strategic level that aren’t always clear until you have a large data volume and dozens or hundreds of Analytics assets. As Analytics builders, we can multiply our effectiveness by planning for these factors, letting them influence our architecture, and empowering our clients to grow alongside their build.
Starting in 2 months, I’ll be sharing a series of walk-throughs of solutions to interesting technical challenges in CRM Analytics and adjacent platforms. Please don’t hesitate to reach out if you have an open challenge which you’d like me to solve and illustrate in this space!
