Traduire les requêtes SQL dans Commerce Intelligence

Dernière mise à jour : 2023-08-17
  • Créé pour :
  • Beginner
    Intermediate
    Admin
    Developer
    User

Vous êtes déjà demandé comment les requêtes SQL sont traduites dans colonnes calculées, mesures, et rapports vous utilisez dans Commerce Intelligence? Si vous êtes un utilisateur SQL lourd, comprenez comment SQL est traduit dans Commerce Intelligence vous permet de travailler plus intelligemment dans la variable Gestionnaire de Data Warehouse et tirez le meilleur parti de Commerce Intelligence plateforme.

À la fin de cette rubrique, vous trouverez un matrice de traduction pour les clauses de requête SQL et Commerce Intelligence éléments .

Commencez par examiner une requête générale :
SELECT
a, Rapport group by
SUM(b) Aggregate function (colonne)
FROM c Source table
WHERE
d IS NOT NULL Filter
AND time < X

AND time >= Y		 Rapport time frame
GROUP BY a Rapport group by

Cet exemple couvre la plupart des cas de traduction, mais il existe certaines exceptions. Explorez, en commençant par la façon dont la variable aggregate est traduite.

Fonctions d’agrégation

Fonctions d’agrégation (par exemple, count, sum, average, max, min) dans les requêtes sous la forme agrégations de mesures ou agrégations de colonnes in Commerce Intelligence. Le facteur de différenciation est de savoir si une jointure est nécessaire pour effectuer l’agrégation.

Consultez un exemple pour chacun des cas ci-dessus.

Agrégation des mesures

Une mesure est requise lors de l’agrégation within a single table. Par exemple, la variable SUM(b) la fonction d’agrégat de la requête ci-dessus serait probablement représentée par une mesure qui additionne les colonnes B.

Examinez un exemple spécifique de la manière dont une Total Revenue peut être définie dans Commerce Intelligence. Examinez la requête ci-dessous que vous tentez de traduire :
SELECT
SUM(order_total) as "Total Revenue" Metric operation (colonne)
FROM orders Metric source table
WHERE
email NOT LIKE '%@magento.com' Mesure filter
AND created_at < X

AND created_at >= Y		 Mesure timestamp (et création de rapports time range)

Accédez au créateur de mesures en cliquant sur Manage Data > ​ Mesures ​> Créer une mesure, vous devez d’abord sélectionner la source qui, dans ce cas, correspond à la variable orders table. La mesure est ensuite configurée comme illustré ci-dessous :

Agrégation des mesures

Agrégation des colonnes

Une colonne calculée est requise lors de l’agrégation d’une colonne qui est associée à partir d’un autre tableau. Par exemple, une colonne peut être créée dans votre customer table appelée Customer LTV, qui additionne la valeur totale de toutes les commandes associées à ce client dans la variable orders table.

La requête de cette agrégation peut ressembler à ce qui suit :
Select
c.customer_id Propriétaire de l’agrégat
SUM(o.order_total) as "Customer LTV" Opération agrégée(colonne)
FROM customers c Table du propriétaire agrégé
JOIN orders o Table source d'agrégation
ON c.customer_id = o.customer_id Chemin
WHERE o.status = 'success' Filtre agrégé

Configuration de Commerce Intelligence nécessite l’utilisation de votre gestionnaire de Data Warehouse, où vous créez un chemin entre vos orders et customers puis créer une colonne appelée Customer LTV dans la table de votre client.

Découvrez comment établir un nouveau chemin entre les customers et orders. L’objectif final est de créer une colonne agrégée dans la variable customers , accédez donc d’abord à la customers dans votre Data Warehouse, puis cliquez sur Create a Column > ​ Sélection d’une définition ​> SUM.

Vous devez ensuite sélectionner la table source. S’il existe un chemin d’accès à votre orders , sélectionnez-la simplement dans la liste déroulante. Cependant, si vous créez un chemin, cliquez sur Create new path et l’écran ci-dessous s’affiche :

Créer un chemin

Ici, vous devez examiner attentivement la relation entre les deux tables auxquelles vous essayez de vous joindre. Dans ce cas, il existe des Many commandes associées à One , par conséquent la variable orders est répertoriée dans la Many , tandis que la variable customers sélectionnée sur la One côté.

REMARQUE

Dans Commerce Intelligence, un path équivaut à un Join dans SQL.

Une fois le chemin enregistré, vous pouvez créer la variable Customer LTV colonne ! Voir ci-dessous :

Maintenant que vous avez construit le nouveau Customer LTV dans votre customers , vous êtes prêt à créer une agrégation des mesures en utilisant cette colonne (par exemple, pour trouver la moyenne LTV par client). Vous pouvez également group by ou filter par la colonne calculée d’un rapport en utilisant des mesures existantes créées sur la variable customers table.

REMARQUE

Pour ce dernier, chaque fois que vous créez une colonne calculée, vous devez ajouter la dimension aux mesures existantes ; avant d’être disponible en tant que filter ou group by.

Voir création de colonnes calculées avec votre gestionnaire de Data Warehouse.

Group By clauses

Group By les fonctions des requêtes sont souvent représentées dans Commerce Intelligence comme colonne utilisée pour segmenter ou filtrer un rapport visuel. À titre d’exemple, nous allons revoir la Total Revenue requête que vous avez explorée précédemment, mais segmentez cette fois les recettes par la variable coupon\_code pour mieux comprendre quels coupons génèrent le plus de recettes.

Commencez par la requête ci-dessous :
SELECT coupon_code, Rapport group by
SUM(order_total) as "Total Revenue" Metric operation(colonne)
FROM orders Metric source table
WHERE
email NOT LIKE '%@magento.com' Mesure filter
AND created_at < '2016-12-01'

AND created_at >= '2016-09-01'		 Mesure timestamp (et création de rapports time range)
GROUP BY coupon_code Rapport group by
REMARQUE

La seule différence par rapport à la requête que vous avez lancée précédemment est l’ajout du "coupon_code" comme groupe par ._

En utilisant la même Total Revenue que vous avez créé précédemment, vous êtes maintenant prêt à créer votre rapport des recettes segmentées par code de coupon ! Regardez l’gif ci-dessous qui indique comment configurer ce rapport visuel qui examine les données de septembre à novembre :

Recettes par code de coupon

Formules

Parfois, une requête peut impliquer plusieurs agrégations afin de calculer la relation entre des colonnes distinctes. Par exemple, vous pouvez calculer la valeur de commande moyenne dans une requête de l’une des deux façons suivantes :

  • AVG('order\_total') OU
  • SUM('order\_total')/COUNT('order\_id')

La première méthode impliquerait la création d’une nouvelle mesure qui effectue une moyenne sur la variable order\_total colonne . Toutefois, la dernière méthode peut être créée directement dans le créateur de rapports en supposant que des mesures soient déjà configurées pour calculer la variable Total Revenue et Number of orders.

Revenez en arrière et examinez la requête globale pour Average order value:
SELECT
SUM(order_total) as "Total Revenue" Mesure operation (colonne)
COUNT(order_id) as "Number of orders" Mesure operation (colonne)
SUM(order_total)/COUNT(order_id) as "Average order value" Mesure operation (colonne) / Opération de mesure(colonne)
FROM orders Mesure source table
WHERE
email NOT LIKE '%@magento.com' Mesure filter
AND created_at < '2016-12-01'

AND created_at >= '2016-09-01'		 Horodatage des mesures (et période des rapports)

Maintenant, supposons que des mesures sont déjà configurées pour calculer la variable Total Revenue et Number of orders. Comme ces mesures existent, vous pouvez simplement ouvrir la variable Report Builder et créer un calcul à la demande à l’aide du Formula fonctionnalité :

Forumula de AOV

Remplissage

Si vous êtes un utilisateur SQL lourd, pensez à la façon dont les requêtes se traduisent en Commerce Intelligence vous permet de créer des colonnes, des mesures et des rapports calculés.

Pour une référence rapide, consultez le tableau ci-dessous. Ceci affiche l’équivalent d’une clause SQL Commerce Intelligence et la façon dont il peut mapper à plusieurs éléments, selon la manière dont il est utilisé dans la requête.

Éléments d’intelligence de commerce

SQL Clause Metric Filter Report group by Report time frame Path Calculated column inputs Source table
SELECT X - X - - X -
FROM - - - - - - X
WHERE - X - - - - -
WHERE (avec des éléments temporels) - - - X - - -
JOIN...ON - X - - X X -
GROUP BY - - X - - - -
Page précédente
Next page

Sur cette page