Brainstorming how to set up a base for my farming CSA using calendar and automation
Hey @flourishflowers!
This is a great use case for Airtable.
I've strayed away from my tendency to build full bases for people on the forums, but since you're brainstorming, I decided to hop back on and see if I've still got it.
Since I don't have many details on the structure of your overall data, I went ahead and just filled the gaps with how I might imagine your data structure. There's also a lot of stuff I've just made up that may or may not interest or be applicable to you.
(Forgot to include the shared base link. Feel free to pop in here, duplicate the base, and do what you might please with it.)
I built a base with five tables. The first table contains records for each customer. It's important to note that all the data here is generic, sample data.
The table is very straightforward and there's nothing you haven't seen before here.
The next two tables I created are called Subscriptions and Services.
This one might not be relevant at all for your use case. I have included it simply for the sake of this demonstration, as it showcases how one might be able to scale this structure to accommodate a growing set of needs.
We'll start with service records. You can think of these as operating similarly to templates. More specifically, each record here represents a unique "service" that you might offer for your customers. As you can see below, I've simply created three services for monthly, biweekly, and weekly delivery services.
Now that we have the service object, I'll go ahead and create the Subscriptions table.
A subscription is a junction between a service and a customer.
At this point, many people would ask: "why wouldn't you just link a customer to a service?"
Let's presume you have a customer that you have been working with for a couple of months. One day, that customer decides to cancel their account and no longer wants to receive flower deliveries.
Okay. Simple enough, right? All you have to do is something simple like mark the customer status as "Cancelled" or just unlink them from any service records. You're not hurting much at first because you still have a record of their past deliveries.
Now, what happens if that customer comes back to you a few months later and decides to not only begin taking deliveries again, but wants to upgrade from a monthly cadence to a biweekly cadence?
You're going to struggle to keep track of the historical context of your business interactions and your customer history. For many, this seems like a small or non-existent price to pay, but this is a valuable set of data that could provide powerful historical business analytics and reporting.
You suddenly have the ability to report on metrics like the average amount of time some customers go before they cancel. You can track how long a customer has been an active consumer of your services despite putting their agreement with your business on hold a few times over the years.
You can granularly track business and financial performance per subscription.
From a data governance perspective, if you need or want to delete your customer records after a certain amount of time, you can preserve all your delivery records and business history and not sacrifice consumer data protection and ethics.
The next big table is what we've been building up to: Deliveries.
You've already pretty much got this part down. Each record is a unique delivery for a given customer.
I won't dig into the details here since it's pretty self explanatory.
But what we can do is dig into how to automate two actions.
The first set of automations we can configure is email notifications. For the sake of demonstration, we'll assume that we want to send an email notification to our customer when they are one week away from their delivery date.
To do this, I'll create a simple formula on the delivery object that tells us how many days are left until the scheduled delivery date. If the delivery date has passed, then it'll return blank:
Next, we can configure a trigger to fire each time a delivery record's Days Until Delivery field returns a value of 7. I already have the full name of the customer on the delivery object, but I specifically want to insert their first name in the email body, so I'm going to do a quick search for the full customer record.
Now all we have to do is craft a quick little email:
Boom. Again, I'm intentionally keeping this simple for the sake of showcasing the overall structure.
You could set up your automated emails to satisfy almost any requirement here.
Our next set of automations is to handle the automatic creation of our scheduled delivery records so that we can worry about the business and not have to dump time into manual data entry.
If I were actually doing this, I would script it, but for the sake of keeping this a bit more open to people reading that might not be comfortable with working with scripts, here's a no-code way to approach the automatic record creation.
I'll start by creating a scheduled automation trigger that runs weekly on the day of my current week's deliveries. So if I make my weekly deliveries each Monday, I'll have the automation create next week's deliveries today.
I'll then search for any weekly delivery subscriptions that are marked as being active subscriptions:
Next, we can iterate through each subscription and create a new delivery record for each subscription we need to fulfill, and since we are exactly a week away, we can insert the Next trigger time output variable as our delivery date since we know that the next delivery day is also the day that our automation will run.
One issue with this approach is that if you have a rather large number of variances in your schedule, you may end up with an overwhelming number of automations depending on how flexible you want your automated communications to be with your customers, but I digress.
Now, I said that I created five tables, but I've only shown four of them.
I also have a Month table that contains records for each month.
I originally created this table out of your description of working with monthly batches, but once I played around a bit, I realized that there's not really much value added by having this table.
That's not to say that there are none. In fact, if you were doing any sort of financial tracking and reporting, this would prove infinitely valuable in many scenarios.
There's not much reason to keep it, but I don't currently have a compelling reason to axe it from this demo... so it'll stay for now.
