Simple Guide to Do Arithmetic on Metrics with Kylignece Zen
It is clear that it is insufficient just to have max, min, count, and sum when trying to do data analysis.
For instance, as an online e-commerce retailer with a large variety of products, I want to keep abreast of the average basket size, i.e., the average number of items per transaction. This is the key to our growth in average transaction value. Do we have to start with a long string of code to perform this analysis?
SELECT * FROM (SELECT COALESCE("t1"."ORDER_DATE") "order_date", "t1"."TOTAL ORDER QUANTITY" / "t2"."TOTAL TRANSACTIONS" "AVERAGE BASKET SIZE"
FROM (SELECT "order_date" "ORDER_DATE", SUM("order_quantity") "TOTAL ORDER QUANTITY"
FROM "default_schema"."retail_stores_by_transaction"
GROUP BY "order_date"
ORDER BY "order_date") "t1"
INNER JOIN (SELECT "order_date" "ORDER_DATE", COUNT(DISTINCT "order_id") "TOTAL TRANSACTIONS"
FROM "default_schema"."retail_stores_by_transaction"
GROUP BY "order_date"
ORDER BY "order_date") "t2" ON "t1"."ORDER_DATE" = "t2"."ORDER_DATE"
ORDER BY COALESCE("t1"."ORDER_DATE")
LIMIT 1000) AS retail_stores_by_transactionAre you starting to get a headache? Actually, we can perform various advanced metrics calculations effortlessly with the graphical interface of Kyligence Zen. In this blog, we will show you how to use Kyligence Zen to create composite metrics for various complex metric calculations.
The datasets and templates used in this article are now available on the Kyligence Zen Metrics template marketplace. Click to try the Metrics for New Retail ABS Analysis.
Calculation logic:
Average Basket Size = Total Transactions/Total Order Quantity
Therefore, we will create these two metrics first. On the Metrics page, click the + NEW button in the middle of the page, and then input the metric name. Given the fact that we are calculating the net profit and the total number of items sold, select order_quantity for Column and SUM in the Aggregation Type. We can select all dimensions for Dimension, and then click Create to complete the creation of the metric of Total Transactions.
We can use similar steps to complete the creation of the Total Order Quantity metric. We will calculate the total number of items of all transactions, which will be obtained by a count_distinct operation on the order ID. The detailed settings are as follows:
On the Metrics page, click to select Composite Metrics. In the Metrics Information section, enter the metrics name. We can enter the metric definition and its calculation methodology in the Description section to facilitate the alignment of the metric definition between different teams at a later time.
In the Metric Definition section, click Edit in the Expression dialog. You can filter the metrics based on the data source. Then you can select the required metrics in the metric list below it, and select the required operator on the right side. The detailed operation is shown below. Upon completion of setup, Kyligence Zen will automatically add dimensions based on the shared dimensions of the given metrics.
So far, we have completed the creation of the Average Basket Size metric. The calculation results have shown that the overall average basket size of the retailer is 2.29. But how is the current work performance of the mall perceived from this data? How is the current performance of the mall? According to the industry standard, the development of average basket size can be divided into three stages:
- Natural sales (1.2–2.0), more than 60% of the UPTs (units per transaction) are 1 unit per transaction, also known as “sell in one SKU”
- Medium level (2.0–3.5), more than 60% of the UPTs are 2–5 units per transaction, also known as “sell in one set”;
- Excellent level (3.5 and above), more than 60% of the UPTs are 3–8 units per transaction, which is called “sell in piles”.
From this point of view, the company’s current average basket size is OK. It is in the early stages of moving from natural sales toward the medium level. With the diversion of sales channels and the increasing of competition, we shall actively increase their average basket size by means of cross-selling and upselling, offer free shipping, product bundling, leverage store layout, reduce stockouts and personalize recommendations.
The datasets and templates used in this article are now available on the Kyligence Zen Metrics template marketplace. Click to try the Metrics for New Retail ABS Analysis and start your data journey today!
