This article explains about how you can transform data into a polished, actionable dataset in Rules Engine using the Pivot tasks with a scenario. In this scenario, the following have been used:
Customer: Abbett Ltd.
Customer’s customer: Johnson Ltd.
Web pages of the customer’s website (used in this scenario):
Customer Support Page
This scenario will help you to perform the Pivot task that allows you to create a column for every data value. This enhances the data summarization process thus giving you a better insight of your data.
Application of Pivot Task
Consider a scenario in which Abbett Ltd. wishes to calculate Page views for its different web pages as mentioned above by a high value customer ‘Johnson Ltd’. The following table shows the number of times each web page has been viewed by various users at Johnson Ltd.
|User Name||Page Name||Views|
|Matt Rick||Home Page||6|
|Scott Coyte||Admin page||2|
|Belinda Clark||Sales Page||4|
|Scott Coyte||Customer Support Page||2|
|Serene Andrews||Admin Page||5|
|Mike Smith||Sales Page||7|
|John Paul||Customer Support Page||3|
|Scott Coyte||Home Page||2|
|Belinda Clark||Admin Page||8|
|Matt Rick||Sales Page||4|
|Mike Smith||Customer Support Page||6|
|Serene Andrews||Home Page||3|
|John Paul||Admin Page||7|
The above table is just a small chunk of imaginary data. However, in real world the number of web pages and number of users can be much more than this table. In such scenarios, it is really challenging to manually calculate as to which page got the highest number of views, which user is more interested in which page, and so on.
By using Pivot tables in Bionic Rules, you can easily perform these difficult tasks. For instance, to calculate the page views for each web page, you just need to drag and drop the Page Views field in the Pivot on section at Rules Engine > Setup Rule screen. Drag and drop the Page Name field in the GroupBy section. When you run the Rule, the following screen is displayed.
From this output table as shown above, you can easily conclude that Admin Page and Sales Page got the highest number of views, followed by the Customer Support Page and Home Page.
You can also see page view data at user level; you can see which user at Johnson Ltd. viewed which Page for how many times. To accomplish this, you must apply Pivot on feature to Page Name field and apply Group By clause on User Name field. When you run the rule, the following screen is displayed.
From the above output table, you can easily view as to which user has viewed which page, and how many times.
So, the main benefit which you can leverage from Pivot tables is summarization of terabytes of raw data into useful information as required by your organization, which helps in decision making.
For additional help, see this short video demo on Pivot task configuration.
A general flowchart of preparing Pivot Tasks in Bionic Rules is shown below.
Usage of Pivot Tasks in Gainsight
CTA Priority:- CTAs have three levels of Priority: High, Medium, and Low. It is highly essential to keep a track of each priority levels and resolve CTAs based on their priorities. In this article, a Pivot table is used to create a summary chart which enlists all the CTAs in accordance of their priorities and gives you a count of the same. This table also displays the Account Name to which a CTA belongs to. The step by step procedure to create Pivot table is as follows:
Create a Rule
Navigate to Administration > Rules Engine.
In the Create Rule screen, enter:
Rule For: Company
Rule Name: Pivot task (example)
(Optional) Description: enter a description.
- Click NEXT. The Setup Rule screen is displayed.
Create Data Set (Fetch Task)
Click DATASET TASK to create a data set.
(Optional) Task Description
Select Call To Action as the source object.
- Add the fields to the Show section as shown in the following image (after 6th step).
- Click SAVE.
- Click <- to go back.
Create Pivot Task
- In the Setup Rule screen, navigate to + TASK > Pivot.
- Enter a name in the Task Name field.
- Select T1 (the name you given to the previous dataset) as the source object.
- Drag and drop the Priority Name field under the Pivot On section. The Pivot conditions to evaluate window is displayed.
- Create Pivot condition as shown in the following image.
In the above image, a column named High Priority CTAs in the Output table has been created, this keeps a count of all High Priority CTAs. In the Condition section, the Pivot table evaluates all CTAs whose Priority is equal to High. In the Operation section, the Pivot table counts the number of Ids for high Priority section. Id field is unique for every CTA. Hence, Id field is used to get accurate values. In the Output section, you must provide a name. This name is displayed in the final Pivot Output table.
- You can similarly create another two columns for Medium and Low priorities. You must click + ADD, to add more rows.
- Drag and drop Account Name and Call To Action Name fields to the GroupBy section.
Now all the CTAs (High, Medium, and Low) are Grouped in line with Account Name and CTA Name fields. The final output table will have five columns. Account Name and Call to Action Name (because they are used in group by clause), and three columns for Priorities, used in Pivot table.
When you drop fields to the GroupBy section, they are automatically added to the Show section. However, sometimes an aggregation method might be auto applied on the fields in the Show section. It is essential to remove this aggregation method, if it exists.
- Click SAVE.
- (Optional) You can setup the action as required in the Setup Action page. For more information about different Rule actions types, refer to the Setup Rule Action Types article.
The final output of Pivot table as is shown below.
So, in this example a Pivot table is created which summarizes all the CTAs based on their priorities. This table also displays the respective CTA Name and Account Name, since they have been used in the Group By section.
The arrangement of columns can vary in your output. You can rearrange columns. Just drag the column name and drop it at the required position.