Master Data Extensions

when working with a big amount of data in salesforce marketing cloud, optimization is the key to success as you may already know, sql activities inside salesforce marketing cloud are limited to 30 minutes by default and if they don't finish running before that time, they will return a timeout error at this point, you will need to start again from the beginning big companies usually struggle when trying to fit their needs in that timespan, and master data extensions tend to be the solution to this headache what are master data extensions? master data extensions are data extensions that contain recurring filters that the marketers need in their campaigns it can be seen as a combination of relevant source data extensions, coming from a crm, a bi tool, an ecommerce, or somewhere else in practice, they are going to work as intermediary data extensions where you can format the data so it's more convenient to your purposes, instead of having to look in several data extensions for every target definition master data extensions are going to help you solve two problems at once as the query activity limit is 30 minutes by default, splitting the queries that you need to do into multiple steps will give you more timespan to complete your segmentation (you'll have 30 minutes to generate the master data extension and 30 minutes to generate your final data extension) it will simplify the day to day segment generation you will keep the complexity on the master data extension generation, and then use them as a simplified view of your data model containing only the information you need define your source data model one of the common mistakes marketers do when defining the data they need in salesforce marketing cloud is to import all the available information in case they would need it at some point you have to keep in mind that salesforce marketing cloud is not a crm or a bi system hence, it is a marketing automation tool, which means you should only keep data that you are going to need either for defining segments or for personalizing communications when defining the data you want to input into sfmc you should answer the following questions do you need this field/data extension to generate segments or to modify the content you are going to show your customers? if your answer is no, you should probably keep that field/data extension out of salesforce marketing cloud worst scenario, you will be able to add it later if you change your mind do you need all the detailed information contained in this data extension, just a portion, or the aggregation calculated from it? in case you only need a part of the entire data contained in a data extension or the aggregations calculated from it, you should apply that filter before updating the data into salesforce marketing cloud a good example would be to define the information you need from your transactional data to either filter it and only upload the most recent ones or calculate the aggregations you may need and only upload those calculations (has bought in the last month?, has bought x category?) note when working with transactional data, you also have to consider the volume of data you are about to import salesforce marketing cloud is not a data processing tool, so it's better to verify the best way to work with it before updating it to the cloud you can reach our support team in case you have any questions about it and we can help you get to the best approach how to define the master data extensions i need? when defining your master data extensions, keep in mind that the main goal is to simplify your daily work, so try to think of daily filters that are usually too complex these are a couple of common scenarios you may be facing going from data extension a to data extension d just to get a field from data extension d imagine you have four data extensions (a, b, c, and d) that are all linked as shown in the following picture maybe you are starting your segments from data extension a and need a field in data extension d, so you would need to do all the way from a to d to get that field that means combining 4 data extensions and reviewing all the data in all of those data extensions, which can be a problem in terms of performance instead, you may want to create a master data extension that contains the data from data extensions a and d that you need, so you can later access this data extension and avoid the extra joins master de with the a data (green) and the d data (blue) needed for this use case performing recurring aggregations to filter records another common scenario is to query a data extension to filter records that fulfill certain conditions for example, get the contacts that placed more than 3 orders in the last month or get the contacts that opened more than a certain number of emails in this scenario, you are going to access the orders data extension, or the open, and sent data views to perform a calculation and then get the records that you want this can cause a performance issue since you need to access data extensions that are too heavy and not very handy instead, the best approach is to create a master data extension with the aggregations and then use it to filter whenever you need it for the previous examples get the contacts who placed more than 3 orders in the last month create a master data extension containing the id of the contact and number of orders in the last month whenever you need to filter out contacts based on the number of orders, you can use this master data extension to avoid accessing the orders data extension and filter themrecurringly get the contacts that opened more than a certain number of emails create a master data extension with id of the contact, number of emails sent, number of opens, and other email engagement information so you can easily access this master data extension in all the use cases related to transactional information master data extensions in deselect once you have defined the master data extensions, all you need to do is create a specific folder for them (so it's easier to find them) and adapt your selections to take them into account to make sure that you are always working with updated data, our recommendation is to use the scheduling of selections feature to automatically run and refresh your master data extensions during the night, so you have the updated data when you need it also, if you have some recurring selections that you already have scheduled, make sure that the master data extensions are refreshed before then you can do this by make sure that the schedule of master data extensions is at least 1 hour before the update of your recurring selections creating a waterfall selection to update the master data extensions in the first steps and then the recurring selections after