Skip to main content

Release - September 26th, 2022

· 3 min read

✨ New ✨​

Transform Connector for Sheets Updates​

Details

We’ve made a few updates to Google Sheets connector!

1) We now have the concept of Saved Queries in Google Sheets. This allows users to save queries they issue with the custom menu, edit them, and refresh on load of the connector. Note currently Saved Queries in the Transform App are independent from Saved Queries in Google Sheets.

2) We’ve added the ability to retrieve the custom function command from the custom menu selections.

3) Instead of users needing to select granularity before Time over Time, we default to the most granular option available for the metric (for most most metrics this is daily).

4) We now automatically select metric time when users make selections.

5) When adding WHERE constraints, we now automatically format the input to fit with accepted parameters.

6) We added validation into the limit field so we now do not allow you to input text in the limit field.

Read more documentation about these features here.

Constraints on input measures for a metric​

Details

Users can now define constraints on input measures for a metric. For example, to define a ratio metric for fraction of frequent purchasers, in the past you’d have to define a separate measure using a custom expr like SUM(IF(…)), or possibly even split one measure into a separate data source with a data source level constraint. Now, users may choose to apply the constraint directly to the measure, like so:

  metric:
  name: frequent_purchaser_ratio
  description: Fraction of active users who qualify as frequent purchasers
  owners:
    - support@transformdata.io
  type: ratio
  locked_metadata:
    value_format: ".2%"
  type_params:
    numerator:
      name: distinct_purchasers
      constraint: is_frequent_purchaser
      alias: frequent_purchasers
    denominator:
      name: distinct_purchasers

Note the new constraint and alias parameters for the measure referenced in the numerator. The constraint parameter defines the constraint the user wishes to apply. That constraint filter will be applied to the measure it is attached to, and no others.

The alias parameter is only necessary if the same measure is used more than once in the metric but with different constraints, as shown above. This is necessary to avoid column name collisions in the rendered SQL queries. If all input measures are already distinct, the alias parameter may be omitted.

This may be used with any metric type. In expr type metrics the expr should always refer to the alias, if it is set, as below:

 metric:
  name: purchase_rate_90
  description: Fraction of purchases with < 90 pct chance of being returned
  owners:
    - support@transformdata.io
  type: expr
  locked_metadata:
    value_format: ".2%"
  type_params:
    expr: "(total_purchases - predicted_returns_90_pct) / NULLIF(total_purchases, 0)"
    measures:
      - name: total_purchases
      - name: predicted_returned_items
        constraint: prediction_score > 0.9
        alias: predicted_returns_90_pct # alias not required, but you may always include one for clarity

🐛 Bug Fixes 🐛​

Null and non-string dimension value filter bug​

Details

We fixed a bug where you could not filter on any non-string dimension values when querying a metric. This includes null, booleans like true, false, and any undefined fields like nan.

Update query metadata without making a change​

Details

We fixed a bug where Saved Query creators couldn’t make changes to the metadata of the query (name, description, etc) unless they made a change to the query parameters themselves.