2

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.
  1. 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!

1 Answer 1

2

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:

2
  • 1
    Gonna point out one key thing you've adressed well in this approach: what Filters do out of the box is that they automatically refresh the DE schema if the master changes, and with sql queries you have to ensure that yourself. Plus, filters are no code which is a point to some users. Of course they sure come with their own limitations. Jun 2, 2022 at 10:07
  • Yes, good point. Jun 2, 2022 at 11:47

You must log in to answer this question.

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