Mastering Consent Analysis in Adobe Real-Time CDP: A Guide to Building Your Customized Insights using Data Distiller

[#1]

Author: @annamalai

Introduction

Monitoring consent trends in Adobe Real-time CDP dashboard is crucial for:

1. Consent Trends Analysis: Track consent trends over time to see shifts in customer preferences, like more opt-ins, showing changes in privacy concerns or strategy impacts.
2. Communication Channel Optimization: Identify top and least favored communication channels to improve engagement and consent compliance.
3. Personalization and Targeting Strategy Development: Develop targeting strategies using consented data to craft personalized marketing.
4. Marketing Channel and Campaign Performance Evaluation: Assess marketing effectiveness by linking consent rates with campaign outcomes, revealing insights into consent strategies and execution.

Monitoring consent trends is crucial for ensuring compliance, gaining strategic insights, fostering customer trust, and improving operational efficiency.

Businesses can tailor insights to their unique needs using Data Distiller’s capability to author insights data models on the accelerated store.

Creating the Dashboards

I want to start with the dashboards I want my marketing and business teams to consume. Let’s review that first and get into how I built them.

Note: Once you create the insights data model using Data Distiller, those models will automatically show up in the Dashboards authoring experience in UI.

Dashboard #1: Consent Analysis - Data collection, Sharing, Personalization

This dashboard displays trends among profiles that have consented to data sharing, personalization, and data collection, broken down by specific values. It also shows the total across all values, as demonstrated in the area chart.

Dashboard #2: Consent Analysis - Marketing Channels

This dashboard displays the trends of overall consent across marketing channels. It includes comparisons between phone and SMS, as well as email and physical mail, to highlight differences in trends. Additionally, it breaks down the overall consent into specific values for each channel.

Dashboard #3: Consent Analysis - Preferred Channel

This final dashboard displays the trends of preferred channels over time, highlighting how some channels are experiencing rapid growth compared to others that are growing more gradually.

Consider the examples mentioned as just a taste of what's possible. With our cutting-edge dashboard fused with the power of Data Distiller, you're now fully equipped to craft any Key Performance Indicators (KPIs) your business teams require to soar to new heights of success.

Designing the Data Model

In this blog, I've tapped into Data Distiller, tailoring my insights with the Consents and Preferences data type. The cool part? This method flexes to fit custom schemas, making consent management a breeze. Let's dive into this adaptable data adventure.

Based on the above schema I have designed my insights data model as follows:

Note: Feel free to architect your own insights data model that resonates with your business's unique pulse. With Data Distiller, you're in the driver's seat, steering towards the decisions that best fit your vision.

Creating the Data Model

To create the insights data model, I use query editor and Data distiller to execute the following model and table creation commands. Note: while creating the database you need to use “ACCOUNT = acp_query_batch” so the model is created on the Accelerate Store.

Create Database:

create database consenttracking
with
  (
    TYPE = QSACCEL,
    ACCOUNT = acp_query_batch
  );

Create Schema:

create schema consenttracking.consenttrackingmodel;

Give user friendly name for the model that shows up in dashboards:

ALTER MODEL consenttracking.consenttrackingmodel
RENAME TO consentanalysis;

Fact and Dimension table creation commands along with constraints:

create table if not exists
  consenttracking.consenttrackingmodel.fact_daily_consent_aggregates
with
  (DISTRIBUTION = REPLICATE) AS
SELECT
  cast(null as int) personalization_consent_count,
  cast(null as int) datacollection_consent_count,
  cast(null as int) datasharing_consent_count,
  cast(null as text) consent_value_id,
  cast(null as timestamp) date
WHERE
  false;

create table if not exists
  consenttracking.consenttrackingmodel.dim_consent_value
with
  (DISTRIBUTION = REPLICATE) AS
SELECT
  cast(null as text) consent_value_id,
  cast(null as text) consent_value
WHERE
  false;

ALTER TABLE fact_daily_consent_aggregates
ADD CONSTRAINT FOREIGN KEY (consent_value_id) REFERENCES dim_consent_value (consent_value_id);

create table if not exists
  consenttracking.consenttrackingmodel.fact_preferred_consent_aggregates
with
  (DISTRIBUTION = REPLICATE) AS
SELECT
  cast(null as int) total_consents,
  cast(null as text) marketing_type,
  cast(null as timestamp) date
WHERE
  false;

create table if not exists
  consenttracking.consenttrackingmodel.dim_marketing_type
with
  (DISTRIBUTION = REPLICATE) AS
SELECT
  cast(null as text) marketing_type,
  cast(null as text) display_name
WHERE
  false;

ALTER TABLE fact_preferred_consent_aggregates
ADD CONSTRAINT FOREIGN KEY (marketing_type) REFERENCES dim_marketing_type (marketing_type);

create table if not exists
  consenttracking.consenttrackingmodel.fact_marketing_consent_aggregates
with
  (DISTRIBUTION = REPLICATE) AS
SELECT
  cast(null as int) total_consents,
  cast(null as text) marketing_type,
  cast(null as text) consent_value_id,
  cast(null as timestamp) date
WHERE
  false;

ALTER TABLE fact_marketing_consent_aggregates
ADD CONSTRAINT FOREIGN KEY (consent_value_id) REFERENCES dim_consent_value (consent_value_id);

ALTER TABLE fact_marketing_consent_aggregates
ADD CONSTRAINT FOREIGN KEY (marketing_type) REFERENCES dim_marketing_type (marketing_type);

Insert the dimension values:

I have the dimensions for consent values based on accepted values and marketing type based on channels

Note: I have attached the xlsx files to this blog which you can convert as csv and use to follow along.

Insert into
  consenttracking.consenttrackingmodel.dim_marketing_type (marketing_type, display_name)
select
  marketing_type,
  display_name
from
  marketing_type_values;

Insert into
  consenttracking.consenttrackingmodel.dim_consent_value (consent_value_id, consent_value)
select
  consent_value_id,
  consent_value
from
  consent_values_mappings;

Scheduling Regular Updates

Since my consent data is stored in Data type structure, this information will be provided in the daily profile export snapshot system dataset: profile export snapshots. Below are the queries that I have scheduled to run once daily to copy from the profile export snapshot to the consents insights data model I created above.

Daily consent aggregates schedule

INSERT INTO
  consenttracking.consenttrackingmodel.fact_daily_consent_aggregates
select
  personalization_consent_count,
  datacollection_consent_count,
  datasharing_consent_count,
  c.consent_value_id,
  CURRENT_DATE as date
from
  (
    select
      personalization_consent_count,
      datacollection_consent_count,
      a.consent_value_id as consent_value_id
    from
      (
        select
          CONSENTS.PERSONALIZE.CONTENT.VAL as consent_value_id,
          count(CONSENTS.PERSONALIZE.CONTENT.VAL) as personalization_consent_count
        from
          profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
        where
          CONSENTS.PERSONALIZE.CONTENT.VAL IS NOT NULL
        GROUP BY
          CONSENTS.PERSONALIZE.CONTENT.VAL
      ) a
      JOIN (
        select
          CONSENTS.COLLECT.VAL as consent_value_id,
          count(CONSENTS.COLLECT.VAL) as datacollection_consent_count
        from
          profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
        GROUP BY
          CONSENTS.COLLECT.VAL
      ) b on a.consent_value_id = b.consent_value_id
  ) c
  JOIN (
    select
      CONSENTS.SHARE.VAL as consent_value_id,
      count(CONSENTS.SHARE.VAL) as datasharing_consent_count
    from
      profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
    GROUP BY
      CONSENTS.SHARE.VAL
  ) d on c.consent_value_id = d.consent_value_id;

Daily preferred consent aggregates schedule

INSERT INTO
  consenttracking.consenttrackingmodel.fact_preferred_consent_aggregates
select
  count(CONSENTS.MARKETING.PREFERRED) as total_consents,
  CONSENTS.MARKETING.PREFERRED as marketing_type,
  CURRENT_DATE as date
from
  profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
where
  CONSENTS.MARKETING.PREFERRED IS NOT NULL
GROUP BY
  CONSENTS.MARKETING.PREFERRED;

Daily marketing consent aggregates schedule

INSERT INTO
  consenttracking.consenttrackingmodel.fact_marketing_consent_aggregates
select
  count(CONSENTS.MARKETING.EMAIL.VAL) as total_consents,
  'email' as marketing_type,
  CONSENTS.MARKETING.EMAIL.VAL as consent_value_id,
  CURRENT_DATE as date
from
  profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
where
  CONSENTS.MARKETING.EMAIL.VAL IS NOT NULL
GROUP BY
  CONSENTS.MARKETING.EMAIL.VAL
UNION ALL
select
  count(CONSENTS.MARKETING.PUSH.VAL) as total_consents,
  'push' as marketing_type,
  CONSENTS.MARKETING.PUSH.VAL as consent_value_id,
  CURRENT_DATE as date
from
  profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
where
  CONSENTS.MARKETING.PUSH.VAL IS NOT NULL
GROUP BY
  CONSENTS.MARKETING.PUSH.VAL
UNION ALL
select
  count(CONSENTS.MARKETING.SMS.VAL) as total_consents,
  'sms' as marketing_type,
  CONSENTS.MARKETING.SMS.VAL as consent_value_id,
  CURRENT_DATE as date
from
  profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
where
  CONSENTS.MARKETING.SMS.VAL IS NOT NULL
GROUP BY
  CONSENTS.MARKETING.SMS.VAL
UNION ALL
select
  count(CONSENTS.MARKETING.POSTALMAIL.VAL) as total_consents,
  'phyMail' as marketing_type,
  CONSENTS.MARKETING.POSTALMAIL.VAL as consent_value_id,
  CURRENT_DATE as date
from
  profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
where
  CONSENTS.MARKETING.POSTALMAIL.VAL IS NOT NULL
GROUP BY
  CONSENTS.MARKETING.POSTALMAIL.VAL
UNION ALL
select
  count(CONSENTS.MARKETING.CALL.VAL) as total_consents,
  'phone' as marketing_type,
  CONSENTS.MARKETING.CALL.VAL as consent_value_id,
  CURRENT_DATE as date
from
  profile_snapshot_export_a818129a_cb88_4642_a2e8_16f921065d70
where
  CONSENTS.MARKETING.CALL.VAL IS NOT NULL
GROUP BY
  CONSENTS.MARKETING.CALL.VAL;

Optional: Populating the Model with Historical Data

Since you created and scheduled the queries to update the insights model every day, you initially won’t have historical data. The history will start from the day this insights model setup was done. If you have access to the historical data, then you can make a one-time push which I have done for this demonstration.

I have the historical data in the following datasets. (refer attachment: Samples_historical_data.xlsx from which each sheet can be saved as a csv to create these datasets.

• fact_daily_consent_aggregates_historical
• fact_preferred_consent_aggregates_historical
• fact_marketing_consent_aggregates_historical

And use the following insert commands to populate my fact tables as a one-time push.

Insert into
  consenttracking.consenttrackingmodel.fact_daily_consent_aggregates (
    personalization_consent_count,
    datacollection_consent_count,
    datasharing_consent_count,
    consent_value_id,
    date
  )
select
  personalization_consent_count,
  datacollection_consent_count,
  datasharing_consent_count,
  consent_value_id,
  TO_TIMESTAMP(date, 'MM/DD/YYYY') as date
from
  fact_daily_consent_aggregates_historical;

Insert into
  consenttracking.consenttrackingmodel.fact_preferred_consent_aggregates (marketing_type, total_consents, date)
select
  marketing_type,
  total_consents,
  TO_TIMESTAMP(date, 'MM/DD/YYYY') as date
from
  fact_preferred_consent_aggregates_historical;

Insert into
  consenttracking.consenttrackingmodel.fact_marketing_consent_aggregates (
    marketing_type,
    consent_value_id,
    total_consents,
    date
  )
select
  marketing_type,
  consent_value_id,
  total_consents,
  TO_TIMESTAMP(date, 'MM/DD/YYYY') as date
from
  fact_marketing_consent_aggregates_historical;

Optional: Integrating with BI Tools

Lastly, if you are already using a BI tool of choice for example Power BI, then point to the same insights data model through Query Service.

Here is a detailed blog I wrote about integrating Power BI: Link

Mastering consent analysis in Adobe Real-Time CDP Dashboards enables businesses to optimize communication strategies, ensure compliance, and tailor marketing campaigns with precision. By leveraging Data Distiller, companies can gain customized insights and drive enhanced customer engagement and trust.

marketing_type.xlsx ‏11 KB

consent_val.xlsx ‏11 KB

Samples_historical_data.xlsx ‏102 KB