Help in Automating an Aggregated Report

[gfondu]

Hi everyone,

I'm relatively new to Airtable, and I need to automate a simple report that aggregates metrics within an Airtable base then emails it to users. 

Here's some examples of the performance metrics I want to calculate:

• Total A IDs generated (there are multiple kinds of IDs)
• A ID "win" rate (i.e. when IDs = A,  and winner = TRUE)
• CTR increase per min
• Most winning ID B/C/D -- (ex: D by XX% over B and by XX% over C)
• Total testing time: XXX mins
• Avg testing time: XXX mins/test

I've setup the initial framework of the automation (i.e. sourcing the table and emailing it users), but I'm struggling to aggregate the metrics that I want sent out. Right now, my rollups are only working on each row, instead of aggregating the entire column. When I try adding a SUM or COUNT formula at the bottom of each column, it doesn't work. Any assistance is much appreciated! 

[TheTimeSavingCo]

Could you provide access to an example base with your data structure?  It's difficult to provide assistance for this without it

Are you saying you have a table where each record is a single ID, it's linked to all your data, and it's not rolling up properly?

[ScottWorld · Accepted solution]

@gfondu 

When emailing aggregations or creating formulas based on aggregations, Airtable only allows aggregations through a linked record field.

So, if you want to aggregate across multiple rows, you would need to choose a “master record” that has all of those other rows linked to it.

Then, you can use a Rollup field to get the aggregations you want.

Airtable’s interfaces also offer some onscreen aggregations for charts and graphs, but that can’t be sent in an email.

Otherwise, if you need to email aggregations from unlinked rows, that is only possible with Javascripting or using no-code tools like Make’s advanced automations & integrations for Airtable. Make offers numeric aggregators, and Make can also send your Airtable data to other apps, such as Excel or Databox or Tableau.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld