SQL Project Google analytics session data
Note: To access data please follow steps below:
- Access console.cloud.google.com/bigquery
- Register for Google Cloud
- Click “Create Project” then name your project (Ex: query data from Google analytics
- Click Create
- In the left side, click SQL workspace
- In search tool, type ‘ga_session’ (click Broaden search to all project if the result don’t appear)
- Now you can use those query command below to explore dataset.
This project include answers (queries) for 08 task questions. Those questions are essential for exploring E-commerce dataset, it include:
• -- Query 01: calculate total visit, pageview, transaction and revenue for Jan, Feb and March 2017 order by month
• -- Query 02: Bounce rate per traffic source in July 2017 (Bounce session is the session that user does not raise any click after landing on the website)
• -- Query 03: Revenue by traffic source by week, by month in June 2017
• -- Query 04: Average number of product pageviews by purchaser type (purchasers vs non-purchasers) in June, July 2017.
• -- Query 05: Average number of transactions per user that made a purchase in July 2017
• -- Query 06: Average amount of money spent per session
• -- Query 07: Other products purchased by customers who purchased product "YouTube Men's Vintage Henley" in July 2017.
• -- Query 08: Calculate cohort map from pageview to add to cart to purchase in last 3 month