1

I'm trying to write a query in Marketing Cloud that will filter for an event (specifically a unique open) that took place on a specific day of the week. So for example, my query would answer the question "how many times has this subscriber uniquely opened on a Monday?".

If I look at data extensions in Contact Builder, date parameters seem to lack this information. They're usually formatted like "6/28/2019 9:14:42 AM". But if I look at the same data in the Subscribers portion of Email Studio, the same date fields do include the weekday like so: "Friday, June 28, 2019 9:14 AM". That makes me think the weekday must be stored in a data view somewhere, right?

However, if I query the _Open data view for EventDates like '%Monday%', I get 0 results. Here's an example query I've tried:

select o.SubscriberKey, o.JobID, cast(min(o.EventDate) as Date) as Date from _open o
where LOWER(DatePart(dw,o.EventDate)) like '%monday%'
group by o.SubscriberKey, o.JobID, o.EventDate

So my question has two parts: Is this sort of query even possible in Salesforce? If it is, how can it be done?

Improve this question
  • n.b. Marketing Cloud uses SQL. Salesforce CRM uses SOQL. – David Reed♦ Jul 30 '19 at 22:10
  • 3
    Dw DatePart returns a number. You would need to make it be "{datepart} = 2" to choose Monday – Gortonington Jul 30 '19 at 22:29
  • @Gortonington is correct. Same functionality can be done in SF using SOQL with the function DAY_IN_WEEK() which returns a numeric – Glen De Marcos Jul 31 '19 at 1:45
2

The issue is that you are comparing apples to oranges in your query.

When using DW as the formatting in DatePart, you are returned a number between 1 and 7. 1 is Sunday and 7 is Saturday.

So in order to find the day, you will need to do the comparison on the number and then translate the value saved from there.

For example:

select o.SubscriberKey, o.JobID, cast(min(o.EventDate) as Date) as Date from _open o
where DatePart(dw,o.EventDate) = 2
group by o.SubscriberKey, o.JobID, o.EventDate

Will only return those with a Monday Date.

If you are looking to get the actual name of the day returned, there are a couple other options as well:

Full Name of Day:   Format(o.EventDate, 'dddd') = 'Monday'
Abbreviated Name: Format(o.EventDate, 'ddd') = 'Mon'

Not sure if this works in SFMC:
DateName function: DATENAME(weekday, o.EventDate) = 'Monday'

Hopefully one of the above helps you to meet your need in date comparison.

Improve this answer
  • 1
    The datename function proved effective, as did using DatePart to filter for a number instead of a text value. I'm going with the DatePart solution just because it runs a couple of minutes faster. Thanks for your help! – K K Jul 31 '19 at 16:31

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.