Aggregating Data Across Mulitple Tables

[Steve_Patterson]

Hi, I am new to Airtable and am working on a property management project.

I’m trying to use Airtable to calculate property rental metrics (e.g. nights stayed, average rate per room, total revenue) by property. Each property (e.g. property 1, property 2, etc) is listed on multiple listing platforms (e.g. Airbnb, VRBO, Expedia, etc). The listing platforms allow me to export stay data, where each record is a stay (e.g. check in date, check out date, nights, total revenue, property, guest, reservation # etc). However, each platform has a different format, so I’ve created a separate table for each platform.

I created a summary table where each row is a property and each column is a metric from one platform (e.g. Airbnb stays [count of rows in Airbnb table that weren’t cancelled], Airbnb total nights [sum of nights field in Airbnb table], Airbnb Revenue [sum of revenue in Airbnb table], same for other platforms). Then I can use a formula to sum the stays, nights, revenue, etc from across all platforms. I tried doing this by creating a property field in the summary table and linking it to the platform tables. I then created a new “roll up” field to aggregate the data by property, but it’s only pulling one instance from each property (e.g. stays all equal 1). Lastly, I had to rearrange the fields in the Platforms table so property was the primary field. Is there a way to link to a non-primary field in another table?

Any tips would be greatly appreciated!

[Justin_Barrett]

Steve_Patterson:

Is there a way to link to a non-primary field in another table?

When linking to records, you’re not actually linking to specific fields. Airtable just shows you the value of the primary field in the linked record, but the link itself is to the record, not the field. With the link in place, you can pull values from any field you want using lookup and rollup fields.

However, I see a bit of an overcomplication with your base:

Steve_Patterson:

I’ve created a separate table for each platform.

I can see how you might have thought that this would make the data collection and aggregation easier, but from my experience (and, from what I gather from your post, yours as well) it’s quite the opposite. Even though each platform has a different format for the data they provide, I’m sure there’s some overlap. For example, all platforms will likely record the check-in date and check-out date. However, for the sake of this example, perhaps Airbnb gives you some data items that the others don’t. From my perspective, that doesn’t warrant making a new table just for Airbnb. Instead, I would keep all transactions for all platforms in a single table, and add fields for capturing data that’s common to all of them. For those unique data items coming from Airbnb, I would add fields to capture them. I would also add a {Platform} field where I can record the platform for the stay. Finally, I would create a view on that table that only shows fields relevant to Airbnb, and similar views for the rest.

When it comes to aggregation, this setup makes the process much easier. For example, the {Airbnb Revenue} rollup would pull from the main stays table, using a condition to only pull from records where the {Platform} field is “Airbnb”. Same for all of the others.

Does that make sense? It means some significant changes to your base design, but in the end, I think you’ll find it a lot easier to aggregate the data points that interest you.