code_snippets/2_StandardSQL_for_BigQuery.sql at master · SarahTTAN107/code_snippets · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- some snippets for handling BQ
-- WITH statement

WITH visitors AS
  (
  SELECT
    COUNT(DISTINCT fullVisitorID) AS total_visitors
  FROM
  `data-to-insights.ecommerce.web_analytics`
  ),

purchasers AS
  (
  SELECT
    COUNT(DISTINCT fullVisitorID) AS total_purchasers
  FROM
    `data-to-insights.ecommerce.web_analytics`
  WHERE
    totals.transactions IS NOT NULL
    )

select
  total_visitors,
  total_purchasers,
  total_purchasers/total_visitors as conversion_rate
from
  visitors, purchasers;

   -- Identify duplicate rows
SELECT
  COUNT(*) AS num_duplicate_rows,
  *
FROM
  `data-to-insights.ecommerce.all_sessions_raw`
GROUP BY
  fullVisitorId,
  channelGrouping,
  time,
  country,
  city,
  totalTransactionRevenue,
  transactions,
  timeOnSite,
  pageviews,
  sessionQualityDim,
  date,
  visitId,
  type,
  productRefundAmount,
  productQuantity,
  productPrice,
  productRevenue,
  productSKU,
  v2ProductName,
  v2ProductCategory,
  productVariant,
  currencyCode,
  itemQuantity,
  itemRevenue,
  transactionRevenue,
  transactionId,
  pageTitle,
  searchKeyword,
  pagePathLevel1,
  eCommerceAction_type,
  eCommerceAction_step,
  eCommerceAction_option
HAVING
  num_duplicate_rows > 1;


  -- count number of unique visitors/ count unique values:
  SELECT
  COUNT(*) AS product_views,
  COUNT(DISTINCT fullVisitorId) AS unique_visitors
FROM
  `data-to-insights.ecommerce.all_sessions`;