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:
- 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.
- Search for the ad by its ad ID in
[Ads], and collect its record ID when found.
- 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.
