EXPLORE 400: Exploring Offer Decisioning Datasets with Data Distiller

Unleashing Insights from Offer Decisioning Datasets with Data Distiller

Prerequisites

You need a basic understanding of how to write nested queries and working with nested data.

PREP 501: Ingesting JSON Test Data into Adobe Experience Platform

You should get familiar with navigating around with web data:

EXPLORE 201: Exploring Web Analytics Data with Data Distiller

You should also familiarize yourself with AJO System Datasets:

EXPLORE 300: Exploring Adobe Journey Optimizer System Datasets with Data Distiller

Offer Decisioning Framework

The journey begins with activities, which are broad tasks or campaigns defining when and where offers will be shown. Within each activity, placements define the specific locations (e.g., web banners or emails) where offers will be delivered. The decisioning engine then uses eligibility rules, ranking algorithms, and profile constraints to determine which offer—either a personalized offer or a fallback offer—is most appropriate for each user in a specific placement. When a decision is made, it generates a decision event, which captures the result of that interaction, including the offer proposition and user engagement with the offer. All these components work together to ensure that users receive the most relevant and timely offers during their journey.

Decisions

At the core of Adobe Journey Optimizer’s offer delivery system is the decision-making process. Decisions are the rules and criteria that determine which offers are presented to a user. Every decision is influenced by a variety of factors, including profile constraints, contextual data, and business rules. Decisions can be thought of as the "brains" behind which offer gets presented at any point in the customer journey. They involve multiple steps:

• Contextual data is real-time information about the user's current environment, such as time, location, device, and session activity. It helps tailor offers based on what’s happening at the moment. For example, users near a store might receive location-based promotions, or users on a mobile device could see mobile-optimized offers. Contextual data ensures offers are timely and suited to the user's immediate situation.

• Eligibility: Decides whether a user qualifies for certain offers based on their profile.

• Ranking: Determines the priority and relevance of offers using scoring and/or rules.

• Constraints: Factors such as time, placement, and profile attributes that limit when and how offers can be shown.

  • Profile constraints are rules based on a user's demographics, behavior, preferences, and audience segments that determine offer eligibility. These include factors like age, location, past purchases, and membership in loyalty programs. For example, a luxury car promotion might only be shown to high-income users or frequent shoppers. By using profile constraints, brands ensure that offers are highly relevant to each individual.

Decisions drive the selection process for offers, taking into account activities and placements to determine the best offer for a user in a given context.

Offers

An offer is the actual content or proposition presented to users. Offers could be discounts, product recommendations, promotions, or other types of personalized content that a brand wants to deliver. Offers are stored in the Offer Library and can be dynamically selected based on the decision criteria. Offers contain:

• Content: The actual message or media delivered to users (e.g., banners, emails).

• Metadata: Details like offer name, description, and associated rules or tags.

There are different types of offers based on how they are chosen and delivered, which brings us to personalized offers and fallback offers.

Personalized Offers

Personalized offers are a special type of offer tailored specifically to individual users. These offers are selected based on detailed user profiles, contextual data, and behavior. The Personalized Offers Dataset provides data about the content and customization of these offers, including the rules that will be applied to personalize the offer to a specific user.

Fallback Offers

A fallback offer is presented when no personalized offer meets the eligibility or decisioning criteria. In cases where primary offers fail (due to constraints like timing, audience mismatch, or other criteria), fallback offers ensure that some content is still delivered to the user. The Fallback Offers Dataset captures data about the fallback logic and the conditions under which these offers are shown. While fallback offers are secondary to personalized offers, they help maintain engagement when personalization fails.

Placements

Placements are the designated spaces or contexts where offers are shown to users. A placement could be a web page banner, an email slot, an in-app message, or any other digital location where an offer might appear. Placements are critical in determining where and how an offer is delivered. Each placement has:

• Channel information: Where the content will be displayed (e.g., web, email, mobile).

• Media type constraints: Ensures the content format (e.g., image, text, video) matches the requirements of the placement.

• Description and names: Describes the function and role of the placement (e.g., "homepage banner").

The Placements Dataset stores data about these locations, ensuring that the right offer is rendered in the right place at the right time.

Activities

Activities are the overarching campaigns or tasks that determine when and how offers are presented within a customer journey. An activity could be an email campaign, an ad shown during a promotion, or a banner placed on a website. The activity serves as the container for offers and is tied to specific placements and decisions.

Activities can have multiple properties:

• Start and End Time: Determines the timeframe during which the activity is active.

• Ranking and Eligibility: Tied to the decisioning rules that determine which offers are shown during the activity.

• Fallback and Constraints: Includes rules for fallback offers if no primary offers are eligible.

The Activities Dataset captures much of the logic behind activities, including ranking and placement constraints.

Decision Events

A decision event is a time-stamped interaction that records what happened when a decision was made. It is essentially the event log that shows which offers were presented, accepted, or rejected by users. The ODE (Offer Decision Events) Dataset records these events, providing detailed information about each decision that occurred during a user’s interaction. Each decision event captures:

• Timestamp: When the event occurred.

• Proposition details: The offer that was proposed.

• Interaction outcome: Whether the user accepted, clicked, or ignored the offer.

• Placement and activity context: Where the offer was placed and within which activity the decision was made.

Decision events allow marketers to track the effectiveness of their offers and adjust their decisioning strategies based on user engagement and outcomes.

High Level Overview of Offer Decisions

Before diving into the datasets, it's crucial to first understand the specifics of the business process—specifically, the steps a user takes to configure the system that generates the datasets. This understanding lays the foundation for meaningful analysis, and without it, grasping the context behind the data becomes much more challenging.

1. Navigate to Decision Management->Offers->Offers->Create Offer

Personalized or Fallback offer optiions.

1. Offers have a time-to-live and include attributes referred to as Characteristics within the datasets.

Offer configuration

1. You can apply constraints at the offer level to control who can view it and limit the frequency of how many times the offer is shown within a specific time period.

Offers can be constrainted in what they do.

1. You can add a decision rule as well:

You can add a decision rule.

1. The representation is where you define the placement, assets, and the channel through which the offer will be displayed.

Representation focuses on channel delivery and placement

1. Offers have to be part of an offers collection on which decision rules will be applied. Navigate to Decision Management->Offers->Collections->Create Collection

Create a new collection and you can edit an existing one.

1. You can add offers to this collection

Adding offers to a collection

1. Navigate to Decision Management->Offers->Collections->Create Decision. Decisions have a time to live,

Decision rule configuration.

1. You will need to add a decision scope, which is essentially a grouped set of rules, and specify a placement.

Decision rule in a decision scope.

1. You will need to add a offer collection

You can add an offer collection

1. With multiple offers available, you can select the audience, algorithm, and other criteria to determine the winning offer. Some offers will be eliminated at this stage if they do not meet the specified criteria.

Decision rules impose further constraints

1. Every decision rule requires a fallback offer:

Fallback offer selection

1. Decision rule on the offer collection which can now be activated.

Final configuration

Decisions Object Repository - Activities Dataset

The Decisions Object Repository - Activities Dataset contains additional information that is more focused on the decision-making logic and criteria behind offer selection ot be done.

• Criteria and Constraints: The Activities Dataset provides detailed information about the criteria used to make decisions, such as the constraints that are applied based on profile information, context, and eligibility rules. Fields like _experience.decisioning.criteria, _experience.decisioning.criteria.profileConstraints, and _experience.decisioning.criteria.placements describe the rules, constraints, and filters applied during decision-making.

• Ranking and Prioritization: The Activities Dataset contains detailed fields about how offers are ranked and prioritized, including scoring functions and ranking strategies. Fields like _experience.decisioning.criteria.ranking, _experience.decisioning.criteria.ranking.order, and _experience.decisioning.criteria.ranking.priority describe how offers are ranked based on scores or priorities.

• Fallback Option Logic: The Activities Dataset contains fields related to fallback options and detailed logic about how and why fallback options are selected if regular options do not qualify. Fields like _experience.decisioning.criteria.fallback explain the conditions under which fallback options are selected, including the logic behind their use.

• Process: The Activities Dataset provides additional metadata related to the decision-making process, such as workflow identifiers (_experience.decisioning.batchID) and revision tracking (ETags). Activities Dataset : Includes fields like _experience.decisioning.batchID, _repo.etag, and _experience.decisioning.criteria.propositionContentKey, which help track the versioning and batch processing behind the decision events.

• Profile and Audience Constraints: The Activities Dataset includes detailed profile constraints and how segments or rules are applied to profiles to determine the eligibility of an offer. Fields like _experience.decisioning.criteria.profileConstraints, _experience.decisioning.criteria.profileConstraintType, and _experience.decisioning.criteria.segmentIdentities are used to track the audiences and segments that influence decisions.:

• Ranking Details: The Decisions Object Repository - Activities Dataset has specific fields that explain how the best option is determined, including ranking orders and scoring functions. It includes fields like _experience.decisioning.criteria.ranking.orderEvaluationType, which specify how options are evaluated and ranked.

Understand the Structure of the Activities Dataset

SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
where table_name = 'decision_object_repository_activities'

The result is:

Top level columns in the Activities dataset.

Explore the Structure of the Activities Dataset

SELECT to_json(p._experience.decisioning) 
FROM decision_object_repository_activities p
LIMIT 10;

Explore the fields under _experience.decisioning.

Retrieve Records from the Activities Dataset

SELECT to_json(_experience) FROM decision_object_repository_activities;

The result is:

Records retrieved from the Activities Dataset.

Retrieve Decisioning Criteria for Offers

This query will show you the decisioning criteria (the rules or algorithms) applied for each activity. This might include complex decisioning logic, filters, and algorithms.

SELECT 
    p._id AS activityId,
    p._experience.decisioning.name AS activityName,
    p._experience.decisioning.criteria AS decisioningCriteria
FROM 
    decision_object_repository_activities p
WHERE 
    p._experience.decisioning.criteria IS NOT NULL;

The results will be:

Results of the decision criteria query

To understand this result, let us navigate to Offers->Decisions->BP Luma Offers in the AEP UI

BP Luma offers decision activity

Let us correlate the result of the query for BPLumaOffes(first line of the result) and the above:

• Activity ID Match: Both the query and the screenshot reference the same activity ID (xcore:offer-activity:15fec9f63011bd8), meaning they are referring to the same decision-making process.

• Placements:

  • The query returns specific placements where offers are shown, such as "xcore-offer-placement:15fdf378e188bb6e", which likely corresponds to one of the placements like Luma - Home Banner.

  • Multiple placements are involved in the same activity, just as in the screenshot where offers are placed in banners, cards, and emails.

  • This would require us to pull metadata about the placements from the Placements Dataset.

• Decisioning Criteria and Filters:

  • The query result shows the decision filters applied (e.g., "xcore-offer-filter:15fdf474893c3ef0"), which control which offers are shown based on the user's profile, context, and placement.

  • The eligibility criteria shown in the query match the audience eligibility shown in the above screenshot (e.g., "allSegments" in the query vs. "1 audience" in the screenshot).

• Ranking Methods:

  • Note that the query result doesn’t explicitly show the ranking method, but we know from the screenshot that the ranking method for certain placements is based on a personalized model (e.g., "Luma Personalized Model" for the Home Banner). In other placements, it is based on offer priority.

• Fallback Offer:

  • The fallback offer shown in the query (xcore:fallback-offer:15fec32dffc546a0) matches the fallback offer in the screenshot ("BP Luma - Fallback"). This confirms that the system will show the fallback offer if none of the primary offers qualify

Decisions Object Repository - Personalized Offers Dataset

The Personalized Offers Dataset represents personalized offers that are created and prepared to be served to users based on various decision-making logic. This dataset includes extensive metadata on offer content, audience segmentation, eligibility rules, and decision criteria, allowing you to tailor offers based on user profiles, behaviors, and contextual data. It also captures the ranking, scoring, and prioritization mechanisms used to determine which personalized offers are presented to users in different scenarios.

Key Features in Personalized Offers Dataset

• Profile Constraints: The Personalized Offers Dataset provides detailed rules and constraints regarding which offers are eligible for certain user profiles, ensuring that offers are customized to meet individual needs. Fields like _experience.decisioning.profileConstraints, _experience.decisioning.profileConstraintType, and _experience.decisioning.segmentIdentities detail the rules applied based on user profiles and segments.

• Content Components: The Personalized Offers Dataset captures granular details about the content associated with personalized offers, including various language variants, formats, and delivery methods. Fields like _experience.decisioning.contents, _experience.decisioning.contents.components.language, and _experience.decisioning.contents.components.format provide detailed metadata about the structure of personalized offer content.

• Ranking and Prioritization: The Personalized Offers Dataset contains fields related to ranking strategies, scoring functions, and order evaluation, allowing for complex decision-making regarding which offers are prioritized for users. Fields like _experience.decisioning.ranking, _experience.decisioning.orderEvaluationType, and _experience.decisioning.rankingStrategy provide detailed ranking logic.

• Lifecycle Management: The Personalized Offers Dataset tracks the lifecycle status of each offer, allowing for better workflow management by indicating whether an offer is in draft, approved, live, or archived state. Fields like lifecycleStatus track the status of offers, ensuring proper management of their visibility and usage in campaigns.

Understand Structure of Personalized Offers Dataset

SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
where table_name = 'decision_object_repository_personalized_offers'

You should get:

Top level columns within the dataset

Retrieve Records from Personalized Offers Dataset

SELECT to_json(_experience) FROM decision_object_repository_personalized_offers

Contents of the Persoalized Offers Dataset

The results you will get will look like this in JSON:

{
  "decisioning": {
    "ranking": {
      "priority": 0
    },
    "name": "BP Luma - Loyalty Membership",
    "contents": [
      {
        "placement": "xcore:offer-placement:15fdf228c3fec9eb",
        "components": [
          {
            "_dc": {
              "format": "image/png"
            },
            "_type": "https://ns.adobe.com/experience/offer-management/content-component-imagelink",
            "deliveryURL": "https://dpqy7l2qgw0r3.cloudfront.net/0aa64df0-e3ce-11e9-ace4-cb8a25ba725b/urn:aaid:aem:8b68c634-151e-4059-a626-a95fdc4e1833/oak:1.0::ci:b7e14744a2dde9486e0a9a45cb9a9e28/93b54966-7c78-3b23-8afb-649f0e8acff8",
            "linkURL": "https://luma.enablementadobe.com/content/luma/us/en/community/members.html",
            "_repo": {
              "name": "Loyalty Banner.png",
              "resolveURL": "https://author-p28416-e87881.adobeaemcloud.com/content/dam/BP/Luma/Loyalty%20Banner.png/jcr%3Acontent/renditions/cq5dam.thumbnail.140.100.png?cacheinfo=653eb618fef5c459aed4b796501437a5",
              "id": "urn:aaid:aem:8b68c634-151e-4059-a626-a95fdc4e1833"
            }
          }
        ]
      },
      {
        "placement": "xcore:offer-placement:15fdf378e188bb6e",
        "components": [
          {
            "_dc": {
              "format": "image/png"
            },
            "_type": "https://ns.adobe.com/experience/offer-management/content-component-imagelink",
            "deliveryURL": "https://dpqy7l2qgw0r3.cloudfront.net/0aa64df0-e3ce-11e9-ace4-cb8a25ba725b/urn:aaid:aem:d4e92f28-38b5-4e14-a7ab-9f6bf6cd7dc1/oak:1.0::ci:3cf0cda086124eae041430323016d94b/de10b283-7648-3f0e-a9c9-bfbe59b01b30",
            "linkURL": "https://luma.enablementadobe.com/content/luma/us/en/community/members.html",
            "_repo": {
              "name": "Loyalty Card.png",
              "resolveURL": "https://author-p28416-e87881.adobeaemcloud.com/content/dam/BP/Luma/Loyalty%20Card.png/jcr%3Acontent/renditions/cq5dam.thumbnail.140.100.png?cacheinfo=a3bd18f557beb74edd958adfb0a1cc17",
              "id": "urn:aaid:aem:d4e92f28-38b5-4e14-a7ab-9f6bf6cd7dc1"
            }
          }
        ]
      },
      {
        "placement": "xcore:offer-placement:15fdf24e2efadcdf",
        "components": [
          {
            "_dc": {
              "format": "image/png"
            },
            "_type": "https://ns.adobe.com/experience/offer-management/content-component-imagelink",
            "deliveryURL": "https://dpqy7l2qgw0r3.cloudfront.net/0aa64df0-e3ce-11e9-ace4-cb8a25ba725b/urn:aaid:aem:8b68c634-151e-4059-a626-a95fdc4e1833/oak:1.0::ci:b7e14744a2dde9486e0a9a45cb9a9e28/93b54966-7c78-3b23-8afb-649f0e8acff8",
            "linkURL": "https://luma.enablementadobe.com/content/luma/us/en/community/members.html",
            "_repo": {
              "name": "Loyalty Banner.png",
              "resolveURL": "https://author-p28416-e87881.adobeaemcloud.com/content/dam/BP/Luma/Loyalty%20Banner.png/jcr%3Acontent/renditions/cq5dam.thumbnail.140.100.png?cacheinfo=653eb618fef5c459aed4b796501437a5",
              "id": "urn:aaid:aem:8b68c634-151e-4059-a626-a95fdc4e1833"
            }
          }
        ]
      }
    ],
    "calendarConstraints": {
      "startDate": "2022-10-25T06:00:00.000Z",
      "endDate": "2050-05-31T06:00:00.000Z"
    },
    "profileConstraints": {
      "profileConstraintType": "none"
    },
    "lifecycleStatus": "approved",
    "tags": [
      "xcore:tag:1771ac5a22abb9f7",
      "xcore:tag:15fdf3abddd39b68"
    ]
  }
}

Explanation of Fields:

1. decisioning: This is the top-level object that encapsulates all decisioning details related to this offer.

   • ranking:

     • priority: The ranking priority of this offer. A value of 0 typically indicates the highest priority.

   • name: The name of the offer, here labeled as "BP Luma - Loyalty Membership", which may indicate that this is an offer targeted at customers in a loyalty membership program.

2. contents: This array holds multiple offer placements. Each object within the contents array represents one placement of the offer in a specific location or context (e.g., on a website, in an app).

   • placement: This is a unique identifier for where the offer will appear (e.g., a banner on a webpage or in-app placement).

   • components:

     • Each component describes the content used in that placement (e.g., an image, text, or link).

     • _dc.format: The format of the content (e.g., "image/png" for PNG image).

     • _type: The type of content component, here it's an image link, pointing to an external resource.

     • deliveryURL: The URL where the content (image) is hosted.

     • linkURL: The URL the user is directed to when they interact with the content (e.g., a banner leading to a loyalty program page).

     • _repo: Contains metadata about the image asset.

       • name: The name of the asset (e.g., "Loyalty Banner.png").

       • resolveURL: A direct link to a thumbnail of the image.

       • id: A unique identifier for the asset.

3. calendarConstraints: These fields define when the offer is valid.

   • startDate: The start date of the offer (in ISO 8601 format), meaning this offer becomes active on October 25, 2022.

   • endDate: The end date of the offer, meaning it will expire on May 31, 2050.

4. profileConstraints: These fields define which user profiles are eligible for the offer.

   • profileConstraintType: The type of profile constraint applied. In this case, "none" means that no specific profile constraints are applied, making the offer available to all users.

5. lifecycleStatus: The current status of the offer.

   • approved: This indicates that the offer has been approved and is ready to be displayed to users.

6. tags: These are tags associated with the offer, typically used for categorization, filtering, or reporting purposes.

   • Examples of tag identifiers: "xcore:tag:1771ac5a22abb9f7", "xcore:tag:15fdf3abddd39b68".

Flatten the Personalized Offers Table

SELECT 
    p._id AS offerId,
    p._repo.etag AS repo_etag,
    p._experience.decisioning.ranking.priority AS priority,
    p._experience.decisioning.name AS offerName,
    p._experience.decisioning.contents[0].placement AS placement,
    p._experience.decisioning.contents[0].components[0]._dc.format AS contentFormat,
    p._experience.decisioning.contents[0].components[0]._dc.language[0] AS contentLanguage,
    p._experience.decisioning.contents[0].components[0].content AS contentData,
    p._experience.decisioning.calendarConstraints.startDate AS startDate,
    p._experience.decisioning.calendarConstraints.endDate AS endDate,
    p._experience.decisioning.profileConstraints.profileConstraintType AS profileConstraintType,
    p._experience.decisioning.profileConstraints.segmentIdentities[0]._id AS segmentId,
    p._experience.decisioning.characteristics['Offer ID'] AS offerIdCharacteristic,
    p._experience.decisioning.characteristics.domain AS offerDomain,
    p._experience.decisioning.characteristics.type AS offerType,
    p._experience.decisioning.characteristics.saleType AS saleType,
    p._experience.decisioning.lifecycleStatus AS lifecycleStatus
FROM 
    decision_object_repository_personalized_offers p;

The field p._experience.decisioning.characteristics refers to a sub-object within the decisioning structure of an offer, which stores specific characteristics or attributes related to that offer. In Adobe Journey Optimizer, characteristics can be thought of as metadata or additional properties that define key details or behavior for an offer. These characteristics are typically used to differentiate offers, apply business rules, or drive personalization and optimization in decision-making.

The results are the following:

Flattening the personalized offers table.

Retrieve Latest Version of Each Offer

SELECT 
    p._id AS offerId,
    p._repo.etag AS repo_etag,
    p._experience.decisioning.ranking.priority AS priority,
    p._experience.decisioning.characteristics.customerLoyalty AS c_customerLoyalty,
    p._experience.decisioning.characteristics['Offer ID'] AS c_offerId,
    p._experience.decisioning.characteristics.productCategory AS c_productCategory,
    p._experience.decisioning.characteristics.discountAmount AS c_discountAmount,
    p._experience.decisioning.characteristics.expiryDate AS c_expiryDate,
    p._experience.decisioning.characteristics.promotionType AS c_promotionType,
    EXPLODE(p._experience.decisioning.contents.placement) AS placementId
FROM 
    decision_object_repository_personalized_offers p
JOIN 
    (SELECT 
         m._id AS offerId, 
         MAX(m._repo.etag) AS latest_repo_etag
     FROM 
         decision_object_repository_personalized_offers m
     GROUP BY 
         m._id
    ) mx 
    ON p._id = mx.offerId
    AND p._repo.etag = mx.latest_repo_etag;

The results will be:

Retrieving the latest version of an offer.

Observe the following:

• m._id AS offerId: Retrieves each offer's unique ID.

• MAX(m._repo.etag) AS latest_repo_etag: Finds the highest (latest) _repo.etag (which represents the version) for each offer.

• GROUP BY m._id: Ensures that the subquery groups the offers by their _id, so that it returns the latest version for each offer.

Retrieve Personalized Offers Greater than Specific Priority

SELECT 
    p._id AS offerId,
    p._repo.etag AS repo_etag,
    p._experience.decisioning.ranking.priority AS priority,
    p._experience.decisioning.characteristics['Offer ID'] AS c_offerId
FROM 
    decision_object_repository_personalized_offers p
WHERE 
    p._experience.decisioning.ranking.priority >1;

Offers that had a priority greater than 1

Filter Personalized Offers by Date Range

SELECT 
    p._id AS offerId,
    p._repo.etag AS repo_etag,
    p._experience.decisioning.calendarConstraints.startDate AS startDate,
    p._experience.decisioning.calendarConstraints.endDate AS endDate,
    p._experience.decisioning.characteristics['Offer ID'] AS c_offerId
FROM 
    decision_object_repository_personalized_offers p
WHERE 
    p._experience.decisioning.calendarConstraints.startDate <= CURRENT_DATE
    AND p._experience.decisioning.calendarConstraints.endDate >= CURRENT_DATE;

Personalized offers filtered by date range

Group Personalized Offers by Product Category

SELECT 
    p._experience.decisioning.characteristics.productCategory AS productCategory,
    COUNT(p._id) AS offerCount
FROM 
    decision_object_repository_personalized_offers p
GROUP BY 
    p._experience.decisioning.characteristics.productCategory;

The results will look like the following:

Grouping offers by product category

Retrieve Personalized Offers by Placement

SELECT 
    p._id AS offerId,
    p._experience.decisioning.characteristics['Offer ID'] AS c_offerId,
    EXPLODE(p._experience.decisioning.contents.placement) AS placementId
FROM 
    decision_object_repository_personalized_offers p
WHERE 
    ARRAY_CONTAINS(p._experience.decisioning.contents.placement, 'xcore:offer-placement:15fdf228c3fec9eb');

Offers by placement

Sort Personalized Offers by Priority

SELECT 
    p._id AS offerId,
    p._experience.decisioning.ranking.priority AS priority,
    p._experience.decisioning.characteristics['Offer ID'] AS c_offerId
FROM 
    decision_object_repository_personalized_offers p
ORDER BY 
    p._experience.decisioning.ranking.priority ASC;

Offers sorted by priority

Retrieve Profile Constraints and Segment Identities

SELECT 
    p._id AS offerId,
    p._experience.decisioning.profileConstraints.profileConstraintType AS profileConstraintType,
    EXPLODE(p._experience.decisioning.profileConstraints.segmentIdentities) AS segmentIdentity
FROM 
    decision_object_repository_personalized_offers p;

Profile constraint and the segment identity

OfferID and PlacementID combination can act like a composite key joining this metadata sith the data in the Offer Decision Events table where the actual offer was delivered.

Decisions Object Repository - Fallback Offers

The Fallback Offers Dataset is very similar to the Personalized Offers Dataset except that it provides a detailed record of fallback offers that should e presented when primary decisioning options do not qualify. This dataset captures rich metadata about the fallback offer content, including components, formats, delivery URLs, and asset repository details, ensuring that the offer is accurately rendered across various digital experiences. Additionally, it tracks the lifecycle status of offers, allowing for effective management of the offer’s state, whether it’s in draft, live, or archived mode. Each fallback offer is further enriched with characteristics such as tags for categorization, and placement details that specify where the offer is deployed.

SELECT to_json(_experience) FROM decision_object_repository_fallback_offers

The fallback offers will look simiilar to the personalized offers.

You can take the same queries that we used on the Personalized Offers dataset and apply it here as the fields are identical.

OfferID and PlacementID combination can act like a composite key joining this metadata sith the data in the Offer Decision Events table where the actual offer was delivered.

Decisions Object Repository - Placements Dataset

The Placements Dataset tracks the various contexts or "placements" where offers are to be delivered to users. A placement is a defined location, such as a banner on a web page, an email slot, or an in-app area, where personalized offers or dynamic content can be presented. This dataset captures metadata about each placement, including the associated content types, media formats, channels, and descriptions that help manage and optimize where and how offers appear to the target audience.

• Placement Descriptions and Names: The Placements Dataset contains detailed metadata describing each placement's function and purpose, such as a web banner or email slot, and provides a human-readable name for each placement. Fields like name and description provide contextual information on where the content will be rendered.

• Content Channels and MIME Types: The Placements Dataset tracks the specific channels (e.g., web, mobile, email) where the placement occurs, as well as the supported MIME media types (e.g., image formats) for content rendered in each placement. Fields like channelID and contentTypes.MIME Media Type capture the constraints on media formats and channels for each placement.

• Content Representation: The Placements Dataset defines the types of content components allowed in each placement. This helps ensure that the right type of content (e.g., image, text, video) is displayed correctly in the right context. Fields like componentType specify the content component types, ensuring compatibility between the content and the placement.

• Placement ETags: The Placements Dataset tracks the revision history of each placement, providing an ETag that helps manage and track changes to the placement over time. Fields like etag capture revision metadata, helping maintain version control of placements.

Understand Structure of the Placements Dataset

Execute the following:

SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
where table_name = 'decision_object_repository_placements';

The top level fields in the Placements dataset.

Explore the Structure of the Placements Dataset

Execute the following:

SELECT to_json(_experience) 
FROM decision_object_repository_placements;

The result will be:

Contents of the Placement dataset.

Group by Channel Type

You can group the placements by their channelID to see how many placements exist for each channel.

SELECT _experience.decisioning.channelID, COUNT(*) AS total_placements
FROM decision_object_repository_placements
GROUP BY channelID;

The result will be:

Results of the channel query

Count of Placements by Component Type

To get an overview of how many placements there are for each componentType, you can run this query

SELECT _experience.decisioning.componentType, COUNT(*) AS total_placements
FROM decision_object_repository_placements
GROUP BY componentType;›

The result helps in identifying the distribution of placements across different content types (e.g., HTML, image, text, JSON).