Equally Split a DE by segment

Question

I have a DE with about 4m records. I am looking to do a 50/50 split for testing but by a specific column. So within that DE, every record has a "Segment". So there might be 500k customers, 100k prospects, 10k Expired etc.

I would like the split to come out with two DE's that are equally split by those segments (250k/250k , 50k/50k, 5k/5k)

I'm guessing it will have to be assigning a number to each record in each segment and pulling one DE and then excluding that new DE and taking the rest but I can't figure it out/ hope there is an easier way.

Thanks

Answer 1

There is an option to separate out each by the column then use a random split or similar - but that is fairly manually intensive (but requires less technical knowledge) and would require 3 for each segment as staging tables, then 6 more for the final audience. You also could check out some options inside of Journey Builder for pathing rather than actually create the sendable audience prior (if using JB).

That all aside, the way I would recommend would be via SQL.

This would require a DE for each split segment (e.g. 3 segments would mean 6 DEs) as well as a staging table.

You would do 1 query to set up the splits with the following staging DE set up and query:

Staging DE:

Primary Key  | Segment | otherfields | SendGroup

Then use the following to fill that DE:

SELECT 
    a.PrimaryKey,
    a.Segment,
    a.otherfields,
    CASE
        WHEN SegmentNumber <= (SegmentTotal * .50) THEN 1
        ELSE 0
    END as SendGroup
FROM (
    SELECT  
        PrimaryKey,
        Segment,
        otherfields,
        rank() over (PARTITION BY Segment ORDER BY newid()) as SegmentNumber,
        count(*) over (PARTITION BY Segment) as SegmentTotal
    FROM [myDE]
) a

Then you could use simple filters to create the DE if you wanted (Segment = X AND SendGroup = 0) or you could continue the SQL path like:

/* For First 50% - mySegment */

SELECT 
    PrimaryKey,
    Segment,
    otherfields
FROM [staging_DE]
WHERE Segment = "mySegment"
AND SendGroup = 0

Then for second group:

/* For Second 50% - mySegment */

SELECT 
    PrimaryKey,
    Segment,
    otherfields
FROM [staging_DE]
WHERE Segment = "mySegment"
AND SendGroup = 1

You then change mySegment to the next segment group you had and so on for each.

There are other options, such as NTILE() for a SQL solution as well.

NTILE() would be a bit simpler, but can be less accurate and depending on the need, less performant. See here for some SQL Segmentation info I have on my blog.

NTILE() Example: (For staging table)

SELECT 
    PrimaryKey,
    Segment,
    otherfields,
    NTILE(2) OVER(PARTITION BY Segment ORDER BY newid()) as SendGroup
FROM [myDE]

Answer 2

You can do that with ntile:

SELECT
SubscriberKey
FROM (
    SELECT
    SubscriberKey,
    ntile(5) over(order by SubscriberKey) as tile_nr /* update number in the bracket ntile(x) for total number of segments */
    FROM [1_All_Contact_For_Deletion]) x
WHERE x.tile_nr = 1 /* Update the digit for specific segment */

You can also check suggestions here.