Best way to auto-link across multiple tables when 500+ rows are auto-generated daily?

[Jake_Hoover]

Hi all!

New to Airtable here. We’re considering using Airtable for a few clients to serve as a CDP (customer data platform). In this case, we’re merging 4 data sources: 1) website conversions, 2) CRM, 3) contracts/invoices, 4) NPS/surveys.

For all 4 of these data sources, there is 1 shared field: Unique ID.

I had planned on posting each data source via Airtable’s very well documented API to a table. So, we have 4 tables in 1 base. The website conversion data source will generate a few hundred rows every day.

My ideal scenario would be for these rows to auto-link across the 4 tables as a matching Unique ID row is added to the other tables.

Example: Today, the customer converts on the website, and they are added to table 1, website conversion data. Tomorrow, they enter the CRM, and are added to table 2. A link between this customer row on table 1 and table 2 is automatically created. A week later they sign the contract and are invoiced, and are added to table 3. A link between the customer row on tables 1, 2, and 3 is automatically created. A month later they fill out the NPS survey, and are added to table 4. A link between all 4 tables is automatically created.

Reading through the community forums, @JonathanBowen describes 2 methods here. Essentially, #1 is copy/pasting data from the Unique ID field into a Link field, and #2 is doing something similar, but with Zapier. Both of these sound doable, but I’m wondering if there is a more common/“standard” approach to this use case. I’m partial to route #2 I suppose, as it removes the opportunity for human error.

Another option would seemingly be to post all of the data from these 4 data sources to 1 table. Because all of our data sources have a matching Unique ID, and the data is submitted sequentially (we could never have a customer in the CRM that did not convert on the website), we could just have 1 table with 100+ columns. From this, I suppose we would just create different views to be able to look at this information in a more curated fashion.

With all of that being said, I’d love the community’s insight on how you might approach this use case. Do any of the 3 methods described above seem optimal? What’s behind door #4?

Side note- I was surprised to not see a lot of chatter around using Airtable as a CDP. Have I not dug deep enough? Are there key pieces of functionality not present in Airtable, that most CDPs have? Loving the simplicity & customizability of Airtable, and at $20/user/mo it is incomparable to CDPs which start at a $3-10k/mo entry point.

[JonathanBowen]

Hi @Jake_Hoover - the two methods I talked about were very much in the no-code/low-code space and accessible to all. But, if you’re already planning to use the API for loading the data, then I would definitely recommend this as the tool to connect your 4 data sources. Definitely possible to have a process the finds records in table B and connects them to the matching records in table A.

On the issue of 4 tables or 1 mega-table, my instinct is to go for 4 “data loading” tables and create a 5th table that connects the records, creates the views and so on and is the “working” table. But that is based on a preference for keeping the loaded data “as is” in case of the need for audit or issue resolution - obviously, I don’t have any understanding of your data, set-up, working practices etc, which would influence the approach.

Jake_Hoover:

I was surprised to not see a lot of chatter around using Airtable as a CDP

Yep - not seen too much of that either. Think there’s a lot of potential to make bigger apps in Airtable if you use the API and the new scripting block.

JB

[Jake_Hoover]

Hi @JonathanBowen, thanks so much for the quick response! Much appreciated.

I took a cursory look over the API, and did not see anything recorded on how we could leverage the Linking feature of Airtable. Do you have any other background/directions you could point me in?

Re: 4 tables vs. 1 mega-table, thank you for the insight! I agree, conceptually it is far more clean. Creating a 5th table to pull all of the data together is definitely the goal-- and then as you mentioned, creating multiple views from the 5th ‘master’ table.

Bonus question, would we be able to use Airtable to parse values from a single string/field into multiple fields? An example application would be taking a URL with various UTM parameters, and auto-generating various fields

We post to a field called ‘Entry URL’ - airtable.com?utm_source=linkedin&utm_medium=cpc&utm_campaign=better-than-spreadsheets

We then have other fields’ Entry URL Source’, ‘Entry URL Medium’, etc. which would parse the URL to identify the ‘Entry URL Source’ is “linkedin”, Entry URL Medium’ as “cpc”, etc. We could also manipulate this data before we post to Airtable, but this type of application would serve a few other uses cases in addition to the example above.

[JonathanBowen]

Jake_Hoover:

Bonus question, would we be able to use Airtable to parse values from a single string/field into multiple fields? An example application would be taking a URL with various UTM parameters, and auto-generating various fields

Yes, you can do this (to some degree, depending upon your scenario) with Airtable formula fields. Better would be the new Airtable scripting block - see here for example.

But even better, as you’re planning to use the API to load the data, I think is to do this on import and make it an integral part of the data loading process (even if you keep the original field data for reference alongside the split fields).

I’ll post again on the API/linking question.

JB

[JonathanBowen · Accepted solution]

Hi @Jake_Hoover - so to show a simple example, let’s say you’ve got this set-up:

Table1 - a data loading table
Master - the master table

What I want to do is have the ID in Master populated with the common ID (across all data loading tables) then have linked fields which hold the same IDs, but as links to Table 1 (and other data loading tables).

So the end result should look like this:

Populating this manually in the base, then looking at the API docs for the base gives me this to create a record:

curl -v -X POST https://api.airtable.com/v0/YOUR_APP_ID/Master \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  --data '{
  "fields": {
    "Name": "9999",
    "Table1 Link": [
      "recqKN5YMTsnqfFr8"
    ]
  }
}'

So the “Table1 Link” field is an array with the record ID from table 1 (it would be an array of length 1 as we only want one record in table 1 to link with a record in Master - but it still needs to be an array).

I tried this out with a simple Python script and it all works fine. There’s obviously some things you would need to think about, e.g load Table 1 into Master, then Table 2 etc. but you need to check if the Master ID already exists, in which case update otherwise create, and so on, but all doable.

Hope this helps!

JB