Query all profiles that have opened at least 2 deliveries

[cdermody]

Hey everyone! 

Just reaching out with a tracking logs question. 

I am wanting to target profiles who have opened at least 2 deliveries during their lifetime in ACS. I can easily segment out profiles who have at least one existing tracking log with the type set to Open, but how do I query those who have 2 open actions? 

I understand that each profile has a running record of engagements and any documentation that can help me navigate these waters is much appreciated. 

Best, 

-Chris 

[tapcxm_josh · Accepted solution]

Hi Chris,

In a new query activity, navigate to the Explorer tab and drag the Tracking Logs link into the space on the right hand side:

From here you can change the operation from a simple 'exists such as' operation to a count and specify the criteria for the count (e.g. greater than or equal to 2)

Then you can add the Tracking Logs filtering conditions as you would normally (as shown below):

Please note that by default, ACS only keeps tracking log records for 6 months, so any opens that occurred before 6 months ago will not be included in the Tracking Log count for each profile.

Thanks,

Josh

[cdermody]

UPDATE: I re-read your comment via the email that hit my inbox and saw your specifications about using the "Count" function vs. Exists. Thanks for you help on this!!

@tapcxm_joshThanks so much for chiming in! I was able to get this far, but when I attempt to identify profiles who have opened at least two emails, I hit a wall. I've tried to do an advanced query using a Case statement wrapped in a Sum formula but I receive an error telling me to contact my administrator. My query was - "Sum(Case(When(@urlType = 2,1),Else(0))>=2"