1

I have a query that's intended to update a field named Last_Click_Date based on the most recent EventDate from the _Click data view.

It doesn't appear to be grabbing the most recent date param and appending the subscriber record in the marketing data extension.

Here's query -- wondering why it would essentially be pulling a non-most recent click date:

SELECT de1.*
            FROM (SELECT 
                    S.SubscriberKey
                    , S.EmailAddress as 'email'
                    /*, S.Status*/
                    , Max(cast(C.EventDate as DateTime)) AS 'last_click_date'
                    , ROW_NUMBER() OVER (PARTITION BY
                        S.SubscriberKey
                            ORDER BY S._CustomObjectKey DESC) rn
                FROM [Subscribers Data View] S
                INNER JOIN [Click Data View] C WITH (NOLOCK)
                    on S.SubscriberKey = C.SubscriberKey
            GROUP BY S._CustomObjectKey, S.SubscriberKey, S.EmailAddress, S.Status, C.EventDate
    ) de1
WHERE de1.rn = 1
  • I think you might have to slightly change your eventdate call in select to the following: cast(Max(C.EventDate) as DateTime) as I believe the other way is not grabbing just the max date from the DV, but is instead the max of the cast value - of which there is only a single date value to see the 'max' of. – Gortonington Mar 21 at 17:34
1

I edited your query a bit and was able to get accurate results using the below query. I believe you went a bit more complex than you needed to with it, which may have been messing with your results:

            SELECT 
                S.SubscriberKey
                , S.EmailAddress as 'email'
                , c.last_click_date
            FROM [_Subscribers] S
            INNER JOIN (
                SELECT Cl.SubscriberKey
                , cast(Max(Cl.EventDate) as DateTime) AS 'last_click_date'
                FROM [_Click] Cl WITH (NOLOCK)
                GROUP BY Cl.SubscriberKey
            ) c
                on S.SubscriberKey = c.SubscriberKey

This returned the max date (you honestly don't even need the cast there as it is stored in datetime in the dataview) of the last click for each subscriber was displayed in my DE.

If you have a large audience, you may need to look at splitting this up or otherwise optimizing it as data views are notoriously slow. (e.g. WHERE Cl.EventDate >= DATEADD(month,-1, GETDATE()) to get only clicks in the last month)

  • You're absolutely right. Went to complex. Was getting close to your solution before I had to take off. – Tom Callahan Mar 21 at 21:48

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.