Pivoting Custom Dimensions

February 02, 2015
By Bounteous
SELECT
     Sku,
     SUM(IF(Customer_Type="Bronze", Quantity, 0)) AS Bronze,
     SUM(IF(Customer_Type="Platinum", Quantity, 0)) AS Platinum,
     SUM(IF(Customer_Type IS NULL, Quantity, 0)) AS none
FROM
  (SELECT   
       hits.item.productSku AS Sku,
       hits.item.itemQuantity AS Quantity,
       LAST(IF(
            hits.customDimensions.index=2,
            hits.customDimensions.value, 
            NULL)) WITHIN RECORD AS Customer_Type
   FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])
WHERE Sku IS NOT NULL
GROUP BY Sku