6

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
   'email1@example.com' AS email_address1,
   'email1@example.com' AS piv_key,
   NULL AS me_key
 UNION
  SELECT
   'email2@example.com' AS email_address1,
   'email2@example.com' AS piv_key,
    NULL AS me_key
 UNION
  SELECT
   'email3@example.com' AS email_address1,
   'email3@example.com' AS piv_key,
   NULL AS me_key
 UNION
  SELECT
   'email4@example.com' AS email_address1,
   'email4@example.com' AS piv_key,
   NULL AS me_key
 UNION
  SELECT
   'email5@example.com' AS email_address1,
   'email5@example.com' 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
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

Data Extension Seed

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

      1. 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
| improve this answer | |
1

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
| improve this answer | |

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.