BQ+ Data studio hack day

bq_hackday.md

Questions to answer

• Heart beat Event distribution in the last 24 hours, per hour resolution.
• Distribution of All events from Oxford Production in the last 48 hours.
• Total number vs Actual number of categorization events on Rugby production per hour in the last 24 hours.

Learning:

• Data studio doesn't support alert yet.
• Once explorer dashboard is created from a BQ query result. query update won't be reflected in the dashboard. seems like data source is fixed to the original query, I assume I need to update the data source once the query is updated, How to?
• Filter metric by metric name is done by adding "Dropdown list control"

2020-10-21

• Data studio can add calculated field for dashboard, so there is no need to do the calculation from SQL.
• If there are more than 2 dimentions to break down, neither time series nor line chart can break down them. the workaround is to create a calculated field which concatenate all dimentions (except datetime) to one value and use it as as dimension.

TODO

• Use parameter to pass value back to unerlying query so the query result is more dynamic https://support.google.com/datastudio/answer/9002005?hl=en
• Try other chart type
• Remove hard coded date range in SQL to support data visualization in the past
• Check if DS suppports automatic aggregation, for example, if date range is is large, like 365 days, data point is aggregated to daily resolution.

Referernces

1. timestamp function https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_sub
2. Set date dimension to "Date Hour" https://support.google.com/datastudio/answer/6401549?hl=en
3. Data studio tutorial https://analytics.google.com/analytics/academy/course/10

console_set_password_event
credentials_background_refresh_request_received
credentials_refresh_declined
credentials_refresh_finished
credentials_refresh_request_received
credentials_refresh_runnable_created
description_prettification_event
expenses_report_billing_event
income_verification_billing_event
payment_request_created_event
process_accounts_event
process_transactions_event
provider_condition_event
random_uncategorization_event
tink_link_interaction_event
tink_link_view_event
transaction_event
transaction_retention_job_event
transfer_created_event
transfer_end_status_event
transfer_intermediary_status_event
user_event
user_recategorization_event