Explore Your Data with Pivot Tables

Learning Objectives

After completing this unit, you’ll be able to:

• Describe pivot tables and when to use them.
  
• Create a pivot table in Intelligence Reports.
  
• Explain the different ways you can export a pivot table.
  

Make the Most of Your Data with Pivot Tables

Pivot tables allow you to explore your data from multiple perspectives. You can use pivot tables to filter, sort, and group large volumes of data according to any metric or dimension. Plus, with pivot tables, you can create custom reports that organize, summarize, and slice your data without the need to write and run queries. 

This means you can easily create a pivot table that shows a breakdown of your email send data, so you can see how many emails were sent, delivered, and bounced for a specific time period. You can also use a pivot table to see which send day had the best performance—meaning which day had the highest open rate, click-to-open rate, and email opens. The options are endless. 

In this module, we assume you are a Marketing Cloud user with the proper permissions to access Intelligence Reports to create pivot tables and scheduled reports. If you’re not, that’s OK. Read along to learn how your admin would take the steps in a production org. Don’t try to follow these steps in your Trailhead Playground. Marketing Cloud isn’t available in the Trailhead Playground.

Let’s see how Paulo, a marketing specialist from NTO, uses a pivot table to explore which email performed best. 

Create a Pivot Table

Paulo navigates to Pivot Tables in Intelligence Reports and clicks Create New Pivot Table. He then sets up the pivot table in just a few steps. 

1. First, he has to add data to his pivot table. He clicks Add Field to begin.
   
2. Next, in the Data dialog, he selects the dimensions and measurements he requires. Using the quick search, he enters Email Nameand Email Subject for the dimensions, and Email Open Rate and Email Click to Open Rate for the measurements.
   When adding data, dimensions are always placed in rows or columns, while measurements are always placed in values. The pivot table changes as you add or remove data.
   
3. After he’s done adding data, in the Pivot Table Settings, he sets the dates for when he wants to pull in the data. In this case, he wants to see the last year of data.
   
   
4. Now, he closes the Add Data dialog so he can see the data.
   All the data appears in a table according to the rows and columns he selected.
   
   
5. Paulo gives his pivot table a descriptive name so he can easily find it later on, and then he toggles on Visualize so he can see a visual representation of his pivot table. Paulo can now see his best-performing emails based on high open and click-to-open rates.
   
   
6. He clicks Save to save the pivot table.
   

Export a Pivot Table

Once you’ve created and customized your pivot table you can now export it. Go to More Actions, and then select how you want to export your pivot table:

There are a few different ways you can export.

• Save To: Exports the pivot table as a widget in the selected dashboard.
  
• Schedule Export: Exports the pivot table with a defined schedule, for example, create a report every Monday at 8 AM. When you select an option, the report format and options change according to the format.
  
  • Pivot Table (Excel): Exports the pivot table to an Excel file as a pivot table object without including the totals.
    
  • Flat Table: Creates a report in an Excel format.
    

• Export: Exports the pivot table to an Excel file. There are two options:
  
  • Pivot Table (As Is): Export the pivot table to an Excel file as is.
    
  • Flat Table: Exports the pivot table as an Excel file as a flat table.
    

Now that you know more about pivot tables, move on to the next unit so you can learn more about reports.