Skip to content
About me
Share
Explore

Note

-- Big project for SQL
-- Link instruction: https://docs.google.com/spreadsheets/d/1WnBJsZXj_4FDi2DyfLH1jkWtfTridO2icWbWCh7PLs8/edit#gid=0


-- Query 01: calculate total visit, pageview, transaction and revenue for Jan, Feb and March 2017 order by month
#standardSQL
select
format_date('%Y%m', parse_date('%Y%m%d',date)) as Month,
sum(totals.visits) as visits,
sum(totals.pageviews) as pageviews,
sum(totals.transactions) as transactions,
sum(totals.totalTransactionRevenue) as revenue
from `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where date between '201701' and '201704'
group by month
order by month

-- Query 02: Bounce rate per traffic source in July 2017
#standardSQL
select
trafficSource.source as source,
sum(totals.visits) as total_visits,
sum(totals.bounces) as total_no_of_bounces,
sum(totals.bounces)*100/sum(totals.visits) as bounce_rate
from `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where date between '20170701' and '20170731'
group by source
order by total_visits DESC

-- Query 3: Revenue by traffic source by week, by month in June 2017
#standardSQL
select
'Week' as time_type,
format_date('%Y%V',parse_date("%Y%m%d",date)) as time,
trafficSource.source as source,
sum(totals.totalTransactionRevenue) as revenue
from `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where date between '20170601' and '20170631'
group by source, time
union all
select
'Month' as time_type,
format_date('%Y%m',parse_date("%Y%m%d",date)) as time,
trafficSource.source as source,
sum(totals.totalTransactionRevenue) as revenue
from `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where date between '20170601' and '20170631'
group by source, time
order by revenue DESC

--Query 04: Average number of product pageviews by purchaser type (purchasers vs non-purchasers) in June, July 2017. Note: totals.transactions >=1 for purchaser and totals.transactions is null for non-purchaser
#standardSQL
with purchase_user as(
select
format_date('%Y%m',parse_date("%Y%m%d",date)) as month,
avg(totals.pageviews) as avg_pageviews_purchase
from `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where (date between '201706' and '201708') and totals.transactions >= 1
group by month
),
non_purchase_user as(
select
format_date('%Y%m',parse_date("%Y%m%d",date)) as month,
avg(totals.pageviews) as avg_pageviews_non_purchase
from `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where (date between '201706' and '201708') and totals.transactions is null
group by month
)

select
month,
avg_pageviews_purchase as avg_pageviews_purchase,
avg_pageviews_non_purchase as avg_pageviews_non_purchase
from purchase_user p
join non_purchase_user n on p.month = n.month
order by month



-- Query 05: Average number of transactions per user that made a purchase in July 2017
#standardSQL
select
format_date('%Y%m', parse_date('%Y%m%d',date)) as Month,
safe_divide(sum(totals.transactions),count(distinct fullVisitorId)) as Avg_total_transactions_per_user
from `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
where totals.transactions >= 1
group by Month;

-- Query 06: Average amount of money spent per session
#standardSQL



-- Query 07: Products purchased by customers who purchased product A (Classic Ecommerce)
#standardSQL



--Query 08: Calculate cohort map from pageview to addtocart to purchase in last 3 month. For example, 100% pageview then 40% add_to_cart and 10% purchase.
#standardSQL


image.png

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.