Kyligence Zen Solves the Count Distinct Issue with Zero Coding Skills Required
For online stores, a vast variety of SKU categories will significantly increase your business’s appeal to users, however, it may also bring a lengthy purchase funnel.
For instance, before placing an order, users may browse the same product repeatedly, continuously add and remove the same product from their shopping cart, or order the same products in several different orders. Since one row of records will be generated for each action, given the customer traffic of an online store, a considerable amount of data can be involved.
How to quickly screen out hot-selling products from these seemingly random user behaviors and calculate the “payment conversion rate” of these products lie at the heart of the merchants. After all, conversion rate is the cornerstone of everything.
In this blog, we will show how to quickly perform COUNT_DISTINCT analytics using Kyligence Zen’s graphical interface. The dataset to be used today contains 284,284 rows of data (the data used in this blog was integrated based on the user behavior dataset of Michael Kechinov).
Each user action (such as browsing, adding to a shopping cart, and purchasing) corresponds to one row of data. Therefore, if we want to quickly identify the hot selling products of the online store and their payment conversion rate, we need to perform a COUNT_DISTINCT operation.
Terminology:
COUNT is a commonly used data analytics function to query the number of column values.
COUNT_DISTINCT is to remove duplicate values on this basis. It is usually used for data analytics scenarios such as sales analytics and website UV statistics.
With Kyligence Zen’s graphical interface, we can run COUNT_DISTINCT with no need for coding skills. Upon completion of data import, click + NEW on the Metrics page. Given the simple counting operation we want to perform, we will select Basic Metric and enter Hot Products as the metrics name. In the Metric Definition section, set the Column of Metrics Value to product_id and set the Aggregation Type to COUNT_DISTINCT. Kyligence Zen will perform COUNT_DISTINCT on the product ID (product_id) to identify the products that users are actually interested in. We will then set the data format and the dimensions we want to analyze. THen select Present All Data as How to Present Metric Value, which finished creating the Popular Product metric.
As can be seen from the figure, among the over 280,000 data records, 20,673 products are browsed by the users. The number of products reached our customers peaked on November 18. The reason for this is most likely to prepare for the upcoming Black Friday sale. The number of products reached our users dropped on Black Friday. We believe this is because after pre-screening, users have already determined the products they want to buy. In future online store marketing campaigns, we shall consider making full use of the golden period, that is, the two weeks before big promotion campaigns and to expose as many products as possible.
As mentioned earlier, payment conversion lies at the heart of merchants. Next we will show you how to use Kyligence Zen to calculate payment conversion for these products.
We will continue to create several metrics to track the conversion rate of users in each shopping stage. To do this, we need to create two fine-grained metrics, namely, the Add to Cart metric (i.e., the number of times a user added something to his/her cart) and the Products Sold metric (i.e., the total amount of products that have been sold over a given period). After that, we will use these metrics to calculate the conversion rate, and identify the weak link of customer conversion.
We will continue to use the graphical interface of Kyligence Zen to create these two basic metrics. To perform COUNT_DISTINC based on product ID product_id, and filter the event_type of cart and purchase in the event type by using the filter function of Kyligence Zen, i.e., calculating the number of times a user added something to his/her cart or the total amount of products that have been sold over a given period.
As can be observed that of the 20,673 products that actually reached users, 3,634 were added to their cart, and 2,360 were ultimately purchased. Preliminary analytics shows that when a user adds a product to his/her cart, the likelihood of final payment is much higher than that of users who browse the products and then add them to his/her cart. Thus, if the conversion rate from user browsing to cart addition can be improved, the final payment conversion will be significantly increased.
We can continue to leverage Kyligence Zen’s interface to create composite metrics and accurately calculate the conversion rate of these two stages. On the Metrics tab, click + NEW and select Composite Metric. If we divide the Add to Cart by Hot products, we can get the Cart Conversion Rate for those popular products. If we divide the Products Sold by Add to Cart, we can get the Payment Conversion Rate.
Like the previous analytics, only 17.58% of the products viewed by users were added to their cart. But 64.94% of the products added to cart were paid for. It is clear that increasing the enthusiasm of users to add a product to their cart is the key to rapidly improving the final payment conversion.
So far, we have completed the COUNT_DISTINCT with the graphical interface of Kyligence Zen, and accurately identified popular products accordingly. We then analyzed the metrics from browsing to cart addition and then purchase, thus completing the tracking of the “Payment Conversion Rate”, the core metric for online store.
Kyligence Zen is open for trial at the moment, welcome to visit Kyligence Zen and start your own data expenditure.
