Analyze your data without SQL
Users love how customizable and powerful Grow is, but many of you have asked for ways to analyze your data that are less technical. Now you can filter, group, sum, and trim without knowing any SQL.
Here is a brief overview of what each button does, going left-to-right:
1. Remove Columns: This allows you to select which columns you want displayed. This can be particularly helpful when you have dozens of columns and want to focus on two-three at a time.
2. Filter Data: Here you can select a specific column, and filter the information based on the data. Incredibly helpful to show data containing a specific keyword, or remove data that is not within a certain range.
3. Group Data: My personal favorite - you can immediately aggregate grouped data from certain cells within your report. This is great for grouping data by week or month.
4. SUM, Count, Average, Min: this is pretty self explanatory, it will perform these actions based on a column of your choosing.
5. Sort Data: You can sort all of the data from the report based on a specific column. Ascending and Descending by number or alphabet.
Now let's look at how to use each function.
In our example data we have a leads report from Salesforce. For the metric I am creating I only want to worry about the percentage of leads owned by each sales rep. So I want to remove a few columns to make the data easier to look at.
First step is to lock the first row as header. We do this so the data stays clean. You don't want 'Lead Owner' counted as a Sales Rep.
Then click the Remove Column button.
If you are removing so many columns that it would be easier to select the columns you want to keep instead of selecting the columns you want to remove, you can click the 'Select All' button to select or deselect all columns.
Now the only data I have is the Lead Owner which is exactly what I want.
Now in our example, our team lead changed his/her mind and she wants to see what percentage each rep has of enterprise sized leads.
So to easily remove all the transformations and get back to the raw data, you can click the 'Clear All' button. It will warn you that you are about to remove all transformations. Confirm and we are back to the raw data.
Now let's filter out all the Leads that are not enterprise size. You define enterprise as 200 employees and up.
First click the Filter button.
Then click 'Add filter'. Here we can select what Column we want to filter and how we want to filter it.
We pick the No. of Employees column and the operator of 'Greater than or equal to' and set the value to '200'. You will see the data update in the background. Click 'Done' to move on.
We now have a list of all the leads with more than 200 employees. But we still need to calculate the percentage each sales rep owns. This is easy with the Group Data tool.
First click 'Group Data'.
We need to select which column we want to group by and if we want to aggregate the data. Let's select Lead Owner as the column we want to group by and that we want to Count the aggregated data.
We now have how many leads of enterprise size that are owned by each sales rep.
Sum, Count, Avg functions
We now have how many leads each rep owns, but we need a total. You could calculate this total using a spreadsheet function, but the slice and dice tools make it super easy. Click the 'Sum, Count, Av' function button.
Click 'Add aggregate' and select type of function, and the column we want to run the function on. Let's select 'Sum' and the 'Count' column. We now have the total number of leads, as well as the number of leads assigned to each sales rep, so we can calculate the percentages.
Now let's sort the data from highest to lowest, or descending. Click the Sort button.
Let's pick the column of Count to sort, and chose 'Descending Order'. We can now clearly see that Austin has the highest number of leads in the enterprise category.
We all know that data isn't magic, but Grow does try to make it easy on you. If you select a Pie or Donut chart, Grow will calculate the percentages for you!
Select the Pie Chart type and then add a Data Series of Column B.
And bada bing bada boom, your pie chart is created. We can easily see that the top rep owns 36% of all the enterprise size leads.
If that is all you needed, we are done, without having touched any SQL.
But let's say that we want to see what percentage each rep owns without having to mouse over so we can put this metric on a TV on the wall.
We can add in the legend with a few clicks. Under the Data Series options, check the 'Show Legend When Metric is Expanded'.
Then click 'Show Percentage in Legend'
This will show the legend and the percentages. One last step to add the Rep Names to the Legend, click 'Add Chart Labels' under Axis Labels and select Column A.
We are done! Enter a metric title, Click Save, and size the metric how you would like on your dashboard.
If you need to use Master SQL or SQL with the Slice and Dice tools more info can be found here.
And as always, if you have any questions please email email@example.com or chat in on the site.