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?

3
  • n.b. Marketing Cloud uses SQL. Salesforce CRM uses SOQL.
    – David Reed
    Commented Jul 30, 2019 at 22:10
  • 3
    Dw DatePart returns a number. You would need to make it be "{datepart} = 2" to choose Monday Commented Jul 30, 2019 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 Commented Jul 31, 2019 at 1:45

1 Answer 1

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.

1
  • 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
    Commented Jul 31, 2019 at 16:31

You must log in to answer this question.

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