I am currently trying to create a flat table in Google Big Query using Google Ads data that has already been pushed into Big Query. My goal is to obtain the gclid from the raw Google Ads data and map it to our CRM so that we can match it with our CRM data. However, I am facing a problem while using the SELECT command to join the clickState table with the campaignBasicState table. I am seeing multiple clicks associated with every single gclid. To investigate this issue further, I tried to check if the gclid is unique using the distinct count and normal count function. But both counts returned the same result. I am perplexed by this issue because according to the Google support page for Gclid, a unique gclid is generated every time a user clicks on an ad. Therefore, I am trying to understand how multiple clicks are being associated with a single gclid. Below is the screenshot for reference screenshot
"I have written the below query to access GclId data corresponding to clicks:
SELECT GclId, SUM(Clicks) as Clicks FROM gcp-bi-reports.google_ads.p_ClickStats_5382334228
GROUP BY GclId ORDER BY Clicks DESC
LIMIT 1000
But I was supposed to see only one click per GclId.