I'm trying to create a data extension with everyone who has ever opened or clicked on an email in it...

The challenge is, that we used to have a SFDC + ExactTarget integration prior to migrating to MarketingCloud fully.

The _Open and _Click tables have SubscriberKey data which was populated by SFDC and is a hexadecimal as well as SubscriberKey data populated by SFMC which is an email address (our primary identifier).

As such, the data is a bit useless using SubscriberKey as identifier and I want to use the Subscriber ID.

However, when creating a target data extension i'm running into an issue where it is not recognizing that i have specified the field type as being 'number' and is providing me with an error suggesting it isn't referencing a number field to the Subscriber ID...

Any idea how to resolve?


  • Where are you seeing this error? Jun 20, 2016 at 11:31

1 Answer 1


I'd probably write the Query Activity like this:

, s.emailaddress
from ( 

  from _open o 
  where o.isunique = 1


  select distinct
  from _click c
  where c.isunique = 1

) x
inner join _subscribers s on (s.subscriberid = x.subscriberid)

The target Data Extension would have these columns

  • SubscriberID, Number
  • EmailAddress, EmailAddress
  • 1
    Be aware as well that this will only be for the last 6 months as that is all that the data views track back to. Jun 20, 2016 at 14:19
  • Hey, Thank you so much for the speedy response!so is there a way to do this for greater than 6months? For 6months and less we are able to use the measures approach and we're really looking for the >6Month data
    – Adam
    Jun 20, 2016 at 16:15
  • If you need more than 6 months of data, you'll (unfortunately) need to create your own DEs for the data and extract and import tracking data. Jul 12, 2016 at 13:57

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.