Calculate Monthly eCPM when your have both CPM and CPS flights

  • Published on Jun 21, 2024
  • 2 minute(s) read
Prev Next

You will need to save three different queries, which you will then schedule to receive monthly. Then you will build out a custom report in Excel.

Part 1: TAP Explore Setup

Report 1: All CPM Campaigns Impressions

  • Report Type: Impressions.

  • Filters:

    • Date range preset: Last Month

    • Pricing model: all except CPS

  • Metrics:

    • Impressions

    • Delivered flight Revenue Net

  • Dimensions:

    • Advertiser Name

    • Campaign Name

    • Flight Name

    • Flight (ID)

    • Flight Goal

    • Station Name

  • Save and Name your report (e.g., Monthly_eCPM_ImpressionsOnly).

  • Schedule the report to be sent to you monthly, every 2nd of the month.

Report 2: CPS Campaigns Impressions

  • Report Type: Impressions.

  • Filters:

    • Date range preset: Last Month

    • Pricing model: CPS

  • Metrics:

    • Impressions

    • Do not add revenue data: the system cannot calculate revenue on CPS campaigns in impressions report.

  • Dimensions:

    • Advertiser Name

    • Campaign Name

    • Flight Name

    • Flight (ID)

    • Flight Goal

    • Station Name

  • Save and Name your report (e.g., Monthly_eCPM_CPS-Impressions_Only).

  • Schedule the report to be sent to you monthly, every 2nd of the month

Report 3: CPS Campaigns Spots

  • Report Type: Spots

  • Filter:

    • Date range preset: Last Month

    • No need to filter by pricing model CPS as Spots-CPS report type only pulls CPS campaigns data.

  • Metrics:

    • Spots

    • Delivered flight Revenue Net

  • Dimensions:

    • Advertiser Name

    • Campaign Name

    • Flight Name

    • Flight (ID)

    • Flight Goal

    • Station Name

  • Save and Name your report (e.g., Monthly_eCPM_CPS-spots_Only).

  • Schedule the report to be sent to you monthly, every 2nd of the month.

Part 2: Combine Reports in Excel

Once you receive your scheduled TAP Explore reports, do the following in Excel.

Build out the Excel Tabs

  1. Open any of the three reports

  2. Add two tabs

  3. Copy the remaining reports into each of the new tabs; you should have three tabs, one for each report.

  4. Name all three tabs accordingly:

    • CPS-Spots

    • CPS-Imps

    • CPM-Imps

Get the Impressions for the CPS flights

Having the impression data for your CPS flights will allow you to calculate eCPM for your spot campaigns.

  1. Copy the CPS spots tab into another new tab.

  2. Name this new Tab CPS-Spots-n-imps.

  3. Insert a column between the Spots and the Delivered Flight Revenue Net columns.

  4. Name it: Impressions.

  5. Using Flight ID as your lookup value, you will get the impressions delivered for each flight via a vlookup formula: =VLOOKUP(D2;'CPS-Imps'!$D$1:$G$916;4;FALSE)

    • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    • Look up value (what you want to lookup) = FlightID column first data point

    • Table_array (where you are looking to find the flight ID value)= CPS-Imp tab, selecting the columns from flightID to Impressions, click F4 to make this an absolute value

    • Col_index_num (the column number containing the value to return) = Impression column is the last, so enter 4)

    • Range_lookup (returns an approx.(True) or exact (False) match) = False

Calculate your global eCPM (CPS and CPM inc)

Now that you have impression data for your CPS flight, you will combine your CPM data with your CPS-spot-n-Imps data together to get a full picture of your inventory eCPM.

  1. Copy your CPS-Spots-n-imps tab to a new tab.

  2. Name it Global eCPM.

  3. Delete the Spot column in this new tab.

  4. Copy the data from the CPM-Imps tab at the bottom of table of the Global eCPM tab.

  5. Calculate your total impressions and total Delivered Flight Revenue Net.

  6. Apply the eCPM formula to these totals:
        • eCPM =(Revenue Net/Impressions) x 1000