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:
- Values are updated
- Date field value is read
- Script finds the last 7 days based on the original date in the updated row (not today)
- Script gets the ad spend and leads values of fields that match this condition
- Values are added and then divided (sum of ad spend / sum of leads)
- 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?
