2

I am trying to get my head around how to keep all data extensions up to date with the most recent information in Marketing Cloud.

For example, if I create a data extension to store form submission data, and on that form, a user changes their job title, is there a way that I can update my root data extension which holds all contacts with that change?

The only way I can see to achieve this is to post to both data extensions via the API, but I keep thinking that the system must handle this somehow.

2

This greatly depends on your setup but one possibility would be creating a scheduled automation in Automation Studio, that regularly executes a SQL query activity which selects data from your form submission data extension and updates the records in your other data extension. For this to work both tables need to have the same primary key (best option would be the subscriber key/id, email address, etc).

Necessary steps / summary:

  • Both Data Extensions need to have the same primary key (e.g. the subscriber key)
  • Create a scheduled Automation
  • Add a SQL Query Activity to your Automation (Data Action: Update, Note that your target DE needs to have the record you wan't to update as this won't add new ones)

Note to your data model:

However you should maybe reconsider your data model as keeping the same data in multiple data extensions (which basically are just database tables) isn't really necessary.

Further information (documentation articles):

  • Thanks for the response. The reason for keeping the same data in multiple data extensions is so that we have a record of exactly what was submitted at that time, so that when we report historically, or anomalies start to appear in the data we can troubleshoot accordingly. – Luke Crickmore Aug 9 '17 at 12:57
1

Answer: Yes this is possible.

For the purpose of this illustration, lets assume the following :

  • your "Root Data Extension" is called "MasterDE" \
  • your form submission data extension is called "FormDE".
  • MasterDE has a primary key

All you need to do is write a SQL query activity to pick up the fields that have been updated in the FormDE by the form submission action and copy this to the master DE.

Here is how to do this.

  1. Assuming the field is Address and EmailAddress is the PrimaryKey in MasterDE

    SELECT EmailAddress ,Address FROM FormDE

  2. On the final screen after you validate your query, select MasterDE as your target data extension, with your "UPDATE" option. It is important that your MasterDE has a primary key or the only action you would be able to do is "OVERWRITE".

  3. You have the option of either running it once from here or running it in automation studio. It is best practice to run it in Automation Studio otherwise you wont know when it finishes (or if it fails). It also helps you debug your automation if it fails.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.