Automate KPI Tracking: autofill Entries that are considered "Completed" within the month, compare to selected Targets

[Conor_Peleman]

Hello, I am trying to see if I can do some higher level (and automated) KPI tracking using Airtable. See the following image:

Here’s what I want to stop doing manually: Tracking per month “Job Completions”.

For some background, I have the “Schedule” tab that contains what we call “Jobs”. These “Jobs” each have their own fields that have automated timestamps when they are started and completed, as well as other information about them. We list our jobs as numbers (ie 505, 525, 530, 541, 531, 538, in 1 above).

To start with my KPI I hand-select the “Target Jobs” (see 1 above) from the Schedule that I would like to see completed in that month. Then (in 2 above), you can get the number of jobs targeted using a count field.

Now, (in 3 above), I would like jobs that are actually completed within this specific month to be autofilled in the completed “lookup” tab. Then I can count them, divide them by the target, and get a completion percentage KPI for the month.

My best indicator of a completion: Inside the schedule, for each job, there is an automated date field that gets timestamped when a completion is triggered. So I’m trying to pull from that timestamp as an indicator of a completion within the month.

This would be great to figure out because its one less thing I have to manually track.

I believe I need to do an automation with a script. Im not sure though.

Thanks in advance.