Setup runs in three phases: design the metric (what business question are you answering), write the SQL query, then materialize and verify.
- Define the business metric
Decide what attribute you want to compute: Lifetime Value per Individual, Average Order Value per Account, Last Engagement Date per Customer Profile. Pick the source DMOs that hold the raw events and the target DMO that will receive the output.
- Write the Calculated Insight SQL
Data Cloud, Calculated Insights, New. Use the SQL editor. Write a SELECT that groups source events by the target DMO''s primary key and aggregates with SUM, COUNT, MAX, AVG. Test the query against current data; review row counts and sample outputs.
- Pick the output destination
Either write to a field on the target DMO (Individual.LifetimeOrderValue) or to a standalone Calculated Insight Object. DMO field is simpler; CI Object handles multi-row outputs.
- Schedule the materialization
Pick a refresh cadence: hourly, every 6 hours, daily, on-demand. Match to actual freshness need; over-refreshing wastes credits, under-refreshing produces stale segments.
- Verify in segmentation
After the first successful run, build a test Segment using the new attribute. Verify the segment counts match expectations. If counts are off, debug the SQL by querying directly via Data Cloud SQL.
DMO field (one value per record) or standalone Calculated Insight Object (multiple rows per record).
Hourly, every 6 hours, daily, or on-demand. Trade freshness against credit cost.
SUM, COUNT, AVG, MIN, MAX, plus window functions. Standard SQL aggregations with Data Cloud extensions.
One or more, joined via configured relationships. Joins are expensive on wide DMOs; filter early when possible.
- Calculated Insights are batch-materialized, not real-time. Segments read the most recent materialized value; field changes mid-cycle lag the segment until the next run.
- Schema changes break downstream consumers. Plan refactors carefully or build new CIs alongside old.
- Wide scans on large DMOs burn credits fast. Filter early, partition where possible, batch related metrics.
- Real-time CIs are still expanding feature-wise. Check release notes before assuming a real-time pattern is supported.
- Standalone CI Objects need explicit relationships to standard DMOs for segmentation to traverse them.