If you've ever used Pivot tables in Excel, you must have encountered these core concepts:
- Grouping (rows & columns)
- Value fields
The basic functionality when analyzing product data in Sniffie is very similar to Excel. This article aims at describing these concepts. If you are already a seasoned analyst, go ahead and skip to our article on creating dashboard analytics in Sniffie. Otherwise, read on.
Let's start by imagining that the product catalog is an Excel sheet, where each row represents one product, and each column represents one field of data for the products: Product name, Category, Website, Price, Brand, etc.
In the form of a spreadsheet, it would look something like this:
Image 1: Our product catalog in the form of a spreadsheet
We have a set of phones and tablets from two competitor sites in addition to our own products. Let's start analyzing our data!
Filtering is the process of selecting product rows with specific properties and ignoring the rest. When creating a filter, we first choose a specific product attribute (column). Next, we set a condition that each row must fulfill. Some examples:
Category must be equal to "Phones"
Product name must contain "phone"
In these examples we would end up with a subset of the product catalog like this:
Image 2: Our product catalog with some rows filtered out
(You can read more about how filters work in Sniffie in our Using Filters article)
Grouping is how we transform our data from individual product rows into analytics. When grouping data, we merge rows with specified similarities. This results in a condensed table with one cell containing the values of multiple cells from our starting point.
We start by choosing a property (a column) we want to group by. As the result, all rows with the exact same value in the specified column will be merged into a single row. We thus end up with a table where all rows have unique values in the specified column. Let's visualize it!
Say we choose Category as our grouping factor. We check the Category column and notice that there are two unique values: Phones (marked with blue) and Tablets (marked with green). In the result, all the blue rows will be merged together, forming a single row containing all the values of those rows. The same goes for the green rows. Thus we end up with a table with two rows.
Image 3: Grouping by Category
It is also possible to group by multiple columns at once. In this case, rows are merged together when they share exact values on all grouping columns. If we expand the previous example with a second grouping factor, say Website, we check unique values in the imaginary Category + Website column. We see that we now have six unique values in our two grouping columns:
- Phones - sniffie.io
- Phones - competitor1.com
- Phones - competitor2.com
- Tablets - sniffie.io
- Tablets - competitor1.com
- Tablets - competitor2.com
Here's what the result would look like:
Image 4: Grouping by Category & website
Choosing values to analyze
Now that we've conducted our filtering and grouping we want to choose what it is that we want to analyze. You might be wondering what happened with the values in the other columns which were to part of the grouping, like Price for instance. In our visualizations above we had several values and now we've lost that data, right? Here's where choosing a value column comes into play. Commonly, the value column will contain numeric data, such as price.
If we look at the example where we grouped rows by Category, (Image 3) the price column contained multiple values and has now been condensed into a single value. We haven't exactly lost the data, it's just that we need to represent all those 12 values in a single cell. We will have to specify a method or function on how this transformation is calculated: a value function.
Some of the most commonly used value functions include
Let's use the example in image 3 again. Let's select our value column: Price. Next, we select a value function used to group all the Price values of the blue and greens rows respectively. Let's go with average. In our result we'll have the average price of all Phones and average price of all tablets. Discarding the other columns in our original dataset, the result would look like so
Image 4: Final result - The average price by category
When the chosen value column is non-numeric, eg. Category, functions such as average and sum are not available. This is because, you can't really calculate the average of "Phones" and "Tablets", right?
When you are ready to start analyzing your product data, check out our article on building dashboard analytics.