2

I'm in the process of building a query that will give me a count of all links a subscriber has clicked on with one of three aliases. The possible alias's are "watch", "win", and "play". I've written three separate queries, all similar to the below. They store the count of clicks for each alias in a corresponding field in a data extension, separated out by SubscriberKey.

select SubscriberKey, count(SubscriberKey) as Play from ent._Click
where LinkName like 'play' and IsUnique = 1
group by SubscriberKey

However, I'm getting some really weird results. For example, I have a measure in email studio that shows there should be 875 subscribers who clicked on "play" links, but my query only gives me back 3 SubscriberKeys. Also, none of the subscribers that my query pulls have numbers in more than one of the fields, and most of them just have a number 1 in the field. I have several million people in my active list. It's not possible that most of them are only clicking one time on one type of link in 6 months.

Eventually I want to see which type of link the subscriber clicks on the most and update my main data extension with that value, but first I'll settle for getting accurate counts.

1 Answer 1

3

I believe it may be the ent. prefix that is causing the issue.

The _Click, _Open and _Sent DV are each local to the BU. So by doing ent._Click you are not querying the _Click DV in your current BU, but only the one at the top level/Parent BU - which is why your Measure returns different results from your Query.

Try the following:

select SubscriberKey, count(SubscriberKey) as Play from _Click
where LinkName like 'play%' and IsUnique = 1
group by SubscriberKey
6
  • That's a good point. So I should definitely omit the ent prefix. The problem is, when I do that, I don't get any results at all.
    – K K
    Jan 16, 2019 at 21:22
  • I would create a new SQL Query to pull over all the records from _Click into the DE and then do a filter on that to find 'play' etc. and make sure they exist on the DV. Oh. Also of note, the LinkName field is case sensitive, so if the value is 'Play' and you are searching 'play' it will be 0 results. you could do Lower(LinkName) like 'play' to remove case sensitivity. Jan 16, 2019 at 21:48
  • Ok, I did that and got much better results. This is the query I used: select SubscriberKey, LinkName from _click where IsUnique = 1 and LinkName is not null One of the Link Names it returned is "Play_Story1". But when I modify the query to the below, it returns 0 results: select SubscriberKey, LinkName from _click where IsUnique = 1 and LinkName like "Play" Maybe there's an issue with the way I'm looking for the match?
    – K K
    Jan 16, 2019 at 22:30
  • Oh duh. Sorry missed it. Need wildcard in your like. Lower(LinkName) like 'play%' should do it Jan 16, 2019 at 22:37
  • 1
    Thank you!! I couldn't find anything about wildcards when I was googling how to use the like filter. Your help is much appreciated.
    – K K
    Jan 17, 2019 at 15:30

You must log in to answer this question.

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