Analysis Tools

The data visualization process is executed through Google Looker Studio: https://lookerstudio.google.com/

Google Looker Studio is a comprehensive platform that facilitates data exploration, analysis, and visualization. It serves as a business intelligence and data visualization tool aimed at enabling users to extract insights from their data. Looker Studio allows connections to various data sources, including BigQuery, and supports the creation of interactive and customizable reports and dashboards.

Key features of Google Looker Studio include:

1. Data Connectivity: Users can connect to multiple data sources, simplifying the analysis and visualization process across different platforms.
2. Data Exploration: The intuitive interface allows users to delve into their data, applying filters to unearth significant insights.
3. Visualization Tools: A variety of visualization options, such as charts and dashboards, enable users to present data engagingly and informatively.
4. Customizable Reports: Users can craft reports and dashboards tailored to their specific requirements, emphasizing the metrics that matter most to their business.
5. Collaboration: The platform encourages teamwork by allowing users to share reports and dashboards, fostering a collaborative approach to data analysis.
6. Query Language (LookML): Looker Studio employs LookML, a modeling language that standardizes data definitions across the organization.
7. Security and Access Controls: Robust security features and access permissions ensure that sensitive data is safeguarded.

In essence, Google Looker Studio empowers users to conduct advanced data analyses, create compelling visualizations, and extract actionable insights from their data, thereby supporting informed decision-making in organizations.

Problem Statement

Following a joint meeting, a group of data analysts has been assigned to develop a dashboard. This dashboard, consisting of two pages, is aimed at tracking and evaluating monthly sales performance. The dashboard must fulfill the following requirements:

1. The marketing team seeks to monitor the progress of campaigns in 2022 and comprehend their trends. They require explanations for these trends along with actionable recommendations for future decisions. The dashboard includes:

   Page 1:

   • a. The correlation 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 Orders
   • 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

   Page 2:

   • a. A table displaying:
     • Product Name
     • Category
     • Before Discount
     • After Discount
     • Net Profit
     • Quantity
     • Customer (unique value)
   • b. Slicers for Order Date, Category, Value Transaction, and Payment.
   • c. Scorecard:
     • Before Discount
     • After Discount
     • Net Profit
     • Quantity
     • Customer (unique value)
     • AOV

2. For the year 2022, showcase the mobile and tablet category purchases made with Jazzvoucher. What are the quantities and customer counts?

3. Generate a chart based on the second dashboard.

Thank you.

Regards, Tim Marketing

Datasets

The dataset utilized is from Tokopedia (not actual data). The dataset description is as follows (df):

Reading Dataset

The dataset is read and analyzed using BigQuery:

Issue 1 — Page 1

Dear Data Analyst,

Following up on yesterday’s joint meeting, we will create a dashboard to monitor and evaluate monthly sales performance. This dashboard will consist of two pages and will be developed using lookerstudio.google.com.

1. The marketing team wants to track the progress of the campaigns in 2022 and understand the trends. Please provide explanations and suggest actionable steps for the team to make informed decisions moving forward.
   • a. The correlation 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:

To begin, open Looker Studio and select a blank report to create a new report:

From the Looker homepage, create a data source using a data connector from BigQuery. This establishes a connection for the data source that will be used in the data visualization report:

Next, choose the data to be used and select CONNECT (Google Looker will link to the file to create a dataset):

Looker will automatically identify the data types for each column upon dataset creation.

#### Adding Fields

The next step involves adding 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, adjust 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

Add a title, "Sales Dashboard Performance," and create a slicer for 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 showing category, sales value, net profit, and AOV, then perform SETUP and STYLE:

#### PAGE 1 — STEP 6 (Additional)

Generate a chart that provides a clearer depiction of the Quantity curve, then perform SETUP and STYLE:

#### PAGE 1 — STEP 7 (Additional)

Create a table that displays value sales, net profit, and AOV (Average Order Value) by month, then perform SETUP and STYLE:

Issue 1 — Page 2

We request your assistance in developing a comprehensive report and visualizations based on the following criteria:

a) A table containing the following details: - Product Name - Category - Before Discount - After Discount - Net Profit - Quantity - Customer (unique value)

2. Include slicers for Order Date, Category, Value Transaction, and Payment.

c) Additionally, incorporate 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 eagerly await your insights and visualizations.

Regards, Marketing Team

#### Creating Visualizations

Page 2:

#### PAGE 2 — STEP 1

Generate a table featuring 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 and tablet category with payments made through Jazzvoucher. Provide information on quantity and the number of unique customers.

#### Solution to Problem 2

In 2022, for the mobile and tablet category, there is one customer who purchased this product with a quantity of 1000 units. The payment was successfully completed using Jazzvoucher.

Issue 3

Create a chart based on the data from the second dashboard.

#### Solution to Problem 3

Analyzing the provided data raises questions regarding the transaction. Is it accurate that a single customer purchased 1000 units? Or is there a data anomaly or input error in the system? This resulted in an unusual surge in sales in September 2022.

Analysis and Conclusion

The visual data on page 1 reveals fluctuations in Sales, Net Profit, and AOV throughout 2022. These metrics indicate a positive trend in the initial months, with fluctuations from January to May followed by a significant drop and subsequent recovery. In September, a notable spike in sales occurred (Value Sales: 559.37 M, Net Profit: 45.06 M, and AOV: 7.66M), followed by a gradual decline towards year-end.

A positive AOV suggests that the company is effectively maximizing revenue per transaction, driving growth. In contrast, a negative AOV indicates underperformance, necessitating strategic adjustments.

Further investigation into the sales spike in September is warranted.

The visual data on page 2 indicates that in 2022, for the mobile and tablet category paid for with Jazzvoucher, a single customer bought 1000 units. Is it indeed accurate that a single customer made this purchase? Or is there a data anomaly or input error in the system?

It is advisable to review transactions related to the spike using transaction evidence.

Given the company's fluctuating profits, there is a pressing need to enhance marketing strategies. This could involve introducing bundling offers or providing free shipping for large purchases of budget-friendly items, or ensuring customer protection for high-value or luxury item acquisitions.

In conclusion, the Looker dashboard serves as more than just a tool for displaying numbers; it is instrumental in uncovering vital insights necessary for addressing pertinent issues. This analytical resource improves understanding of the company's performance by presenting agreed-upon metrics, ultimately aiding in the formulation of a more effective, data-driven strategy.

References:

“Fundamentals of Data Visualization” by Claus O. Wilke

Full stack Intensive Bootcamp 14 for Data Analyst by MySkill.id (Data Visualization Parts 1 and 2)

Looker Studio Documentation: https://support.google.com/looker-studio/

Acknowledgments:

Achmad Nafila Rozie (Data Visualization Tutor at My Skill)

Lorenzo Mozes (Mentor at My Skill)