This is more of a planning question than a scripting question.
My team has scrapped some amazon data for our client's products and competition brand products. The data is in a BigQuery table that looks like this:
In another table, we have a list of ids that map to either client or competition products, which looks like this:
Both the client_product_id and competition_product_id can be joined to the product_id column in the details table. Joining them is easy but I'm confused on how they should be joined.
The end goal is to get this data ready for a data studio visualisation where I show how each of my client's products is stacking up against the competition products that they have defined as per the second table. The challenges I'm facing is how to prevent duplication of data since some of the competition products are repeated as the visualisation will need to have roll ups of data from the granular product level to the brand level.
Let me know if I can provide any additional information to help find an answer to this question