Joining with Refund Data

February 02, 2015
By Bounteous
SELECT
     hits.transaction.transactionId AS Transaction_ID,
     hits.transaction.transactionRevenue/1000000 AS Revenue,
     Refund_data.RefundAmount AS Refunded,
     hits.transaction.transactionRevenue/1000000- Refund_data.RefundAmount AS Net_Revenue
FROM FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910],hits) AS GA_data
JOIN 
    (SELECT TransactionId, RefundAmount
     FROM [google.com:analytics-bigquery:LondonCycleHelmet.refunds_201309]) AS Refund_data
    ON GA_data.hits.transaction.transactionId = Refund_data.TransactionId
WHERE hits.type = "TRANSACTION"