AEP and 1:M Relationships
02 Feb 2025 » Platform
One of the fundamental features of the Adobe Experience Platform (AEP) is that its database revolves around a single entity: the profile. All schemas and all datasets are just part of the unified profile. I know, there are some exceptions, like the lookup tables and B2B tables, but they do not have the same power as the profile. The reason for this decision is very simple: when running marketing campaigns, you want to personalize to the individual. It has been proven time and again that generic campaigns are less successful than personalized messages, be it on the Web or in an email.
The Challenge
This design decision in AEP’s architecture comes at a cost: managing 1:M relationships. If you have encountered this situation, you know what I am talking about. If not, let me give you a few examples:
- An individual can own multiple products.
- An individual can have multiple email addresses.
- In some verticals, it is common to have a parent account that has one or multiple child accounts.
Relational databases have a very simple way of managing this relationship: using multiple tables and foreign keys. You can then have multiple entries in a table that refer to a single entry in a different table. A very simple SQL statement with a JOIN combines the information in a way that is useful.
AEP, on the other hand, does not have such a simple solution. There are many features that you can use to mimic this 1:M relationship, although none is perfect. Choose your poison!
Is It Really Needed?
Before going into the solutions, I would suggest that you take a deep breath and ask yourself: “Do I really need 1:M relationships?”
As counterintuitive as the previous question might sound, it is really not that foolish. Many IT and marketing teams will request this feature and put it as a requirement. However, in some cases, the moment you start scratching a bit under the surface, you will notice that they just want it “just in case” or because “their previous tool had it”.
These two answers should not be accepted. AEP is not a general data processing tool nor a CRM. It is a tool for a very specific purpose: digital marketing. If there is no marketing use case that requires a 1:M relationship, you should not try to implement it.
The previous paragraph should actually be applied to everything in AEP: you should only implement what you need. Trying to build a mammoth solution that caters to every potential future use case is a tried and tested path to failure. No matter what your crystal ball tells you, nobody can predict the future; in other words, no implementation is future-proof. I do not see an issue if you have to redo parts of the implementation in one year, after you have made millions with a simple implementation that took three months but did not cover all use cases. The alternative option is to spend one full year on an implementation that will cost you a lot of money, without getting any value during that period.
In summary, if you do not need 1:M relationships in AEP, skip it and, with it, the rest of this post.
Solutions
Let me show you now the solutions that I have seen in my projects. As I said, none is ideal, but I am sure there is one that allows you to fulfill your use case. I am sure that there are more solutions, so if you have another one, please share it in the comments.
Arrays
AEP supports arrays natively, just like JSON does. An array can be of scalar types or objects.
In theory, you may think that this is the one-size-fits-all solution for 1:M, but arrays have a number of drawbacks:
- Any modifications to the array will require sending the full array again.
- There is limited support for arrays in RTCDP destinations.
- There is limited support for arrays in AJO.
The segment engine, on the other hand, works just fine with arrays. So, if you are only going to use arrays for segmentation, then I would say “go for it”. Otherwise, look for other options to see if they work better.
Events
As crazy as it might sound, I know of implementations where arrays have been modeled like events. The reason is very simple: a profile can have any number of events, thus allowing for a 1:M relationship.
With this solution, you create an ExperienceEvent XDM schema and a dataset that you fill with the values of the object that have a cardinality greater than 1.
The advantages are obvious:
- You can keep on adding to the events dataset.
- Segmentation becomes very easy.
But there are also important drawbacks to take into account:
- The events are deleted when the dataset TTL is reached, at which point you will have to resend the events.
- You cannot delete events.
- You cannot export events through RTCDP destinations.
- AJO only supports events that are captured during a journey.
Flattening the Data
In some scenarios, the M in 1:M has an upper boundary and this upper boundary is a single digit. Or in X% of the cases, the M is a single digit. In this situation, we tend to recommend flattening or denormalizing the data.
The idea is to create M field groups in the schema, all with the exact same attributes. Then, your ETLs populate as many field groups as needed.
The most interesting advantage of this approach is that you can use it in RTCDP segments, in RTCDP destinations, in AJO, in CJA… Everywhere!
On the other hand, using the values can become a bit cumbersome. For example, for a segment that relies on an attribute of those field groups, you may need to add M conditions to test each of the field groups. It can also be difficult to traverse all field groups and extract the one that contains the value that you are looking for.
Multiple Profile Types
One of the use cases I mentioned at the beginning is when you have an account hierarchy. For example, as a bank client, you may have a current (checking) account, a savings account, a credit card, and your mortgage. Obviously, each client will have a different combination of accounts. What is even more surprising to me is that you may have submitted a different email address or phone number for each account.
Use cases that deal with these scenarios may want to send emails to clients, but also only to credit card holders, using the email address submitted in the credit card form.
Besides the options that I have suggested above, in this particular scenario, I have seen the following solution: create two types of profiles, one for customers, one for accounts. Each type of profile would have a different ID. The customer profile would have an array with all the accounts it has, and the account profile would have all the information about the customer. This is similar to the non-people profiles solution I gave some time ago.
With this solution, if you want to send an email to all customers who do not have a credit card, for example, you would use the customer profile. If you later want to send an email to all credit card holders who live in NYC, you use the account profile.
The most obvious drawback is that you are exploding the number of profiles in AEP, affecting your license. Be mindful if you choose this option.
Photo by Eric Prouzet on Unsplash