1

Great feedback. How ever, I still don't get the result I want. This is what I have with the outcome and what I expect:

SMS Tracking view

I created the SMS tracking DE with all the fields from the SMS tracking data view. In the first step I run the following query:

SELECT  
*
FROM [_SMSMessagetracking]
WHERE Name = 'SMS notificatie test data view'

The outcome is 1 record as expected because I did 1 sent.

DE trigger data

I have a DE where the data is comming in with some additional fields like: klantnaam, telefoonnummer

Then I run the following query as you suggested (with two more fields):

SELECT DISTINCT   
sns.klantnaam, Count (*) as [TotalSent],
sm.Mobile,
sm.Name
FROM [Notificaties_SMS_Tracking] sm
INNER JOIN [Notificaties_standaard_test] sns on sm.Mobile = sns.telefoonnummer 
WHERE sns.klantnaam IS NOT NULL AND sm.Name = 'SMS notificatie test data view'
GROUP BY sns.klantnaam, sm.Name, sm.Mobile

The outcome is 3 records where I expect/want 1 record: screenshot query studio

Like I said in the beginning I did just 1 sent to the 'SMS notificatie test data view' SMS. I did the send to the record with the klantnaam 'Een klantnaam'. It is possible that the phonenumber occurs multiple times in the Notificaties_standaard_test DE because there are different SMS messages (per client (klantnaam) there are multiple messages).

Improve this question
5
  • Can you clarify what you want to see? I think your query is returning every record where there is a match on telephone number, if the klantnaam (name) is not null and matches the SMS name. So if there are 3 records it will return 3 records. If you just want to see one record you need to use GROUP BY and ORDER BY. In your case I believe you'll need group by SMS Name AND Mobile. So it will show 1 record each. Also you can add the subscriberKey to mobile connect as an attribute and then do the mapping using that unique identifier instead of the name.
    – SYUYDY
    Commented Mar 28, 2022 at 8:53
  • I want to see the one record that was sent with the selected attributes. And how to make use of the GROUP/ORDER BY function because right now I only can use those functions if I put every attribute in it (error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause). and do you mean adding the subscriberkey to the _SMSMessagetracking DE?
    – Martijn e
    Commented Mar 28, 2022 at 9:40
  • Can you please share how you imagine the result to display? My understanding so far is that you want to see a result like this: Klantnaam Grouped uniquely = client name SMS NAME = SMS NAME Total number of SMS? E.g. klantnaam: Salesforce SMS Name: SMS 1 Total Send: 90 SMS Is this what you are trying to achieve?
    – SYUYDY
    Commented Apr 1, 2022 at 11:59
  • Yes that is exactly what I want. However the result is 3 even when I did a test send once. I edited my post so see the screenshots for more understanding.
    – Martijn e
    Commented Apr 5, 2022 at 13:42
  • Ok yes because the 3 names are different. So actually what you want is to group by SMS Name, but you will lose the details of customer name and mobile number as you will have 1 line.
    – SYUYDY
    Commented Apr 7, 2022 at 20:41

1 Answer 1

Reset to default
1

Starting from a DE containing the SMS tracking Data view (I created a partial table with fake values)

enter image description here And a DE containing your customer details where Klantnaam is the company name. Multiple records can have the same name. This is not a problem if this is actually the customer name and it's unique. The below queries will work for both scenarios. enter image description here

Your original query returns distinct rows for each subscriber: enter image description here So each record identified by mobile number, that was sent SMS1, will appear in the list.

  1. If you want to group by Company Name and see the total count of SMS sent. Use a query similar to:

    SELECT DISTINCT sns.klantnaam, Count (*) as [SMS1Count] FROM [SMS_Tracking_DataView] sm INNER JOIN [Test_CustomerList] sns on sm.Mobile = sns.telefoonnummer WHERE sns.klantnaam IS NOT NULL AND sm.Name = 'SMS1' GROUP BY sns.klantnaam

enter image description here

This code will calcultate how many SMS NAME were sent to company X. Now note, you won't be able to aggregate each send results as part of this query as it's grouped by Company Name. You could calculate and aggregate how many of those were opened, show the name of sms and short code.

  1. if you want to group by mobile number and filter by SMS Name, use the following code. You are restricted in this query by the columns you can include but you can use a second query to join and enrich the count.

    SELECT DISTINCT sns.telefoonnummer, Count (*) as [SMS1Count] FROM [SMS_Tracking_DataView] sm INNER JOIN [Test_CustomerList] sns on sm.Mobile = sns.telefoonnummer WHERE sns.klantnaam IS NOT NULL AND sm.Name = 'SMS1' GROUP BY sns.telefoonnummer

enter image description here

Or this code if you wanted to group by unique id (mobile number) and count all SMS sent to them

SELECT DISTINCT sns.telefoonnummer, Count (*) as [SMS1Count]
FROM [SMS_Tracking_DataView] sm
INNER JOIN [Test_CustomerList] sns 
on sm.Mobile = sns.telefoonnummer
WHERE sns.klantnaam IS NOT NULL 
GROUP BY sns.telefoonnummer
enter code here

enter image description here

Improve this answer
0

You must log in to answer this question.

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