How to Calculate Daily and Rolling Three-Day Ad Performance in Airtable

So you have downloaded your Meta ad performance report from Meta Ads Manager. The export gives you your Spend, your Leads, and any other performance metrics on a daily basis for each Ad Set.

That means Day 1 has Ad Set 1, Ad Set 2, and so on. Day 2 again has Ad Set 1, Ad Set 2, and so on.

So a single day can have multiple ad sets, and each ad set contributes its own Spend and Leads for that day.

What you want is to turn this raw data into something structured.

First, you want Airtable to automatically build a list of unique dates. Once each date exists only once, you want Airtable to sum up the Spend and the Leads for that specific date across all ad sets.

You also want a rolling three day view. For example, for Day 3 you want the combined performance of Day 1, Day 2, and Day 3. In other words, for any given day you need the results from that day plus the two days that came before it.

In Excel this is simple because each row has a fixed position. You can point directly to the rows above and sum them. Airtable does not work that way. Records in a table do not have a true position, so you cannot tell Airtable to look at the previous two rows.

How do you build a structure for such a system in Airtable?

1. Set Up Your Data Tables

Create two tables. The first one is your Ad Performance Details table, which holds the raw export.

This table includes fields like Date, Ad Set, Spend, Leads, and any other metrics you normally export.

You also create a formula field called Date String that formats the date using DATETIME_FORMAT(Date, "YYYY-MM-DD"). This formatted value will make your linking easier.

The second table is your Daily Summary table, which holds one record per day and is where you calculate your totals and your rolling values.

Here you only need a few fields: Name, Date, a linked record field called Ad Records that connects to your raw data, and two rollups called Total Spend and Total Leads.

These rollups simply add up the Spend and Leads coming from the linked records for that specific day.

2. Link Records for Daily Aggregation

The entire process works through linking. In your Ad Performance Details table, add a link field called Day Link that points to the Daily Summary table.

Then create an automation that triggers whenever a new record is added in the Ad Performance Details table. The automation should update that same record and set the Day Link field to the Date String value.

Once this is in place, Airtable will automatically create a new day in the Daily Summary table whenever a new formatted date appears.

From that point on, the Daily Summary table will always maintain a clean list of unique dates, and the rollups like Total Spend and Total Leads will calculate themselves without any manual steps.

3. Calculate Rolling Three Day Performance

To calculate a rolling three day performance in Airtable, you cannot rely on a simple rollup in the Daily Summary table because a rollup only sums the records linked to that specific day.

That means it will never include the previous two days, so the totals will be incorrect. The way to handle this is to create a separate table for rolling calculations and use automation to link the correct records.

Create a new table called Rolling Three Days. Each record in this table represents one date. You need a few fields:

• Date – holds the day for which the rolling total is calculated.

• Ad Records for Rolling – a linked record field that will contain all Ad Performance Details records for the current day plus the previous two days.

• Rolling Spend – a rollup that sums the Spend from the linked records.

• Rolling Leads – a rollup that sums the Leads from the linked records.

The key part is the automation. You set up an automation that triggers whenever a new Daily Summary record is created. The automation then:

• Finds all Ad Performance Details records where the Date is on or before two days the current day.

• Updates the corresponding record in the Rolling Three Days table, setting the Ad Records for Rolling field to include all of these found records.

With this setup, every record in the Rolling Three Days table always contains the current day plus the previous two days. The rollups for Rolling Spend and Rolling Leads then calculate the correct totals automatically.

This ensures that every day in the Rolling Three Days table shows the true rolling three day performance as soon as new ad performance records are imported.