Affidavits provide an hourly (+minutes) breakdown of spots, but because it takes multiple impressions to count as one spot, and because a flight with multiple creatives can deliver any combination of those creatives to achieve that spot, we cannot assign a specific creative to a specific recorded spot at a specific time. The impression-to-spot calculation is based on the flight’s overall delivery, not individual creatives.
Example:
If a station requires five impressions to count as one spot, and the flight includes two different creatives:
At 10:01 AM, the flight serves:
Creative A to John and Jane.
Creative B to Gary, Steve, and Tracy.
This results in five impressions and one spot being recorded, but two creatives contributed to that single spot, making it impossible to assign the spot to one specific creative.
To work around this, we will work on a day breakdown and only use TAP Explore, combining a SPOT-CPS report with a Creative Report.
SPOT-CPS Query
In TAP Explore, select Report Type: Spots - CPS.
Select a date range: (in this example we select Last Week)
Filter for Campaign Name and enter the campaign name.
Select Metrics: Spots.
Select Dimensions: Date, Flight Name, Flight Goal, Station Name.
Export the report as XLSX.
Creative Query
In TAP Explore, select Report Type: Creative.
Date range: (in my example I selected ‘Last Week’)
Filter to ‘Pricing Model = CPS'
Filter to ‘Campaign Name’: enter the campaign name
Metrics: Impressions
Dimensions: Date, Flight Name, Station Name
Export the report as XLSX.
Merging the two Reports
Open a new Excel workbook.
Copy each exported report into separate tabs.
Name your tabs accordingly (e.g.: Spot-CPS report, Creative Report)
If you have one or more flights containing more than one creative, you should pivot on the Creative Report to see what weighted rotation was applied; this will help you adjust the merged report later on. In the example below, we can see that Creative B and Creative C have a 50/50 rotation for Flight 1 and a 55/45 for Flight 3, while Creative A in the only creative in Flight 2 and in Flight 4.
Power Pivot
We will use Power Pivot to add creative to the CPS report.
In the Spot-CPS tab, place your cursor anywhere in the table.
Go to Data
Select From Table/Range.
In the Create Table pop-up window, make sure to check the box for My table has headers and click OK.
This will open the Power Query Editor. Click Close & Load; it will close this window and create a new tab in your workbook called Table 1.
Repeat steps 1 to 3 in the Creative Report tab.
Once the Power Query Editor opens again, you will see on the left you now have two Queries: Table 1 and Table 3.
Select Table 1 (as this CPS report will be the main report you will be adding creative to).
Click Merge Queries.
In the pop-up window, select the columns you will use as the common columns in both reports.
Below Table 1, open the dropdown and select Table 3. Select the same column as in Table 1.
Click OK.
An additional column is now added to the table.
Expand the column.
Select Creative File Name.
Close & Load.
An additional tab is added to your workbook, named Table 3.
You will need to go to Table 1 to find Spots data per flight as well as the associated creative.
The last step is to break down the spot data per day based on the creative weighted rotation.
Using Custom sort, sort Table 1 by Date, Flight Name, Station Name, Creative Name.
Add a Column at the end of the table and name it Spots/Creative.
Filter by Flight Name. Select one flight and apply the weighted rotation.
Repeat for each flight.
Example:
As discovered with the pivot on creative report:
Flight 1 has two creatives on a 50/50 weighted rotation.
Flight 2 only has one creative.
Flight 3 has two creatives on a 55/45 weighted rotation.
Flight 4 only has one creative.
Filter to Flight 1.
In added column add the formula Spots/2.
Now change to Flight 2.
You will notice that the formula applied to Flight 1 carried over to all flights.
Delete the data in Spots/Creative for flight 2.
Enter a new formula where Spots/Creative = Spots.
Flight 3 will need to apply the 55/45 calculation.
Flight 4, repeat what you did for flight 2.
Formatting to make it client friendly
Copy the table and paste the value only to a new workbook.
Remove Column E (Spots).
Format the copied table as you wish.
In this example we use Format as Table.
Add a tab with Detail and (optionally) your Logo.
In this example we added the Triton Digital logo.
Name your report.