SQL for SFMC

Pulling one unique record from specific columns

SELECTrow_number() over (partition by o.SubscriberKey, o.EmailName order by EventDate asc) rankingFROM ...

OR

inner join ( select c1.ID, row_number() over ( partition by c1.accountid order by c1.CreatedDate desc ) as RowNum from Contact_Salesforce c1 ) b on c.ID = b.ID and b.RowNum = 1

Breaking this down

  1. After "partition by" is the column (or columns) that will hold the unique criteria.

  2. After "order by" are the columns to order your results by. Considering that if you want the first time someone opened an email then you will want to sort by EventDate in order or oldest date to newest (ASC - Ascending).

Notes:

  1. You can comma separate the partition by columns and order by columns if you need to have multiple criteria

  2. Consider your primary key(s) in your Data Extension, For the above to work you would need PKs for SubscriberKey and EmailName

Querying Opens and Clicks to Build a "Resending to Opened/Unopened" List

Breaking this down

The actual columns you create in your sending data extension are debatable, but it is important when querying for a resend list of for Opened/Engaged that you exclude consideration of clicks to unsubscribe and view the privacy policy.

Quick SQL to Count Domains in a List for Warming

SELECT top 5000 SUBSTRING ([Email], CHARINDEX( '@', [Email]) + 1, LEN([Email])) AS [Domain], COUNT(Email) AS TotalFROM DATA_EXTENSION_NAMEGROUP BY SUBSTRING ([Email], CHARINDEX( '@', [Email]) + 1, LEN([Email]))order by COUNT(Email) DESC

Quick SQL Match Bounced Emails with ContactIDs

SELECT

c.id as contactid,

b.emailaddress,

'Held' AS status

FROM Contact_Salesforce c


INNER JOIN DATA_EXTENSION_BOUNCES b

ON c.email = b.emailaddress


WHERE b.emailaddress is not null

Finding Null Columns in a Data Extension

If there are a many columns in a Data Extension and you want to identify those with no values (could be missing from a query) here is one way to go about it.

  1. Copy the Data Extension and populate it with one value from the original DE. (ex. SELECT TOP 1 * FROM ORIGINAL_DE)

  2. Export the single record and delete the new DE (you only needed it to get all the column names)

  3. Open the downloaded file in excel, copy all of the column names and paste the transposed (so instead of across they are in rows).

  4. Insert these 2 functions in the 2nd and 3rd columns:

    1. Colmun B: =CONCATENATE(", pp.",A1)

    2. Column C: =CONCATENATE(" , SUM(CASE WHEN p.",A1," IS NOT NULL THEN 1 ELSE 0 END) AS ",A1)

  5. Build the below SQL query with the data you composed in Excel (line 3 comes from Column C, line 7 is column B):

  1. SELECT
  2. Columns
  3. , SUM(CASE WHEN p.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END) AS COLUMN_NAME
  4. FROM (
  5. SELECT
  6. 'one' as Columns
  7. , pp.COLUMN_NAME
  8. FROM ORIGINAL_DE pp
  9. ) p
  10. GROUP BY
  11. Columns