asked on

Need help to write SQL query in Sales force Marketing cloud

Hello,
In Salesforce Marketing cloud we have four system tables. 1. Job , 2.Sent , 3.Open and 4.Cllick. Now we have one html file where user receive by email. user open the link and click the button (Click to Buy or Click to Call). Now we have to make three query.

1. No Open , No Click
2. Open , No Click
3. Open and Click

Data Views Links:
Job Data view
Sent Data View
Open Data view
CLick Data view
Subscriber Data View

Can you please help me to write quesry?

PortletPaul

The documentation says that the SQL used is the same as MS SQL Server version 2005

There is also a sample query, see "Query: Find Subscribers with No Opens or Clicks" and this shows how 3 of the relevant tables are joined together:

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)

Open in new window

What do you expect your query to provide? for example: what are the columns you expect to see?

nb: Each of the tables appear to have a number of "foreign keys" which will be used in joins (refer to the sample above)

JOB
+---------------+-----------------------------------------------------------+----------+--------------------------+----------+
|     NAME      |                        DESCRIPTION                        | DATATYPE | DATA EXTENSION DATA TYPE | NULLABLE |
+---------------+-----------------------------------------------------------+----------+--------------------------+----------+
| JobID         | The job ID number for the email send                      | int      | Number                   |          |
| EmailID       | The email ID for the job                                  | int      | Number                   | X        |
| AccountID     | The ID number for the account that performed the job      | int      | Number                   | X        |
| AccountUserID | The ID number for the account user that performed the job | int      | Number                   | X        |
+---------------+-----------------------------------------------------------+----------+--------------------------+----------+

SENT
+--------------+-----------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+
|     NAME     |                                                   DESCRIPTION                                                   | DATATYPE | DATA EXTENSION DATA TYPE | NULLABLE |
+--------------+-----------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+
| AccountID    | Your account ID number                                                                                          | int      | Number                   |          |
| OYBAccountID | The account ID number for any related On-Your-Behalf accounts. This field applies to enterprise accounts only.  | int      | Number                   | X        |
| JobID        | The job ID number for the email send                                                                            | int      | Number                   |          |
| ListID       | The list ID number for the list used in the send                                                                | int      | Number                   |          |
| BatchID      | The batch ID number for any batches used in the send                                                            | int      | Number                   |          |
| SubscriberID | The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record. | int      | Number                   |          |
+--------------+-----------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+

OPEN
+--------------+-----------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+
|     NAME     |                                                   DESCRIPTION                                                   | DATATYPE | DATA EXTENSION DATA TYPE | NULLABLE |
+--------------+-----------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+
| AccountID    | Your account ID number                                                                                          | int      | Number                   |          |
| OYBAccountID | The account ID number for any related On-Your-Behalf accounts. This field applies to enterprise accounts only.  | int      | Number                   | X        |
| JobID        | The job ID number for the email send                                                                            | int      | Number                   |          |
| ListID       | The list ID number for the list used in the send                                                                | int      | Number                   |          |
| BatchID      | The batch ID number for any batches used in the send                                                            | int      | Number                   |          |
| SubscriberID | The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record. | int      | Number                   |          |
+--------------+-----------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+

OPEN
+--------------+----------------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+
|     NAME     |                                                     DESCRIPTION                                                      | DATATYPE | DATA EXTENSION DATA TYPE | NULLABLE |
+--------------+----------------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+
| AccountID    | Your account ID number                                                                                               | int      | Number                   |          |
| OYBAccountID | The account ID number for any related On-Your-Behalf (OYB) accounts. This field applies to enterprise accounts only. | int      | Number                   | X        |
| JobID        | The job ID number for the email send                                                                                 | bigint   | Number                   |          |
| ListID       | The list ID number for the list used in the send                                                                     | int      | Number                   |          |
| BatchID      | The batch ID number for any batches used in the send                                                                 | bigint   | Number                   |          |
| SubscriberID | The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.      | int      | Number                   |          |
+--------------+----------------------------------------------------------------------------------------------------------------------+----------+--------------------------+----------+

Open in new window

sharepoint0520

ASKER

HI,
Can you please help me write all three query?

PortletPaul

I cannot read your mind.... I need to know what you expect the query to do

sharepoint0520

ASKER

Hello,
I am so sorry to give half details. We sent link to users by email. Those information store in sent table. Now if user open the email then it's goes to open table. If user click the button on link , that information goes to click table. Subscriber table has subcriber information. Now i will need the three queries.

1. Who did not open and did not click
2. Who open the link but did not click on button
3. Who open the link and clcik the button.

I will need some basic columns

SubscriberKey
JobID
BatchID
SendDate
Open Date (if necessary)
Click Date (if necessary)

I really appreciate your help.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharepoint0520

ASKER

Thank you so much.