1

I'm attempting to create my own DE's of the tracking exports so that I have a consolidated view of tracking data since I can only pull 30 days at a time using the Tracking Extract (Data Extract Activity). I've created all the automation to do this.

My question is around the Primary Key. I'm using an "Add Only" (aka Append) when I import to the DE. Essentially creating a consolidated copy of the data extract.

However, I'm unable to do so because a primary key is required for an "Add Only" and in several of the data extracts (i.e. Clicks, ClicksImpressions, Opens), there's not a unique data attribute since customers can click and open more than once. So, some of the records won't import.

I've read several threads where people are doing this, but no one has mentioned how to get around the primary key issue (that I've found). I don't want to perform an overwrite. Any and all help is appreciated.

Improve this question

1 Answer 1

Reset to default
1

One way to get around this is to add multiple Primary Keys to your import data extension(s). This will allow for multiple unique values per record.

For example, you could set both "SubscriberKey" and "EventDate" as primary keys, which would help alleviate the primary key violation you're running into. If a subscriber happens to have the exact same eventdate twice, you may want to add a third primary key to your import data extension (although I believe this scenario is unlikely).

Update/Edit: In order to best avoid primary key violations, it's best to set all Salesforce-defined keys as Primary Keys on your import data extension. These include:

  • ClientID
  • SendID
  • SubscriberKey
  • EventDate
  • BatchID
  • SubscriberID

More information on tracking extracts and their keys can be found here: https://help.salesforce.com/s/articleView?id=sf.mc_as_tracking_extract.htm&type=5

Improve this answer
5
  • Thanks, @ropedog. I think this may work. I'm going to test it over the next few days and will update this thread and add a vote once I'm able to fully validate.
    – Clint Woodruff
    Nov 11, 2019 at 15:36
  • 1
    This solution worked. I use multiple primary keys and I'm now able to import and append the tracking data extracts into their own DE's that use an exact schema. First, I manually imported prior date ranges and now have the automation activity set to a rolling range of 7 days. Thanks again for your help @ropedog!
    – Clint Woodruff
    Nov 19, 2019 at 14:30
  • 1
    The answer should be improved; any keys id's should be set as PKs in this case otherwise you end up with potential overlap.
    – JordanGS
    May 19, 2022 at 6:11
  • 1
    @JordanGS The answer has been updated to include all Tracking Extract key id's based on Salesforce's documentation.
    – ropedog
    May 20, 2022 at 15:16
  • Thanks, I upvoted the answer. The only suggestion I have, is that you shouldn't PK both the SubscriberID and SubscriberKey, otherwise you can run into some funky situations. It's really preference on which one you like to use. I prefer the SubscriberID simply because it's static and cannot be changed by a marketing user.
    – JordanGS
    May 20, 2022 at 16:05

You must log in to answer this question.

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