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.
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.