Trying to calculate CSAT score using Airtable (formula, rollup, linked records, count)

[maedhbhveed]

Hi 👋 I'm not an expert with formulas, but I think I'm overthinking this / making it more complicated than it needs to be.

I have a CSAT (customer satisfaction score) form in Typeform that is being automatically sent to Airtable. What I'm trying to do is:

• a) calculate the CSAT score (# of 4/5 star ratings, divided by # of total responses, multiplied by 100)
• b) have it calculated every calendar week (so there should be a new rating every Monday)

I can do this manually for now, but I want a way that each week, people can look and see what the score is, without anyone having to manually calculate it.

I'm totally lost, although I think I am overthinking this. Any ideas? 😅

I've attached screenshots of the set-up currently. 

• We have two sources for the feedback form, so there's one table for each of those.
• Then I have the 'Score' table, which has 5 records, one for each rating (1-5 stars), plus another one for 'TOTAL' which I added in my efforts to calculate the score (it can be deleted if not needed).
• There's a fifth table for tagged feedback but I don't think that's relevant right now.

EDIT: would it make more sense to run a script once per week? 🤔

[Databaser · Accepted solution]

You could add a new table with 1 record and link the records "5 stars" and "4 stars" to it once. In that new table, you could rollup the "editor # of stars" and "dashboard # stars". Make the sum in a formula field. Roll up the "total # of x star" field. Finally, create a formula field: " {total of 4 and 5 stars} / {total stars} " and give it a percentage format. 

You could create a formula with a time trigger that takes that percentage and puts it in another field every Monday. 

Something like that?

[maedhbhveed]

It took me a while to get my head around it but it worked! Thank you so much for your help!

[Databaser]

"You could create a formula with a time trigger that takes that percentage and puts it in another field every Monday." > should have been "an automation" 🙂

[maedhbhveed]

Haha thanks, I actually started playing around with the automations not long after my last reply. Thanks again so much, I really appreciate it!