E-Mail Open and opened again metrics question

[Soph75]

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

[akshaaga]

Hi @Soph75 ,

Yes, it's possible to add a column to your data that would represent the count of open or click activities in the broad log table. You can use a database join operation to combine data from the broad log table with the data from the tracking log. The join operation will allow you to search both tables, and match the rows based on the specified join criteria, in your case, membershipNum and campaignID.

You can use the SQL query as below:

SELECT tl.email, bl.activity, COUNT(*) FROM trackinglog tl JOIN broadlogRcp bl ON tl.membershipNum = bl.membershipNum AND tl.CAMPAIGNID = bl.CAMPAIGNID GROUP BY tl.email, bl.activity

The COUNT function returns the number of occurrences of the activity in the broad log table for each email.

You can use a similar approach in your export workflow to add a column that represents the count of open or click activities in the broad log table. This will allow you to easily identify whether an email was opened or clicked for the first time, or if it was opened or clicked again.

You can also write a SQL query that joins the tracking log and broad log table on the recipient ID and the campaign ID. This way, you can have all the relevant information in one record for each recipient and each campaign.