Data Visualization with Google Looker Studio (Case Study E-Commerce)
Introduction
Data visualization is the graphical representation of data and information using visual elements such as charts, graphs, and maps. The primary goal of data visualization is to convey information effectively and efficiently to the audience, making it easier for them to understand patterns, trends, and relationships within the data. With advancements in technology and the field of data visualization, more and more organizations and companies are using data visualization as a tool to support decision-making, identify opportunities, and communicate analytical findings. Data visualization can also aid in discovering hidden patterns, analyzing complex data, and telling stories visually. Thus, data visualization is highly significant in the context of data analysis and decision-making across various fields, including business, academia, and research.
In this article, we explore an engaging case study focused on the leading e-commerce platform . This case study forms part 3 of the My Skill Data Analysis Bootcamp series. Using Google Looker Studio, we reveal the capabilities of this advanced tool in extracting insightful data on customer behavior, sales trends, and market dynamics. Through this case study, our goal is to demonstrate the practical use of Google Looker Studio, its influence on decision-making, and its potential to foster growth and success within evolving e-commerce sector.
Analysis Tools
The process of data visualization is carried out through the Google platform known as Looker Studio : https://lookerstudio.google.com/
Data Analysis with Google Looker Studio is a comprehensive tool that facilitates data exploration, analysis, and visualization. It is a business intelligence and data visualization platform designed to help users gain insights from their data. Looker Studio allows users to connect to various data sources, including Big Query, and create interactive and customizable reports and dashboards.
Key features of Data Analysis with Google Looker Studio include:
- Data Connectivity: Looker Studio enables users to connect to a variety of data sources, making it easy to analyze and visualize data from different platforms.
- Data Exploration: Users can explore and understand their data through an intuitive interface. Looker Studio provides the ability to drill down into details, apply filters, and uncover meaningful insights.
- Visualization Tools: The platform offers a range of visualization tools, such as charts, graphs, and dashboards, allowing users to present data in a visually appealing and informative way.
- Customizable Reports: Looker Studio allows users to create highly customizable reports and dashboards tailored to their specific needs. This flexibility enables users to focus on the metrics and KPIs most relevant to their business.
- Collaboration: The platform supports collaboration by allowing users to share reports and dashboards with team members. This promotes a collaborative approach to data analysis and decision-making.
- Query Language (LookML): Looker Studio utilizes LookML, a modeling language that helps define the relationships in the data model. This allows for consistent and standardized data definitions across the organization.
- Security and Access Controls: Looker Studio provides robust security features and access controls, ensuring that sensitive data is protected, and users have appropriate permissions.
In summary, Data Analysis with Google Looker Studio empowers users to perform advanced data analysis, create compelling visualizations, and derive actionable insights from their data, contributing to informed decision-making within organizations.
Problem Statement
After a joint meeting within the company, a team of data analysts has been tasked with creating a dashboard. This dashboard will comprise 2 pages designed for monitoring and assessing monthly sales performance. The dashboard must meet the following criteria:
- The marketing team wants to observe the progress of campaigns in 2022 and understand their trends. They need an explanation of these trends and suggestions for actionable insights to inform future decisions. The dashboard includes :
Page 1 :
a. The relationship between Value Sales (before discount), Net Profit, and AOV (average order value).
Note:
- Net profit = Value Sales (before discount) — (cost of goods sold * quantity)
- AOV = Value Sales (before discount) / Total Unique Ordersb. There are slicers for Order Date, Category, Sales Value, Value Transaction, and Payment.
Note:
- Value Transaction:
- Valid → is_valid = 1
- Not Valid → is_valid = 0
- Payment → payment_method
Page 2 :
It can display:
a. A table containing:
- Product Name
- Category
- Before Discount
- After Discount
- Net Profit
- Quantity
- Customer (unique value)b. There is a slicer for: Order Date, Category, Value Transaction, Payment
c. Scorecard:
- Before Discount
- After Discount
- Net Profit
- Quantity
- Customer (unique value)
- AOV
2. During 2022, display the mobile & tablet category that has been paid using Jazzvoucher. How many quantities and customers are there?
3. Create a chart based on dashboard number 2.
Thank you.
Regard,
Tim Marketing
Datasets
The data used is from Tokopedia (not actual data). The explanation of the dataset is as follows (df):
Reading Dataset
Reading and examining the dataset using BigQuery :
Issue 1 — Page 1
Dear Data Analyst,
Following up on yesterday’s joint meeting, we will be creating a dashboard to monitor and evaluate monthly sales achievements. This dashboard will consist of 2 pages and will be created using lookerstudio.google.com
1. The marketing team wants to track the progress of the campaigns in 2022 and understand the trends. Please provide an explanation and suggest call-to-action steps for the team to make informed decisions moving forward.
a) The relationship between Value Sales (before discount), Net Profit, and AOV (average order value). Note:
> Net Profit = Value Sales (before discount) — (cogs * qty)
> AOV = Value Sales (before discount) / Total Unique Order
b) Slicers for Order Date, Category, Sales Value, Value Transaction, and Payment. Note:
> Value Transaction:
—Valid → is_valid = 1
—Not Valid → is_valid = 0
> Payment → payment_method
Solution to Issue 1 :
Open the Looker Studio platform, then choose a blank report to create a new report:
From the Looker homepage, we will proceed to create a data source using a data connector from BigQuery. This will establish a connection for the data source that will be utilized in the data visualization report page:
Next, select the data to be used and then choose CONNECT (Google Looker will connect to the file to create a dataset):
Looker will automatically detect the data types for each column when the dataset is created.
Adding Fields
The next step is to add fields for Net Profit, AOV (Average Order Value), and Value Transaction:
Net Profit :
before_discount - (cogs * qty_ordered)AOV :
SUM(before_discount)/COUNT_DISTINCT(id)Value Transaction :
CASE WHEN is_valid=1 THEN "Valid"
WHEN is_valid=0 THEN "Not Valid" END
Modify Data Type
Next, Modify the data types for after_discount, base_price, before_discount, cogs, discount_amount, net_profit, price, and AOV to Currency (IDR — Indonesian Rupiah (Rp)):
Creating Visualizations
Page 1 :
PAGE 1 — STEP 1
Create a Combo Chart (Bar chart and Time Series) and perform SETUP and STYLE :
PAGE 1 — STEP 2
Create a title with the name “Sales Dashboard Performance” and create a slicer order date range control, then perform SETUP and STYLE :
PAGE 1 — STEP 3
Create controls (drop-down lists) for category, value transaction, and payment method, then perform SETUP and STYLE:
PAGE 1 — STEP 4 (Additional)
Create scorecards for quantity, profit, value sales, and AOV (Average Order Value), then perform SETUP and STYLE:
PAGE 1 — STEP 5 (Additional)
Create a table displaying category, sales value, net profit, and AOV, then perform SETUP and STYLE:
PAGE 1 — STEP 6 (Additional)
Create a chart that provides a clearer view of the Quantity curve, then perform SETUP and STYLE:
PAGE 1 — STEP 7 (Additional)
Create a table displaying value sales, net profit, and AOV (Average Order Value) per month, then perform SETUP and STYLE:
Issue 1 — Page 2
We would like to request your assistance in creating a comprehensive report and visualizations based on the following criteria:
a) A table containing the following information:
> Product Name
> Category
> Before Discount
> After Discount
> Net Profit
> Quantity
> Customer (unique value)b) With slicers for Order Date, Category, Value Transaction, and Payment.
c) Additionally, include a scorecard for:
> Before Discount
> After Discount
> Net Profit
> Quantity
> Customer (unique value)
> AOV (Average Order Value)Thank you for your prompt attention to this request. We look forward to your insights and visualizations.
Regards, Marketing Team
Creating Visualizations
Page 2 :
PAGE 2 — STEP 1
Create a table containing Product Name, Category, Before Discount, After Discount, Net Profit, Quantity, and Customer (unique value), then perform SETUP and STYLE:
PAGE 2 — STEP 2
Create slicers for Order Date, Category, Value Transaction, and Payment, then perform SETUP and STYLE:
Create slicers for Order Date, Category, Value Transaction, and Payment, then perform SETUP and STYLE:
PAGE 2 — STEP 3
Create scorecards for Before Discount, After Discount, Net Profit, Quantity, Customer (unique value), and AOV (Average Order Value), then perform SETUP and STYLE:
Issue 2
For the year 2022, display data for the mobile & tablet category with payments made through Jazzvoucher. Provide information on quantity and the number of unique customers.
Solution to Problem 2
During the year 2022, for the mobile & tablet category, there is one customer who has purchased this product with a quantity of 1000 units. The payment status has occurred, and the payment was made using Jazzvoucher.
Issue 3
Create a chart based on the data from the second dashboard
Solution to Problem 3
Based on the data provided, further investigation is needed regarding this transaction. Is it indeed true that there was a sale of 1000 units by a single customer? Or is there a data anomaly/input error in the system? This resulted in a rapid surge in sales in September 2022.
Analysis and Conclusion
From the data visualization presented on page 1, we observe the fluctuation in Sales, Net Profit, and AOV throughout 2022. These metrics demonstrate an increasing trend in the initial 1 Years. From January to May, the trend appears to be unstable, with sales experiencing fluctuations, followed by a drastic decline and subsequent increase. August saw a significant drop, and then in September, there was a remarkable spike in sales. In September, there was a significant spike (Value Sales: 559.37 M, Net Profit: 45.06 M, and AOV: 7.66M), followed by a gradual decline until the end of the year.
A positive AOV indicates that the company is effectively maximizing revenue per purchase, fostering growth. Conversely, a negative AOV suggests that revenue falls short of expectations, necessitating strategic adjustments.
Based on the data provided, further investigation is needed regarding the upward trend in September.
And from the data visualization presented on page 2 we can see during the year 2022, for the mobile & tablet category payment with Jazzvoucher, there is one customer who has purchased this product with a quantity of 1000 units. Is it indeed true that there was a sale of 1000 units by a single customer? Or is there a data anomaly/input error in the system?
It is recommended to review the transactions associated with the spike using transaction evidence.
Furthermore, given the fluctuating company profits, there is a necessity for enhancing campaigns. This can be accomplished by introducing bundling schemes or providing complimentary delivery for large purchases of economical or budget-friendly items or ensuring customer protection for their acquisitions of high-value or luxury items.
In summary, the Looker dashboard serves as more than just a platform for showcasing numerical data; it’s a tool for uncovering insights crucial for tackling relevant issues. This analytical resource enhances comprehension of the company’s performance by presenting agreed-upon metrics, thereby facilitating the development of a more effective, data-driven strategy.
| Reference :
“Fundamentals of Data Visualization” by Claus O. Wilke
Full stack Intensive Bootcamp 14 for Data Analyst by MySkill.id (Data Visualization Part 1 and 2)
Looker Studio Documentation: https://support.google.com/looker-studio/
| Thanks to :
Achmad Nafila Rozie (Tutor Data Visualization from My Skill)
Lorenzo Mozes (Mentor from My Skill)
