2

I'm trying to come with a data extension schema to store an attribute that could contain a number of different values (eg. hobby). We will get a daily extracts of files with a list of contacts and their related hobbies. We want create an automation that imports these files into a data extension. So for example:

File 1:

ContactID Hobby
ContId111 Hobby1
ContId222 Hobby1
ContId333 Hobby1

File 2:

ContactID Hobby
ContId111 Hobby2
ContId222 Hobby2
ContId444 Hobby2

File 3:

ContactID Hobby
ContId111 Hobby3
ContId555 Hobby3

Please note if there is new hobby then a separate file will be created and we will need to import that in the same data extension. Options considered:

  1. Have separate column for each of the hobbies.

Pros: Single contact record with hobbies.

Cons: What happens when a new hobby needs to be created?

Cons: Import automation will need to changed to cater for new hobby.

Cons: Could end up having hundreds of columns (scalability?).

ContactID Hobby1 Hobby2 Hobby3
ContId111 Y Y Y
ContId222 Y Y
ContId333 Y
ContId444 Y
ContId555 Y
  1. Have single column to store hobbies.

Pros: No need to make changes to the automation when new hobby is imported.

Pros: Can be easily referenced in the journeys by looking at just one field.

Cons: Could end up having a large number of records as contacts are repeated for each hobby (scalability?).

ContactID Hobbies
ContId111 Hobby1
ContId111 Hobby2
ContId111 Hobby3
ContId222 Hobby1
ContId222 Hobby2
ContId333 Hobby1
ContId444 Hobby2
ContId555 Hobby3
  1. Store each file in a different data extension

Cons: Automation will need to be modified and create a new data extension each time a new hobby is created.

My question - What is the best way to create an automation to import these files into a data extension and how do we structure the data extension? Please note I'm using hobby as an example but in real life there could be hundreds of hobbies and millions of contacts.

1 Answer 1

0

You are definitely on to something.

Have you thought about:

Contact, Key, status
ContId111,Hobby1,true
ContId111,Hobby2,true
ContId222,Hobby1,true
ContId222,Hobby3,false

Contact & Key == Primary Keys; That allows you to:

a) grow the list by new hobbies (as you say)

b) remove associations without deleting (think preference center!)

...or by extension:

Contact, key, status, value
ContId111,favoriteHobby,true,tennis
ContId222,favoriteHobby,true,football
ContId333,favoriteHobby,true,chess
ContId4444,favoriteHobby,true,baseball

Contact & key again are the Primary keys. That way you can also store Key:Value pairs and again, "turn them off". Of course the second structure can be used like the first if you just have pseudo "booleans", and make the "value" column optional.

Here is a performance indication on DE size:

https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/optimizing_api_calls_and_data_structures_to_improve_performance_in_the_salesforce_marketing_cloud.html

  • I think you will be fine with this unless you go into hundreds of millions. As long as you set primary keys, lookups / joins shouldn't be too bad.

As a rule of thumb, Marketing cloud is actually pretty forgiving with "long" tables (lots of rows) than it is with "wide" ones (lots of columns). Just consider that in Sales Cloud, the opposite is true - long tables are expensive there.

Still, having a key / value structure is immensely valuable and I have seen / used it in many implementations in and around Marketing Cloud whenever flexibility is needed and data structure is not expected to be static. As you say - in the first of your examples, any integration would have to change alongside your DE for each new "hobby". That will be very complex / will need integration re-tests very often.

The only downside is that you'll have to have very generic data type (text, some maxlength) on "value". When it comes to working with "values" that are e.g. dates or numbers instead of text, there is always a bit of conversion effort, but that can be done. In my book, worth it.

2
  • Thank you @Jonas Lamberty. Our main concern was around performace but if Marketing cloud is forgiving with lots of rows as you mention then option 2 will suit us. Also, yes we will have a status so your suggestion will work. Just one more thing - I was thinking of having some kind of "cleanup" activity to minimise the number of records. We will get another file that will tell whether a "hobby" is valid anymore or not. So if Hobby1 is no longer valid then I want to remove all records assoicated with in the DE. Any thoughts how would you do that? Sep 6, 2022 at 8:49
  • add a second DE ("hobbyDefinition") with list of all "hobby keys" and a marker like "active" or "deprecated"; then mark all records in your hobbies table that match hobbyDefinition on key , where hobbyDefinition.marker = deprecated >> e.g. set them to "delete" in their status field. once they are marked like that, deletion is easy, see: salesforce.stackexchange.com/questions/384663/… Sep 6, 2022 at 10:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .