Metrics for Custom Dimension Values

February 02, 2015
By Bounteous
SELECT
     Customer_Type, 
     COUNT(fullVisitorId) AS Users, 
     SUM(totals.transactionRevenue)/1000000 AS Revenue,
     SUM(totals.transactionRevenue)/1000000/COUNT(fullVisitorId) AS Revenue_per_User
FROM 
     (SELECT
          fullVisitorId,
          totals.transactionRevenue,
          LAST(IF(
              hits.customDimensions.index=2,
              hits.customDimensions.value, 
              "Regular")) WITHIN RECORD as Customer_Type
     FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])
GROUP BY Customer_Type