Pivoting Data Tables

When Amplitude introduced its new Data Tables reporting interface, it created a new way for Amplitude customers to visualize their data. Data Tables allow customers to add multiple events/metrics to a table and see them side by side. Data Tables also provide a way to view events/metrics by multiple properties through property breakdowns.

Since its launch, Data Tables has quickly become the fourth most popular data visualization in Amplitude’s analytics product. This is the fastest that a new visualization has risen in popularity over the past ten years! We expect the Data Table visualization to be even more popular when more customers discover it over time.

But like everything in Amplitude, our customers always ask us for more. Amplitude prides itself on listening to customer feedback and using that feedback to improve its product. As a product-led growth company, we believe that our customers’ feedback is the fuel that helps us continuously build better products.

One of the feature requests we received from customers related to Data Tables was the ability to pivot the columns and rows. Many customers are experts in spreadsheet pivot tables and wanted to bring that functionality into Amplitude. The ability to pivot Data Tables just went live, so in this post, I will share how it works and provide an example of its use.

Data Tables

For those not familiar, Amplitude Data Tables are useful when you want to view multiple metrics/events side by side or different variations of a metric/event. For example, you may want to see Lead Form Completions from different cities. To do this, you would add the Lead Form Completed metric/event to the table and then add a different cohort for the cities you want to focus on. In addition, the columns of metrics/events can also be broken down by your Amplitude properties. Property values are typically added in the leftmost column as you might in a spreadsheet. For example, you might want to view the Lead Form Completed metric/event by the day of the week on which the event occurred. If you bring all of this together into a Data Table, you might have a view like this:

The Data Table shown above makes it easy to see the intersection of City and Day of Week for Lead Form Completions. But there may be times when you would want to pivot this data to see the columns as rows and the rows as columns. One way to do this would be to export the Data Table to a CSV file and create a pivot table there, but at Amplitude, we are fans of keeping people in our product. Once you take data out of Amplitude, it becomes more difficult to dig deeper into the data and ask secondary and tertiary questions.

As mentioned earlier, we have made it easy to pivot Data Tables by simply clicking a button. Here is how the preceding table can be pivoted:

Once the Data Table has been pivoted, you can resize one or all columns or use the auto-fit function to size each column appropriately. Here is what the resulting Data Table might look like after being pivoted:

If needed, you can also rearrange the order of the columns to meet your needs. For example, you may want to reorganize the Day of Week columns to be in a more logical order. This can be done by simply dragging the columns as needed. You can choose to sort on any column. If needed, you can export the data once data is pivoted.

The Fine Print

There are some situations where pivoting data in Data Tables is not feasible. Typically, these are situations where you perform more advanced reporting in Data Tables, and pivoting the data would produce complex results. These include:

• Using session metrics
• Using metrics with attribution applied
• Using rows that have nested properties
• Using columns that show non-absolute numbers

If you have one of these scenarios in your Data Table, the pivot function will be disabled and you will see the following warning:

Other Pivot Table Use Cases

While pivoting data can be useful for visualizing your data in different ways, there are some specific use cases where pivoting Data Tables can be especially helpful:

• If you have many metrics/events you want to view together and don’t have a need to break these events down by a property, viewing events as rows instead of columns allows you to view more on one screen (vertically instead of horizontally)

• Viewing metrics/events as rows instead of columns so you can use the sort function to view events by volume (descending or ascending)

• Viewing dates as columns instead of rows is a good way to see data over time. For example, you may create a Data Table that looks like this:

Then you can pivot the data to look like this:

We look forward to seeing how our customers take advantage of this new pivot functionality!