Multi-dimensional analysis with Data Tables

When analyzing a rich dataset, analysts often need to compare multiple metrics at once, and slice and dice that data by different dimensions to generate a custom analysis. Amplitude’s Data Tables enable multi-metric, multi-dimensional analyses in a single view. It is an extremely flexible chart, allowing you to quickly analyze any combination of user behavior, user attributes, and metrics. 

NOTE: You may also find this course on Data Tables helpful.

Data Tables are useful for:

• Marketing attribution (total visits, page views, and conversion rate by UTM source)
• Market Segment analysis (comparing several actions broken down by country)
• Experiment analysis (multiple metrics by control vs variant groups)
• Trend investigation (quickly and easily breaking down a number by multiple properties)
• Comparing time periods across multiple metrics (metric A, metric B, and metric C, broken down by category, compared to last quarter)

You can easily sort columns in ascending or descending order (just click the metric header), drag and drop or resize columns, and highlight, copy and paste any number of cells from your Data Table.

NOTE: This feature is available to customers on the Enterprise, Growth, and Scholarship plans only.

TIP: To get a quick overview of how Data Tables work, check out our short Loom tutorial.

Set up a Data Table

To set up and use a Data Table, follow these steps:

1. Navigate to Create New > Analysis > Data Table.
   
2. In the empty Data Table panel, click Add an event or metric and select the event or metric you’re interested in. A new Data Table will open, with your chosen event or metric in the first column. Add more by clicking + Add Event or Metric in the rightmost column in the Data Table.
   
   You can create a new metric at this point, if you need to. 
   
   
3. To break out your events and metrics by property values—country, for example, or platform, or week—click Select property… in the leftmost column of the table and choose the property you’re interested in.
   

This will run a group-by on your events and metrics, grouping by the property you selected. You can include up to five top-level group-bys in a single Data Table.

NOTE: When you do a top-level group by in a Data Table and include a Formula Metric, the results are consistent with measuring by a Formula in Event Segmentation and grouping by an Event property (as opposed to grouping by a Segment in Event Segmentation).

4. Once you’ve added a group-by property, you can run a secondary group-by on that row of your Data Table. For example, you can break your events and metrics out by the Day of Week property nested within Country.
   
   
   
   Click in the rightmost group-by column and select the property you’re interested in.
   
   
   
   
5. Next, add user segments, if desired. Saved segments are accessible. Multiple segments will show up in the table as separate columns within the same metric.

NOTE: Within any cell, click the Options icon to: 

• • • Open as chart, which will open a new tab with the chosen metric applied;
    • Create cohort, which allows you to save the chart's data points as a cohort;
    • Copy the data so you can paste elsewhere as needed or export the data as a CSV file.

Using metrics in Data Tables

With Data Tables, including a "-" character in any cells included in your formula's calculation will result in an error.

In some cases, using Uniques as a metric type in combination with group-bys can generate results that appear counterintuitive at first. For example, when a group-by is added to the event in the left column, the total sum for the event (as seen on the top row) is not a sum of each of the rows below. Because there is a group-by applied to the event, the same user can exist in multiple rows.

The same logic applies to the Session Totals metric. When a group-by is added in the left column, the total number of sessions in the top row can be fewer than the sum of the rows below. This is because a session containing property values X and Y will be counted under both X and Y groups.

Filter your events and metrics for specific values within a group-by 

You can click  to select which property values you want to keep or hide in the table.

You can also add an ad hoc filter for in-line events or metrics. To do so, click the three dots from the event or metric header and select “Add Filter.” This will let you apply filters on top of your events or metrics. Once applied, you can see what filters are applied by hovering over the  icon.

NOTE: there are some display limits when sharing analyses externally via a Public Link:

• Session-based and attribute-based metrics are not supported, and 
• display options Relative % for totals nor Data bars in cells are not displayed.

Results limits and sorting logic

For more complex analyses, it's important to understand how Amplitude Analytics decides what results to display, as well as what happens when you sort on a given column.

• If your group-by includes a large number of different property values, Amplitude Analytics will only display the top 100 group-by results. With multiple top-level group-bys, the limit is 500.
• If you are nesting group-bys, Amplitude Analytics applies the limit to each one separately:
  
  • For example, if you have a top-level group-by around the city property, where the limit is 100, Amplitude Analytics will only display the top 100 group-by results. If you then add a second-level group-by around the email property, 100 emails at most will be displayed for each city.
• Any metric with attribution will be limited to ten results.
• If your Data Table includes metrics with different limits, the smallest row limit will apply.
• If your table contains metrics that are not segmentation-based—like conversion, attribution, or session, for example—and you're doing multiple group-bys, you may see fewer results than the limits described here might suggest. Reach out to your CSM or Amplitude Support if this is an issue.

Sorting logic

Once you have these results, any sorting you do will apply only to them, and will not bring in any new results. For example, imagine your group-by has enough different property values that Amplitude Analytics limits the results displayed to the top 100. By default, these results are sorted in descending order. If you opt to view your results in ascending order, you will not see the “bottom 100” results instead. You will still see only the same top 100 results—only their sorting order has changed.

When you are using multiple metrics, sorting by a particular column will display data for all columns based on the values in the sorted column. For a data table with multiple segments, multiple metrics, and a period over period comparison, sorting a period-over-period column within a metric will give you a dataset based on the first segment's current period.

Limits for .CSV exports

Results exported to .CSV have row limits based on the metric type. If the data table contains multiple metrics of different kinds, the smallest row limit will be applied. Rows exceeding the limit will be pruned and will not appear in the exported .CSV.

• Event segmentation metrics without attribution are limited to 10,000 rows.

• Funnel metrics without attribution are limited to 300 rows.

• Session metrics are limited to 100 rows.

• Identical to its display limit, any metric with attribution will be limited to ten rows.

Limits for Dashboard REST API queries

With results queried from the Dashboard REST API, event segmentation metrics are limited to 1000 rows. This is the only difference from the .CSV limits described in the previous section.

How time properties work in Data Tables

When you apply time properties as group-bys, all limits described above will apply to each group of the property.

Examples:

• If you are using multiple top-level group-bys, the display limit is 500 rows.
  If, for example, you add another top-level group-by for the month property (a time-related property), and the date range includes three months (and thus, three different values for the property), up to 1500 rows (500 rows * three property values) will be displayed in your Data Table.
• If you export a funnel metric without attribution, grouped by a non-time property, the .CSV export result limit is 300 rows.
  If, for example, you add another top-level group-by for the day property (a time property), and the date range includes seven days (and thus, seven different values for the property), up to 2100 rows (300 rows * seven property values) will be exported to your .CSV.

Transpose rows and columns

Columns and rows of a Data Table can be transposed when:

• you've toggled on a period over period comparison,
• segments exist in your chart definition, 
• you've added top-level group-bys to your data table, or
• time properties exist.

Transposing is not possible if:

• nested group-bys exist,
• if the table contains session or attribution-based metrics,
• nor if the user has unchecked the Absolute numbers.

NOTE: A transposed data table will not support display options Relative % for totals, Data bars in cells, nor Color % delta.

To transpose a Data Table, follow these steps:

1. Add events or metrics to horizontal access.
2. Add top level group-bys to vertical axis. 
3. Change the Columns dropdown to rows to flip the axes. 

NOTE: Transposed Data Tables will be read-only and not allow editing.