Creating Seed DE with Query

Question

I'm trying to create a samples DE of records from the deployment audience, but I'm having trouble making sure all people get all possible values that the email could display. Using the code below I get one test record with final data for each seed recipient, whereas I should get at least 5 (since there are 5 possible values for the variable I'm sorting by).

Can anyone help?

SELECT
 my_recips.email_address1,
 my_recips.piv_key,
 my_data.campaign_id,
 my_data.wave_id,
 my_data.me_key,
 my_data.split_code

FROM (
  SELECT
   '[email protected]' AS email_address1,
   '[email protected]' AS piv_key,
   NULL AS me_key
 UNION
  SELECT
   '[email protected]' AS email_address1,
   '[email protected]' AS piv_key,
    NULL AS me_key
 UNION
  SELECT
   '[email protected]' AS email_address1,
   '[email protected]' AS piv_key,
   NULL AS me_key
 UNION
  SELECT
   '[email protected]' AS email_address1,
   '[email protected]' AS piv_key,
   NULL AS me_key
 UNION
  SELECT
   '[email protected]' AS email_address1,
   '[email protected]' AS piv_key,
   NULL AS me_key
) my_recips

CROSS JOIN (
 SELECT *
  FROM (
   SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY [SPLIT_CODE]) AS row_number
     FROM
     [FINAL LIST]
  ) sub
  WHERE sub.split_code IN ('1','1L','2','2L',3)
) my_data

Answer 1

Here is how we populate our seeds with a random sampling of a targeted email.

1. Create a Data Extension that will contain the email address(es) of those you want to seed.
   • For this example, we will name this Data Extension: MY_SEEDS

2. Duplicate the Data Extension of your targeted email
   • For this example, we will assume the following fields exist in your targeted Data Extension
   • Targeted Data Extension Name: campaign_2019
   • Duplicated Data Extension Name: campaign_2019_seed

MyCustomerID
email
firstname
lastname
offer

3. Create the following SQL Query Activity

/* Code Block 1 */
SELECT B.MyCustomerID
      ,A.email AS EMAIL
      ,B.firstname
      ,B.lastname
      ,B.offer
FROM (
    SELECT RANK() OVER(ORDER BY NEWID()) AS PK
          ,email
    FROM MY_SEEDS
) AS A
RIGHT JOIN (
    /* Code Block 2 */
    SELECT A.PK
          ,A.MyCustomerID
          ,A.firstname
          ,A.lastname
          ,A.offer
    FROM (
        /* Code Block 3 */
        SELECT RANK() OVER(PARTITION BY offer ORDER BY NEWID()) AS PK
              ,*
        FROM campaign_2019
    ) AS A
    WHERE A.PK <= ( SELECT COUNT(*) FROM MY_SEEDS )
) AS B
ON A.PK = B.PK

Code Block Notes

• Code Block 1
  • Ensure all of the fields with a B alias match your Targeted Data Extension: campaign_2019
  • Ensure the A alias matches the email field from your Seed Data Extension: MY_SEEDS
• Code Block 2
  • Ensure all of the fields with a A alias match your Targeted Data Extension: campaign_2019
  • Include A.PK, seen further below, and do not include the email

• Code Block 3

  • In our use case, we wanted each seed to see each dynamic email since we had a different email using the offer field.

  • Select a field that makes sense for your use case

    4. Run SQL Query Activity to overwrite duplicated Data Extension: campaign_2019_seed

You should now have 2 data extensions that you can target when you send the email and can use this same process going forward with emails.

• campaign_2019
• campaign_2019_seed

Answer 2

Upvote Spencer Curtis. I tried your solution and it worked nicely.

I was not as concerned about the randomness of the rows return so I was able to optimise the query's run time significantly by replacing NEWID() with ROW_NUMBER().

I also aliased the Data Extensions differently to more clearly delineate which one is which. Hopefully this adds a tiny bit more clarity to someone less familiar with sql. Full props to Spencer.

SELECT 
    s.email,
    cp.contact_id,
    cp.email_version,
    cp.customer_segment,
    cp.beh_segment,
    cp.mechanic,
    cp.threshold,
    cp.amount,
    cp.creative_test,
    cp.engage_rank
FROM 
(
    SELECT 
        m.email,
        m.PK
    FROM
        (
        SELECT
            email,
            ROW_NUMBER() OVER(ORDER BY email) as PK
        FROM [MY_SEEDS_DE]
        ) as m
) AS s

RIGHT JOIN 
(
    SELECT 
        x.contact_id,
        x.email_version,
        x.customer_segment,
        x.beh_segment,
        x.mechanic,
        x.threshold,
        x.amount,
        x.creative_test,
        x.engage_rank,
        x.PK
    FROM 
    (
        SELECT 
            contact_id,
            email_version,
            customer_segment,
            beh_segment,
            mechanic,
            threshold,
            amount,
            creative_test,
            engage_rank,
            ROW_NUMBER() OVER(PARTITION BY [threshold], [amount], [creative_test] ORDER BY [EMAIL_VERSION]) AS PK
        FROM [CAMPAIGN_SEND_DE]
    ) AS x
    WHERE x.PK <= ( SELECT COUNT(*) FROM [MY_SEEDS_DE] )
) AS cp
ON s.PK = cp.PK