1.4. Live Workshop: Creating an eCommerce Dashboard in Looker Studio - Part 2
Learning Objectives
- Understand the process of creating pages in a Looker Studio report and reusing charts and components on different pages.
- Learn how to create and use parameters and controls in Looker Studio.
- Learn how to perform calculations using custom fields and data blending.
- Understand how to share a Looker Studio report with end users.
Creating Page Two: Cost vs Profit
On the second page of the report, we'll explore more advanced features.
- Copy the logo, date range selector, and e-commerce title from the first page and paste them onto the second page.
- Make the logo and date range selector report level components so they'll appear on all pages.
Adding Parameters and Controls
To allow users to adjust the average order value increase percentage, create a parameter and add a control (either an input box or slider) for it.
- Create a new parameter called "Average Order Value Increase Percentage" with a whole number range from 0 to 200 and a default value of 10.
- Add a slider control connected to the parameter, allowing users to adjust the percentage value.
Now users can adjust the average order value increase percentage, and it's time to use this value in the report calculations.
First, let's copy and paste another scorecard. We want to change the background to green to distinguish the one calculated based on the parameter from the rest. Reset the comparison period to none.
Calculating Projected Average Order Value
We can't find a field called "projected average order value" in our data. We need to create one by calculating it from our existing data. Use this formula to calculate the projected average order value:
(1 + (parameter_value / 100)) * actual_average_order_value
Change the field type from numeric to currency and select US dollars.
Calculating Projected Revenue
Copy and paste the scorecard again. Click on the fx icon to change the function to "projected revenue." We can calculate revenue by multiplying the number of transactions by the projected average order value.
Adding Cost Data from Google Sheets
We need cost data from Google Sheets for this part. Add the Google Sheet as a data source, and start creating scorecards with the costs. Change the data source for each scorecard to the Google Sheet, and adjust the type of the field to currency.
Calculating Total Cost
We don't have a "total cost" field in the data source, so we'll create one at the data source level. The formula for total cost is:
cost_of_goods_sold + ad_management + ad_cost + fixed_overhead + shipping
Calculating Profit and Projected Profit
You might think we can simply subtract total cost from revenue to get profit. However, we can't directly do that because they come from two different data sources. Stay tuned for the next lesson, where we'll explore how to calculate profit and projected profit using data from different sources.
Data Blending Basics
Sometimes, we need to combine fields from different data sources in a single calculation. To do this, we blend the data. Let's say we need a blended data source with total cost, revenue, and projected revenue. Here's how to create it:
- Select the scorecards containing the metrics you want to use.
- Right-click and choose "Blend Data".
- A new scorecard appears, connected to the blended data.
The blended data is not connected to the original data sources. Instead, it contains the chosen metrics, cross-joined together. This is just one example of data blending; there are many other possibilities and join types to explore.
Creating Custom Fields
With the blended data, we can now create custom fields for profit and projected profit. To do this, subtract total cost from revenue and projected revenue, respectively. The result is profit based on actual revenue and projected profit based on hypothetical revenue.
📩 Receive my weekly Looker Studio tips
🎥 Subscribe to my YouTube channel
🖇 Connect with me on LinkedIn