Looking for an Airtable Expert to Organize Monthly Lead Data

[Katon]

I'm working with Airtable and I want to populate information into the Data table and create a simple automation to it monthly.

This Data table is supposed to show a monthly overview of multiple data points from the Dpto. Comercial table (Commercial table). I want to have a formula inside the Data table showing these columns: Show-Up Rate %, Cancelled Calls %, Close Rate Based on Show-Up %, Close Rate based on Booked Call %, and Disqualification % (Information which can be obtained from the Commercial table.

I need help for a way to create multiple columns inside the Data table with formulas that tackle each of the points described above dynamically, for every month.

No need to start from scratch - there are columns already.

No need for interfaces - we just care about the correctness of the data.

Points to Consider:

For example, the Show-Up Rate % should probably be created this way: When and IF lead has "Asistencia" (Show Up) column marked as "Si" (Yes) it means it's a show-up and it should be divided by "Llamada Agendada" (Booked Call) -> Maybe this should be improved to remove inconsistencies, but this is the general idea for every column other column mentioned above.

[Mike_AutomaticN]

Hey @Katon!

Please feel free to reach out via private message, or schedule a call using the link below. I’d love to help out!

Mike, Consultant @ Automatic Nation

[TheTimeSavingCo]

In the Commercial table, try creating a formula field that will output the month year value of each data record, e.g. `June 24` or something and then create an automation that pastes the value from that formula field into the linked field to the Data table.  This'll create one record per month in the Data table and link them to all the appropriate records in the Commercial table. 

With that done, you can now create conditional fields (https://support.airtable.com/docs/conditional-counts-lookups-and-rollups) to get you the count of things you want, for example you could conditionally filter by the 'Show Up' column being marked as 'Yes' like you mentioned, and you'd be able to calculate the % with a formula field

This is how it would look put together:

And this is the rollup field setup:

Link to base

---

I'm pretty active on the forums so you can get a grasp of how I work from my activity here as well as some reviews, and I've got a bunch of example projects that you can find here!

If you think I could help, here's a link to schedule a free half hour call to see if we're a good fit!

Best of luck either way!

[Katon]

I want to thank you both! Especially to TheTimeSavingCo. Fortunately right after I sent the message we found the solution, but I want to still thank you for so much details, absolutely outstanding how you made an example base, I greatly appreciate it and will definitely contact you for future projects given your readiness and diligence!