Create custom reports with Excel PivotTables

This chapter outlines a basic road map about how to create custom Excel reports as the data mining reports can be used to create custom reports and charts in Excel.

Throughout the chapter, we will take the per creative per day report as an example. 

It is essential to take this chapter in its entirety as the various exercises follow each other.

Create a basic report spreadsheet

To create a basic spreadsheet for the Per creative per day report:

  1. Click Datamine in the left navigation menu. The Datamine screen opens.
  2. In the menu, under General reports, click Per creative per day.
  3. Click the From field, a small calendar opens.
    • First, select the first date of the period you wish to create a report for.
    • Select the last date of the period you wish to create a report for.
  4. Click the Download as excel-file button to create and download the Per creative per day report.
  5. Open the file in Microsoft Excel. The spreadsheet lists the daily number of contacts, impressions, and clicks, and the daily click-through rate for all bookings a creative is combined with.

Create a pivot table

A pivot table summarizes data and reveals patterns and trends.

To create a pivot table:

  1. Click any cell that contains text or numbers.
  2. Click the PivotTable button in Excel.
    The PivotTable buttonThe PivotTable button
  3. Depending on the Excel version you are using, a dialogue window may ask you to confirm which data you want to analyze and where you want to place the pivot table. If you see the Create PivotTable dialogue, click OK to confirm the default values.
  4. Excel creates a pivot table based on all data in the current worksheet and places the pivot table in a new worksheet. Depending on the Excel version, your new pivot table may be empty or filled with default parameters.

Change the content of a pivot table

The pivot table we are going to recreate is quite basic. It shows you the daily number of impressions and clicks for each type of format.

Pivot table

To recreate the above pivot table, we need to change the pivot table by dragging items between the Row Labels, Column Labels, and Values areas in the PivotTable Builder.

To change the pivot table:

  1. Go to the PivotTable builder.
  2. Select the following variables in the Field name section:
    • campaignName,
    • formatName,
    • start,
    • clicks,
    • impressions.
  3. Drag the campaignName and start variable to the Row Labels area. Put the start variable under the campaignName variable.
  4. Drag the formatName variable to the Column Labels area.
  5. Drag the clicks and impressions variable to the Values area.
    PivotTable builder
    Add new variables or drag variables from area to area to get acquainted with pivot tables' intricacies. The content of the pivot table will change according to the adjustments in the PivotTable Builder. 

Other uses of a pivot table

Data mining reports allow you to produce more than pivot tables alone. For example, you can use the pivot table's data to plot graphs or make comparisons.

Plot a graph

We will graph the evolution of a campaign's daily impressions for the leaderboard and medium rectangle format.

Plot a graph

To get the above graph, create a pivot table first (see Create a PivotTable and Change the content of a PivotTable).

To restrict the report to an individual campaign, click the filter icon next to Row Labels and deselect the campaigns you do not want to see in the report.

Filter data

Then, select the values from the Sum of impressions columns and insert a chart.

Compare the daily click-through rate of formats

To compare the click-through rates for the various formats of a campaign:

  1. Add a calculated field to the pivot table. Go to the Insert menu and click Calculated Field …. The Insert Calculated Field dialogue window opens.
  2. Provide a name in the Name field, for example, Click-rate.
  3. Insert the formula in the Formula field.
  4. Click the Add button to save the calculated field.
  5. Click the Ok button.
  6. To add the newly created calculated field to the pivot table, drag the Click-rate variable to the PivotTable Builder's Values section.
  7. Select the values and
    • Add a conditional formatting rule (Format > Conditional Formatting …) to highlight high and low values in the pivot table, and/or
    • Plot a chart to visualize the comparison.
      Conditional formatting
      Plot a chart