1

The basic process we are planning to use for achieveing this is:

  1. Create a DE that has SubscriberKey and 6 columns "EmailsCount_7days" "EmailsCount_1day" "SMSCount_7days" "SMSCount_1day" "PushCount_7days" "PushCount_1day"
  2. Create a query to count number of Emails, SMS, Push sent to users in last 7 days and 1 day.
  3. Use the DE to use as exclusion script or exclusion criteria.

I am able to query _Sent to get a count of Emails sent in the last 1 day and 7 days. However, I am unable to do the same for Push and SMS? Is there a way to I can combine it in a single query?

Here is the query for Emails:

SELECT
    s.SubscriberKey,
    SUM(CASE
        WHEN DATEDIFF(DAY, EventDate, GETDATE()) <= 1 THEN 1
        ELSE 0
    END) AS Email_Count_1day,
    SUM(CASE
        WHEN DATEDIFF(DAY, EventDate, GETDATE()) <= 7 THEN 1
        ELSE 0
    END) AS Email_Count_7days
FROM (
    SELECT
        SubscriberKey,
        EventDate
    FROM _Sent
    WHERE DATEDIFF(DAY, EventDate, GETDATE()) <= 7
    GROUP BY SubscriberKey, EventDate
) s
GROUP BY s.SubscriberKey

However when I try to combine this with the query for SMS:

SELECT
    s.SubscriberKey,
    SUM(CASE
        WHEN DATEDIFF(DAY, s.EventDate, GETDATE()) <= 1 THEN 1
        ELSE 0
    END) AS Email_Count_1day,
    SUM(CASE
        WHEN DATEDIFF(DAY, s.EventDate, GETDATE()) <= 7 THEN 1
        ELSE 0
    END) AS Email_Count_7days,
    SUM(CASE
        WHEN DATEDIFF(DAY, m.CreateDateTime, GETDATE()) <= 1 THEN 1
        ELSE 0
    END) AS SMS_Count_1day,
    SUM(CASE
        WHEN DATEDIFF(DAY, m.CreateDateTime, GETDATE()) <= 7 THEN 1
        ELSE 0
    END) AS SMS_Count_7days
FROM (
    SELECT
        SubscriberKey,
        EventDate,
        'Email' AS Channel
    FROM _Sent
    WHERE DATEDIFF(DAY, EventDate, GETDATE()) <= 1
    UNION ALL
    SELECT
        SubscriberKey,
        EventDate,
        'Email' AS Channel
    FROM _Sent
    WHERE DATEDIFF(DAY, EventDate, GETDATE()) <= 7
    UNION ALL
    SELECT
        SubscriberKey,
        CreateDateTime,
        'SMS' AS Channel
    FROM _smsmessagetracking
    WHERE DATEDIFF(DAY, CreateDateTime, GETDATE()) <= 1
    UNION ALL
    SELECT
        SubscriberKey,
        CreateDateTime,
        'SMS' AS Channel
    FROM _smsmessagetracking
    WHERE DATEDIFF(DAY, CreateDateTime, GETDATE()) <= 7
) AS s
GROUP BY s.SubscriberKey

I am getting the following error:

An error occurred while checking the query syntax. Errors: The multi-part identifier "m.CreateDateTime" could not be bound.

1 Answer 1

2

Error 1:

An error occurred while checking the query syntax. Errors: The multi-part identifier "m.CreateDateTime" could not be bound.

This error occurs when you are trying to reference a table alias (m in this case) which you haven't assigned in the rest of the query.

That's the first error and you can solve it by just replacing m.CreateDateTime with s.CreateDateTime.


Error 2:

When you will fix the first error, another one will appear:

An error occurred while checking the query syntax. Errors: Invalid column name 'CreateDateTime'.

You need to add empty fields to the records you are trying to get before you apply UNION ALL so that the resulting table has all the columns that will later be aggregated.

Add this to the sub-queries:

  • null AS CreateDateTime to emails
  • null as EventDate to the sms

With this out of the way, the query should start working.

Just be aware of the fact that the number of records in SMSMessageTracking for a customer does not usually represent the total numer of text messages visible to the customer - multi-part SMS get broken into multiple records in this data view.

You must log in to answer this question.

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