Split Impressions Paid by Revenue Channel

You can use a spreadsheet to break down the impressions paid by direct, programmatic, or other revenue channels.

Also see:

• How to Calculate Current Month STR

• Sell-thru Rate

• Impressions

This article shows how to use Microsoft Excel to combine Sell-thru Rate and Impressions reports for the direct and programmatic revenue channels.

Step 1: Create Your Reports in TAP Explore

Customize your impressions paid spreadsheet

You can add additional filters or dimensions, such as Region, as long as both reports use identical parameters.

1. In TAP Explore, create a Sell-thru Rate report. See Create Reports.

   • Set Report Type to Sell-Thru-Rate.

   • Set Date Range to Last Month and Dimensions to Station Name.

2. Export the report as an xslx file. See Export Triton Metrics Reports.

3. In TAP Explore, create an Impressions report.

   • Set Report Type to Impressions.

   • Set Date Range to Last Month and Dimensions to Station Name.

4. Export the report as an xslx file.

Step 2: Prepare Your Excel File

1. In Microsoft Excel, open the exported Impressions report then select all cells and copy into your clipboard.

2. Close the Impressions report.

3. In Excel, open the exported Sell-thru Rate report.

4. Rename the Report worksheet to STR.

5. Create a new worksheet, name it Impressions, and paste the clipboard.

6. Convert each worksheet to a table by pressing Ctrl+T. See Create and format tables.

Step 3: Create a PivotTable for the Impressions Report

This summarizes the total paid impressions by station and by revenue channel.

1. Go to the Impressions worksheet.

2. Insert a PivotTable and place it in a new worksheet. See Create a PivotTable to analyze worksheet data.

3. In Drag fields between areas below, configure the PivotTable :

   • Rows: Station Name

   • Columns: Revenue Channel

   • Values: Impressions

4. Rename the new worksheet to Pivot.

Step 4: Add Revenue Channel Columns to the STR Report

1. In the STR worksheet, select the Impressions Paid column.

2. Next to the Impressions Paid column, insert two new columns with these header names:

   • Direct Paid

   • Programmatic Paid

3. If you have more channels, for example Resold REX, insert one column per channel.

Step 5: Populate the Data Using VLOOKUP

1. In the first data cell of the Direct Paid column, use the VLOOKUP function.

   • =vlookup(lookup_value, table_array, col_index_num, range_lookup)

   • lookup_value: [@[Station Name]]

   • table_array: The range of data in the Pivot worksheet. Do not include the rows for headers and the last row of totals. Make the range absolute by pressing F4.

   • col_index_num: The column number of Direct in the PivotTable. Example: If Direct is in column B, use 2.

   • range_lookup: false

   • Example: =vlookup([@[Station Name]],'Pivot'!$A$4:$C$69,2,false)

2. Once entered, Excel automatically populates the column with the VLOOKUP function.

3. Repeat the same for Programmatic Paid, updating the column index number to the correct one from your pivot table.

Step 6: Finalize

1. Review the values to confirm they match across stations.

2. Optional: Delete the original Impressions Paid column from the STR report.

You now have a detailed STR report showing impressions paid split by direct, programmatic, and other revenue channels.