Media Console Data Analysis Series, Part 1: Advanced campaign data analysis with Microsoft PowerPivot for Microsoft Excel 2010

Analyzing campaign data quickly and efficiently is fundamental to making informed decisions for online marketing campaigns. While Atlas offers a wide range of standard reports that offer a deep set of insights on media performance, we’re always looking for ways to uncover even more value in Atlas data. With Microsoft PowerPivot, you can aggregate, analyze, and query data in an easy to understand environment – Microsoft Excel!

Features in PowerPivot include:

· Data-importing and -refreshing via connections to Text, Analysis Services, Azure Marketplace, and SQL Server Analysis Services.

· The ability to query large data sets using the SQL Server Analysis Services native language MDX

· A built-in DAX query language to create custom measures and filters on data tables.

· The ability to create charts and graphs easily with the table-relationship tool in Microsoft Excel, useful for advanced filtering.

In 10 simple steps, we’ll show you how to build a reporting dashboard with PowerPivot. Specifically, we’ll use the table-relationship tool to set spend thresholds.

Once we’re done, we’ll have a four-chart view using three slicers. Like this:

So let’s get started.
Importing a report

Step 1: Pull a report from Atlas, and save it to your desktop. For more information about custom reports see the blog post Media Console Reporting Series, Part 3: About user-defined reports.

The following is an example of the report components you’ll need to select in Report Builder.

Step 2: Download PowerPivot as an add-in for Excel by clicking here. The add-in is completely free to everyone, but works only in Microsoft Excel 2010, so be sure you have this version of Excel installed on your computer.

Step 3: Open Microsoft Excel and click the PowerPivot tab in the ribbon.

A new PowerPivot for Excel workbook will open.

Step 4: Import the data into PowerPivot.

1. In PowerPivot for Excel, click the Home tab, and then click From Text.

2. In the Table Import Wizard dialog box, click the Browse button, and then point to the file path of the report.

3. In the Column Separator box, select Tab.

4. Select the Use first row as column headers check box.

Once you’ve imported the data, you’ll see the following confirmation message:

Step 5: Click Close to view your data in PowerPivot.

Adding a custom filter

We can now go to work on the data by adding a custom filter based on three user-defined thresholds based on Net Media Cost. With this custom filter, you can view actual cost data at three levels: small, medium, and large. I’ve used 1, 2, and 3 to represent each of these.

To add a custom filter, first we need to build a new table in PowerPivot, and then add a custom formula to give us our ‘Spend level’ values.

Step 7: Build a new table in PowerPivot.

1. On the Home tab, click From Text.

2. In the Table Import Wizard dialog box, browse to the file path of the report.

3. In the Column Separator box, select Tab.

4. Select the Use first row as column headers check box.

5. Unselect the check boxes for all of the column field names, and then select only the Publisher and Net Media Cost check boxes.

6. Click Finish.

Step 8: Add a custom formula.

1. Rename Add Column to Spend Level.

2. In the formula bar above the table, create a formula for the Spend Level column by clicking the function button (fx) and adding the following formula: =IF(Table3[Net Media Cost] < X, "3", IF(Table3[Net Media Cost] <Y, "2","1")) where X and Y are variables for any number.

3. When you have finished building the formula, press ENTER to accept the formula.

PowerPivot will calculate the spend levels for each publisher.

Creating relationships
We’ve built two tables, so now we need to connect them by using a field that’s common to each table. In PowerPivot, we can do this by using a many-to-one relationship. This means we create a link between the single instance of a publisher record in a new table and relate it to the many instances of the same publisher record in the first table we built.

Let’s take a look.

Step 9: Build a simple relationship between tables.

1. In PowerPivot for Excel, click the Design tab, and then click Create Relationship. Like this:

2. Map the publisher column in the first table to the publisher record in the latest table. (In this scenario, the first table I imported is called ‘PP_DATA’ and the new table is called ‘Table 3’.)

3. In the Create Relationship dialog box, click Create.

We’re ready to roll! Our model is built. Now we can use it repeatedly to access the data source and refresh our data.

Working in Excel

Step 10: Create pivot charts and slicers.

1. In PowerPivot for Excel, click the Home tab, click PivotTable, and then click Four Charts. Like this:

Excel creates a four-chart pivot framework.

2. Add slicers.

3. Click Chart 1 and add your axis fields and values. Like this:

Congratulations! You’ve now completed Chart 1. You can use the “Spend level” slicer to monitor impression volumes captured by “Spend level” in your chart. You can also add data to the remaining three charts to complete your dashboard.
Tip: You’ll need to build a new field Creative Dimensions in PowerPivot before adding it to your Excel dashboard.