I wanted to calculate product detail views, but I got more than double product detail views in query result than google anlaytics interface. Could you help me what is wrong with my query?
SELECT
--cast(cast(year as varchar) || '-' || cast(month as varchar) || '-' || cast(day as varchar) as date) as date_day,
--device.deviceCategory AS device_category,
prod.v2ProductName,
viewid,
count( prod.v2ProductName) as product_detail_views
from table_name,
UNNEST(hits) AS nested_data(hit),
UNNEST(hit.product) AS nested_data(prod),
UNNEST(customdimensions) AS nested_data(cd)
WHERE
hit.eCommerceAction.action_type = '2'
and MONTH=1 AND YEAR=2023 and DAY = 1
AND (viewid in ('96019039'))
AND (prod.isImpression IS NULL OR prod.isImpression = FALSE)
--AND prod.v2ProductName = 'kontakt'
group by 1,2;
What should be the accurate query?