Organizing Data - By Year?

Hi @K_Sandum - I think it depends upon your use case, but as a rule of thumb I would tend to not have a base per year, but for each record to have a year field and then create views filtering by year if you want to focus on a specific set of records. This also helps if you want to report across years or compare years (but you may not need to do this of course).

JB

1 Like

Given that Airtable has a relatively low operating ceiling in terms of table size, the question of scale should be addressed before any storage approach is considered.

1 Like

I manage/design the airtable setup for a PR & Marketing company. I would, in general, counsel against a base by year situation. They track extensive metrics for the marketing programs by month, year, marketing representative, client, and the type of media hit (ranked).

In order to get the reporting we want, however, I’ve setup integration with Google Sheets - and a Google script runs a couple times a day to pull any new data and update their reports.

Airtable itself is simply too limited in its presentation and reporting. This provides a far more robust mechanism for looking at numbers. Plus, from a couple airtable views, I can pull just the data needed for the various reports.

2 Likes

Agree 100%.

Often, organizations (and information system creators) conclude that Airtable is ideal and therefore, should be fit for almost every IT challenge.

Airtable is indeed a wonderful user-facing tool that provides an elegant experience in all things information. But there are many things it cannot do nor should we try to force it to be more than it can be.

Yep - it’s [a] perfect combination for even very extensive reporting tasks.

1 Like

Eventually I would like to get there. Linking to sheets or excel for reporting etc.

1 Like

This is where we are going with my client. I pull a similar type of dataset as your linked article shows and generate both visual charts and a couple internal and external sheets.

We are also looking to create emails with data - my client currently shares certain items using the “Send Record” feature. This, of course, results in numerous clients requesting access to AT because the link is embedded in the email.

I love AT for its ease/speed of setup. I find a number of things very frustrating or strange in their absence.

Thank you everyone!! I will use one base.

Start with some R&D (ripoff and duplicate) and then program in CP (cut & paste). :wink:

There are some decent examples online - I’ll be publishing something shortly as well.

Any information on how you do this would be helpful. Does it work both ways? (Updating airtable updates sheets?) I actually prefer the idea of data entry in airtable (Using forms) but need data for visual reporting. The “blocks” look a little limited.

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.

1 Like

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. :wink: 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).
1 Like

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!. :wink:

@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) -

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.

1 Like

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. :wink:

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.

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.

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

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

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?

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.

Thanks everyone. Got it.