I have created 2 filtered data extension from a shared data extension using filter. I want to automate those filters so that the DE's will be auto refreshed. I realized that automating filter activity creates a new DE rather than appending to existing one. I had to go for query automation but while selecting the target DE, I cannot see my filtered DE in the list. Is this standard functionality that query does not appends into filtered DE? If yes, what is alternate way to refresh the existing filtered DE without manually doing it?
1 Answer
Unfortunately you cannot target a filtered Data Extension with a query. These are able to be manipulated by your filter only. If you cannot utilize the filter to get what you need, you will need to create a new data extension to target with the query.
If your filter is appropriate/sufficient and you just want to automate it, you can utilize a filter activity inside of SFMC Automation Studio (as @RachidMamai suggested) to schedule this. This would require you to:
- Save your filter inside the 'filter' section of Email Studio
- Create a Filter Activity inside of Automation Studio for use in your automation, assigning the filter you created in step 1.
- Create a target DE for your filter to land in (this is done automatically in the Filter Activity creation [not needed to be done manually], but you cannot use an already existing DE)
Another alternative is to create a script activity to utilize an undocumented REST API endpoint to refresh an existing filtered Data Extension.
POST /email/v1/filteredCustomObjects/{{filteredDEObjectID}}/refresh
Host: {{yourSubDomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{AuthToken}}
Content-Type: application/json
Although this is a POST method, no payload is necessary to refresh this. You will just need to place the Object ID of your filtered Data Extension into the URL and then send it.
You can get the ObjectID through a few ways:
- SOAP API call to get the DE info based on Name/Customerkey
- Using The Salesforce Marketing Cloud - Developer tools extension for Chrome
- Using the 'Network' tab on your developer tools. Then find the API call that was made when you opened the Object in the UI and copy the ObjectID from there.
-
Thank you for detailed explanation, I shall try API thing to refresh the DE. Unfortunately creating new DE for filter automation is not an option for us.– seekerDec 20, 2019 at 7:14