SELECT
CONCAT(fullVisitorId, CAST(hitTime AS String), '-', hitNumber) AS _id,
TIMESTAMP(DATETIME(Year_Current, Month_Current, Day_Current, Hour, Minutes, Seconds)) AS timeStamp,
fullVisitorId as GA_ID,
-- Fake CUSTOMER ID
CONCAT('3E-D4-',fullVisitorId, '-1W-93F' ) as customerID,
Page,
Landing_Page,
Exit_Page,
Device,
Browser,
MarketingChannel,
TrafficSource,
TrafficMedium,
-- Enhanced Ecommerce
TransactionID,
CASE
WHEN EcommerceActionType = '2' THEN 'Product_Detail_Views'
WHEN EcommerceActionType = '3' THEN 'Adds_To_Cart'
WHEN EcommerceActionType = '4' THEN 'Product_Removes_From_Cart'
WHEN EcommerceActionType = '5' THEN 'Product_Checkouts'
WHEN EcommerceActionType = '6' THEN 'Product_Refunds'
ELSE
NULL
END
AS Ecommerce_Action_Type,
-- Entrances (metric)
SUM(CASE
WHEN isEntrance = TRUE THEN 1
ELSE
0
END
) AS Entries,
--Pageviews (metric)
COUNT(*) AS Pageviews,
-- Exits
SUM(
IF
(isExit IS NOT NULL,
1,
0)) AS Exits,
--Bounces
SUM(CASE
WHEN isExit = TRUE AND isEntrance = TRUE THEN 1
ELSE
0
END
) AS Bounces,
-- Unique Purchases (metric)
COUNT(DISTINCT TransactionID) AS Unique_Purchases,
-- Product Detail Views (metric)
COUNT(CASE
WHEN EcommerceActionType = '2' THEN fullVisitorId
ELSE
NULL
END
) AS Product_Detail_Views,
-- Product Adds To Cart (metric)
COUNT(CASE
WHEN EcommerceActionType = '3' THEN fullVisitorId
ELSE
NULL
END
) AS Adds_To_Cart,
-- Product Removes From Cart (metric)
COUNT(CASE
WHEN EcommerceActionType = '4' THEN fullVisitorId
ELSE
NULL
END
) AS Product_Removes_From_Cart,
-- Product Checkouts (metric)
COUNT(CASE
WHEN EcommerceActionType = '5' THEN fullVisitorId
ELSE
NULL
END
) AS Product_Checkouts,
-- Product Refunds (metric)
COUNT(CASE
WHEN EcommerceActionType = '7' THEN fullVisitorId
ELSE
NULL
END
) AS Product_Refunds
FROM (
SELECT
-- Landing Page (dimension)
CASE
WHEN hits.isEntrance = TRUE THEN hits.page.pageTitle
ELSE NULL
END
AS Landing_page,
-- Exit Page (dimension)
CASE
WHEN hits.isExit = TRUE THEN hits.page.pageTitle
ELSE
NULL
END
AS Exit_page,
hits.page.pageTitle AS Page,
hits.isEntrance,
hits.isExit,
hits.hitNumber as hitNumber,
hits.time as hitTime,
date as Fecha,
fullVisitorId,
visitStartTime,
device.deviceCategory AS Device,
device.browser AS Browser,
channelGrouping AS MarketingChannel,
trafficSource.source AS TrafficSource,
trafficSource.medium AS TrafficMedium,
hits.transaction.transactionId AS TransactionID,
CAST(EXTRACT(YEAR FROM CURRENT_DATE()) AS INT64) AS Year_Current,
CAST(EXTRACT(MONTH FROM CURRENT_DATE()) AS INT64) AS Month_Current,
CAST(EXTRACT(DAY FROM CURRENT_DATE()) AS INT64) AS Day_Current,
CAST(EXTRACT(DAY FROM DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)) AS INT64) AS Day_Current_Before,
CAST(FORMAT_DATE('%Y', PARSE_DATE("%Y%m%d", date)) AS INT64) AS Year,
CAST(FORMAT_DATE('%m', PARSE_DATE("%Y%m%d",date)) AS INT64) AS Month,
CAST(FORMAT_DATE('%d', PARSE_DATE("%Y%m%d",date)) AS INT64) AS Day,
CAST(EXTRACT (hour FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS Hour,
CAST(EXTRACT (minute FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS Minutes,
CAST(EXTRACT (second FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS SecondS,
hits.eCommerceAction.action_type AS EcommerceActionType
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) AS hits
WHERE
_table_suffix BETWEEN '20170101'
AND '20170331'
AND totals.visits = 1
AND hits.type = 'PAGE'
)
GROUP BY
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14
ORDER BY 2 DESC
