Skip to main content

Looker Studio

Looker Studio builds reports from data sources made with Google's connectors. Three of them fit Send2Bucket data:

You haveUse
Send2Bucket objects in Google Cloud Storage, or data in BigQueryThe BigQuery connector
Records loaded into PostgreSQLThe PostgreSQL connector
Records loaded into MySQLThe MySQL connector
note

This page follows Google's Looker Studio documentation. The steps in Looker Studio were not run by us. The SQL is from the engine pages, where we ran it against our sample data.

BigQuery​

Create the s2b dataset and the s2b.records view from BigQuery. Looker Studio works best with flat columns, so add a view per signal with the fields you chart:

CREATE OR REPLACE VIEW s2b.events AS
SELECT date,
received_at,
JSON_VALUE(data, '$.event') AS event,
JSON_VALUE(data, '$.user_id') AS user_id,
CAST(JSON_VALUE(data, '$.cents') AS INT64) AS cents
FROM s2b.records
WHERE signal = 'event';

In Looker Studio, choose Create > Data source > BigQuery, then the project, the s2b dataset and the events view. Set cents to aggregate as Sum, and add a calculated field revenue_usd as SUM(cents) / 100.

Credentials:

  • With owner's credentials, the default, only the data source owner needs the BigQuery Data Viewer and BigQuery Job User roles. Report viewers see the data without BigQuery access of their own.
  • With viewer's credentials, every viewer needs both roles.
  • A service account can own the data source instead of a person.

Grant BigQuery Data Viewer on the s2b dataset and BigQuery Job User on the project that pays for queries. The external table from the BigQuery page reads the bucket with the caller's own access, so the same identity also needs Storage Object Viewer on the bucket.

Every chart runs BigQuery jobs that you pay for. Use the date range control on the date column, which BigQuery uses to read only the matching folders, and keep the data freshness at the default of 12 hours unless a report needs fresher data.

Custom queries with a date range​

Instead of a view, a data source can be a custom query. Enable the date range parameters in the connection panel, and Looker Studio passes the report's date range as the text parameters @DS_START_DATE and @DS_END_DATE, which Google's examples parse with '%Y%m%d':

SELECT JSON_VALUE(data, '$.event') AS event,
COUNT(*) AS events,
SUM(CAST(JSON_VALUE(data, '$.cents') AS INT64)) / 100 AS revenue_usd
FROM s2b.records
WHERE signal = 'event'
AND date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE)
AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
GROUP BY event

PostgreSQL and MySQL​

Load records on a schedule as in PostgreSQL or MySQL, then create a data source with the PostgreSQL or MySQL connector: host, port, database, and a user that can only read.

  • Looker Studio connects from Google's IP ranges. Allow them in the database's firewall; the connector's documentation lists the current ranges.
  • Turn on SSL for any database reached over the internet.
  • The connector lists tables in the public schema only, and columns of types it does not support do not become fields. Use Custom query to select the JSON fields you need as plain columns, as below, or a view with flat columns, such as the s2b_events view from Metabase.
  • A query returns at most 150,000 rows. Aggregate in SQL, not in the report.
SELECT received_at,
data->>'event' AS event,
(data->>'cents')::int AS cents
FROM s2b_records
WHERE signal = 'event'

Notes​

  • Looker Studio caches query results per data source. Change how often it checks for new data under Data freshness on the data source, from 1 minute to 12 hours.
  • Google's own Looker Studio connectors do not cover Trino, Athena, ClickHouse or DuckDB. To report on data you query with those, use a partner connector, or load a summary table into BigQuery, PostgreSQL or MySQL.