Hi!! I've been having a really rough time trying to solve this but i keep on getting bad Data. The context is i'm trying to join Leads with effective purchases, and i need all the values from the purchases to add on to the matching Social Security numbers on my Table of Leads (or another table alltogether) in order to understand conversion rate values.
The challenge i'm trying to figure out is that in Tables B and C, sometimes the Social security numbers are duplicated because one person can be the buyer for several products, so I can only count the record once or I get crazy conversion rates (Ex: Google shows, for product B, 120% conversion rate with 40 leads, and more than 40 sales because it isnt de-duplicating correctly with "DISTINCT")
What I need to figure out is how to load the following so that the Lead, that has richer data, joins with the sales data from tables B and C.
Here's an example of the data i'm loading:
Table A (LEADS):
SOCIAL SECURITY NUMBER,
Origin Platform (Facebook/Google/ETC),
Name,
Last Name,
If lead is PAID or ORGANIC,
PRODUCT INTERESTED IN (between A and B),
DATE
TABLE B (PURCHASES PRODUCT A):
SOCIAL SECURITY NUMBER (OF PURCHASER),
DATE (OF PURCHASE),
TABLE C (PURCHASES OF PRODUCT B):
SOCIAL SECURITY NUMBER (OF PURCHASER),
DATE (OF PURCHASE).
Can anybody help me solve this? It's eating my brain away.
