Accelerate Marketing Insights: Harnessing SQL and Data Distiller Views in Adobe Experience Platform

Disclaimer: This piece is intended as an idea exercise rather than a traditional marketing piece or a tutorial extolling the virtues of a capability. The goal is to get you to think on paper. A more detailed tutorial that delves deeper into these ideas is planned in the Data Distiller guide .

Balancing Cost and Usability in Data Models: A Critical Tradeoff

When designing a Data Distiller Data Model , one of the key advantages is the ability to normalize the reporting data model. This normalization allows for precise updates and deletions without the need to reprocess all of the data, which can be a significant cost-saving measure. For instance, if you anticipate frequent changes in your lookup tables, it's wise to keep that data separate from your fact tables. This separation minimizes the need for expensive data reprocessing.

However, it's important to acknowledge that every data modeling or architectural decision involves tradeoffs. If your architect can't clearly articulate the pros and cons of a particular approach, it's a red flag.

This normalization strategy, while beneficial in certain aspects, comes with its own set of drawbacks:

1. Performance Impact on Ad Hoc Analysis: When data is spread across multiple tables, ad hoc analysis requires you to reconcile (or join, in SQL terms) the data at runtime. This can lead to performance issues, necessitating additional compute resources. Essentially, you have to decide whether to bear the cost upfront during data processing or later when accessing insights.
2. Usability Concerns for End Users: End users, myself included, are often more comfortable working with flat data structures, like Excel files, where data is easily accessible across rows and columns. Reconciling data across multiple sources can be time-consuming and frustrating, especially when you’re accustomed to working with simpler, flat files. The question then arises: should we, as end users, bear the brunt of the data engineer's cost-saving decisions?

In conclusion, while normalizing data can be a cost-effective approach, it’s essential to weigh these savings against the potential impact on usability and performance.

Data Distiller Data Views offers a solution to the usability challenges posed by normalized data models. By allowing data engineers to create flat views of the underlying data specifically tailored for marketing users, these views can be easily exposed for dashboard building or even for ad hoc exploration. This approach means that users can work with the simplified, flat data structures they're familiar with, without needing to navigate the complexities of the underlying normalized model.

By leveraging a combination of smart data modeling techniques and Data Distiller Data Views, you can strike a balance between optimizing your data processing costs and ensuring ease of access and creation of insights. This method not only saves on compute resources but also enhances the overall user experience, making it easier for non-technical users to extract valuable insights from the data.

Data Distiller Views: When To Use Them?

Crafting a user-friendly experience starts with the complexities of our data models. By creating views, we distill complex relationships into accessible insights. This abstraction layer means that dashboard users can focus on the insights, not the intricacies of SQL joins or table schemas.

Tailored Data Perspectives

Different marketing stakeholders require different data slices to inform their decisions. Views serve as a bespoke lens, offering customized snapshots of data tailored to the needs of various roles within the organization—be it marketing, compliance, or customer service.

Consistent and Intuitive Insights

The heart of a good dashboard lies in its consistency and ease of interpretation. Views encapsulate common calculations and transformations, presenting a consistent, read-only data model that can power your dashboards efficiently, allowing end-users to generate reports with confidence.

Optimal Performance

A well-designed view can significantly reduce query complexity and improve the performance of your dashboards. By materializing views, we can pre-compute and store complex aggregations, ensuring that dashboards load swiftly, and user experience remains smooth.

Paper Demo

Scenario

The use case described below involves analyzing consent data within Adobe Real-Time CDP by categorizing consents into different types, such as personalization, data collection, and data sharing. Under the marketing category, we have multiple channels (email, push, sms) that we use for tracking consent values. There is also a preferred field that shows you which channel is preferred for communication. It turns that we are mimicking a XDM fieldgroup for consents that can show you how this data is organized per profile.

This scenario is a common use case in Data Distiller, where users harmonize consent data from multiple sources, apply business rules, and generate consent values categorized by channel for each profile. These values can be updated as often as necessary, and the process is scalable to handle billions of records per job.

Where is the Consent Data for Analysis?

If your data is in the Real-Time Customer Profile and you need to build a data model for analytics in the Data Distiller Warehouse (also called the Accelerated Store), here's what you should know:

The attributes added to a Real-Time Customer Profile are available as a daily profile snapshot in a system dataset on the data lake for every merge policy.This dataset includes the identity map, segment memberships, and attributes, including those used to populate the Real-Time Customer Profile. You can use Data Distiller to create streamlined data models from this dataset and publish the processed data into the Data Distiller Warehouse. This approach is cost-effective, easy to maintain, and provides the interactiveness needed for dashboarding, all without additional costs.

These categories are tracked daily to understand user preferences and behaviors. Additionally, the analysis includes identifying preferred communication channels (like email or SMS) based on aggregated consent data. This helps businesses optimize their outreach strategies by focusing on the channels that users have consented to, ensuring compliance and enhancing user engagement.

However, it's essential to resist the temptation to consolidate all this data into one massive table containing every possible value. Instead, we should focus on strategically building smaller, purpose-driven tables that cater to the specific analyses we aim to perform. This approach not only optimizes performance but also ensures that our data models are efficient and easier to maintain. By being selective and smart in our data structuring, we can achieve more targeted and actionable insights.

Data Model Strategy For Accelerating Time-to-Value

Imagine that consent analysis use cases arise every few months or quarters. To accelerate time-to-value, it's essential to create simple tables that minimize the need for complex data modeling and rework. In this scenario, our data engineer will construct three distinct tables within a single data model, each tailored to address a specific use case. This approach ensures quick adaptability and efficient use of resources, allowing for faster insights without unnecessary complications. However, this approach involves a tradeoff: bespoke implementations for each use case can limit the ability to analyze data across multiple use cases. By focusing on tailored solutions, you may face challenges when trying to integrate or compare data from different scenarios, potentially reducing the flexibility and comprehensiveness of your overall analysis.

1. Daily Consent Categories Aggregate Fact Table: This fact table tracks daily consent counts for different categories like personalization, data collection, and data sharing.
2. Daily Marketing Consent Aggregate Fact Table : This fact table captures consent data by marketing type (e.g., email, SMS) and consent value (e.g., agreed, declined).
3. Daily Preferred Consent Aggregate Fact Table: This fact table aggregates the total consents across different preferred marketing channels over time.

All of these tables leverage marketing channels(such as email, SMS, etc.) and consent types (Yes -opt in, No - default etc) as lookup tables to save space and reduce costs by avoiding the storage of duplicate values within the fact table. This design choice not only optimizes storage but also makes it easier to modify the tables later when new consent values are introduced or additional marketing channels are added. This approach ensures that your data model remains flexible and scalable over time.

Consent Trends Over Consent Categories

As mentioned earlier, this is the Daily Consent Categories Aggregate Fact Table looks like:

This setup is impressive, and a data engineer skilled in handling joins can create highly effective dashboards based on this data structure. However, at this stage, the marketing user will have access only to a pre-configured, "canned" dashboard, limiting their ability to customize or explore beyond the provided insights.

Consent Trends in Marketing Channels

Let us drill into the Marketing Category and create a simple fact table called Daily Marketing Consent Aggregate Fact Table

Let me repeat: This setup is impressive, and a data engineer skilled in handling joins can create highly effective dashboards based on this data structure. However, at this stage, the marketing user will have access only to a pre-configured, "canned" dashboard, limiting their ability to customize or explore beyond the provided insights.

Consent Trends on Preferred Channels

Let us drill into the Marketing Category's preferred channel and create a simple fact table called Daily Preferred Consent Aggregate Fact Table.

The fact table above is quite similar to the one used for marketing channels, with the key difference being that it doesn’t track consent values for the preferred channel. If you match the marketing type from this table to the marketing channels consent table, you can retrieve the corresponding consent values.

Let me repeat: This setup is impressive, and a data engineer skilled in handling joins can create highly effective dashboards based on this data structure. However, at this stage, the marketing user will have access only to a pre-configured, "canned" dashboard, limiting their ability to customize or explore beyond the provided insights.

A Challenge to Data Engineer: Reduce Time-to-Value For a New Use Case

A new use case requires that a marketer have the full flexibility to build charts and dashboards across marketing fact tables (not the category tables) mentioned above. All the lookup values need to be available in a single view for the marketer. No more canned dashboards.

This is where the Data Distiller View becomes invaluable. The data engineer writes a SQL query that seamlessly combines data elements from all relevant tables into a virtual table, presenting the data to the marketing user without the need to reprocess any of it. The marketing user interacts with this consolidated view as if it were a single table, unaware of the complex joins happening behind the scenes.

--Using Consents Insights Data model DB:consenttracking, Schema:consenttrackingmodel
CREATE VIEW
  consenttracking.consenttrackingmodel.view_full_marketing_consent AS
SELECT
  fmc.date,
  fmc.marketing_type AS marketing_type_id,
  dmt.display_name AS marketing_type_name,
  fmc.consent_value_id,
  dcv.consent_value,
  fmc.total_consents,
  fpc.total_consents AS preferred_total_consents
FROM
  fact_marketing_consent_aggregates fmc
  JOIN dim_marketing_type dmt ON fmc.marketing_type = dmt.marketing_type
  LEFT JOIN dim_consent_value dcv ON fmc.consent_value_id = dcv.consent_value_id
  LEFT JOIN fact_preferred_consent_aggregates fpc ON fmc.date = fpc.date
  AND fmc.marketing_type = fpc.marketing_type;        

Here's what it does:

1. It pulls specific columns, including the date, marketing type ID, marketing type name, consent value ID, consent value, total consents, and total preferred consents.
2. it does a inner Join with dim_marketing_type (dmt) to get the marketing type name. It does a left Joins with dim_consent_value (dcv) to retrieve consent values and with fact_preferred_consent_aggregates (fpc) to include the preferred total consents.

This view provides a comprehensive dataset combining marketing consent data, consent values, and preferred consent counts, making it easier for users to query and analyze without having to manually join the tables.

If you noticed, the CREATE VIEW syntax simply adds this as a virtual table to the existing data model. This allows a marketing user to access the virtual table directly in the dashboarding area:

Read More About Data Distiller

If you haven't explored Data Distiller yet, you might be overlooking a crucial product in Adobe's portfolio.

I am making updates to the book I am writing. You can check out some cool examples here.