1

I'm working with an existing Exact Target list that does not have a subscriber key setup ( defaults to email address ).

I'm trying to associate clicks from my email back to the original subscriber. So I'm using the Subscriber ID.

My personalization string is something like:

<a href="http://fakeshare.com?origin=%%subscriberid%%" target="_blank">Share</a>

Switching to %%_subscriberkey%% works but includes the subscribers email in the link, which is not ideal for situations where we want the subscriber to share and exposes the user's email address in the URL ( which seems like a poor privacy practice ).

Which works well but I can't seem to get an export of the subscriber id from my list? The list export doesn't include in via the export. Is the only option to access through the API? I'm not even 100% certain it's available via the API...

Thanks.

Improve this question

1 Answer 1

Reset to default
1

You can get SubscriberID via API or Tracking Extract. Neither will be ideal for you, I'm guessing. The API call will return rows in batches and you'll have to extract ALL lists subscribers in the Tracking Extract (which is a ton of data, potentially). SubscriberID is also available in the Subscribers Tracking Extract, but that won't give you list membership.

The _Click System Data View is record all clicks -- by SubscriberID. You could just build a query to write to a Data Extension for anyone with a specific ListID. The configuration would include:

  1. Data Extension - for receiving the data from the Query Activity
  2. Query Activity - selects the rows from _Click by ListID
  3. Data Extract Activity - extracts the Data Extension
  4. File Transfer Activity - transfers the Data Extension to your FTP share
  5. Automation or Program - to automate steps 1-4

The query could look something like this:

select 
c.subscriberid
, s.EmailAddress /* if you need it */
, c.EventDate /* when the click occurred */ 
, c.LinkName /* value of the alias tag from your email */
from _Click c 
inner join _Subscribers s on (s.subscriberid = c.subscriberid)
where c.listID = 1234 /* the ListID from the List Properties*/
and c.isUnique = 1 /* only select the unique clicks */
Improve this answer
4
  • When I try this I'm getting errors on both the _Clicks and _Subscribers tables that say:
    – Colin Smillie
    Commented Jun 10, 2015 at 13:55
  • If you haven't already, try taking out my comments. The from is a reserved word...as is when. The SFMC query parser is pretty picky/dumb about comments.
    – Adam Spriggs
    Commented Jun 10, 2015 at 13:57
  • Still no go, even tried : SELECT * from _subscribers SELECT * from _Subscribers The error message is : _Subscribers is not a known data extension or system data view. You can only query existing data extensions or system data views.
    – Colin Smillie
    Commented Jun 10, 2015 at 14:07
  • 1
    You'll have to contact SFMC support and have the System Data Views enabled. There's no cost.
    – Adam Spriggs
    Commented Jun 10, 2015 at 14:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .