Pivots help you derive significance and insights from your user data. The Pivot analysis tool summarizes your data and helps you slice it and dice it with the help of tables and other data visualizations. A Pivot analysis is especially useful when you are dealing with a large volume of data as it allows you to create incisive summaries and view custom reports to gleam insights.

You can access this data analysis tool under the “Beta” section of your CleverTap Dashboard. Its available for a limited period as a beta feature for all accounts, once out of beta, it will be available only for clients on the enterprise plan.

Pivot Analysis will help you answer questions like –
– At what time of the day, which categories of my products are selling best?
– In which city are my sales for Nike Shoes the lowest?
– How many mins of sports content do my platinum customers consume each day of the week?

Getting started with Pivots

Picking your event

Once you are on the Pivot analysis section, you need to select the event which you’d like to analyze. This event will form the basis of your analytics, and you will be able to couple this event & its properties along with all the information CleverTap has for your users.
Example: If you want to understand content consumption in your video application, you will pick the “watched” event. i.e the custom event you raise whenever someone watches a video

Pivot 2

Picking your segment

After you’ve selected the event you’d like to analyze, you can decide if you’d like to analyze this event for all your users, or if you’d like to examine this event for “all your users” or for a particular segment of users. This feature is extremely useful, as behaviour for users varies across segments. Example: Content consumption for your new user segment will be very different to the consumption of people that have already consumed at least 50 hours of content.

Pivot 1

Picking the properties to Pivot on

Here, you have to select the properties on which you would like to carry out your analysis. These properties could be
– Event properties
– User properties
– Geography
– Technographics
– App Fields

Pivot 3

Aggregating your query by – count vs conversion property

You can group your data by either the “occurrences” of an event. I.e the number of times this event has happened. Or, if the event you are analysing is your conversion event, and your conversion property is an integer, you can group your data by the sum of this property. Example: A flight aggregator can view this data by either the count of “flight booked” event, or by the sum of the “amount” property of the flight booked event.

Understanding the Pivot Analysis

Understanding the Pivot Table

  • Pool of available properties: This is a list of properties, that you selected while creating the pivot table. You can use these properties in any combination to slice and dice your data to derive insights. These properties could act as rows, or columns in the tabular view, and as cartesian axes in the graphical view
  • Row(s) – The properties you’d like to select as rows in your tabular views
  • Column(s) – The properties you’d like to select as columns in your tabular views
  • Data visualizations – Here, you can select what visualization you’d like to see the data you’ve sliced and diced in.

Example Analysis

Let’s take up an example to understand the output of our pivot analysis. In this example we are a video application, we’re using the Pivot analysis to understand content consumption patterns in our platform. To do so, we’ve selected the event “video watched” which is raised after someone is done watching a video.
The properties we’re selecting for this pivot analysis are
– Hour of the day
– Day of the week
– Video Name
We will also be picking our measurement property as “video duration watched” since it’s critical for us to measure video performance not just by number of views, but also by duration of content consumption.

We’ll now explain each view and tell you what insight can be drawn for each view supported by our pivot analysis.


Best for – getting a quick overview of selected data

Table heat map – Points to the warmest cell in the table

Best for – Analyzing highest contributor in a given data set. Example: If your row is day of the week, and column is video name. This heat map will tell you which video was played the most, and on which day of the week did this happen.


Columnar heatmap – Points to the warmest cell in a column

Best for – Analysing outliers in any column. Example: If your row is day of the week, and column is video name. This heat map will tell you which video played most on monday, which on tuesday, through to sunday.

Column Heatmap

Row-wise heatmap – Points to the warmest cell in a row

Best for – Analyzing outliers in any row. Example: if your row is day of the week, and column is video name. This heat map will tell you on which day of the week, each video has performed well. Game of thrones performs best on Sundays, whereas F.R.I.E.N.Ds works best on Saturdays.

Row Heatmap

Stacked bar chart

Best for – Visualizing the change in performance of one variable over time, when compared to several other variables. Example: if your row is day of the week, and column is video name. You might see that the performance of Game of Thrones peaks around Sunday/Monday, whereas it’s a lot lower for the rest of the week as compared to the consumption of F.R.I.E.N.Ds, Prison Break and Breaking Bad, which are consistent across the week.

Stacked Bar Chart


Best for – Visualizing categorical or qualitative data over time. Example: if your row is day of the week, and column is video name. You will see how the consumption of Game of Thrones has been over the week.

Bar Chart

Line chart

Best for – Helps you visualize the change of variables over time. Since all lines are plotted on the same graph, this visualization helps you determine the relationship between different variables. Example: if your row is day of the week, and column is video name. You might see that Breaking Bad, and Prison Break follow the same trend across the week. This would imply that demand for these videos is consistent.

Line Chart

Area chart

Similar to Line charts, but better for analyzing part to whole relationships. Example: If your row is day of the week, and column is show name. Stacked area chart will not only show you the trend variation of Game of Thrones over the week, it will also show you the contribution of Game of Thrones to your total video views, as compared to the rest of your titles.

Area Chart

Salient points

Currently in Pivots, we sample your data to ensure that results load faster – so you might see a minor difference in counts when you compare these numbers to numbers in the rest of your dashboard. Having said that, this sampling has been optimized for accuracy and your numbers will be directionally correct. Sampling kicks in if the query contains more than 100,000 events, and the sample rate beyond 100,000 events is 10%.
Pivots in their beta form support selecting up to 7 properties at a time

Pivots are expected to move out of a beta stage and move to production by November, 2017*. Once in production, this feature will be available only for enterprise accounts. For any feedback and/or questions you can contact support@clevertap.com. And, if you’ve bothered to read this whole article, and don’t have an account with CleverTap yet, sign up now!