Customizing Views

In this tutorial we're going to look at how to start customizing a catalog in Countmetrics. When you first generate a catalog you'll usually begin with a set of auto generated views. These are used as a foundation but they don't always capture the exact insights you need. That's where customization comes in. We'll start by working with views since they're the building blocks of any catalog. Let's begin by building a new view in a canvas. To create a new view from a canvas, you can either create a new canvas or you can work from an existing one. Let's open the existing canvas here. I have artist level data and track level streaming data on my connected data source. I'd like to build a view that shows the top track for each artist alongside that artist level data. To do that, I'm going to use a SQL cell to rank each track by total streams per artist, and then I'll join that result to the artist table while filtering only on the top track. Now I'm going to create a new cell which will join the result from this query to the Spotify artist table bringing that top track per artist. Let's turn this cell into a view. When you create a view from a cell, you will see only the fields in the cell that you generate the view from, but that doesn't mean that it won't bring those dependencies with it. So when we generate the view on this cell, this dependency will still be carried into our catalog view. It just won't form part of the fields. So let's export the view. Our final query has been exported into a view. The system has captured all the upstream dependencies and each cell's connection information is preserved. This gives me a solid starting view ready for further customization. First of all, let's improve the usability of the view. Let's give it a user friendly label. I'm going to give it a short description. When you add a description to a view or a dataset, this will appear on the home page of your catalog. I'm going to add a short description. This will appear in the user interface of your catalog home page. For the fields, we can also add labels and descriptions. Clear labels are really important here because they help my end users understand exactly what each field is for. The descriptions will show up in your catalog home page, but they'll also show up in the canvas UIs, so that's a really nice way to give a little bit of extra context around those fields. Now that I've added my labels and descriptions, I want to declare one of my fields a primary key. In this case I'll use artist ideas. I know that this is unique. The other thing I wanted to do is to actually hide this field. Setting hidden to true means that the field will disappear from the interface but it's still available for me when I want to do joins on this view in my dataset. Now that we've made some high level changes, let's validate and commit this view before we continue. When views have many fields, it helps to cluster them into logical groups for better navigation. In the UI, these groups appear as expandable sections making it simple for users to locate the fields they need. Aggregates and time frames allow end users to quickly roll up data without writing SQL. Let's start looking at our fields that we want to apply aggregates to. I'm going to start with this artist name. Generally, aggregates are something that you would only want to use on numeric fields. However, I do want to include an ability to count artists or to count distinct artists. You can apply aggregates using the aggregates keyword, and then it's just a list. If you're looking to find the list of all of the possible options that you can have in aggregates, all you need to do is turn to this schema on the right hand side. And scrolling down, you'll find the aggregate section here. For this example, I'm just going to use count, count distinct, and then I'm also going to include null, which basically means that my end users can select no function or raw value only. Let's move down through the numeric fields now and apply a full set of aggregates. The quickest way to do this is to copy the list from the schema on the right and then paste it inside the square brackets of your object on the left. Now you may not want to include every single option. In the case of popularity, I'm going to remove my standard deviation and my variance just because I don't have a use case for them at the moment. We can always update this catalog later to bring in additional functions if needed. I'm now going to take this list of aggregates as my standard core set and I'm going to apply it to every numeric field in my view. Let's add a time frame. So I only have one date field in my view here. You can see on the right in the schema here, right under aggregates is my list of time frames. Adding time frames is almost identical to adding aggregates. So all you do is add a new attribute in here called time frames, and then you can take what you want from the right hand side here. All of these trunk functions or these truncates are essentially rounding down to the beginning of that period. The remaining functions will actually do an extraction. As with the aggregates, null is the raw value so this allows you to apply no function. For my use case I'm going to bring all the truncate functions in. I'm not going to bring week truncate. I don't have any time on this one that I'm interested in, so I'm not going to bring that. But I am going to bring in week instead of week truncate. So this will give me the week number between one and fifty two. And I'm also going to bring in day of the week so I can get the the weekday Monday to Sunday. I always like to bring in the null value just so I can have that raw field if I want it. So both of these aggregates and time frames are going to appear as expandable drop downs in the UI when my end users are interacting with the catalog in their canvases. Calculated fields allow you to create new fields derived from existing data without writing separate SQL queries. For example, let's create a field called follower density to calculate the total followers per popularity point. To create a new field, you just need to add an additional element to this array. I've just copied the last one onto a new line and we can edit them in place. Let's give our new field a name. Now let's give it a user friendly label. And finally I'm going to give it a description to provide my end users with more context. For the data type, because I'm dividing followers by popularity point, I want to be able to have decimals. So I'm going to change this from string to number. Now I'm going to add my expression. This is where you can actually add the calculation for your calculated field. Expressions are written in a SQL like language. It will be really familiar to anyone that's created calculated fields on visualizations. For more information on that dialect, you can go into the schema and you'll see the link out to our docs here. Let's calculate the total followers per popularity point. Let's calculate the follower density. Notice that I didn't use the user friendly labels of these fields. I've used the names. This is important as the system will work with the names while your user friendly labels are for your UI in the canvas. Now let's update the group. And finally, I'm going to add some aggregates to this numeric field. I'll use my standard set that I defined for my other numeric fields here. Let's validate and commit this view at this point. Now let's apply some formatting. So formatting defines how your data appears in the canvas. Let's apply some field formatting. So formatting defines how your data is going to appear in the canvas. There are lots of formatting options and they're all detailed in the schema on the right hand side. You can format numeric values, you can also format dates. So for my date field updated at I'm going to apply my formatting. Now the format forms a group within your list element. First, let's apply the type. I now want to define the format of each element within my date. In the right hand side, you can see the different options of what you can format within the date. So I can format year, and I'm going to pick numeric. The numeric option means that it will return the year as a four digit integer. Two digit would return just the last two digits of the year. I'm also going to format my month, and I'm going to format it as long. Long will return the full name of the month. Let's format the weekday. Weekday would also like to return as long. This will bring the full weekday. And then finally, let's format day. I'm going to go for numeric, which means that there will be no zero padding. I also want to show you currency formatting, but we don't actually have a currency field on this view, so let's create a mock one. Now we can apply the formatting. For currency fields, the type must be currency. We can then specify what currency. It makes sense to define how many decimal places you want to allow. We can also define units. So for values which are over a thousand, the last thing I want to do is give it its own distinct group so I can tell it apart from the other fields. And let's validate and commit this view again. The last thing I want to update in this view is the caching. Caching improves performance by storing results of heavy queries. Each view can define a duration for how long the data is cached and a schedule for when it refreshes. Let's add some caching now and we'll start by defining the duration. Duration is defined in seconds. You can set it as short as you want or as long as you want. Setting the duration to eighty six thousand four hundred means that this cache will sit on the server for twenty four hours before it's deleted or released. Setting up a schedule means you can control when your data is refreshed. Schedule and duration are independent of each other, but you can configure them with coinciding time ranges so that they link up nicely. I'm going to set a schedule so that my data refreshes every single day at the same time. Schedules are configured using cron expressions. If you have a look in our schema on the right side here, you'll see a link out to more information about how to define a cron expression. Essentially, a chronic expression is made up of five characters. If you hover under the three dots at the beginning of the chronic expression, you'll see an example of the next five iterations of this schedule. The other thing to bear in mind is that these chronic expressions are set to UTC. I would like this to run every morning at six AM, and I'll just double check that's correct. So that's bringing back my, five AM UTC, which is showing as six AM in my time zone every single day. So that's perfect. The very final thing I just wanted to draw your attention to is commenting. You can add a comment using the hash key, and that's pretty much it. Let's commit this final version of the view. So now we've customised our views, let's head back to the home page and see how everything looks. So you can see that we have our new view that we created right at the beginning of this video and the description that we wrote in there appears inside the rectangle here which is really cool. If we click into the view you'll notice that we have all of the descriptions that we created and we also have the data types and the names of the fields as well as the label. You can see our view description appears up top here. I can see my updated metrics on the right showing I now have four views, and I can see the canvas with the cells that I've generated views from. This is already added to my project, the customer success training lab, so we're actually ready now to go and explore the catalog for real in a canvas.