Automated bulk refresh of filtered DEs - API or data filters?

Question

I work for an organization with a very decentralized model, and we have 25-30 departments/teams sending out of SFMC. Each of these teams has their own set of filtered DEs (probably 30-50 per team), which they are supposed to refresh before sending, but frequently fail to do, causing no end of consternation when customers start complaining about, e.g., receiving e-mails from which they have already unsubscribed.

Our data is pulled in from SF CRM nightly, and a series of SQL automations update our core data model; the filtered DEs are built off of this data.

Because of the above, our CIO has made it our (read: my) highest priority to get all of the filtered DEs refreshing automatically every night. My research shows that there are two ways to do this:

1. Create data filters for each of the filtered DEs, and then create automations, etc. which run nightly and refresh the data. The obvious problem here is that there are hundreds of DEs for which this will need to be done, and future maintenance will be needed every time a new DE is added.

2. Use the undocumented endpoint (POST /email/v1/filteredCustomObjects/{{myObjectID}}/refresh) and write a script that iterates through the directory tree containing all the DEs and refreshes them.

Does anyone have any particular experience that would be useful to do this? Anything I should be considering for one (or both) options? Any other solutions? Any reason that one, or both, solutions might not work? My boss indicated that I can, for the purpose of this question, ignore the inherent risk of using an undocumented endpoint.

Thanks in advance!

Answer 1

I've done this with Filter and Query Activities -- iterating through a list of Stores and upserting a Filter or Query for each and then executing it.

I did it with WSProxy (SOAP API) in a single Script Activity. No need for the REST API.

Here's an outline of that process:

• Retrieve the DataExtensionFields of the Master DE. The results for each store will be a copy of this DE. This can be hardcoded, but it better to have it dynamic if you ever expand the schema in the primary DE
• Build a list of fields for creating each Store's sending DE
• Lookup records from the Store DE
• Loop through the results. For each Store:
  • Get the Store's ID and Name
  • Concatenate both to form the Store's DE's Name
  • Create a new Store sending DE using the Master DE fields
  • Create a new QueryDefinition. The query text is calculated within the script, selecting from the Master DE and targeting the Store's DE.
  • Perform the QueryDefinition. This executes the query immediately.

This worked surprisingly well for refreshing and/or creating over 300 DEs every day.

You can do the same thing for Filters, however, I opted for Queries and Data Extensions since you can target multiple DEs but only a single Filter audience in a Send Definition -- if I recall correctly.

So just to review here are the SOAP Objects involved:

• DataExtensionObject (Store rows for the loop)
• DataExtensionField (Master fields for target DE)
• DataExtension (Target DE)
• QueryDefinition (Query to populate Target DE)