Support Article

Pega Marketing: Segments are not random

SA-37249

Summary



The user is using Pega Marketing v7.22 with Pega 7.2.2. They have reported that when using the Sampled Segment Options to create a segment with Pega Marketing hosted with SQL Server, the sampled population is not random. Leveraging global DB trace, this is tracked down to incorrect SQL syntax. The SQL the system generates is:

INSERT INTO mktseggokul
(customerid)
SELECT DISTINCT PC0."customerid" AS "CustomerID"
FROM (SELECT TOP(3600) *
FROM dbo.mkt_customer
ORDER BY Rand()) PC0
WHERE ((((( PC0.email IS NOT NULL )))))


Rand() is incorrect to get a random sample in SQL Server. Instead, the correct syntax should be to use NewID() like this:

INSERT INTO mktseggokul
(customerid)
SELECT DISTINCT PC0."customerid" AS "CustomerID"
FROM (SELECT TOP(3600) *
FROM dbo.mkt_customer
ORDER BY NewID()) PC0
WHERE ((((( PC0.email IS NOT NULL )))))


Error Messages



Not Applicable


Steps to Reproduce

  1. Create a segment on a Pega Marketing instance hosted on SQL server.
  2. On the Options and Schedule tab, under Sampled Segment Options select to use "Select a portion (sample) of criteria results".
  3. Select the radio button for "Select a maximum of ______ of the results" and provide a value.
  4. Run the segment and examine the results.


Root Cause



A defect in Pegasystems’ code or rules is identified as root cause of this issue.

Resolution



Perform the following local-change:
  1. Update the SQL statement to use NewID() function instead of Rand() as shown in below example:
INSERT INTO mktseggokul
(customerid)
SELECT DISTINCT PC0."customerid" AS "CustomerID"
FROM (SELECT TOP(3600) *
FROM dbo.mkt_customer
ORDER BY NewID()) PC0
WHERE ((((( PC0.email IS NOT NULL )))))

Published April 28, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.