Commerce Intelligence - Filter Sets

Transcript

Hi, I’m Erin, and I’m a Sales Engineer here at RJMetrics. Are there only certain aspects of your data that you want to analyze? Did you find that prior to RJMetrics you were deleting or cleaning data from your Excel files? Well, RJMetrics allows you to simplify this process through filter sets. In this video, we’re going to create a filter set and apply it together. It’s important to note that you need admin permission to edit and create filter sets, but if you don’t have that permission, it’s okay. I still recommend following along to learn about this feature. Okay, let’s get started.

For example, let’s say that I want to build a filter set to make sure I’m not tracking any customers who work at RJMetrics. Basically, I want to exclude anyone with an RJMetrics email address domain. First, you’ll want to open up your Filter Sets page. You can do this by navigating to Data, Filter Sets.

Here you can see all filter sets in your account. Filter sets are global to your RJMetrics account which means changes will impact all users. Any admin user will be able to view this page and can add or edit a filter set.

You can start a new filter set by clicking Add Filter Set or you can edit an existing filter set by selecting Edit next to the name of that filter set. Our team generally adds a blank filter set for some of the tables we see filtered most commonly, such as orders we count and users we count. They also add that filter set to any applicable metrics and calculated columns they build during the onboarding project to make it easy for you to apply your business logic. For example, our team would create a blank filter set for orders we count on the orders table and add that filter to any metrics built off the orders table, such as number of orders, revenue, and average order value. If you start looking at the data and realize that you only want to include orders where the status is equal to complete, you could update the orders we count filter set to include status equals complete. Because this filter set was added to the number of orders, revenue, and average order value metrics, those metrics will instantly update to reflect this change.

Here you can see that there are filter sets for users we count, order items we count, and orders we count. If we click on Show Details we can see information about that filter set. For example, on the orders we count filter set, we can see that there’s a filter added for pizza oven pants order equals zero. This means that we are filtering so that we only see orders where the column pizza oven pants ordered is equal to zero. If we look at users we count, we can see that nothing has been added to this filter set yet. Okay, so back to the example we wanted to work through. I want to make sure that I’m not counting customers who have an RJMetrics domain email address. I could do this by building a new filter set and clicking the button here.

But since we already have a users we count filter set, I’m going to edit that filter set to include this logic. We can see that this filter set is built on the customer’s table. It’s titled users we count and does not currently include any filters. We’re going to want to add a filter to indicate that we do not want to include any customers with RJMetrics.com in their email address. Before we build this filter, it’s important to understand the different filter operators. If you know, SQL, these should look familiar. If you don’t, don’t worry because we’re going to walk through them now. You have your standard operators, less than, greater than, less than or equal to, greater than or equal to, equal and not equal to. These are fairly self explanatory but you also have a few other operators to choose from. LIKE is pattern matching. It is designed to look for and match with a specific value or data set. It must be used in conjunction with the wild card character’s percent for a wild card with a variable number of letters or underscore for a wild card, single letter.

A wild card character simply indicates that anything could appear in that location.

For example the restriction LIKE_ake% means to return anything that matches any single letter AKE any series of letters following. This would return true for Jake Stein, Jake Smith, or Fake Smith. It would return false for Drake Smith because underscore only allows for one variable letter.

LIKE%ake% would return true for all of these options.

One other thing to note is that filter sets are not case sensitive. So the above would remain true for Jake Stein or Jake Stein if it was all capitalized.

NOT LIKE is similar to pattern matching above but checks for which patterns don’t match. IS checks if the column is NULL or empty. IS NOT as similar to the IS operator above, but checks for non-NULL columns. So columns that are not NULL or not empty.

IN checks for the value’s presence in a comma separated list. Example, color IN red, orange is the equivalent of color equal to red or color equal to orange.

NOT IN is similar to IN above, but checks for a value’s absence. Okay, so now that we understand filter operators, we can add a filter to exclude customers whose email address contains RJMetrics.com. We would do this by adding a filter for A email, NOT LIKE % RJMetrics.com To this filter set. Reminder, the percentage wild card can be used to indicate that any values could come before RJMetrics.com, as long as it ends in RJMetrics.com, it will be filtered out. Once you’ve added filters to the filter set, you can verify your filter logic and that parentheses and operators are placed correctly. I want to emphasize that this step is extremely important. An incorrect filter is often the cause of data discrepancies between RJMetrics reports and your expected results. Last save the filter set.

When logic is altered saving the filter set will apply the changes to all metrics and calculated columns. It will often take an update cycle for some changes to take effect. You can also apply it to any metric that is already built on the same table or any new metrics you build on that table. For example, a filter set built on the customer’s table can be applied to any metrics built on the customer’s table such as number of users. Let’s walk through how to do this now. We can navigate to the Metrics page, edit the number of users metric, scroll down to the Filters area, select Add to add the users we count filter set.

Using a similar process, you can also apply filter sets to calculated data dimensions in your data warehouse. For example, for customer’s lifetime revenue if you want to exclude canceled or returned orders, you can add your orders we count filter set, so that only completed order values are used. That’s all for filter sets today. True review, we learned how to define and build filter sets using filter logic and how to apply them to calculated columns and metrics to ensure that you always have relevant data. If you want to learn more about filter sets feel free to check out our Help Center for additional content. Bye. -