Traducción de consultas SQL en Commerce Intelligence
¿Alguna vez se ha preguntado cómo se traducen las consultas SQL en las columnas calculadas, métricas e informes que usa en Commerce Intelligence? Si usa mucho SQL, comprender cómo se traduce SQL en Commerce Intelligence le permite trabajar de forma más inteligente en el Administrador de Datas Warehouse y aprovechar al máximo la plataforma Commerce Intelligence.
Al final de este tema, se encuentra una matriz de traducción para cláusulas de consulta SQL y Commerce Intelligence elementos.
Comience por ver una consulta general:
SELECT
a,
group by
SUM(b)
Aggregate function
(columna)FROM c
Source
tablaWHERE
d IS NOT NULL
Filter
AND time < X
AND time >= Y
time frame
GROUP BY a
group by
Este ejemplo abarca la mayoría de los casos de traducción, pero hay algunas excepciones. Sumérjase en, empezando por cómo se traduce la función aggregate
.
Funciones agregadas
Las funciones agregadas (por ejemplo, count
, sum
, average
, max
, min
) en las consultas toman la forma de agregaciones de métricas o agregaciones de columnas en Commerce Intelligence. El factor diferenciador es si se requiere una unión para realizar la agregación.
Observe un ejemplo para cada uno de los elementos anteriores.
Agregaciones de métricas aggregate
Se requiere una métrica al agregar within a single table
. Por ejemplo, es muy probable que la función de agregado SUM(b)
de la consulta anterior esté representada por una métrica que suma la columna B
.
Observe un ejemplo específico de cómo se puede definir una métrica de Total Revenue
en Commerce Intelligence. Observe la consulta siguiente que intenta traducir:
SELECT
SUM(order_total) as "Total Revenue"
Metric operation
(columna)FROM orders
Metric source
tablaWHERE
email NOT LIKE '%@magento.com'
filter
AND created_at < X
AND created_at >= Y
timestamp
(y sistema de informes time range
)Vaya al generador de métricas haciendo clic en Manage Data > Métricas > Crear nueva métrica; primero debe seleccionar la tabla source
adecuada, que en este caso es la tabla orders
. A continuación, la métrica se configuraría como se muestra a continuación:
Agregaciones de columnas
Se requiere una columna calculada al agregar una columna unida desde otra tabla. Por ejemplo, puede tener una columna creada en la tabla customer
llamada Customer LTV
, que suma el valor total de todos los pedidos asociados con ese cliente en la tabla orders
.
La consulta de esta agregación puede tener un aspecto similar al siguiente:
Select
c.customer_id
SUM(o.order_total) as "Customer LTV"
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.status = 'success'
La configuración de esto en Commerce Intelligence requiere el uso del administrador de Datas Warehouse, donde se crea una ruta de acceso entre la tabla orders
y customers
y, a continuación, se crea una columna denominada Customer LTV
en la tabla del cliente.
Observe cómo establecer una nueva ruta de acceso entre customers
y orders
. El objetivo final es crear una nueva columna agregada en la tabla customers
, así que primero vaya a la tabla customers
de la Data Warehouse y, a continuación, haga clic en Create a Column > Seleccionar una definición > SUMA.
A continuación, debe seleccionar la tabla de origen. Si existe una ruta de acceso a la tabla orders
, simplemente selecciónela en la lista desplegable. Sin embargo, si está creando una nueva ruta, haga clic en Create new path y aparecerá la siguiente pantalla:
Aquí debe considerar cuidadosamente la relación entre las dos tablas que está intentando unir. En este caso, hay potencialmente Many
pedidos asociados con One
cliente, por lo tanto la tabla orders
se muestra en el lado Many
, mientras que la tabla customers
está seleccionada en el lado One
.
path
equivale a un Join
en SQL.Una vez guardada la ruta, puede crear la columna Customer LTV
. Consulte lo siguiente:
Ahora que ha creado la nueva columna Customer LTV
en su tabla customers
, está listo para crear una agregación de métrica con esta columna (por ejemplo, para encontrar el LTV promedio por cliente). También puede group by
o filter
por la columna calculada en un informe usando las métricas existentes creadas en la tabla customers
.
filter
o group by
.Ver crear columnas calculadas con el Administrador de Datas Warehouse.
Group By
cláusulas
Las funciones de Group By
en las consultas se representan a menudo en Commerce Intelligence como una columna utilizada para segmentar o filtrar un informe visual. Por ejemplo, vamos a revisar la consulta Total Revenue
que exploró anteriormente, pero esta vez segmentemos los ingresos por el coupon\_code
para comprender mejor qué cupones generan la mayor cantidad de ingresos.
Comience con la siguiente consulta:
SELECT coupon_code,
group by
SUM(order_total) as "Total Revenue"
Metric operation
(columna)FROM orders
Metric source
tablaWHERE
email NOT LIKE '%@magento.com'
filter
AND created_at < '2016-12-01'
AND created_at >= '2016-09-01'
timestamp
(y sistema de informes time range
)GROUP BY coupon_code
group by
Con la misma métrica Total Revenue
que creó anteriormente, ya puede crear su informe de ingresos segmentado por código de cupón. Observe el gif que aparece a continuación y que muestra cómo configurar este informe visual con los datos de septiembre a noviembre:
Fórmulas
A veces, una consulta puede implicar varias agregaciones para calcular la relación entre columnas independientes. Por ejemplo, puede calcular el valor de pedido promedio en una consulta de una de las dos maneras siguientes:
AVG('order\_total')
OSUM('order\_total')/COUNT('order\_id')
El método anterior implicaría la creación de una nueva métrica que realice un promedio en la columna order\_total
. Sin embargo, este último método se podría crear directamente en el Report Builder, suponiendo que ya se hayan configurado las métricas para calcular Total Revenue
y Number of orders
.
Retroceda un paso y observe la consulta general de Average order value
:
SELECT
SUM(order_total) as "Total Revenue"
operation
(columna)COUNT(order_id) as "Number of orders"
operation
(columna)SUM(order_total)/COUNT(order_id) as "Average order value"
operation
(columna) / Operación de métrica (columna)FROM orders
source
WHERE
email NOT LIKE '%@magento.com'
filter
AND created_at < '2016-12-01'
AND created_at >= '2016-09-01'
Ahora suponga que ya tiene métricas configuradas para calcular Total Revenue
y Number of orders
. Dado que estas métricas existen, simplemente puede abrir Report Builder
y crear un cálculo bajo demanda utilizando la característica Formula
:
Ajuste
Si es un usuario de SQL con mucho peso, pensar en cómo se traducen las consultas en Commerce Intelligence le permite generar columnas calculadas, métricas e informes.
Para obtener una referencia rápida, consulte la siguiente matriz. Esto muestra el elemento Commerce Intelligence equivalente de una cláusula SQL y cómo se puede asignar a más de un elemento, dependiendo de cómo se utilice en la consulta.
Elementos de Commerce Intelligence
SQL Clause
Metric
Filter
Report group by
Report time frame
Path
Calculated column inputs
Source table
SELECT
FROM
WHERE
WHERE
(con elementos de tiempo)JOIN...ON
GROUP BY