Using Airtable for creating reports based on HubSpot Data

With this article, we would like to share our experience of using Airtable for creating reports and graphs based on HubSpot Data.

Operating as a tech department for one of our clients, each month we received a request to create reports based on Deals and Contacts from HubSpot and the data stored in the client’s portal. As we have already mastered saving the data from HubSpot in Portal, we decided to automate the creation of reports. First, we wrote the logic on the backend, then added a page to the frontend with Filters, Export to Excel Button and finally launched it in production. 

Everything was fine for the first two months… After that, something went wrong. We began to receive requests to add new fields, filters, groupings and so on. Tasks were coming as urgently, and therefore, they were distracting us from our current work. The client had to wait until we finished development, testing and launched in production. 

Our next team meeting involved brainstorming and discussing how to simplify things not only for the client but for ourselves as well. To put the data into Excel was not an option as it could turn into many tables sent by mail. We chose to adopt  Airtable, as it allowed us to do all the necessary data manipulations and draw graphs, plus the client already had experience working with it. 

To start with, we took the functional package airtable-python-wrapper. To simplify code support, we wrote a Wrapper. We also described the logic for bringing the data to the type that is required by the Airtable API, and the logic for linking data from different tables. The only difficulty we encountered was that the creation of tables and fields needs to be done manually, through the Airtable interface. 

As a result of this work we created classes:

1. AirtableWrapper — sets the connection
2. AirtableField
3. AirtableDecimalField
4. AirtableReference
5. AirtableReferenceHasMany

Configuration Example:

DEALS_TABLE = ‘Deals’
CONTACTS_TABLE = ‘Contacts’

TABLES = {
    DEALS_TABLE: {
           ‘id’: AirtableField(‘Id’),
           ‘deal_id’: AirtableField(‘Deal Id’),
           ‘contact’: AirtableReference(‘Contact’, table=CONTACTS_TABLE, pk=‘Id’, key=‘id’),
        ...
    }
    CONTACTS_TABLE: {
           ‘id’: AirtableField(‘Id’),
           name: AirtableField(‘Name’),
           email: AirtableField(‘Email’),
        ...
    }
}

An example of use:

data = {
        ‘id’: 1,
        ‘deal_id’: 0000000001,
    ‘contact’: {
        ‘id’: 1,
        ‘name’: ‘FirstName LastName’,
        ‘email’: ‘email@example.com’,
    ...
     }
...
    }
wrapper = AirtableWrapper(api_key=‘<airtable api="" key="">‘, base_key=‘<database key="">’, table=DEALS_TABLE)
wrapper.insert_or_update(data, pk=‘Id’)</database></airtable>

Examples have been shortened for this article, but the point is clear. As a result of the code execution, the data gets into the Airtable, and the links between the tables are established according to the configuration. As a result, the client has access to data that they can easily process in order to create reports and graphs. The tech department now only has to describe new fields and relationships.
There is no source code in this article. So, if you have any questions, feel free to contact us