0



I am having a hard time with a query that I did for Salesforce Marketing Cloud, and you might know where is the error. I am querying the table _Click, (table schema), and I want all my users that had cliked more than three times in a link with the same LinkNameI also want to see only the LinkName with the maximum number of clicks for each user. The query works pretty well, if I don't have the same user clicking the same number of times in two or more different LinkName, trying to avoid that I used the data EventDate and ask for the latest click date, but this is also not working.

Some important information: The data retrieve from the table _Click will be use to populate a table with the follow schema
Field name - EmailAddress > DataType - EmailAddress > Length - 254 > PrimaryKey - True
Field name - SubscriberID > DataType - Number > Length - not limited > PrimaryKey - False
Field name - LinkName > DataType - Text > Length - 100 > PrimaryKey - False
Field name - maxClicks > DataType - Number > Length - not limited > PrimaryKey - False
Field name - ClickDate > DataType - Date > Length - not limited > PrimaryKey - False

Query

SELECT DISTINCT
a.SubscriberID,a.maxClicks,a.LinkName,a.SubscriberKey as EmailAddress,a.ClickDate
FROM (SELECT SubscriberID,COUNT(LinkName) as maxClicks,LinkName,SubscriberKey,MAX(EventDate) as ClickDate
FROM _Click
WHERE convert(varchar(24), EventDate, 113) >= convert(varchar(24), getdate()-9, 113)
GROUP BY SubscriberID,SubscriberKey,LinkName) as a
LEFT JOIN (SELECT SubscriberID,COUNT(LinkName) as maxClicks,LinkName,SubscriberKey,MAX(EventDate) as ClickDate
FROM _Click
WHERE convert(varchar(24), EventDate, 113) >= convert(varchar(24), getdate()-9, 113)
GROUP BY SubscriberID,SubscriberKey,LinkName) as b
ON a.SubscriberID = b.SubscriberID AND a.maxClicks < b.maxClicks AND a.ClickDate < b.ClickDate
WHERE a.maxClicks > 3
AND convert(varchar(24), a.ClickDate, 113) >= convert(varchar(24), a.ClickDate, 113)
AND a.LinkName in ('Beach','Skiing','Nature','Wellness','Business','Shopping','Food_Culture','Fun')
AND b.maxClicks is NULL AND b.ClickDate IS NULL

Could you help me please?

  • Felipe, this isn't really a Marketing Cloud question, but rather a clusterSQL. Can you describe what the output should look Ike,please? Keen to help while sparing clock-cycles. – Macca Apr 24 '17 at 16:24
  • Hey Andrew, the output for this query should be, if the same user did 9 clicks in a 'New York' deal at 25.12.2017 09:35, and also 9 clicks in a 'Las Vegas' deal at 25.12.2017 09:36, I should get him in the table with the following data `SubscriberID = 111111,maxClicks = 9,LinkName = ''Las Vegas, EmailAddress = ''XXX'',ClickDate = 25.12.2017 09:36. Thanks for your help :) – Felipe Jordani Apr 25 '17 at 9:55
0

Felipe, I'm not entirely certain I understand your requirements, but based on your description, it sounds like you could use the ROW_NUMBER function here to return only one row per subscriber. Something like this...

SELECT
  c.SubscriberID,
  c.EmailAddress,
  c.LinkName,
  c.ClickDate,
  c.NumClicks AS MaxClicks
FROM
  (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY SubscriberID ORDER BY COUNT(1) DESC) AS RowNumber,
      SubscriberID,
      SubscriberKey AS EmailAddress,
      LinkName,
      MAX(EventDate) AS ClickDate,
      COUNT(1) AS NumClicks
    FROM
      _Click WITH (NOLOCK)
    WHERE
      EventDate >= GETDATE() - 9 AND
      LinkName IS NOT NULL
    GROUP BY
      SubscriberID,
      SubscriberKey,
      LinkName
    HAVING COUNT(1) > 3
  ) AS c
WHERE
  c.RowNumber = 1

If you need one row per subscriber/linkname combination, then Partition by those two fields, rather than SubscriberID alone.

  • Good. Please mark as answered if that's sorted for you, now. – Macca Apr 25 '17 at 21:01

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.