Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Automations Data Formulas
Solved
Jump to Solution
1879 4
cancel
Showing results for 
Search instead for 
Did you mean: 
maedhbhveed
5 - Automation Enthusiast
5 - Automation Enthusiast

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? 🤔

1 Solution

Accepted Solutions
Databaser
12 - Earth
12 - Earth

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?

See Solution in Thread

4 Replies 4
Databaser
12 - Earth
12 - Earth

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?

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

"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" 🙂

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