0

I am building replications of some system data views in order to be able to hold data that goes back further than 6 months (we are a seasonal business so want to look back at least 13 months).

I considered two approaches:

  1. Use an "Append" and query for records that happened YESTERDAY
  2. Use an "Update" and query for records over the last 7 days

I decided to use option 2 because, in the event the automation fails to run (which happens from time to time) then option 2 will automatically look back far enough to pick up the missed records and add them to the DE.

Option 1 would be quite fiddly to amend the WHERE clause to get the correct dates and not double up on records by accident.

The one requirement for option 2 is that I have the correct primary key setup so that records already in the DE get updated, rather than duplicated, when running the query.

I have achieved this for all the data views I'm using except for _Click. I am getting a primary key violation for this one.

The primary key combination I'm using for _Click is:

  • JobID
  • ListID
  • BatchID
  • SubscriberKey
  • EventDate

I'm surprise about the violation as I would have thought EventDate would provide the required level of uniqueness given it measures down to the second. But I guess some people are clicking links twice within 1 second?

Can anyone think of another way to setup a primary key combination that would avoid any chance of a violation? Or will I simply have to use option 1 (ie: Append with specific date ranges) for getting _Click?

Thanks

Improve this question
2
  • 1
    Yeah the combination you are using will definitely experience violations, one thing you could do is return only the latest EventDate using an Row Number SQL function with an PARTITION ORDER BY if duplicate rows are not relevant in your scenario and return the first result either ascending or descending, that way you only get 1 result, alternatively I would also look to see if you can query the _CustomObjectKey field in the _Click data view as this will be a unique numeric value and can be used as a Primary Key.
    – thechrishaddad
    Feb 4, 2020 at 5:22
  • Thought that might be the case. I will try just getting the latest results and see if that works for my use case. I found some guidance here (salesforce.stackexchange.com/questions/254801/…) on a query to use for this. But slightly different query that doesn't use PARTITION ORDER BY. Will see if I can get it to work.
    – Ben
    Feb 5, 2020 at 3:21

2 Answers 2

Reset to default
1

Depending on your business requirement, you might use "WHERE isUnique = true" to only give you unique clicks. You would admittedly lose the information "how often did a person click" but that might be fine in many cases and involves minimal effort. So it might be a good effort / value tradeoff.

Then your PK can be a combination of:

JobID + ListID + BatchID + SubscriberKey / SubscriberID

If the only value is in a full replication, then why not use no primary key at all? Granted, you then have no indexed field (PK), but you have the full data.

Unfortunately, I could not get a _CustomObjectKey query to work on a data view, which would have given you an indexed row key. SELECT _CustomObjectKey AS "CustomObjectKey" From _Click produces an error. This seems to only work with Data Extensions.

Improve this answer
3
  • Thanks. As commented above, will look at just using the latest result and see if that works for my use case.
    – Ben
    Feb 5, 2020 at 3:22
  • Follow up re why use a PK? It's because the replication needs to last longer than 6 months. So I need to do something like "get results from yesterday" and then run this each day. The problem is if the automation fails for some reason, then it's a pain to alter to query to make up the missing data, and I also risk duplicating data (since it would be an Append operation). So I wanted to use PKs and a rolling window to overcome this. But looks like it's not possible.
    – Ben
    Feb 5, 2020 at 3:24
  • IsUnique on the _click data view is based on job, not specific urls. See Johannes Schapdick's answer here: salesforce.stackexchange.com/a/246300/26886
    – jake314159
    Feb 5, 2020 at 22:38
1

We've used this combination with some success:

JobID + ListID + BatchID + SubscriberID + EventDate + URL + LinkName + LinkContent

...and then use a row_number()/partition to handle any duplicates in that combination of fields.

Improve this answer

You must log in to answer this question.

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