Google Looker
E-Commerce
Analysis
The data is provided by Google Looker, ranging from August 2019 to November 2023, which was when the project took place.
​
A little overview of the database:
- The business is global as it sees transactions across North and South America, Asia, Europe, and Australia. Among them, the biggest markets by country are America, China, and Brazil.
​
- The ten distribution centers are all based in the U.S.
​
- The trended revenue sees an exponential increase, and 2023 sees the highest revenue in the history of the business.
​
To see more, please visit my Github repository here.
1. Relational Database Using SQL
The original database has seven tables, which we further broken down to three departments: product, marketing and operations.
The key discoveries from this stage are as such:
​
-
The business contains two departments, 2750 brands, and a large amount of individual products. To better understand the distribution, we divided the brands into 100 bins, and the top one bin accounts for 27% of the revenue. Moving on, it'd be beneficial to target these brands in the marketing effort.
​​
-
The marketing funnel of `event_page` goes:
-
Home page --> Department page --> Product page --> Add to Cart --> Purchase page.
-
The trended view shows that customers have increasing website visits at each stage, before traveling down to the purchase page. This indicates that the marketing department can increase targeting frequencies on unique customers.
​
-
Shipping efficiency sees discrepancies among countries with low order values, such as Columbia. This can be further improved by seeking more reliable shipping partners.
Utilizing two tabs, my team and I showcased metrics over several perspectives such as revenue, marketing channels, and shipping time.
To see more, please click here.
​
There are two tabs in the dashboards:
-
Marketing and Product:
-
Utilize filtering tools to filter for the year, quarter, or month of your interest
-
As well, stakeholders can adjust for country and traffic sources (marketing channels)
-
​​
-
Operations:​
-
Using countries and distribution as filters, one can see the status of orders
-
Time series filters can also be applied
-
​