Randomly select 5 rows from a synchronized Data Extension

Question

I am wondering if it’s possible to randomly select 100 rows from a Synchronized Data Extension over 500 thousands records, without adding extra columns to it and using ampscript to do so at send time? Based on the selection show those 100 rows a specific promo code.

Answer 1

As far as I know there is no straight forward approach using AMPscript and it won't possibly lead to the desired outcome anyways as the AMPscript will be evaluated for every email that is sent out, rather than per job.

Depending on how your sendout happens, there are a couple of approaches:

• Creating a random data extension to select your lucky subscribers, who receive the coupon. Use this data extension for the sendout/selection and suppression if you also like to send to the rest of your subscribers.
• Use an SQL query in an automation to randomly select your lucky subscribers. Use this data extension for the sendout/selection and suppression if you also like to send to the rest of your subscribers.
• ..

The SQL could look something like this:

SELECT TOP 100
    Id,
    EmailAddress,
    FirstName,
    LastName
FROM
    Contact_Salesforce
ORDER BY newid()

Related documentation:

• Create a Random Data Extension in Marketing Cloud
• NEWID (Transact-SQL)

Answer 2

You can follow the below approach :

• Create a sendable DE with same fields as of synchronized Data Extension.
• Create Automation with sql query where you populate that sendable DE with 100 records only every time that query runs(Not sure if you will be using any filter criteria, so that no record is populated in those 100 records again, if query re-runs)

Use this sendable DE while sending the email.