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 important 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:
- Click Datamine in the left navigation menu. This opens the Datamine screen.
- In the menu, under General reports, click Per creative per day.
- 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.
- Click the Download as excel-file button to create and download the Per creative per day report.
- 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:
- Click any cell that contains text or numbers.
- Click the PivotTable button in Excel.
- Depending on the version of Excel you are using, a dialog 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 dialog, click OK to confirm the default values.
- 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 version of Excel, 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.
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:
- Go to the PivotTable builder.
- Select the following variables in the Field name section:
- Drag the campaignName and start variable to the Row Labels area. Put the start variable under the campaignName variable.
- Drag the formatName variable to the Column Labels area.
- Drag the clicks and impressions variable to the Values area.
Add new variables or drag variables from area to area to get acquainted with the intricacies of pivot tables. 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.
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.
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:
- Add a calculated field to the pivot table. Go to the Insert menu and click Calculated Field …. This opens the Insert Calculated Field dialog window.
- Provide a name in the Name field, for example Click-rate.
- Insert the formula in the Formula field.
- Click the Add button to save the calculated field.
- Click the Ok button.
- To add the newly created calculated field to the pivot table, drag the Click-rate variable to the Values section in the PivotTable Builder.
- 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.