I'd like to query the CLICK system table for members who clicked a certain URL string (for example, http://www.store.com/men) and assign a default value to those members.

My initial thought was to find members are per the above and then write to a Data Extension (as per a Query activity) called "men_clicks" and assign a default value of 1 in "men_clicks" field (with the other field being email address).

Is this possible? I'm not a SQL guru by any stretch.

    Case URL
        When 'http://www.store.com/men' Then 'men_clicks'
        When 'http://www.store.com/women' Then 'women_clicks'
        Else 'NA'
From _Click
Where DateDiff(d,EventDate,GetDate()) < 30

Something like the above should work. You're essentially in need of a CASE statement. The DateDiff() is added, and I'd recommend in order to reduce the load. Depending on your setup, and how long that URL has been in use, it might take a long time to load results.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.