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:
SELECTa,group bySUM(b)Aggregate function (columna)FROM cSource tablaWHEREd IS NOT NULLFilterAND time < XAND time >= Ytime frameGROUP BY agroup byEste 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:
SELECTSUM(order_total) as "Total Revenue"Metric operation (columna)FROM ordersMetric source tablaWHEREemail NOT LIKE '%@magento.com'filterAND created_at < XAND created_at >= Ytimestamp (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:
Selectc.customer_idSUM(o.order_total) as "Customer LTV"FROM customers cJOIN orders oON c.customer_id = o.customer_idWHERE 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 bySUM(order_total) as "Total Revenue"Metric operation(columna)FROM ordersMetric source tablaWHEREemail NOT LIKE '%@magento.com'filterAND created_at < '2016-12-01'AND created_at >= '2016-09-01'timestamp (y sistema de informes time range)GROUP BY coupon_codegroup byCon 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:
SELECTSUM(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 orderssourceWHEREemail NOT LIKE '%@magento.com'filterAND 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 ClauseMetricFilterReport group byReport time framePathCalculated column inputsSource tableSELECTFROMWHEREWHERE (con elementos de tiempo)JOIN...ONGROUP BY