Creating a formula for a moving average

[vierless_julian]

Hey guys,

I spent the day trying to get my head around a moving average function. In my case I want to calculate the 7 day average of my Google Ads CPL (cost per lead) metric. I have daily data from Google Ads in my base along with all important metrics like ad spend, clicks, impressions and so on. I also track the amount of leads. For the day to day calculation a formula does quite well but when it comes to calculate things "across" rows Airtable is quite limited.

My approach is to use an automation that runs a script every time I update either the amount in ad spend or leads. This way I kind of "fake" a formula field. I feel like I am almost there but it's not working still and I am having a hard time trying to debug with ChatGPT.

My logic is:

1. Values are updated
2. Date field value is read
3. Script finds the last 7 days based on the original date in the updated row (not today)
4. Script gets the ad spend and leads values of fields that match this condition
5. Values are added and then divided (sum of ad spend / sum of leads)
6. Last step is updating the 7-D CPL field in the original row

This works in my head at least haha. The script should be dynamic to accept any number of days in the past. This way it can be reused for other amount of days like the last 30 days for example. Did anyone do this already and can point me in the right direction?

[AlliAlosa]

Hard to say without seeing your set up, but you might be able to take advantage of linked record fields for this. This is what I’ve done in the past to capture rolling averages over specific time periods.

Assuming you have one record per day, you could link each record to the desired number of previous days. Then you could use rollups and formulas to calculate the values you need. This would also eliminate the need to run a script every time a value changes as the rollups would update automatically.

[TheTimeSavingCo]

+1 for what @AlliAlosa said about linked fields.  Assuming a single record gets created each day, you could try:
1. Create a new table called "Summaries" or some such
2. In your original table, create a formula field that will identify the records that you'd want to use to calculate the average (i.e. last 7 days, not today)
3. In "Summaries", create conditional rollup fields that will output the ad spend and lead values of the fields that match the condtion from step 2, and create the formula field for CPL
4. Link all the records in your original table to a single record called "Rollup" or something in "Summaries"
5. Create an automation that'll trigger when a new record gets created, and its action will be to link it to the "Rollup" record, do a "Find Record" action on the "Rollup" record to get the updated output from the formula field for CPL, and to update the original record with said value

[vierless_julian]

@AlliAlosa @TheTimeSavingCo 
Ok, I think I understand what you are trying to say. I am just thinking it is a lot to link isn't it? I mean this would mean creating a 30 day moving average requires me to link each record to the previous 29 days. Is that not a lot to do?

Is there an example where I can see this method in action?

It would be so much nicer in my opinion to just do this programmatically and save time by making it dynamic.

[Dan_Montoya]

Here is a sample base that creates a rolling 7 and 30 day average without using any scripts. 

[vierless_julian]

Thanks for this! I think I am getting closer now. First question I have is: Is this setup scaleable? Can I connect unlimited amounts of data entries (Google Ads days) to the reporter?

I automated the import from Google Ads data via Make.com using a API call. So once a day I push the data to Airtable. In that case I'd also append it to the connected items in the reporter field right?

Next problem:
The formula I need basically divides the sum of all ad spend of the last 7 days by the sum of all leads in that time period. So for each field I should get the average based of the last 7 days in my Google Ads base.

You can see here how it is set up in my Reporter base:

The problem is that the formula is not dynamic on the right and in the rollup field I can only select one field. Is there a way to make this work?

{GA: 7-D Ad Spend}/{GA: 7-D Leads}

[jsep]

@vierless_julian 

The simplest and easiest way is to use the "Summaries" or "Report" table, as others have mentioned. The limit of links per linked record is technically the same as the limit of records per table, so there's no worry about it being too much.

The advantage of this method is that it's very simple to add other metrics you want to track in the "Summaries" or "Report" table.

Not to say that it can't be done programmatically using scripts or automations, but that will require more time and expertise, not to mention that if you want to add or modify something, you will have to change the code.

I hope this helps! If you need assistance implementing this solution, feel free to schedule a free call with me. I'll help you out.

- Juan, Code and No-Code Solutions Expert

[vierless_julian]

Update:

I created a field for "7-D Ad Spend" and one for "7-D Leads" in my main "Google Ads" base.
Next I created a script that runs twice for each of those fields (4 in total):

1. On entry creation
2. On entry update

This is the script:

let config = input.config();

if (!config.tableName || !config.dateField || !config.inputField || !config.resultField || !config.numDays) {
    console.error("Error: Please provide all required configuration parameters.");
    return;
}

let table = base.getTable(config.tableName);
let query = await table.selectRecordsAsync();

for (let record of query.records) {
    let date = record.getCellValue(config.dateField);
    let valueInput = record.getCellValue(config.inputField);

    if (date !== null && valueInput !== null) {
        let dateRangeStart = new Date(date);
        let dateRangeEnd = new Date(date);
        dateRangeStart.setDate(dateRangeStart.getDate() - (config.numDays - 1)); // Subtracting (n - 1) to include the current day

        let sortedRecords = query.records.slice().sort((a, b) => {
            return new Date(a.getCellValue(config.dateField)) - new Date(b.getCellValue(config.dateField));
        });

        let lastNRecords = sortedRecords.filter(r => {
            let recordDate = new Date(r.getCellValue(config.dateField));
            return recordDate >= dateRangeStart && recordDate <= dateRangeEnd;
        });

        let sum = lastNRecords.reduce((acc, r) => {
            let value = r.getCellValue(config.inputField);
            return acc + (value !== null ? value : 0);
        }, 0);

        await table.updateRecordAsync(record, {
            [config.resultField]: sum
        });

        console.log(sum);
    }
}

The script uses the input config. So, I needed to setup the fields used in the script. Those are:

• tableName (The table inside the base your field lives in. In this case "Google Ads")
• dateField (The field you store your date value in. Here it is "date")
• inputField (The value you want to get the sum of. This is either "Ad Spend" or "Leads" in this case)
• numDays (Can be any number. Here I am using "7" since I want to get the sum of the last 7 days)
• resultField (The field that you want to use as output. Mine is called "7-D CPL")

You don't need to change anything inside of the code itself. The only thing you need to adjust is the automation for each field. Keep in mind you need them twice per field (once for creating and once for updating the field).

Here you can see the setup in my automation:


Last step was to create a third field in my main "Google Ads" base called "7-D CPL". This is a formula field that divides the two values.

IF(
    AND({7-D Ad Spend}, {7-D Leads}),
    {7-D Ad Spend} / {7-D Leads},
    BLANK()
)

This formula then calculates the moving average (7 days) for our CPL value.

After you set this up, you can hide the fields used for calculating the sum (7-D Ad Spend) and (7-D Leads) since they are only needed for calculation.

[jsep · Accepted solution]

The only thing I would change is to move the sortedRecords out the main loop, this only needs to be done 1 time and not for every record. This will make the script more efficient removing unnecessary work.

config = input.config();

if (!config.tableName || !config.dateField || !config.inputField || !config.resultField || !config.numDays) {
    console.error("Error: Please provide all required configuration parameters.");
    return;
}

let table = base.getTable(config.tableName);
let query = await table.selectRecordsAsync();
let sortedRecords = query.records.slice().sort((a, b) => {
  return new Date(a.getCellValue(config.dateField)) - new Date(b.getCellValue(config.dateField));
});

for (let record of query.records) {
    let date = record.getCellValue(config.dateField);
    let valueInput = record.getCellValue(config.inputField);

    if (date !== null && valueInput !== null) {
        let dateRangeStart = new Date(date);
        let dateRangeEnd = new Date(date);
        dateRangeStart.setDate(dateRangeStart.getDate() - (config.numDays - 1)); // Subtracting (n - 1) to include the current day

        let lastNRecords = sortedRecords.filter(r => {
            let recordDate = new Date(r.getCellValue(config.dateField));
            return recordDate >= dateRangeStart && recordDate <= dateRangeEnd;
        });

        let sum = lastNRecords.reduce((acc, r) => {
            let value = r.getCellValue(config.inputField);
            return acc + (value !== null ? value : 0);
        }, 0);

        await table.updateRecordAsync(record, {
            [config.resultField]: sum
        });

        console.log(sum);
    }
}

[vierless_julian]

True! I updated this now and it still works great 🙂