Я работаю над функцией для маркетингового облака, и мне нужно создать расширение данных с этими полями: NumberOfOpenMails, NumberOfClicks и всеми другими полями, относящимися к подписчику. Я обнаружил, что могу выполнить некоторый запрос SOQL, чтобы найти это с помощью AutomationStudio, но, поскольку я новичок, я не совсем понимаю все. Я нашел этот пример запроса, который извлекает подписчиков без открытий или кликов. . Вот используемый код:

Select distinct
s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
left join [_click] c
on s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
where
s.JobID = JobID
and (o.SubscriberID is NULL and c.SubscriberID is NULL)

Я понимаю, что должен изменить JobId на идентификатор задания моей электронной почты, но я не совсем понимаю, почему это :(o.SubscriberID is NULL and c.SubscriberID is NULL) возвращает число 0 кликов и электронных писем. В моем примере я должен сделать COUNT o.subscriberID и c.subscriberID, чтобы получить мои новые поля?

РЕДАКТИРОВАТЬ: результат запроса со свойством OverWrite введите здесь описание изображения

3
Johannes Schapdick 24 Окт 2019 в 12:40

1 ответ

Лучший ответ

Извините, что делаю это на лету, но ниже вы должны получить то, что вам нужно. Имейте в виду, что это будет довольно интенсивный запрос, так как он затрагивает 3 представления данных (я бы порекомендовал отправить ваши представления данных в DE, а затем запросить их и, возможно, использовать журнал отправки вместо представления отправленных данных).

  1. Он берет аудиторию отправки и ограничивает ее определенным идентификатором задания.
  2. Он объединяет тех, кто отправляет аудиторию с открытиями, и подсчитывает каждый раз, когда подписчик появляется внутри открытия (общее количество открытий) - можно использовать поле IsUnique, если вы хотите, чтобы были только уникальные открытия.
  3. Объединяет аудиторию отправки/открытия с аудиторией кликов и подсчитывает общее количество кликов подписчика.

SQL:

Select 
s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate,
c.[Click],
o.[Open]
FROM (
    SELECT
    sn.SubscriberKey,
    sn.JobID,
    sn.BatchID,
    sn.EventDate
    FROM [_sent] sn
    WHERE JobID = 123456
) s
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Open], JobID
    FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Click], JobID
    FROM [_Click] cl
    GROUP BY cl.SubscriberKey, cl.JobID
) c
ON s.SubscriberKey = c.SubscriberKey
WHERE s.JobID = c.JobID
AND s.JobID = o.JobID
AND s.JobID IS NOT NULL
2
Gortonington 4 Июл 2018 в 15:23
Большое спасибо за помощь! Однако, когда я пытаюсь поместить этот запрос в студию автоматизации, он говорит: «× Произошла ошибка при проверке синтаксиса запроса. Ошибки: неправильный синтаксис рядом с ключевым словом «Открыть». Я не понимаю, почему, потому что это кажется правильным SQL
 – 
Otor
3 Июл 2018 в 10:23
1
Единственное, что я вижу, возможно, нет условия для левого соединения («ВКЛ»), но, возможно, я что-то пропустил
 – 
Otor
3 Июл 2018 в 15:26
Я случайно использовал WHERE вместо ON. Будет редактировать. Я также собираюсь добавить скобки, чтобы открыть и щелкнуть имена столбцов, чтобы убедиться, что они не перепутаны с функцией Open в SQL.
 – 
Gortonington
3 Июл 2018 в 16:16
Вы забыли поставить скобки перед FROM, но теперь он говорит: Ошибки: неправильный синтаксис возле ключевого слова «ON».
 – 
Otor
3 Июл 2018 в 16:23
1
Я нашел проблему! Присоединение было по JobId, где оно должно быть по ключам подписчика.
 – 
Otor
4 Июл 2018 в 13:18