Administer Marketing Cloud - Développement pour Marketing Cloud

Query Data with SQL /Interroger des données avec SQL

Learning Objectives /Objectifs de formation

After completing this unit, you’ll be able to: /Une fois cette unite terminée, vous pourrez :

  • Define SQL and why it is used. /Définir SQL et savoir pourquoi il est utilisé.
  • Use SQL within Marketing Cloud. /Utiliser SQL dans Marketing Cloud.
  • Identify best practices for using SQL. /Identifier les meilleures pratiques d’utilisation de SQL.

What Is SQL? /Qu’est-ce que SQL?

You’ve moved in to Marketing Cloud, unpacked, and even started to arrange your space. /Vous avez emménagé dans Marketing Cloud, déballé et même commencé à organiser votre plateforme.

 Now for the finishing touches. As we mentioned earlier, Marketing Cloud data extensions and Contact Builder use a relational database. / Maintenant pour la touche finale. Comme nous l’avons mentionné précédemment, les extensions de données Marketing Cloud et Contact Builder utilisent une base de données relationnelle.

 So, you need a good way to get to all of that rich data. Enter: Structured Query Language (or SQL), a domain-specific language that can do just that. /Vous avez donc besoin d’un bon moyen d’accéder à toutes ces données riches. Utilisez: Structured Query Language (ou SQL), un langage spécifique au domaine qui peut faire exactement cela.

 In Marketing Cloud, an SQL query activity is used to execute queries and retrieve data for reporting or segmenting audiences. /Dans Marketing Cloud, une activité de requête SQL est utilisée pour exécuter des requêtes et récupérer des données pour la génération de rapports ou la segmentation d’audiences.

Use SQL in Marketing Cloud / Utiliser SQL dans Marketing Cloud

First, let’s review how a few specific SQL commands are used in Marketing Cloud. / Tout d’abord, revoyons comment quelques commandes SQL spécifiques sont utilisées dans Marketing Cloud.

  • SELECT: command to locate data on a database /SELECT: commande pour localiser les données dans une base de données
  • FROM: location where data reside within Marketing Cloud (usually a data extension) /FROM: emplacement où résident les données dans Marketing Cloud (généralement une extension de données)
  • JOIN: allows the query to search multiple tables and/or data extensions /JOIN: permet à la requête de rechercher plusieurs tables et / ou extensions de données
  • WHERE: used to filter out data you do/don’t want / : utilisé pour filtrer les données que vous soutaitez / ne souhaitez pas

Here’s an example. /Voici un exemple.

SELECT emailaddress as ‘Email_Address’, favoritecolor as ‘Favorite_Color’
FROM [MasterData Extension] m
INNER JOIN JuneMarketingSend j
ON m.emailaddress = j.emailaddress
WHERE m.emailaddress is not NULL

Need a translation? Running this query retrieves email and favorite color from the master data extension, and joins it with any matching email addresses from the June marketing send. / Besoin d’une traduction? L’exécution de cette requête récupère les e-mails et la couleur préférée de l’extension des données de base et les joint à toutes les adresses e-mail correspondantes de l’envoi marketing de juin.

Additionally, it only returns emails that are not missing. /De plus, il ne renvoie que les e-mails qui ne manquent pas.

Also notice the m and the j. These are SQL aliases that are used to give a table, or a field in a table, a temporary name. / Notez également le m et le j. Ce sont des alias SQL qui sont utilisés pour donner un nom temporaire à une table ou à un champ d’une table.

 This is especially helpful when you have a common field name between data extensions, like CustomerID or EmailAddress. You can also see it written like the following. /Cela est particulièrement utile lorsque vous avez un nom de champ commun entre les extensions de données, comme CustomerID ou EmailAddress. Vous pouvez également le voir écrit comme suit.

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerID=o.CustomerID

Note /Remarque

SQL commands are not case sensitive: select is the same as SELECT. / Les commandes SQL ne sont pas sensibles à la casse: select est identique à SELECT.

SQL Data Views / Vues de données SQL

System data views are Salesforce-created queries that you can use to find information about your subscribers. /Les vues de données système sont des requêtes créées par Salesforce que vous pouvez utiliser pour rechercher des informations sur vos abonnés.

 Just remember, you won’t be able to make changes to these precreated data views. Here are some of the most commonly used views. /N’oubliez pas que vous ne pouvez pas modifier ces vues de données précréées. Voici quelques-unes des vues les plus utilisées.

Data ViewQueryInformation
Bounce_bounceIdentify and then suppress commonly bounced email addresses from sends. /Identifiez, puis supprimez les adresses e-mail couramment renvoyées des envois.  
Click and Open_Click

_Open
Query these data views in Automation Studio to view click and open data for emails from your Marketing Cloud account. / Recherchez ces vues de données dans Automation Studio pour afficher les données de clic et d’ouverture des e-mails de votre compte Marketing Cloud.

Helps to identify additional messaging opportunities by indicating subscriber engagement on a specific JobID. /Aide à identifier des opportunités de messagerie supplémentaires en indiquant l’engagement des abonnés sur un JobID spécifique.
Complaint_ComplaintQuery this data view in Automation Studio to view complaints data related to emails from your Marketing Cloud account. / Recherchez cette vue de données dans Automation Studio pour afficher les données relatives aux plaintes liées aux e-mails de votre compte Marketing Cloud.

Use to prune your lists to ensure more accurate audiences and improve deliverability. /Utilisez pour affiner vos listes pour garantir des audiences plus précises et améliorer la dérivabilité.
Journey_JourneyFind a journey’s status, created and last modified date, and other general journey information using this Journey Builder data view. / Trouvez le statut d’un parcours, la date de création et la dernière modification et d’autres informations générales sur le parcours à l’aide de cette vue de données Journey Builder.

Note/Remarque

Click and open tracking data displays in Central Standard Time, does not observe Daylight Savings Time, and is rounded to the nearest second. /Cliquez et ouvrez les données de suivi s’affichent en heure normale centrale, ne tient pas compte de l’heure d’été et sont arrondies à la seconde la plus proche.

So how do you use these data views? / Alors, comment utiliser ces vues de données?

First create a data extension to store the data you need from these views, then navigate to Automation Studio to create a query activity based on the desired data view table. /Créez d’abord une extension de données pour stocker les données dont vous avez besoin à partir de ces vues, puis accédez à Automation Studio pour créer une activité de requête basée sur la table de vue de données souhaitée.

After you complete and run the automation, the output is stored in your created data extension. /Une fois l’automatisation terminée et exécutée, la sortie est stockée dans votre extension de données créée.

Here is a query example that pulls data from the complaint data view for the last 6 months and provides SubscriberKey, JobIDs, and Bounce Reasons. /Voici un exemple de requête qui extrait des données de la vue des données de réclamation pour les 6 derniers mois et fournit SubscriberKey, JobIDs et Bounce Reasons.

SELECT SubscriberKey,JobID,SMTPBounceReason
FROM _Complaint

Joining Data with SQL / Joindre des données avec SQL

Beyond data views, SQL queries can also be customized to solve for a variety of business needs. / Au-delà des vues de données, les requêtes SQL peuvent également être personnalisées pour répondre à divers besoins de l’entreprise.

Say you want to send an email that has data stored in the send log and data found in your customer data extension. / Supposons que vous souhaitez envoyer un e-mail contenant des données stockées dans le journal d’envoi et des données trouvées dans votre extension de données client.

To craft these queries, you’ll want to understand the concept of joins—or taking data from one source and joining it with data from another source. /Pour créer ces requêtes, vous devez comprendre le concept de jointures, ou prendre des données d’une source et les associer à des données d’une autre source.

SQL joins can be complex, so let’s review some join scenarios using a Venn diagram. / Les jointures SQL peuvent être complexes, examinons donc certains scénarios de jointure à l’aide d’un diagramme de Venn.

SQL joins graphic with diagrams 1-7, showing groupings of data from A or B.

Let’s start with the top row, known as the left and right joins. /Commençons par la ligne du haut, appelée jointure gauche et droite.

Diagram 1: Left Outer Join

You want all records from data extension A, plus matching records in the data extension B. / Vous voulez tous les enregistrements de l’extension de données A, ainsi que les enregistrements correspondants dans l’extension de données B.

SELECT *
FROM DataExtension A
LEFT JOIN DataExtension B
ON A.Field = B.Field

Sample Result /Exemple de résultat

Data Extension AData Extension B
NameEmailNameEmail
Joejoe@email.comJoejoe@email.com
Jennjenn@email.comnullnull
Justinjustin@email.comnullnull

Diagram 2: Left Outer Join with Exclusions

In this join, we exclude the records we don’t want from data extension B. /Dans cette jointure, nous excluons les enregistrements que nous ne voulons pas de l’extension de données B.

SELECT *
FROM DataExtension A
LEFT JOIN DataExtension B
ON A.Field = B.Field
WHERE B.Field IS NULL

Sample Result /Exemple de résultat

Data Extension AData Extension B
NameEmailNameEmail
Joejoe@email.comnullnull
Jennjenn@email.comnullnull
Justinjustin@email.comnullnull

Diagram 3 and 4: Right Joins

As for the right joins, they’re the reverse of the previous scenarios. /Quant aux jointures de droite, elles sont l’inverse des scénarios précédents.

DiagramCode
3SELECT * FROM DataExtension A RIGHT JOIN DataExtension B ON A.Field = B.Field
4SELECT * FROM DataExtension A RIGHT JOIN DataExtension B ON A.Field = B.Field WHERE A.Field IS NULL

Now let’s look at inner and outer joins. /Voyons maintenant les jointures internes et externes.

SQL joins graphic with diagrams 1-7

Diagram 5: Inner Join

This query returns records that have matching values in both data extensions. /Cette requête renvoie des enregistrements qui ont des valeurs correspondantes dans les deux extensions de données.

SELECT *
FROM DataExtension A
INNER JOIN DataExtension B
ON A.Field = B.Field

Sample Result /Exemple de résultat

Data Extension AData Extension B
NameEmailNameEmail
Joejoe@email.comJoejoe@email.com
Jonathanjonathan@email.comJonathanjonathan@email.com

Diagram 6: Full Outer Join

Full outer joins return all matching records from both data extensions. /Les jointures externes complètes renvoient tous les enregistrements correspondants des deux extensions de données.

SELECT *
FROM DataExtension A
FULL OUTER JOIN DataExtension B
ON A.Field = B.Field

Sample Result /Exemple de résultat

Data Extension AData Extension B
NameEmailNameEmail
Joejoe@email.comJoejoe@email.com
Jennjenn@email.comnullnull
Justinjustin@email.comnullnull
nullnullJenniferjennifer@email.com
Jonathanjonathan@email.comJonathanjonathan@email.com

Diagram 7: Outer Join 

Returns all matching records from both data extensions, minus the records we don’t want. /Renvoie tous les enregistrements correspondants des deux extensions de données, moins les enregistrements dont nous ne voulons pas.

SELECT *
FROM DataExtension A
FULL OUTER JOIN DataExtension B
ON A.Field = B.Field
WHERE A.Field IS NULL OR B.Field IS NULL

Sample Result /Exemple de résultat

Data Extension A Data Extension B
NameEmailNameEmail
Joejoe@email.comnullnull
Jennjenn@email.comnullnull
Justinjustin@email.comnullnull
nullnullJenniferjennifer@email.com
nullnullJonathanjonathan@email.com

Note/Remarque

Be wary of outer joins as they can potentially return very large result sets! / Méfiez-vous des jointures externes car elles peuvent potentiellement renvoyer de très grands ensembles de résultats!

See SQL in Action /Voir SQL en action

Cloud Kicks has decided to send an exclusive offer to customers who have opened an email in the last 6 months and have made a purchase during that time/Cloud Kicks a décidé d’envoyer une offre exclusive aux clients qui ont ouvert un e-mail au cours des 6 derniers mois et qui ont effectué un achat pendant cette période. L’architecte

Solution Architect Maggie Quinn needs to complete this task by using a SQL query. Let’s follow along. / L’architecte de solution Maggie Quinn doit effectuer cette tâche à l’aide d’une requête SQL. Suivons.

Maggie starts by creating a data extension to store the information about the customers that meet this criteria. / Maggie commence par créer une extension de données pour stocker les informations sur les clients qui répondent à ces critères.

She then navigates to Automation Studio and follows these steps. /Elle accède ensuite à Automation Studio et suit ces étapes.

  1. Click New Automation. /Cliquez sur Nouvelle automatisation.
  2. Select a starting source from Schedule or File Drop. /Sélectionnez une source de départ dans Schedule ou File Drop.
  3. Drag SQL Query to workflow and click Choose. (You can select an existing query or create a new query.) /Faites glisser la requête SQL vers le flux de travail et cliquez sur Choisir. (Vous pouvez sélectionner une requête existante ou créer une nouvelle requête.)
  4. Select Create New Query Activity. /Sélectionnez Créer une nouvelle activité de requête.
  5. Add query properties: name, external key, folder location, and description. /Ajoutez des propriétés de requête: nom, clé externe, emplacement du dossier et description.
  6. Next, build your SQL query. /Ensuite, générez votre requête SQL.

Maggie uses this query. /Maggie utilise cette requête.

SELECT c.EmailAddress, c.CustomerID, c.First_Name
FROM Customers c
INNER JOIN Purchases p
ON c.CustomerID = p.CustomerID
WHERE [Purchase Date] > DateAdd(month, -6, GetDate()) AND [Open Date] > DateAdd(month, -6, GetDate())

Note /Remarque

Data extensions and field names with spaces in their names need to be enclosed in [  ] brackets. /Les extensions de données et les noms de champ avec des espaces dans leurs noms doivent être placés entre crochets []

Once completed, she follows the remaining steps. /Une fois terminée, elle suit les étapes restantes.

  1. Click Validate Syntax. /Cliquez sur Valider la syntaxe.
  2. Choose the data extension created to store the query results. /Choisissez l’extension de données créée pour stocker les résultats de la requête.
  3. Choose the data action the query activity performs: Append, Update, or Overwrite. /Choisissez l’action de données que l’activité de requête effectue: ajouter, mettre à jour ou remplacer.
  4. Click Save. /Cliquez sur Enregistrer.

Once she is ready, Maggie can run the automation. /Une fois qu’elle est prête, Maggie peut exécuter l’automatisation.

Common SQL Queries /Requêtes SQL courantes

Parent Accounts /Comptes parents

You can query data extensions in the Shared Data Extension folder from the parent account by adding the prefix ent. to the data extension name in the query. /Vous pouvez interroger les extensions de données dans le dossier Shared Data Extension du compte parent en ajoutant le préfixe ent. au nom de l’extension de données dans la requête.

SELECT EmailAddress
FROM ent.MasterDataExtension

All the Data /Toutes les données

Sometimes you want everything. /Parfois, vous voulez tout.

SELECT *
FROM MasterDataExtension

Use SELECT * very carefully as this can cause slowness in the system as it is processing a large amount of data. /Utilisez SELECT * très soigneusement car cela peut entraîner une lenteur dans le système car il traite une grande quantité de données.

The larger the dataset, the greater time and effort the system has to use to obtain the data. Queries time-out after 30 minutes, and the more data to process, the more likely the query can time out. /Plus l’ensemble de données est volumineux, plus le système doit consacrer de temps et d’efforts pour obtenir les données. Le délai des requêtes expire après 30 minutes, et plus il y a de données à traiter, plus la requête peut expirer.

To help with this, it is more effective to provide the exact fields you are looking for in the query. /Pour vous aider, il est plus efficace de fournir les champs exacts que vous recherchez dans la requête

SELECT field1, field2, field3
FROM MasterDataExtension

SQL is something you can spend years mastering, but for now, you have the basics. /SQL est quelque chose que vous pouvez passer des années à maîtriser, mais pour l’instant, vous avez les bases.

We hope you—and your data—are feeling settled in your new home. And we can’t wait to see what you achieve with the power of your Marketing Cloud data! /Nous espérons que vous – et vos données – vous sentez installés dans votre nouvelle maison. Et nous avons hâte de voir ce que vous réaliserez avec la puissance de vos données Marketing Cloud!

Resources /Ressources

Leave a Reply

Your email address will not be published. Required fields are marked *