How to left join both custom dimensions and transactions

Question

I have left joined values from custom dimensions (user id and merchants). I would also like to add transactions. Could I get some advice on how to add that into this query? Or do I have to change the structure completely? This is how I tried it out, but I get a syntax error that says it expects an end input where the FROM statement is.

merchants.merchant_Id,
otherMerchants.merchant_id AS otherMerchants,
merchants.market AS market,
COUNT (merchants.transactions) AS transactions,
COUNT(DISTINCT merchants.userId) users
FROM(

SELECT 
#här hämtar vi alla userIds och merchantIds
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 4) merchant_Id,
(SELECT VALUE FROM UNNEST (customDimensions) WHERE INDEX = 8) market,
hits.transaction.transactionId) as transactions

 FROM `qliro-66ee8.128389777.ga_sessions_20210628`
 UNNEST (hits) AS hits
) merchants

LEFT JOIN 

(
SELECT 
#här hämtar vi alla userIds och merchantIds igen för att kunna nyckla ihop det
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 4) merchant_id,
(SELECT VALUE FROM UNNEST (customDimensions) WHERE INDEX = 8) market,
hits.transaction.transactionId) as transactions

 FROM `qliro-66ee8.128389777.ga_sessions_20210628`
 UNNEST (hits) AS hits
) otherMerchants

#nycklar på att det är samma user samt inte samma merchant för att inte få dubbelräkning
#nycklar på att det är samma user samt inte samma merchant för att inte få dubbelräkning
ON merchants.userId = otherMerchants.userid
AND merchants.merchant_Id != otherMerchants.merchant_id
AND merchants.transactions = otherMerchants.transactions
AND merchants.market = otherMerchants.market

GROUP BY 1,2,3

Answer 1

I think you're very close! You should do something like this

SELECT
merchants.merchant_Id,
otherMerchants.merchant_id AS otherMerchants,
merchants.market AS market,
SUM(merchants.transactions) AS transactions,
COUNT(DISTINCT merchants.userId) users
FROM(

SELECT 
(SELECT VALUE FROM UNNEST(t1.customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(t1.customDimensions) WHERE INDEX = 4) merchant_Id,
(SELECT VALUE FROM UNNEST (t1.customDimensions) WHERE INDEX = 8) market,
count(distinct hits.transaction.transactionId) as transactions

 FROM `qliro-66ee8.128389777.ga_sessions_20210628` t1,
 UNNEST (hits) AS hits
 GROUP BY 1,2,3
) merchants

LEFT JOIN 

(
SELECT 
#här hämtar vi alla userIds och merchantIds igen för att kunna nyckla ihop det
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 4) merchant_id,
(SELECT VALUE FROM UNNEST (customDimensions) WHERE INDEX = 8) market

 FROM `qliro-66ee8.128389777.ga_sessions_20210628`
) otherMerchants

ON merchants.userId = otherMerchants.userid
AND merchants.merchant_Id != otherMerchants.merchant_id
AND merchants.market = otherMerchants.market

GROUP BY 1,2,3

Edit: missing GROUP BY statement in query