Our web access logs capture the product IDs (the item field in the logs) that the users view and add to their shopping carts. Understanding the top products that people view can help influence our sales and marketing strategy, and even product direction. Products viewed on an e-commerce website might not always necessarily translate into sales of that product though.
In this recipe, we will write a Splunk search to chart the top 10 products that users successfully view and compare against the number of successful shopping cart additions for each product. For example, if a product has a high number of views but the product is not added to carts and subsequently purchased, this could indicate that something is not right—perhaps the pricing of the product is too high.
To step through this recipe, you will need a running Splunk Enterprise server, with the sample data loaded from Chapter 1, Play Time – Getting Data In. You should be familiar with the Splunk search bar and the time range picker.
Follow the given steps to search for the top products being searched over the past week:
- Log in to your Splunk server.
- Select the Search & Reporting application.
- Ensure that the time range picker is set to Last 7 days and type the following search into the Splunk search bar. Then, click on Search or hit Enter.
index=main sourcetype=access_combined uri_path="/viewItem" OR uri_path="/addItem" status=200 | dedup JSESSIONID uri_path item | chart count(eval(uri_path="/viewItem")) AS view, count(eval(uri_path="/addItem")) AS add by item | sort – view | head 10
- Splunk will return a tabulated list of items (products), detailing the number of times a product was successfully viewed versus the number of times that product was actually added to the shopping cart.
- Save this search by clicking on Save As and then on Report. Give the report the name
cp02_top_products_viewedand click on Save. On the next screen, click on Continue Editing to return to the search.
In this recipe, our search returned a count by item of how many items were viewed versus how many were added to the cart. In this case, the item field represents a unique item ID that pertains to a specific product. Let's break down the search piece by piece:
|
Search fragment |
Description |
|---|---|
|
|
You should now be familiar with this search from the earlier recipes in this chapter. |
|
|
Following best practice of making our search as granular as possible, we only search for events that contain |
|
|
Using the |
|
|
Using the |
|
|
Using the |
This recipe provides us with some insight into product views and subsequent shopping cart additions that might then lead on to a sale. However, we can keep adding to the search to make it even easier to understand the relationship between the two.
We can further amend the search from this recipe to evaluate a new column that calculates the percentage of product views added to the cart. We do this using the eval command and some basic math, as follows:
index=main sourcetype=access_combined uri_path="/viewItem" OR uri_path="/addItem" status=200 | dedup JSESSIONID uri_path item | chart count(eval(uri_path="/viewItem")) AS view, count(eval(uri_path="/addItem")) AS add by item | sort – view | head 10 | eval cart_conversion=round(add/view*100)."%"
We firstly evaluate a new field called cart_conversion and then calculate the percentage, dividing purchase by view and multiplying by 100. We use the round function of eval to eliminate the decimal places and then tack on the % character at the end. Now, we can easily see what percentage of views lead to cart additions.