Offer Proposition Logs Analysis via Data Distiller Query Pro Mode

[#1]

Offer Proposition Logs Analysis via Data Distiller Query Pro Mode

Author: @annamalai  (Senior Product Manager)

Reviewer: Arun Manoharan (Director Product Manager)

SQL: Jabeerulla Shaik (Software Development Engineer)

Welcome to a guide on utilizing Data Distiller's Query Pro Mode to create an SQL-based analysis that allows the practitioner to perform examination on Offer proposition history.

First, let’s consider the different use cases for this analysis

Use Cases

Compliance and Regulations: Customers requiring a history of propositions may also be necessary for regulatory compliance, especially in heavily regulated industries.

Tracking Past Offers: Keep a log of the specific offers or propositions presented to each profile over time.

In this blog, I will cover two analyses:

At Offer Level: Select one or more offer IDs and see the profile identities

At Profile Level: Select one or more profile identities and see the offer & placement details.

Step 1: Access The Dataset

I will use the ODE Decision Events dataset, a system-generated dataset from Adobe Offer Decisioning. While this blog follows the system-generated dataset, you can perform the same analysis on any datasets you bring to the Adobe Experience platform or from a derived dataset from the system-generated dataset. You can also bring in the post-offer delivery feedback dataset and perform the analysis on the combination of both the system datasets and the feedback dataset.

Step 2: Write The SQL

Feel free to explore the dataset(s) using the query editor to arrive at the data you need for your analysis. For this blog, I am focusing on retrieving the profile identities and offer placement details from the system dataset, and the SQL I used is provided below. Please note that the table name “ode_decisionevents_emea_uk_sc_decisioning” is specific to the sandbox dataset name from the previous step. You will have to find the table specific to your sandbox and replace it in the SQL.

 SELECT
  profile,
  propositionDetails.placement.id placementID,
  propositionDetails.placement.name placementLabel,
  COALESCE(selection.id, fallback.id) offerID,
  (
    CASE
      WHEN (
        (selection.id IS NOT NULL)
        AND (selection.name IS NOT NULL)
      ) THEN selection.name
      ELSE fallback.name
    END
  ) offerLabel,
  (
    CASE
      WHEN (
        (selection.id IS NOT NULL)
        AND (selection.name IS NOT NULL)
      ) THEN false
      ELSE true
    END
  ) isFllbackOffer
FROM
  (
    SELECT
      CONCAT(key, ':', VALUE[0].id) AS profile,
      _experience.decisioning.propositionid propositionID,
      _experience.decisioning.propositiondetails AS propositionDetails,
      _experience.decisioning.propositiondetails.fallback fallback,
      explode_outer (
        _experience.decisioning.propositiondetails.selections
      ) selection
    FROM
      (
        SELECT
          explode (identitymap),
          *
        FROM
          ode_decisionevents_emea_uk_sc_decisioning
      ) flattened_namespace
  ) AS flattened_decisioning_events
GROUP BY
  profile,
  propositionDetails,
  fallback,
  selection;

Step 3: Build Data Model

Now, I can persist the result of the SQL analysis from Step 2 in the accelerated store for easy analysis by business and marketing personas. I can build the necessary data model and then create the charts for the template using Data Distiller Query Pro mode.

Here are the SQL for the data model creation, including the table to store the results from Step 2:

--Create a database to store all the tables for our dataset based dashboard/chart creation
create database offersanalysis
with
  (
    TYPE = QSACCEL,
    ACCOUNT = acp_query_batch
  );
--Create a schema to store all the tables for our insights data model
create schema offersanalysis.offersanalysismodel;
--Provide the insights data model a user friendly name that shows up in the dashboard section
ALTER MODEL offersanalysis.offersanalysismodel
RENAME TO offersanalysisinsights;

CREATE TABLE IF NOT EXISTS
  offersanalysis.offersanalysismodel.ode_offer_profile
with
  (DISTRIBUTION = REPLICATE) AS
SELECT
  cast(null as text) profile,
  cast(null as text) placementID,
  cast(null as text) placementLabel,
  cast(null as text) offerID,
  cast(null as text) offerLabel,
  cast(null as text) isFllbackOffer
WHERE
  false;

Once you have created and inserted the data, you can schedule the refresh of your SQL based on your desired dashboard refresh needs. Note: The schemas for the above data model are visible in the Schemas UI. Once you enable show ad-hoc schemas, you will see the accelerated store schemas. You can use this to set the desired governance labels.

Step 4: Build Offer Proposition Template

Finally, you can start building the desired charts by reviewing the tutorials as follows

1. Global filter creation: Link
2. Advanced table widget creation: Link
3. Data Distiller Query Pro Mode: Link

Below are the filters I have created one for Offer ID and one for Profile Identity

And the table widget I created as well as linked to the two global filter above is as follows:

While in this blog I only cover one table widget, you are welcome to add additional summary metrics such as total offers, count of fall back offers etc. as per your business needs.

Final Outcome

And that's it. You set up this template once, and every day, both business and technical personas can easily visit it, update the filters to select the desired profile identities or Offer IDs, and review the KPIs. What's more, you also automatically download it as PDF and CSV without any additional work. Below is the output of how it looks once completed.