Questions about Airtable functionality

[Megan_Dampier-M]

Our primary lead source comes from Canada Post neighborhood mailings. Right now we are sending about 300K flyers/month across 4 divisions but will be increasing to 450K plus come the new year. We have over 20 areas a week and multiple flyer types which we are looking for a more stable way to keep track of.

I am wondering a couple of key things before I dive more heavily into mastering this myself.

1. As we schedule new routes, is a recurring date scheduling function something that can be coded in? From what I have come across, this is not something I’ve seen others need to use, but for us it would be essential. Ideally as a mailing route is generated you could schedule it one of 3 ways.
   a: Say the route has 10,000 homes, and you want to send them out over 4 weeks on mondays starting X date, it splits up the routes (taking into account the varying number of homes) and generates a plan with an assigned flyer type.
   b: You want to send at least 2000 flyer per week starting on X date and the number of weeks are generated
   c: you are able to slot in data at will from the weekly view to fill in holes

2. As I mentioned we utilize a tool provided by Canada Post. Are we able to program our Airtable in such a way where it communicates with Canada Post, generates the necessary paperwork, and saves the data. You can export .csv from this tool. Ideally we are looking for it to function by inputting the route data so we can schedule it, and then at a later date recalling a portion of that data, using it to complete a form which generates a PDF of the paperwork. Sometimes the data varies on the date we prepare the paperwork from the date it was initially planned, this number would need to be recorded as well. Are we able to have this tool function in Airtable without needing to go to an external site?

3. Are we able to incorporate a map view? Ideally generating postal codes. We would love to be able to plot the data and know how many times we’ve mailed a certain area without combing through data.

4. I would also be adding in invoicing, inventory, and CRM data but this is much less complicated then the above queries.

5. How complex of a project would this be? I have begun to create a layout and wrap my head around how we want this to function and look, so there will be a jump off point as well as a ton of data.

Any hits, tips, tricks are valued!

[ScottWorld]

Welcome to the community, @Megan_Dampier-McNeil! :slightly_smiling_face:

I’d love to hear what other people have to say about this, but in my own personal opinion, I think that Airtable is the wrong tool for this job.

Not sure if you’re keeping track of all the addresses on your route or not, but Airtable has a limit of 50,000 records PER BASE on the Pro Plan, and that limit only increases to 100,000 records PER BASE on the Enterprise Plan, which costs a whopping $3,000 per month.

The other things you listed are either impossible in Airtable or extremely challenging to achieve in Airtable — and they would require extra costs by depending on external tools.

Airtable is a very basic & very simple program — it has very little interface customizability in the way that you’re looking for, and it’s closer to a spreadsheet than a true relational database. Airtable doesn’t even have the built-in ability to automate the creation of PDF’s or automatically generate printed reports — without turning to external tools.

Personally, I would not recommend Airtable for your project.

For only $200 dollars per month, you could actually get a real enterprise-level relational database system like FileMaker that has no limitations at all (it can handle billions of records), provides you with a blank canvas that can be designed in any way that you want it to be designed (including mapping routes), and can handle everything that you want to do easily & effortlessly — all within the platform itself, without using any external tools at all.

I am both an Airtable Consultant and a Certified FileMaker Developer. If you’d like to chat more about your project, please feel free to contact me through my website at scottworld.com.

[Bill_French]

Megan_Dampier-McNeil:

Right now we are sending about 300K flyers/month across 4 divisions but will be increasing to 450K plus come the new year.

@ScottWorld is spot on - 450,000 of anything is not where Airtable shines. You should fear Airtable in this case.

However…

Before going out on a limb and saying Airtable is either right or wrong for your grand challenge, it makes sense to probe the requirements, which I must say are very well described and represent a thoughtful consideration of effort.

Megan_Dampier-McNeil:

As we schedule new routes, is a recurring date scheduling function something that can be coded in?

Yes. The mention of “route” suggests geometries and this - in turn - suggests computations which also implies script. Since Airtable supports many scripting avenues, by definition, it supports geometries, although not with many native features.

Megan_Dampier-McNeil:

Are we able to incorporate a map view?

Yes - there is a Map App but it is limited in many ways. There is also a way to build maps that serve as custom apps. I’m building exactly such an app for a medical research team on Covid. Pretty much anything you can imagine in location science is possible with Airtable and a custom app. Brief example…

Megan_Dampier-McNeil:

We would love to be able to plot the data and know how many times we’ve mailed a certain area without combing through data.

A simple matter of data and script.

Megan_Dampier-McNeil:

How complex of a project would this be?

Typically, custom apps are costly, but it’s difficult to say how much without a little more precision in the requirements. In location science - like data science - the costs rise in concert with the complexities of the geometries and desired user experience. Without knowing all the details, your narrative suggests to me a custom app might be affordable.

Megan_Dampier-McNeil:

there will be a jump off point as well as a ton of data.

This is a red flag in the Airtable realm. However, we need to understand the data before ruling Airtable out. I have some customers with 500,000 records in Airtable that are compressed into small JSON elements. It is possible to store many hundreds of details in a single row using one field. Doing this is not advised in many cases, but works well in some cases and location data is typically one of those cases.

For example, the polygon that describes the state of Texas is massive in scale. Those little squiggly lines on the border create about a thousand points that Airtable needs to display the state boundaries. But, does it really need to live in Airtable? Can it be blended with an external data source in real-time? The answer is yes to both questions. By compressing the polys we can store 1000+ locations for one state in Airtable.

[Megan_Dampier-M]

Hi Scott,

Ideally we would like to have all of our info in one place, but could we link airtable to various google sheets as well? We plan to do 450K per month, but we don’t record every address. Only the postal codes and the mailing route as defined by Canada Post. Ie:M9C recalls 6318 individual homes and 29 unique routes with the number of homes in each varying from route to route. In this case we are mailing 19 one week, 7 the next and there are 3 that don’t fit our demographic so we skip. We would only record the routes we use. Right now we are sitting at about 5000 records in that sense.

However as our business continues to grow we need to considered what our needs will be in the future. I had a feeling that the answer would be more custom application, but I’m so glad these forums exist so we can brainstorm together! I will reach out to you on your website and am eager to hear how you think you could help :slightly_smiling_face:

[ScottWorld]

Bill.French:

Yes - there is a Map App but it is limited in many ways

Yes, and one thing to note about the Maps App is that it is also known to excessively & erroneously chew through Google Maps data, which ends up costing lots of extra $$$ in Google API charges. Airtable has yet to fix this bug.

[ScottWorld]

Megan_Dampier-McNeil:

I will reach out to you on your website and am eager to hear how you think you could help :slightly_smiling_face:

Hi Megan,

Great! I will chat more with you when I receive your email! :slightly_smiling_face:

Thanks for the additional details, and yes, you can link Airtable to Google Sheets (with some known limitations that we can discuss), so that could be a good possibility as well.

[Bill_French]

ScottWorld:

and one thing to note about the Maps App is that it is also known to excessively & erroneously chew through Google Maps data, which ends up costing lots of extra $$$ in Google API charges.

Indeed, you would never want to plot much data with the integrated maps app. If you integrate any degree of location features inside Airtable, you need to do it independently of encoding services. Mapbox integration is open-source and free for the most part.

[Bill_French]

Megan_Dampier-McNeil:

could we link airtable to various google sheets as well?

Yep - and there are no serious record limitations because Google also provides a deep integration with Firebase (billions) and Google Cloud Platform (trillions).

Moving a lot of data between Airtable and Google has limitations but primarily related to Airtable itself.

And as I said about a custom app designed to integrate maps into Airtable, the sky is the limit - example showing vector lines to each county where Covid deaths are at least 50% higher than other counties in the same state. This is roughly 3400 computations and vectors produced on the map along with 45,000 poly lines - all in about 3 seconds to render.