Looker Studio: Data Visualization in a Pocket

Data Visualization is the practice of presenting your information and data into graphical representation. This can be done through visual elements such as charts, graphs and maps. Data visualization could help you understand the pattern or trend in your data or information. Data visualization could also help you understand the data quicker since it is easier to read, share and understand compared to a written tables or codes of your data.

Looker Studio, or previously called Google Data Studio, is one of the visualization tools that you can use to visualize your data. Google Data Studio is free and cloud-based so you can access it easily whenever you want to turn your data into an easy to read compact form such as chart, graphs or map. Looker Studio is cloud-based, meaning that the reports that you create in Looker Studio are automatically saved into Google Drive, making it easier for you to access it anytime and anywhere.

In this instance we will be using Looker Studio to visualize the data that is provided in the Data Analytics Bootcamp that is provided by MySkill. We will be solving a case given by a marketing team.

In order to be able to access Looker Studio, you would have to have a Google account and sign into that account. Afterwards you can access Looker Studio via this link: https://lookerstudio.google.com

Upon entering the site you would be greeted with this page.

Looker Studio’s Homescreen

You can click on Blank Report to create a new report. Next, you would need to add your data to Looker Studio. You would be greeted with this page upon creating a new report.

Today we will be using Looker Studio to visualize a dataset called “Pakistan’s Largest E-Commerce Dataset” that we can access from Kaggle. We will be using this csv dataset and import it to Looker Studio. We will be using the Google Sheets method to import our data so we can click on that.

Add data via Google Sheet

Since we have the url to the csv file, we can go ahead and click on the url tab and paste said url to the text column that is provided by Google Sheet.

Where to put the url in Looker Studio

Once we have this we can go ahead and click add to add our data into Looker Studio. We will now be greeted with this once we have successfully import our data to Looker Studio.

Now that we have successfully import our data into Looker Studio we can look at the case in a form of a request from a marketing team.

Dear Data Analysts,

Following yesterday’s joint meeting, we will create a dashboard to monitor and evaluate sales achievements for each month. The dashboard will consist of 2 pages and will be created with lookerstudio.google.com.

On page 1, please display:

1. The relationship between Sales Value (before discount), Net Profit, and AOV (average order value).
   Note:
   Net profit = Value Sales (after discount) — (cogs * qty)
   AOV = Value Sales (before discount) / Total Unique Orders.
2. Provide slicer that contains: Order Date, Category, Sales Value, Transaction Value, Payment.
   Note:
   Value Transactions:
   Valid → is_valid = 1
   Not Valid → is_valid = 0
   Payment → payment_method

On page 2, please display:

1. A table that contains:
   Product Name
   Categories
   Before Discount
   After Discount
   Net Profits
   qty
   Customers (unique values)
2. Provide slicers that contain: Order Date, Category, Value Transaction, Payment
3. Scorecard:
   Before Discount
   After Discount
   Net Profits
   Quantity
   Customers (unique values)
   AOV

Make the default view for the time period as the entire year of 2022.

Thank You.

Regards,
Marketing Team

Now that we have received this request from the marketing team we can begin our journey in Looker Studio with the data that had been imported to Looker Studio earlier.

Page 1.1
Relationship between Value Sales (before discount), Net Profit, and AOV (average order value).

A. Calculate Net Profit (after_discount — (cogs*qty_ordered))

We can now head to this part of the page and click on add a field to start calculating the net profit.

Add a field

We can copy and paste the formula for net profit on the formula box and name the field net_profit and go ahead and click save and done. We now have a new data called net_profit.

Creating and Calculating net_profit

B. Calculate AOV (sum (before_discount) / COUNT_DISTINCT (id))

We can click on add a field again to add and calculate the data for average order value (AOV). put the formula in the formula box and name the field as aov.

Create and Calculate aov

C. Add a chart.

Now that we have the necessary data for Page 1 we can go ahead and click on Add a chart that is provided in the ribbon.

Note: Combo chart is the farthest left on the line chart category

Since we will be looking at the relation between sales value, net profit and AOV (Average Order Value) we will need a suitable chart for it. In this case I will be using combo chart to better visualize the relation in our data. Once we click on combo chart we will be greeted with this chart and a chart panel right next to our data panel.

We will be using this chart panel to choose the data that we would like to include in our chart by dragging the data that we have from data panel to our chart panel.

1. Data Source: Gives us control over the data that we will be using
2. Data Range Dimension: Give us control over the timeframe of the selected components, the current page, or even the report as a whole.
3. Dimension: Acts as the x axis of the chart
4. Metric: Acts as the y axis of the chart.
5. Sort: Sorts our visualization based on a particular variable.
6. Filter : (a function that is not captured in the screen capture image) Acts as a filter to reduce the data that is shown in the visualization.

In this case we put order_date as the dimension as we are trying to monitor the monthly data. We use Year Month on order_date to display the data monthly.

We use aov, net_profit and before_discount as the metric since we are trying to monitor these data. We will also be sorting the chart by order_date. We can now go ahead and click on the style tab to better visualize our data.

Shows the points on Series#1 (AOV) and put it on right axis

To smooth the line on Series#1

To display the Y-Axis on the left and right of the chart

Once we are done with everything we will now be greeted with this combo chart on our page.

Combo chart to see the relation between sales value, net profit and AOV (average order value)

We can also change the name of the X-Axis and Y-Axis on our chart for a better visualization through the small box that is located on the left of our data.

The pencil allows us to make changes

Combo chart after name changes

Page 1.2

In this part we are asked to add a slicer that contains Order Date, Category, Sales Value, Transaction Value, and Payment. To do this we can click on Add a control on the ribbon.

Add a control in Looker Studio’s ribbon

Since we want to add an order date slicer, we should add 1 date range slicer to the page. We can do this by clicking on the Date range control. We would also like to add 3 Drop-down list for the other slicers.

Adding Slicers to our page

1. We need to set the data on the date range to 2022. We can do this by clicking on the Date range control on our page and set the start date to 1 January 2022 and the end date to 31 December 2022.
2. We should change one of the slicers to Category. Go ahead and use the control panel on the right to change the Control Field to Category.
3. Change the other slicer to Payment Method. Use the control panel to change the Control Field to Payment Method. We should also change the metric to before_discount since our chart is trying to look at the sales value that uses before_discount as its value.
4. Next we would like to add Transaction Value as our Control Field. However we would need to add a new field to our data in order to get the transaction value. Therefore we will add a field under the name transaction_value and use this formula in the formula box:

IF (is_valid=1, "Valid", 
IF(is_valid=0, "Not Valid", "Undefined"))

We can now add transaction_value to our Control Field and set the name as Transaction Value.

Now that we have put the metrics in, our page should look like this.

Solution to Page 1

Page 2.1

We can now move on to our second page. In this page we are asked to display a table and add slicers just like in Page 1. We can go ahead and click Add page on the ribbon in Looker Studio to add a new page to our report.

Click on Add a chart and choose the table on the far left to add a table to our page.

Add a chart ribbon

We will be greeted with this table on our page.

Adding a table to Page 2

We can now go ahead and set the table as asked by the marketing team. In this table we would like to include Product Name, Categories, Before Discount, After Discount, Net Profits, qty, and Customers (unique values).

1. Set the dimension by adding sku_name (prpduct name) and category.
2. Set the metric by adding before_discount, after_discount, net_profit, qty_ordered, and customer_id.

We will now have this table on our page. Remember, you can always change the name of the columns to give a better visualization of the data by clicking on the little pencil on the left side of the data.

Modified Table for Page 2

Page 2.2

Now we need to add slicers to our page just like we did in Page 1. These slicers are supposed to consists of Order Date, Category, Value Transaction, and Payment. We pretty much need the same slicers as the ones that we have in Page 1. You can do the steps just like in Page 1 or you can just copy and paste the slicers into Page 2 with Ctrl+C and Ctrl+V. Once we have done that we can now see the slicers in Page 2

Add slicers to Page 2

Page 2.3

We need to add scoreboard to our page. These scoreboards are supposed to contain the data in before_discount, after_discount, net_profit, qty_ordered, customer_id, and aov (average order value).

In order to do this we can click on Add a chart. We can go ahead and click on the scoreboard option on the right to display a more compact numbers on our page.

Add a scoreboard to the page

Since we need 6 scoreboard we can go ahead and add 6 scoreboards to our page. Once we have added the scoreboards, our page should look like this:

Adding scoreboards to Page 2

We need to change the information in the scoreboard to the desired data. To do this click on the scoreboard and head on to the Chart Panel.

Use Chart Panel to control the data

You can go ahead and make changes on the metric to match our desired data that are Before Discount, After Discount, Net Profit, Qty Ordered, Customer ID, and AOV (Average Order Value). Remember that you can change the displayed name on the scoreboard with the pencil on the left of the data.

After we have made changes to the scoreboard, our page should look like this:

Solution for Page 2

We have now fulfilled the request given by the marketing team. We have used Looker Studio to visualize our data with charts and table. We use slicers so that it will be easier for us to look for certain information in our visualization. We have also added scoreboard to give information of some of the data requested by the marketing team.

Looker Studio or Google Data Studio is a fairly easy tool to use for Data Visualization. After learning about it on the Data Analytics Bootcamp from MySkill, I realize that Looker Studio is pretty compact and easy to use. It truly feels like a pocket size tool given the simplicity of Looker Studio.

I have written about SQL and Python prior to this. Feel free to check it out.

References:

https://www.tableau.com/learn/articles/data-visualization