0

I am trying to count to number of subscribers have made a transaction. I am using the code below:

SELECT 

M.Id SubsriberKey
,COUNT (Transaction_ID__c) OVER (PARTITION BY M.Id) AS NumberOFTransactions 


FROM  MasterSubscriberDE M 

LEFT JOIN SalesDE S   

ON M.Id = S.Id

GROUP BY M.Id, S.Id, Transaction_ID__c 

My query is returning zero for all the subscribers, even though there are transactions in the Sales table (i.e Transaction_ID_c is not empty).

enter image description here

I will appreciate any help on this.

  • Are you looking to count the number of subscriber with transactions or the number of transaction per subscriber? – David Devoy Dec 19 '16 at 18:12
  • Hi @DavidDevoy I am trying to count number of transactions per customer – Samuel Dare Dec 19 '16 at 18:16
1

I don't believe you need to partition the data and you have too many fields specified in you group by statement. Here is how I would revise the query.

SELECT 

M.Id SubsriberKey
,COUNT (S.Transaction_ID__c) AS NumberOFTransactions 

FROM  MasterSubscriberDE M 

LEFT JOIN SalesDE S   

ON M.Id = S.Id

GROUP BY M.Id
  • Hi David, I have ran your script and it seems to still not count the transactions per customer. I am editing the question to show the query I am using to validate the first one. – Samuel Dare Dec 19 '16 at 18:38
  • If the query is returning zeros in the NumbersOfTransactions column, then I would suspect the subscriberKeys in your MasterSubscribersDE are not in the SalesDE. Check to make sure both DE(s) are storing the 18 char version of the subscriberkey. – David Devoy Dec 20 '16 at 16:27
  • This is the case. Thank you very much. Please update this in your answer so I can accept it. – Samuel Dare Dec 21 '16 at 10:48

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.