Random Data Extensions: Need to split larger audience into 20 different DEs

Question

As part of IP warming - I need to segment a Data Extension of 5 million users into roughly 20 segments, some of different values as we slowly warm the IP across the fist send through the user base. When randomly splitting the larger audience, I noticed it can only be split into 12 segments, which cannot be further split. Is there any workaround to get around this limit of 12?

The only other option I can think of is to export the larger audience, manually separate it into two different files in Excel, and then reupload and spilt that way. Any other suggestions on how to do this in the platform or a more user friendly way?

Answer 1

The way to get the most accurate split of your audience would be to use a rank() and count() to a staging table and then use math in the following queries to build out your segments.

Staging Query:

  SELECT  Subscriberkey
        , EmailAddress
        , Field1
        , ...
        , rank() over (PARTITION BY SubscriberKey ORDER BY newid()) as rn
        , count(Subscriberkey) over (PARTITION BY SubscriberKey) as total
  FROM [myMasterDE]
  /* Target: staging_table */

Then follow that with 20 queries that target the different DEs you want to split it into:

SELECT  SubscriberKey
      , EmailAddress
      , Field1
      , ...
FROM [staging_table]
WHERE rn <= (total * .05)
/* Target: DE_1 */

SELECT  SubscriberKey
      , EmailAddress
      , Field1
      , ...
FROM [staging_table]
WHERE rn between (total * .06) AND (total * .10)
/* Target: DE_2 */

SELECT  SubscriberKey
      , EmailAddress
      , Field1
      , ...
FROM [staging_table]
WHERE rn > (total * .95)
/* Target: DE_20 */

You can also utilize NTILE() via a staging DE ( NTILE(20)) to label which group it should be in - with the volume of records, this should get you closer to accurate than the _customobjectkey and MOD % function, but it still has a margin of error that some may find unacceptable. The major benefit though is that it is much easier to use and may require less processing than above math example.

NTILE Example

  SELECT  Subscriberkey
        , EmailAddress
        , Field1
        , ...
        , NTILE(20) order by newid() as group
  FROM [myMasterDE]
  /* Target: staging_table */

Then use 20 queries like below to separate out:

SELECT  SubscriberKey
      , EmailAddress
      , Field1
      , ...
FROM [staging_table]
WHERE group = 1
/* Target: DE_1 */

Utilizing the _customobjectkey is definitely quick as that field is guaranteed to be indexed so its usually faster than other methods, but due to the margin of error (numbers not guaranteed to be consecutive), it may not work for you.

Answer 2

You can do it at least in 2 ways:

1. Split the audience within the Journey. You would create the first Random Split to have 50% + 50% and then from each branch of 50%, you would have another Random Split with 10%+10%+10%+10%+10%+10%+10%+10%+10%+10%. This way you would have 20 more or less similar audience batches. Yet, take into account that Journey Builder has some processing best practices, which are going to be ignored by this implementation.

2. You can also prepare 20 separate Data Extensions via 20 SQL Queries in Automation Studio. Each SQL would have something like -

SELECT * FROM [your 5 mil. master DE]
WHERE _customobjectKey % 12 = 0 /* "0" would be increased with each next SQL up to "11" */