0

I want to get the average number of email send on monthly basis per account from the Salesforce. I have created a Master DE in SFMC with Contact & Account Data combined in it and I was trying to join with _Sent & _Job data view but I am not able to get the desire output.

My Desire OutPut is " Average Number of Email Sent Per Account on Monthly basis "

Can anyone help me with this, How should I write the query for the same?

Thanks Adam

5
  • "I am not able to get the desire output" - what is the desired output? What are you getting instead? Feb 6 at 8:51
  • Average Number of Email Sent Per Account on Monthly basis @LukasLunow
    – Adam Coder
    Feb 6 at 9:41
  • Account you mean here is the object Account in Salesforce CRM? Have your SF CRM enabled the person accounts feature yet?
    – Duc Le
    Feb 6 at 9:48
  • @DucLe yes, we have the Account information in the SFMC and I have created a MasterDE in combination with Account & Contact Data. and Now, using this MasterDE, I am trying to get the Avg. number of email send per month from each account.
    – Adam Coder
    Feb 6 at 10:06
  • @LukasLunow I have got the AccountID, Month, Year and Number of Email sent and now, I am looking for How to group this AccountId to get the average number of email sent on monthly basis from each account.
    – Adam Coder
    Feb 6 at 10:08

1 Answer 1

1

You can leverage _Sent data view to get the total sends of each subscriber.

For example: in this SQL query, I can get the subscriber key and the sends count in Jan 2024.

SELECT
SubscriberKey,
count(SubscriberKey) as cnt
FROM
_Sent
WHERE
MONTH(EventDate) = 1 AND YEAR(EventDate) = 2024
GROUP BY SubscriberKey
HAVING count(SubscriberKey) > 0

Since you have a Master DE in SFMC with Contact & Account Data combined, so you can join the above result, group by account ID and sum the count.

E.g. I assume your master DE has these columns ContactID and AccountID for Contact and Account

Final query:

SELECT
b.AccountID,
SUM(a.cnt) as cnt_by_Account
FROM
(SELECT
SubscriberKey,
count(SubscriberKey) as cnt
FROM
_Sent
WHERE
MONTH(EventDate) = 1 AND YEAR(EventDate) = 2024
GROUP BY SubscriberKey
HAVING count(SubscriberKey) > 0) as a
INNER JOIN
[Your master DE] as b
ON
a.SubscriberKey = b.ContactID
GROUP BY b.AccountID

Bring this from my comment:

_Sent data view records dating back six months from the day the query runs are available. If you want to show number of email sent monthly in 6 months, you can try like this:

SELECT
b.AccountID,
SUM(a.cnt)/6 as average_6_months_sent
FROM
(SELECT
SubscriberKey,
count(SubscriberKey) as cnt
FROM
_Sent
GROUP BY SubscriberKey
HAVING count(SubscriberKey) > 0) as a
INNER JOIN
[Your master DE] as b
ON
a.SubscriberKey = b.ContactID
GROUP BY b.AccountID
3
  • Hi @DucLe, it's working well. I have updated a query little bit to get the data from the Monthly basis for each month and not only for specific month. Now, My query looks like below: how can i group by AccountID as I see many AccountID, I want to group them and show the months under each account id.
    – Adam Coder
    Feb 6 at 11:48
  • see this is the code SELECT Distinct b.AccountID, a.EventM, a.EventY, SUM(a.cnt) as cnt_by_Account FROM (SELECT SubscriberKey, count(SubscriberKey) as cnt, Month(EventDate) as EventM, YEAR(EventDAte) as EventY FROM _Sent GROUP BY SubscriberKey, EventDate HAVING count(SubscriberKey) > 0) as a INNER JOIN [SFMCMasterDE] as b ON a.SubscriberKey = b.ContactKey GROUP BY b.AccountID, a.EventM, a.EventY Now, there are multiple accountID, I need to get unique AccountID and within them I want to show number of email sent monthly.
    – Adam Coder
    Feb 6 at 11:57
  • @AdamCoder _Sent data view records dating back six months from the day the query runs are available. You can try to modify my original query that you can remove the condition in Where clause and make the SUM is divided it by 6
    – Duc Le
    Feb 6 at 12:32

You must log in to answer this question.

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