Skip to main content
Question

How to calculate correct weighted averages (CPM, CPC, CTR) in Interfaces for filtered date ranges?

  • February 5, 2026
  • 2 replies
  • 28 views
J

Hi everyone,

I'm tracking daily ad performance data (Meta Ads) in Airtable and displaying it in an Interface. Each row represents one day for one ad set, containing raw metrics like Spend, Impressions, Clicks, and Conversions.
 

The problem:

I need to display metrics like CPM, CPC, and CTR for flexible date ranges (sometimes 2 days, sometimes 70 days, always varying). However, Airtable Interfaces only offer "Average" as an aggregation option, which gives mathematically incorrect results for these metrics.

For example: The correct CPM for a period should be (Total Spend / Total Impressions) × 1000, not the average of daily CPMs.

What I've tried:

I can display "Sum" for Spend and "Sum" for Impressions separately, but there's no way to divide these two aggregated values within the Interface to get the correct CPM.

My question:

Is there any native Airtable workaround to calculate formulas based on aggregated/summarized values in an Interface? Or a way to pass the current Interface filter to a script that calculates the correct values?

I know external tools like Jet Admin can solve this, but I'd prefer to stay within Airtable if possible.

Thanks for any ideas!

    2 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Is there any native Airtable workaround to calculate formulas based on aggregated/summarized values in an Interface? Or a way to pass the current Interface filter to a script that calculates the correct values?

    I don’t think either of these are possible at this point I’m afraid

    ---

    My sort of workaround for this is to sacrifice dynamic date ranges and just use a couple of set date ranges instead, don’t know if that would work for you though

    The idea is to have a new table with a single record in it called ‘Rollup’ or something, and we link every data record to it.  We then create one set of fields per date range, and so let’s say we want to have the date ranges of 1 week and 1 month, we’d end up with:

    1. Last week:
      1. Rollup field for Spend for last week
      2. Rollup fiield for Impressions for last week
      3. Calculation field for last week: (Rollup field for Spend for last week / Rollup fiield for Impressions for last week) / 1000
    2. Last month:
      1. Rollup field for Spend for last month
      2. Rollup fiield for Impressions for last month
      3. Calculation field for last month: (Rollup field for Spend for last month / Rollup fiield for Impressions for last month) / 1000

    Not ideal, but it works

    Hopefully someone else has a better idea!

    Adam, Airtable Consultant - thetimesaving.company
    Alisasanian
    Forum|alt.badge.img
    • Alisasanian
    • New Participant
    • February 9, 2026

    To be honest, I have to start by asking what tier of Airtable account you are on. If you have AI access, this should take no time. 

    Have a Waterfall sequence of agents take into consideration a similar date window and aggregate the data to calculate “daily” values. Then you can multiply that into the date window you have in mind.

     

    If no AI access, you have to pay close attention to this upcoming excerpt:

     

    The truth is, without AI columns, you have 2 ways:

     

    1- Semi easy path: Prepare all the core data needed for the calculations on a view in Airtable, write a script that offloads the data into a Local database, SQL server preferred (for easy connection to Tableau, PBI), Then create a model with your custom calendar and calculations to update the matrix visuals on whatever frequency you have set the refresh from Airtable at. The Tableau, PBI dash is easily able to be embedded in most places. Mainly if it's internal facing.

     

    2- Hard Path that keeps everything inside Airtable: semi recreation of PBI data model in Airtable:

     

    Create 3 tables, one for calendar, one for core data, one for date specific aggregations. You will need robust automations that would wait for changes in a “current” view on the calendar table to trigger updates in the aggregate table. Will have to use a chain sequence of automations that are all triggered by a change in the calendar table.

     

    This should get you close to what you are planning, but for everyone’s sake, I hope you have access to AI Columns otherwise you are about to venture into Airtable state of the art development.

     

     

    Terms & ConditionsAccessibility statement