Écriture de requêtes SQL personnalisées à l’aide d’Amplitude Query

Le module complémentaire relatif au produit Amplitude Query permet aux clients d’interroger leurs données brutes via leur base de données Snowflake gérée par Amplitude. Les données sont chargées dans Snowflake toutes les 30 minutes. Query inclut également un nouveau type de graphique très efficace appelé Amplitude SQL, qui permet aux clients d’écrire du code SQL personnalisé portant sur leurs données Amplitude directement au sein de la plateforme Amplitude.

REMARQUE : cette fonctionnalité est uniquement disponible pour les clients Entreprise et Growth qui ont fait l’acquisition du module complémentaire relatif au produit Query.

Premiers pas avec Query

Amplitude SQL est accessible comme n’importe quel autre type de graphique via le menu déroulant Create Chart situé en haut à droite de la barre de navigation. Pour en savoir plus sur l’utilisation de ce type de graphique, consultez la section Amplitude SQL ci-dessous.

Vous pouvez également vous connecter directement à votre base de données Snowflake via un terminal ou une application tierce telle que SQL Workbench. Snowflake propose également un connecteur pour Python ici. Veuillez nous contacter via ce lien ou contacter votre Success Manager pour obtenir vos informations d’identification Snowflake. 

REMARQUE : cette fonctionnalité est uniquement disponible pour les clients Entreprise et Growth qui ont fait l’acquisition du module complémentaire relatif au produit Query.

Description du schéma de requête

Nomenclature des tableaux

Le package Query utilise un schéma de tableau simplifié pour Snowflake et Amplitude SQL. Amplitude SQL utilise un schéma de tableau unique qui peut être référencé à l’aide du raccourci $events pour un projet donné. Si vous souhaitez accéder à d’autres tableaux, vous pouvez utiliser leur nom complet que vous retrouverez en cliquant sur « Show Schema ».

Bien que le tableau $events gère automatiquement les mappages d’utilisateurs fusionnés, le schéma du tableau des utilisateurs fusionnés est également mis à disposition sous le schéma relatif à $events. Il est utile si vous souhaitez visualiser le nombre d’utilisateurs qui ont été fusionnés en un seul utilisateur.

L’un des principaux avantages de Query est que les tableaux peuvent contenir un nombre illimité de colonnes. Les propriétés d’utilisateur personnalisées et les propriétés d’événement sont stockées en tant que variantes ; elles peuvent être interrogées en tant que colonnes individuelles. Les propriétés d’utilisateur personnalisées sont munies du préfixe user_properties: et toutes les propriétés d’événement présentent le préfixe event_properties:.

REMARQUE : si vos propriétés d’utilisateur ou d’événement contiennent un point ou un espace, vous devrez mettre le nom de la propriété entre guillemets. Par exemple, user_properties:"first name".

Si vous recherchez une certaine valeur, vous devrez entourer la valeur de la propriété de guillemets simples. Par exemple, user_properties:"plan type"='enterprise'.

Schéma de colonne

Tableau $events

Colonne	Description
$amplitude_id NUMBER(38,0)	L’ID Amplitude d’origine de l’utilisateur. Utilisez ce champ pour gérer automatiquement les utilisateurs fusionnés. 2234540891
adid VARCHAR(16777216)	(Android) ID publicitaire des services Google Play (AdID). Cet ID est en général effacé après son ingestion et sera donc vide. « AEBE52E7-03EE-455A-B3C4-E57283966239 »
amplitude_attribution_ids	Il s’agit du hachage anonymisé des ID publicitaires que nous stockons à des fins internes ; il n’est en aucun cas utile au client. Il apparaîtra cependant si des ID publicitaires ont été envoyés, prouvant l’existence d’adid/idfv même après leur effacement.
amplitude_event_type VARCHAR(16777216)	Identifiants propres à Amplitude reposant sur les événements générés par Amplitude. Il s’agit d’un champ hérité, donc event_type devrait suffire pour toutes les requêtes.
amplitude_id NUMBER(38,0)	Un ID interne utilisé pour compter les utilisateurs uniques. 1234567890
app NUMBER(38,0)	L’ID de projet se trouvant sur la page Settings de votre projet. 123456
city VARCHAR	Le nom de la ville. « San Francisco »
client_event_time TIMESTAMP	L’horodatage local (UTC) du moment où l’appareil a consigné l’événement. 2015-08-10T12:00:00.000000
client_upload_time TIMESTAMP	L’horodatage local (UTC) du moment où l’appareil a chargé l’événement. 2015-08-10T12:00:00.000000
country VARCHAR	Le nom du pays. « États-Unis »
data VARIANT	Le dictionnaire dans lequel certains champs tels que first_event et merged_amplitude_id sont stockés.
device_brand VARCHAR(16777216)	La marque de l’appareil. « Apple »
device_carrier VARCHAR(16777216)	L’opérateur de l’appareil. « Verizon »
device_family VARCHAR(16777216)	La famille de l’appareil. « iPhone Apple »
device_id VARCHAR(16777216)	L’identifiant propre à l’appareil. « C8F9E604-F01A-4BD9-95C6-8E5357DF265D »
device_manufacturer VARCHAR(16777216)	Le fabricant de l’appareil. « Apple »
device_model VARCHAR(16777216)	Le modèle de l’appareil. « iPad Mini »
device_type VARCHAR(16777216)	Le type d’appareil. « iPhone 5s Apple »
dma VARCHAR(16777216)	La zone de marché désignée (DMA). « San Francisco-Oakland-San Jose, Californie »
event_id NUMBER(38,0)	Un décompte qui distingue les événements. 1
event_time TIMESTAMP	L’horodatage d’Amplitude (UTC) qui correspond au client_event_time ajusté en fonction de la différence entre server_received_time et client_upload_time. Plus précisément, cela correspond à ce qui suit : event_time = client_event_time + (server_received_time - client_upload_time) 2015-08-10T12:00:00.000000 Nous utilisons cet horodatage pour organiser les événements sur les graphiques Amplitude. Remarque : si la différence entre server_received_time et client_upload_time est inférieure à 60 secondes, event_time ne sera pas ajusté et sera égal à client_event_time
event_type VARCHAR(16777216)	Le type d’événement attribué. « Ajouter un ami »
followed_an_identify BOOLEAN	A pour valeur true s’il y avait un événement identify entre cet événement SDK actuel et le dernier événement SDK observé.
groups VARIANT	Représente les types de groupes. Reportez-vous à la documentation Accounts pour en savoir plus.
idfa VARCHAR(16777216)	(iOS) L’identifiant de l’annonceur. Cet identifiant est en général effacé après son ingestion et sera donc vide. « AEBE52E7-03EE-455A-B3C4-E57283966239 »
ip_address VARCHAR(16777216)	L’adresse IP. « 123.11.111.11 »
location_lat FLOAT	La latitude. 12,3456789
location_lng FLOAT	La longitude. -123,4567890
os_name VARCHAR(16777216)	Le nom du système d’exploitation. « ios »
os_version VARCHAR(16777216)	La version du système d’exploitation. « 1.0 »
paying VARCHAR	A pour valeur true si l’utilisateur a déjà consigné des revenus, sinon la valeur est « (none) ». Remarque : la valeur de la propriété peut être modifiée via l’ API Identify. true
region VARCHAR	La nom de la région. « Californie »
server_upload_time TIMESTAMP	L’horodatage d’Amplitude (UTC) du moment où nos serveurs ont reçu l’événement. 2015-08-10T12:00:00.000000
session_id NUMBER(38,0)	L’heure de début de la session exprimée en nombre de millisecondes depuis l’epoch. 1396381378123
start_version VARCHAR	La version de l’application sur laquelle l’utilisateur a été suivi pour la première fois. « 1.0.0 »
user_creation_time TIMESTAMP	La valeur event_time (UTC) du premier événement de l’utilisateur. 2015-08-10T12:00:00.000000
user_id VARCHAR(16777216)	Un ID lisible que vous spécifiez. « [e-mail] »
uuid VARCHAR(16777216)	Un identifiant unique associé à une ligne donnée (représentant un événement envoyé). bf0b9b2a-304d-11e6-934f-22000b56058f
version_name VARCHAR(16777216)	La version de l’application. « 1.0.0 »

Tableau des utilisateurs fusionnés

Pour en savoir plus sur la façon dont Amplitude procède au suivi des utilisateurs uniques, consultez cet article.

Colonne	Description
amplitude_id NUMBER(38,0)	L’ID Amplitude qui est fusionné avec l’ID Amplitude d’origine d’un utilisateur.
merge_event_time TIMESTAMP	L’heure de l’événement où le nouvel ID Amplitude d’un utilisateur a été associé à son ID Amplitude d’origine.
merge_server_time TIMESTAMP	L’heure du serveur lors de l’événement où le nouvel ID Amplitude d’un utilisateur a été associé à son ID Amplitude d’origine.
merged_amplitude_id NUMBER(38,0)	L’ID Amplitude attribué à l’origine à l’utilisateur lors de sa création.

Type de graphique Amplitude SQL

Le graphique Amplitude SQL est intégré à l’expérience Amplitude native et apparaît dans la liste déroulante « Create Chart » située en haut à droite de la barre de navigation. Tout comme l’ensemble des autres contenus issus des graphiques d’Amplitude, les analyses qu’il permet de réaliser peuvent être enregistrées, partagées et intégrées à des tableaux de bord. Ce type de graphique vous permet d’écrire des requêtes SQL personnalisées portant sur vos propres données Amplitude.

REMARQUE : cette fonctionnalité ne prend actuellement pas en charge la mise en file d’attente des données pour les vues Portfolio.

Configuration du graphique

Par défaut, une requête SQL simple s’affiche, renvoyant la date, les utilisateurs uniques et le nombre total d’événements déclenchés au cours des 30 derniers jours. La syntaxe SQL sera mise en surbrillance pour vous aider à distinguer les commandes SQL du reste de votre requête.

En outre, Amplitude SQL prend en charge la saisie semi-automatique des noms des colonnes du tableau. Au fur et à mesure de votre saisie, l’éditeur de requête recommandera des noms de colonnes pour vous aider à accélérer votre saisie.

Champs spéciaux

Étant donné qu’Amplitude SQL est intégré directement dans l’expérience de graphique Amplitude, vous pouvez employer la plupart des fonctionnalités de l’interface utilisateur d’Amplitude que vous connaissez déjà, telles que le sélecteur de date et l’enregistrement de graphiques. Pour ce faire, vous pouvez utiliser les champs spéciaux d’Amplitude SQL en tant que raccourcis efficaces :

• $date : lors de l’utilisation de ce raccourci, la plage de dates choisie par le sélecteur de date est automatiquement appliquée et mettra à jour la requête au fil du temps. Il fait référence à l’heure d’événement figurant sur l’événement et respectera le fuseau horaire sur lequel le projet a été défini. En l’absence d’un fuseau horaire, Amplitude SQL renverra les données au format UTC.
  • Remarque : ce raccourci doit être utilisé conjointement avec $events.
• $events : le raccourci utilisé pour faire référence au tableau dans votre projet actuel. Lors de l’utilisation de ce tableau, la gestion des utilisateurs fusionnés s’effectue de manière automatique.
  • Remarque : ce raccourci doit être utilisé conjointement avec $date.
• $amplitude_id : l’ID Amplitude d’origine de l’utilisateur. Utilisez ce champ pour gérer automatiquement les utilisateurs fusionnés.

Résultats de requête

Pour exécuter votre requête, cliquez sur « Compute » tout à droite. Une fois l’exécution de la requête terminée, les résultats seront affichés en dessous dans un tableau de données. Les résultats du tableau de données et du fichier CSV exporté sont limités à 1 000 lignes. De plus, les données renvoyées seront présentées visuellement dans un graphique sous le tableau. Pour le moment, seule une série chronologique peut être affichée.

Pour personnaliser la visualisation, vous pouvez utiliser un ensemble de commandes figurant sous l’éditeur de requête. Les options disponibles dans les commandes de visualisation correspondent aux champs que vous renvoyez dans votre instruction SQL SELECT. Par exemple, dans la requête ci-dessus, les 3 champs que nous pouvons visualiser sont « DATE », « UNIQUES », et « TOTALS ».

• Colonne de l’axe des abscisses : sélectionnez ce qui est tracé sur l’axe des abscisses. À l’heure actuelle, seules les séries chronologiques sont prises en charge.
• Colonne de métrique : sélectionnez le champ renvoyé dans votre instruction SQL SELECT tracée sur l’axe des ordonnées.

Application de regroupements

Pour regrouper un graphique en fonction d’une colonne, saisissez le nom de la colonne dans le champ « Label columns ». Cliquez ensuite sur le bouton « Compute ».

Partage et enregistrement de requêtes

Comme avec les autres graphiques Amplitude, vous pouvez exporter les résultats sous forme de fichier PNG, PDF ou CSV via l’icône « EXPORT » en haut à droite. De plus, vous pouvez enregistrer vos analyses et les partager avec votre équipe, et même ajouter les visualisations que vous créez à un tableau de bord dans Amplitude.

ETL Snowflake

En plus du module complémentaire Query, les clients payants ont accès à une interface utilisateur en libre-service permettant d’exporter les données d’Amplitude vers leur propre compte Snowflake. Cette fonctionnalité est incluse dans tous les plans payants Amplitude et est prise en charge quelle que soit votre région. Grâce à elle, vous pouvez conserver la propriété et assurer la confidentialité de votre entrepôt de données, ainsi qu’effectuer vous-même des tâches de maintenance, d’administration et de personnalisation, sans avoir besoin de l’assistance des ingénieurs d’Amplitude. 

L’ancien service de partage de données d’Amplitude, l’ETL Snowflake, a vocation à disparaître progressivement. Si vous utilisez actuellement l’ETL Snowflake, veuillez contacter votre Success Manager qui vous donnera des conseils relatifs à la migration vers l’exportation en libre-service.

Ce tableau décrit les avantages de l’exportation Snowflake en libre-service :

Fonctionnalités	Exportation des données vers une instance Snowflake détenue et gérée par le client. Contrôle complet sur les données et les accès et possibilité de modifier les autorisations s’y rapportant.
Comptes	Nécessite une relation et un entrepôt Snowflake.
Public cible	Les clients souhaitant utiliser Snowflake en tant qu’entrepôt de données principal et voulant disposer de capacités d’écriture sur d’autres ensembles de données, ainsi que d’un contrôle sur les modifications et les accès.