The Group function in the Analytics Edge add-in lets you easily group or categorize data. It evaluates the values (text, numbers or dates) in a selected column, and adds a new column with group or category labels. The simple wizard interface gets the job done in seconds.
Using the Group Function
Once you have loaded your data with the add-in, the Group function wizard lets you build one or more group rules simply by selecting the criteria, entering a value and assigning a label. The rules can only be evaluated based on one column, and the resulting labels are written to a new column that you can name. The function also includes the ability to catch anything that doesn’t pass any of the rules — an ‘other’ category.
As an example, the Group function can be used to group position metrics into groupings, and another Group function can categorize page URLs. A detailed list of page-query positions could be transformed into a summary table showing the number of impressions by position group and page category.
Creating Group Rules
You start by selecting the column to be evaluated (“based on”), and enter a name for the new column that will hold the group labels. Note that the criteria being offered for the rules depends on the type of column selected (text, number or date), so this step must be done first. Using a criteria for a mismatched column could result in unexpected results or errors.
Each Group Rule is created by selecting the Criteria (test method) to use, plus a Value to test against and a Label to assign if the test passes.
Cascading Rules
Rules are evaluated in order, so the order is important when building progressive range groupings such as those used in histograms. For example, entering these group rules in order:
Less Than 3, label "1-3" Less Than 10, label "3-10" Less Than 20, label "10-20"
The first rule catches any numbers less than 3.
The second rule captures numbers that are NOT caught by the first rule (not less than 3), but are still less than 10. i.e. it captures numbers greater than or equal to 3 but less than 10.
The third rule captures numbers that are NOT caught by the first or second rule (not less than 3, and not less than 10), but are still less than 20. i.e. it captures numbers greater than or equal to 10 but less than 20.
Cascading rules can be built up in this manner to cover progressively larger numbers. The ‘Other Group’ label can be used to capture the highest grouping.
Categorization of Text Values
When evaluating text strings, the group rules can be used to assign categories to each row. You can test using typical ‘equals’, ‘contains’, ‘begins with’ type of tests, or use more powerful regular expression tests.
Rules are evaluated in order, so the most-specific rules should be placed in order first, allowing less-specific rules to catch values that may overlap but are not one of the more-specific cases. An example of this is for page URLs, looking for specific landing pages, then looking for any (other) page in the same subdomain.
Optional Other Group Label
Note that if you do not use the ‘Other Group’, and a value does not pass any of the rules for any reason, the label will be empty. You should be prepared to handle these empty labels in your macro if unexpected results appear.