0

I have a rather large data extension I want to mail to over the course of a week. I want to use SFMC's built in throttling tool (i'm warming up a new IP). I'd like to take my data extension, break it into 4 or 5 random segments and mail to each of them at different throttled rates. For example, segment 1 at 500 emails per hour for a day. Segment 2 at 1000 emails per hour for a day and so on...

I'm having a hard time figuring out the best way to break my data extension into randomized segments. Is there a tool do this is Marketing Cloud?

Thanks!

3

while the query definitely works there is a really handy tool for random data extension splitting (we are on a Corporate Edition).

How to go about it:

  1. Click Create in your data extension folder
  2. Select "Random data extension"
  3. Select your source data extension
  4. Design your randomized data extension structure with this wizard:

enter image description here

Concerning your dedup issue you should be able to create an exclusion script: https://help.salesforce.com/apex/HTViewSolution?id=000213980&language=en_US

As the marketing cloud is adding subcribers to the All Subscribers List on the first send from a data extension you should be able to use this list (_Subscribers) as target and filter against the EmailAdress field with a Rowcount > 1. Duplicates would then be filtered out at send. Don't forget to set "Deduplicate by Email Adress" when you create the send to avoid duplicate sending within the same data extension (I'm not sure at which send process step the dedup check against the subscriber list comes in).

After all sends are through you can use the source list and the All Subscribers for a join query to create a new deduplicated source data extension.

I hope this works for you.

Have a nice weekend, Stephan

| improve this answer | |
  • This is a great solution - however, i need to dedup from a data extension, not all subscribers as all of our subscribers. I think the best way to handle this would be to dedup a data extension first, than split the send with the split tool you mention. – Peachy Jun 5 '16 at 17:32
  • With the join query you only select those subscribers from all subscribers that exist in your data extension. So, you actually dedup the data extension. – Stephan de Paly Jun 6 '16 at 21:01
2

You can select a random sample of a Data Extension using a Query Activity that orders rows by newID(). Like this, where DataExtension is the master:

select distinct top 5000
d._ContactKey as ContactKey
, d.emailAddress
from DataExtension as d
order by newID()
/* target: DataExtension1 */

The other queries could exclude the previous data-sets:

select distinct top 5000
d._ContactKey as ContactKey
, d.emailAddress
from DataExtension as d
left join DataExtension1 d1 on (d1.ContactKey = d._ContactKey)
where d1.ContactKey is null
order by newID()
/* target: DataExtension2 */

The third segment would be something like this:

select distinct top 5000
d._ContactKey as ContactKey
, d.emailAddress
from DataExtension as d
left join DataExtension1 d1 on (d1.ContactKey = d._ContactKey)
left join DataExtension1 d2 on (d2.ContactKey = d._ContactKey)
where d1.ContactKey is null
and d2.ContactKey is null
order by newID()
/* target: DataExtension3 */

Just add additional left joins for any subsequent segments.

| improve this answer | |
  • Hi Adam - thanks for the advise. Is there a way to do this without using a query (or SQL)? I'm also concerned because I know there are dup addresses in the data extension and I worry because I don't think I can dedup between segments (or at least I don't know how to). – Peachy May 16 '16 at 16:35
  • As far as I know, there's no drag-drop randomization for Data Extensions. If you're using Lists, you can create a random Group. – Adam Spriggs May 16 '16 at 16:48
  • I've updated my answer to de-duplicate by email address using the distinct directive. The left join statements and criteria in the where clause ensure the segments are mutually exclusive. – Adam Spriggs May 16 '16 at 16:52
  • Thanks for the update. A couple of quick questions - why the 'd' after the data extension (and before emailAddress)? Also, if these are dropping into a new data extension that I'm creating, how do I tie in the unique contact ID to it? My main data extension has an 18 digit _ContactKey... but I can't create an _ContactKey in my new data extension (can't create a field that starts with underscore). – Peachy May 18 '16 at 20:19
  • The d is just an alias for the preceding table. The as is optional in T-SQL. You can use an alias to change the name of a column. I've updated my answer above with an example. – Adam Spriggs May 18 '16 at 20:55

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.