Background
A general view of Google Merchandise Store’s history and business
Google Merchandise Store mainly sells
Google-branded merchandise as well as YouTube brands, Android branded apparel
Clothes,stationaries,drinkware,bags,etc
The company:
Founded in 2015
Headquartered California in Mountain View(USA)
An e-commerce platform established to facilitate the sale of Google products
Objectives
The obejects of the project are:
Understand how Google Merchandise Store(GMS) is doing and derive insights on the performance of the online store
Conduct Extract-transform-load (ETL) process to convert Google Analytics data of GMS (1.4 million records) into readable format
Design KPIs that reflect GMS website performance and create dynamic dashboards with relative KPIs to explore monthly web traffic situation by different channels and different pages
Methodology
Task 1: Investigate the source data
Data Source:
The raw data comes from GA_Sessions (2016 -2017), I used Google BigQuery to conduct data query as the data source.
Levels:In Google BigQuery, there are three levels of data recorded as follows
Users：Visitors who have initiated one session with website or app within a specific period of time
Sessions: A group of user interactions with website that take place within a give time frame
Hit: An interaction that results in data being sent to Analytics
Google Analytics Table Schema: Apart from levels, some data schemas are recorded in nested structure as Tree Program.
Thus, I need to use Unnest() function to unnest nested schema in BigQuery
Task 2: Identify Key Performance Indicators (KPIs) required in the dashboard
In Task 2, I selected relative KPIs based on business requirements and objectives:
KPI – Pageviews
Definition: how many times a page has been opened within one session
Importance:
particularly useful when looking at traffic to individual website pages and measuring the performance of individual pages;
In most cases, pageviews give an indication of how popular a post or page is
In detail, Pageviews can be divided into following 2 KPIs
Total Pageviews: how many times a page has been opened including multiple loading within one session
Total Unique Pageviews: how many times a page has been opened, multiple loading within one session should be counted as 1
KPI – Total Time on Page
Definition: Seconds spent on a page
Importance:
Time on the page indicates if website content is relevant to visitors
Short time on a page indicates that the page is irrelevant or visitors have less interest; if users spend too long time on page without converting, which may mean they feel confused about the website intention (e.g.what product or service they offer)
Likewise, the KPI can be divided as follows:
Time on Non-exit page
Definition: the time difference between time landing on Next page and time on current page
Formula: time on Next Page - time on current page
Time on Exit page
Definition: the time difference between time on Last interaction and time on exit page
Formula: time on Last interaction - time on exit page
KPI – Total Sessions
Definition: How many times a session begins with a page
Importance:
Total sessions reveal how users truly interact with the website.
Business can identify opportunities or problems within their website that can be optimized for improved performance down the line.
KPI – Total Bounces
Definition: How many time a session has only one interactive hit
Importance:
Total Bounces might indicate that the page content is irrelevant, or confusing to your site visitors. A high bounce on a web page would be alarming because that means people are only viewing that page alone, then clicking away, which may lead to losing out on conversions.
Using Total Bounces, we can derive a new KPI: Bounce Rate, which is most commonly used when measuring website performance
Bounce Rate Formula: （Total Bounces/Total Sessions）%
Task 3: Prepare data using SQL queries and ETL
In Task 3, I mainly completed Data Preparation and ETL in the following steps:
Conduct SQL query in Google BigQuery (Query)
Create a Base Table containing basic dimensions(time, location, channel group,source etc)
Merge the base table with queried tables of Pageviews, Time on Page,Total Sessions,Total Bounces using Left join
Export BigQuery tables to Google Cloud Storage
Download data to local drive in csv file
The part of the final data is shown in right
（583050 rows × 13 columns)
Task 4: Data Visualization
After ETL process, I loaded data to tableau and created following visuals to interpret selected KPIs:
Also I created slicers for month and year to check each month performance.
Using July 2017 as the example month for selected KPIs:
Visual -- Total sessions
Visual Explanation: In July 2017, the total sessions of GMS is 71812, which is 12.95% more than the sessions of last month
Visual -- Avg Unique Pageviews
Visual Explanation: The average unique pageviews indicate what overall unique pageviews look like in every month. The above figure shows average unique pageviews in July 2017 is 2667, which has increased by 0.3% compared with that of last month
Visual -- Session and Bounce Rate by devices
Visual Explanation: In July 2017, the most sessions (63%) are completed through desktop, followed by the sessions using mobile device and tablet (32% and 4% respectively). Also, visitors using desktop have the lowest bounce rate.
Visual -- Avg Duration
Visual Explanation: Average Duration is a average metric of total time on website(including total time on page and total time on exit page). In July 2017,
visitors spent 146 seconds on GMS website in average,increasing 7.1% more than that of last month
Visual -- Traffic by Source
Visual Explanation: The figure above shows the sessions by different sources. We can see most sessions came from google search and the second most sessions are derived from direct search in July 2017.
Visual -- Traffic by Location
Visual Explanation: From the above map, we can see, in July 2017, the most sessions were completed in North America, especially in the USA. There was a large number of sessions in Europe as well, ranking the second most.
Visual -- Acquisition by channels
Visual Explanation: The above chart describes the performance of each channel in sessions and pageviews. In July 2017, 52.4% of total sessions came from organic search, 35.3 % larger than the proportion from direct channel. The acquisition via referral channel featured the largest average unique pageviews (4,4 pages in average).
Visual -- Top pages
Visual Explanation: To check the performance of GMS pagepath, I created visuals to show total sessions, sessions month over month and bounce rate for top 10 pages (ordered by total sessions) for each month. In July 2017, apart from homepage, the page of “shop by brand/youtube” featured the most sessions and high bounce rate.In addition, the sessions of “waze baby on board” increased a lot compared with that of last month.
Combining all created visuals, I generated the final dashboard as following preview shows:
Tableau Dashboard Preview (Please click Dashboardfor details)
Insights
After analysis by month using the dashboard, I summarized the following insights
The total sessions kept increasing and pyked in November 2016, then it decreased in a large degree and started to increase a little from June 2017
Users visited 3 pages in average and spent 1.5 mins to 2 mins on GMS website
Increasing number of acquisition volume is attributed to organic search channel
The number of acquisition volume from social channel decreased a lot after November 2016 and increased a little until July 2017
Customers tend to feel interested in men’s shirt, men’s outerwear,drinkware, bags, electronics such as Bluetooth headphone. Especially, men’s shirts are the most popular among visitors.
Increasing number of visitors use mobile device to visit Google Merchandise Store website
Comments