How to Write SQL Queries in Looker Studio

In Looker Studio (formerly Google Data Studio), you can connect to a variety of data sources, including SQL databases such as Google BigQuery, MySQL, and PostgreSQL, among others. Writing SQL queries in Looker Studio allows you to customize and control the data that is pulled into your reports, which can help you optimize performance, filter data, or create complex metrics that go beyond the built-in functionality.

Here’s a step-by-step guide on how to write SQL queries in Looker Studio:

Step-by-Step Guide to Writing SQL Queries in Looker Studio

1. Connect to a SQL Database

Before you can write SQL queries, you need to connect Looker Studio to a SQL-based data source.

Step 1: Open Looker Studio and click on Create > Data Source from the main dashboard.
Step 2: Choose the appropriate SQL connector based on your database type. Common connectors include:
- Google BigQuery
- MySQL
- PostgreSQL
- SQL Server
- Google Cloud SQL
If your database type is not listed, you may also be able to use a third-party connector.
Step 3: Follow the prompts to authenticate and configure the connection to your database. You will need the credentials, such as the hostname, database name, username, password, and port (if applicable).

2. Create a Custom Query Data Source

Once your database connection is set up, you can create a custom data source by writing your SQL query.

Step 1: After selecting your SQL connector and authenticating, click Custom Query (for BigQuery) or choose the option to write a SQL query (for MySQL, PostgreSQL, or other SQL databases).
Step 2: A new window will open where you can enter your SQL query.

Example SQL Query (BigQuery)

Here’s a simple SQL query example for a BigQuery table:

SELECT
  product_name,
  SUM(sales) AS total_sales,
  COUNT(order_id) AS total_orders
FROM
  `my-project.my-dataset.orders`
WHERE
  order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
  product_name
ORDER BY
  total_sales DESC

Explanation:
- This query selects the product_name and calculates the total sales and total orders for each product.
- It filters the data to include only orders placed between January 1, 2023, and December 31, 2023.
- The data is grouped by product_name and ordered by total sales in descending order.

Example SQL Query (MySQL)

If you're working with a MySQL database, you can write a similar query:

SELECT
  customer_name,
  COUNT(order_id) AS order_count,
  SUM(order_amount) AS total_spent
FROM
  orders
WHERE
  order_date >= '2023-01-01'
GROUP BY
  customer_name
ORDER BY
  total_spent DESC;

Explanation:
- This query retrieves the customer names, total orders, and total amount spent for each customer from the orders table.
- It filters by orders placed after January 1, 2023, and groups the data by customer_name.

3. Add the Query as a Data Source

Step 1: After writing your SQL query, click Add to save it as a data source.
Step 2: Name your data source (for example, “Customer Orders 2023”).
Step 3: Once the query is saved, the result of the query will appear as a data source in Looker Studio, and you can start building visualizations based on that data.

4. Use the Query Results in Visualizations

Once you’ve created a custom query, you can use it in your reports just like any other data source.
You can create charts, tables, scorecards, and other visualizations using the fields and metrics generated by your SQL query.
Drag and drop the dimensions and metrics from your custom SQL query data source into your visualizations.

Common Use Cases for Writing SQL Queries in Looker Studio

1. Filtering Data Before It Enters Looker Studio

SQL queries allow you to filter the data at the database level, reducing the amount of data that is pulled into Looker Studio. This is useful when working with large datasets, as it helps improve performance.

Example:

SELECT * FROM sales WHERE region = 'North America'

2. Creating Aggregations

You can use SQL queries to pre-aggregate data before it enters Looker Studio. This reduces the need to perform complex calculations in Looker Studio and improves query performance.

Example:

SELECT product_category, SUM(sales) AS total_sales FROM sales GROUP BY product_category

3. Joining Multiple Tables

If your data is split across multiple tables, you can use SQL queries to join these tables and create a unified data source for Looker Studio.

Example (for MySQL):

SELECT
  customers.customer_name,
  orders.order_id,
  orders.order_amount
FROM
  customers
JOIN
  orders ON customers.customer_id = orders.customer_id

4. Complex Data Transformations

SQL allows you to perform more complex data transformations that might not be possible within Looker Studio’s built-in functionality, such as subqueries, window functions, or CASE statements.

Example (using a CASE statement):

SELECT
  product_name,
  CASE
    WHEN sales_amount > 1000 THEN 'High'
    WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium'
    ELSE 'Low'
  END AS sales_category
FROM
  products

5. Using Window Functions

You can use window functions (available in many SQL databases like BigQuery and PostgreSQL) to perform calculations across a set of rows related to the current row, such as calculating running totals or ranks.

Example (running total in BigQuery):

SELECT
  order_id,
  order_date,
  order_amount,
  SUM(order_amount) OVER (ORDER BY order_date) AS running_total
FROM
  orders

Tips for Writing SQL Queries in Looker Studio

Optimize for Performance: When writing SQL queries for Looker Studio, try to limit the data being fetched by filtering or aggregating data before sending it to Looker Studio. This helps improve performance, especially when working with large datasets.
Test Queries Before Adding: It’s a good practice to test your SQL queries in your database platform or console (like BigQuery’s query editor or MySQL Workbench) before adding them to Looker Studio to ensure they work as expected.
Limit Data Rows: If you’re working with large datasets, consider limiting the number of rows returned by your query to improve performance in Looker Studio.
```
SELECT * FROM sales LIMIT 1000
```
Use Aliases: Use aliases (AS) to make your field names more readable and clear when using the resulting fields in Looker Studio.
```
SELECT product_name AS Product, SUM(sales) AS TotalSales FROM sales
```
Leverage Built-in Database Functions: SQL databases like BigQuery, MySQL, and PostgreSQL have built-in functions for date handling, string manipulation, and mathematical operations. Use these functions to optimize and clean your data before pulling it into Looker Studio.

Conclusion

Writing SQL queries in Looker Studio allows you to control and optimize the data you use in your reports. Whether you're filtering data, creating complex aggregations, or joining multiple tables, SQL queries provide flexibility that can make your reports more powerful and efficient. By leveraging custom queries, you can reduce the load on Looker Studio and ensure that only the most relevant data is visualized in your reports.

For further customization, explore other advanced features in Looker Studio like using calculated fields or creating custom formulas.

Published 14 Oct 2024