Automatical generation of connected table

[Viktoriya_Ivano]

Hello!
I’m creating a database, where I’m trying to analyze the results of my advertising campaign. I push the data to my main table via Airtable API. Among other things, my data contains the ad id and user name, who was clicked on this ad. One ad may be clicked by many users, one user may click only one ad (one-to-many relation). How can I automatically create a table, where I will have all unique ids of ads in one field, in the second field - all user names, who clicked on the ad with the number from the first field, in third - number of such users? Also, I have the ad budget and I need to divide it on the number of users who clicked this ad

[Justin_Barrett]

Welcome to the community, @Viktoriya_Ivanova! :grinning_face_with_big_eyes: It sounds like you might have a good start, but the workflow you describe doesn’t leave you with the data in a very organized system for the kind of reporting you desire.

What I recommend is a three-table system:

[Ads] - This table only contains ads, organized by their ID.
[Users] - This table only contains users, organized by their username.
[Clicks] - This would be a junction table, with each record linking to one ad and one user based on the data you’re receiving.

You would build your [Ads] table in advance, creating enough records for all the ads you’re running. Your API system would then take a single click report and do several things:

1. Search for that user in the [Users] table. If the user doesn’t exist, a record is added and the resulting record ID is captured for use in step 3. If the user exists, collect their record ID.
2. Search for the ad by its ad ID in [Ads], and collect its record ID when found.
3. Add a record to the [Clicks] table, linking to the user and ad using their respective record IDs.

As you add more records to the [Clicks] table, each ad in [Ads] will have more incoming links. Count the links to see how many users clicked on a given ad, which will give you a value to use in your budget calculations. To see all users who clicked on the ad, create a lookup field in [Clicks] that pulls in the username using the user link in each record, then add a rollup in [Ads] to aggregate all usernames.