Organizing Data - By Year?

[K_Sandum]

Hello - New to airtable and community. Using this to track marketing projects and corresponding reach numbers. (Also man other fields such as contact, etc etc)

I have a base created but am questioning now how I organized the fields

Should I have a unique base per year?
Or should I have projects indexed by year in one base. (Using filters to calculate numbers per year)

Thoughts would be appreciated. Thank you

[Matthew_Moran]

You can modify data in Airtable using Google Scripts. I do in a limited fashion - more as feedback on reporting. Almost all of our data entry is performed in AT - though you can only really create records using AT forms. They do not provide a mechanism for editing records using forms.

[Bill_French]

Matthew_Moran:

This, of course, results in numerous clients requesting access to AT because the link is embedded in the email.

Follow the money. This is the viral “engine of expansion” used by every PaaS and SaaS on the planet. But it’s a misdirected approach to creating a broader base of users because the outcome of these embedded links creates a mess for the table creators and all sorts of potential breaches and lapses in security. Oddly, all attachments [URLs] in Airtable are public (if you have the link).

These “sharing” misdeeds prove that democratization of IT does not always result in favorable outcomes. :winking_face: However well-intentioned the users may be, Airtable is setting them up for a few headaches and some poorly planned self-managed IT solutions.

These are the recommendations I provide to every client who has adopted, or who is contemplating adoption of Airtable. These points are targeted primarily at enterprises.

• Register (in a common Google sheet) every Airtable Base and it’s purpose.
• Create a role account in G-Suite and a matching role account in Airtable.
• Require users to share all bases created with the role account.
• Develop a simple script process (in the G-Suite role account) that notifies key IT leaders or management that a new base has been created.
• Advise all users to be cautious about sharing and invitations to share with other people (establish specific policies concerning the use of Send options in Airtable).

[Matthew_Moran]

I walked into a messy table. Coming from years of SQL development, one of the challenges with a “Spreadsheet database” is that data is added using columns before adding an appropriate table.

Moving cleaning up data was cumbersome - though exposing the RECORD_ID() for every record in every table allowed me to aggregate and paste linked data to expedite this process.

Your suggestions are good ones. I just wrote a blog about “The Good, The Bad, and the UGLY” of Airtable. Not super technical and not comprehensive, it list some of the aspects I like, dislike, and HATE!. :winking_face:

PULSE INFOMATICS – 2 Dec 19

Airtable is a cloud-based database tool that looks like a spreadsheet. The...

A brief explanation of some of the benefits and pitfalls of Airtable - cloud-based relational database.

[Bill_French]

@Matthew_Moran,

We apparently see the Airtable colors in a similar light. This passage from your article, for example, is spot on and why I have not been a big fan of Zapier or Integromat (both are fine and powerful tools, but they may create serious issues) -

Matthew_Moran:

“What if a user sets up some Zapier automation but we change critical elements of the data. Perhaps their automation continues working but is creating bad data. Furthermore, what if the organization begins relying on that automation - though they have no documented idea why it is happening or with whose account. The user who set it up leaves the company, their account is disabled/removed, and voila - the automation goes away.”

Indeed, tools like Airtable + Zapier (as an example) make it very easy for an enterprise’s business logic to move out to the edge and out of view and in some cases completely out of its control. And the secretion of business logic occurs in many places in Airtable itself.

Consider the formulas alone; these are atomized fragments of a solution that if ever lost, will render the solution worthless. Deleting a single column could result in a disaster for users depending on inaccurate data until the deletion is detected (in ref your no audit trail issue). Airtable is notorious for added fields complexities to hack-around showstoppers as users grope for ways to handle things that aren’t supported natively in Airtable.

One might argue that these issues abound in Spreadsheets. However, the key difference is that Spreadsheets in Google’s G-Suite are fully known and there are audit systems and viable ways to manage the IP known as business logic. Airtable is a free-for-all, and we can’t necessarily blame Airtable or its users - both are well-intentioned innovating actors. We simply need to educate ourselves and mitigate these [potentially] horrific aftertastes of what is [seemingly] a very tasty diet of IT freedom.

Apologies to @K_Sandum and Airtable - I have officially derailed this topic.

[Matthew_Moran]

Agreed - on the issues and derailing the topic. Though, I believe some may find it educational/eye-opening.

Back on track with @K_Sandum. Until something else dictates a change, one table for multiple years.
** and voila! We are back on topic. :winking_face:

[K_Sandum]

Sorry to inundate you with questions. This is not airtable related but since you are in the field of marketing metrics… How do you handle analyzing data that is counted in different bases. For instance web hits are cumulative; some data is yearly. Trying to sort through impressions reach etc. Thank you for you valuable feedback.

[Bill_French]

K_Sandum:

How do you handle analyzing data that is counted in different bases

This question makes the assumption that Airtable is the original source “document” for web hits. In my experience, this is not the case. Web “hits” are a function of the analytics headwaters which is typically Google Analytics and from there the data wends its way into other platforms such as ElasticSearch and Data Studio perhaps, but not Airtable per-se.

Airtable has a low operating ceiling - if you throw 50,000 events at it, it will degrade in performance relatively abruptly. As such,“hits” which are the rawest form of event metrics, is [therefore] not a good fit for Airtable. Aggregates of hits (maybe), but only for analysis and possible limited reporting objectives.

[K_Sandum]

Yes, only aggregate. Just for analysis. Data comes from google analytics (manually)

[Bill_French]

K_Sandum:

only aggregate. Just for analysis. Data comes from google analytics (manually)

Good, so you really have a disparate aggregates problem. :winking_face:

Data is being broadcast across multiple bases. One must ask - why?

Is there some reason the data is being disaggregated or is Airtable serving to be the point where you aggregate the aggregates?

[Matthew_Moran]

I’d have to understand how your bases are structured.

The challenge with metrics that need to be updated over time (hits on an article, instagram post, youtube video, etc.) is ongoing. Look at Coverage book - which updates some metrics dynamically. Instagram is problematic even for them.

I’ve written some tools to that read web-data into Excel, Access, or Google sheets - but, under most of those organizations, writing a tool that does this outside of their API’s runs afoul of their usage agreement.

I’ll plead the 5th on whether I’ve ever scripted such things… though Amazon’s legal department sent me a pointed email regarding a blog entry where I demonstrated how to grab reviewer contact info… um… oops.

However, you could, in theory, write a tool outside of Airtable that updates key metrics in AT.