Situation: We've integrated Salesforce's Marketing Cloud with an autodialer to aid in getting in touch with leads. The team's availability is variable, and the last thing we want is to blast too many calls, resulting in people just waiting in queue. So, I'm going to provide the team's manager with a web form where she can enter a number of leads to have the system call.

Problem: Marketing Cloud's SQL Activity does not support variables. So, while I'd like to do something like:

SELECT TOP @X lead.phone FROM lead WHERE lead.status = 'Open' ORDER BY lead.dateCreated DESC

This just can't be done.

My Question to You, Oh SQL Guru's: Is there another way that I can go about solving this problem?

  • Hi Corey, Are these leads entered in a Data Extension? If yes, Can you please provide the snapshot of data extension Thanks – Chandan Mar 8 '18 at 22:24
  • @Chandan - Indeed, the leads are stored in a DE and the variable could be stored in one as well, if needed. I don't see the need to upload the exact table structure to this question. What solution are you thinking of that needs more info than all lead data being available in a single DE? – user43881 Mar 8 '18 at 22:42

In the absence of a SQL solution, I was able to:

  • Use a Webform to collect a number of leads to target
  • Use AMPScript to store this number in a Data Extension
  • Use an SSJS activity to get the number of leads needed for the list

The big caveat is that SSJS Row Lookups max at 2000 rows. For this project, this was enough.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy