Hello, I have a question regarding the export workflow and the approach that we have been using works partially. The functionality we are trying to create is the following:
- The nightly data export with records of email activity from the tracking log and broad log table. What we need is if the email was an ‘open’ or ‘open again’ and also if there was a ‘click’ on a link or a ‘click again’.
- The code that was written was working only in the same extract file i.e., as the code was looking at the line number in the file and if the line number for certain criteria was > 1 then it would be an open again .
What we tried to do was use an enrichment to add a column to the data that would be a count from the broad log table if that open or click activity. From what we have tried it doesn’t appear to be a straight forward as expected. The criteria would be similar to this SQL query
SELECT COUNT(*)
FROM broadlogRcp
WHERE membershipNum = currentrow.membershipNum
AND CAMPAIGNID= currentrow.CAMPAIGNID
Is this something that one of you had previous experience or best design on how to use this and provide any suggestions or documentation?
Thanks,
Soph