By Vivian Lu
As a self-service BI solution, Transform is committed to enabling coherent, data-informed workflows and data-driven stories centered around businesses and organizations. To encourage easy adoption for people coming from all sorts of technical backgrounds, the team at Transform has built several various downstream integrations (Mode, Hex, and Tableau to name a few). One of our favorite tools is the Transform Connector for Google Sheets! In this post, we share our love for the Transform Connector and provide a quick spreadsheet demo of all the cool things you can do with it!
Why the Transform Connector with Google Sheets is Awesome
A bit of context: Transform’s metric framework – MetricFlow – is a semantic layer that sits on top of your data warehouse and allows you to define metrics via YAML. You and your team gain streamlined access to a governed set of consistently defined metrics without having to duplicate or re-write SQL logic. Moreover, the MetricFlow framework allows joins between metrics! You can easily slice and dice metrics by various dimensions across data sources through joins defined in your configurations.
While data analysts and practitioners generally rely on SQL and some form of data-wrangling language (i.e. Python) to create custom visualizations, Google Sheets can be just as powerful and flexible in compiling and sharing insights. TLDR - don’t underestimate just how powerful spreadsheet functions can be!
Once your metrics and configurations are defined via the MetricFlow framework, the Transform Connector can easily pull your defined metrics and dimensions into a Google Sheet for further analysis. Gone are the days where you first write SQL to pre-aggregate inputs to calculate metrics, download into a CSV, and finally dump into a Google Sheet just to share a table or chart! All your pre-defined metrics are made available via a custom function like this:
Ex: Suppose I want to see the weekly count of transactions, total customers, and gross sales in all of my US-based warehouses from August 2022 onwards. The Transform connector translates this request as such:
If you’re not a fan of writing functions, or perhaps would like a bit more help in seeing what metrics or dimensions are available for data exploration, you can launch the Transform connector in Google Sheets! Essentially, the Transform Connector acts as a clickable UI, where you can search for defined metrics and dimensions without having to write functions or SQL queries.
For those who are familiar with SQL, you may already notice how the Transform Connector for Google Sheets is designed with SQL-like inputs and parameters in mind:
Lastly, here are some other specific features we’d like to highlight because Transform adds so much more than just a connector to select metrics:
Easy access to your pre-defined metrics
Simply select the metric name and you never have to worry if you’re plugging in the right formula or pulling from the right table. Transform’s MetricFlow framework establishes governance from the start! (Bonus – Transform customers who host their metrics with Transform get access to a metrics page dedicated to each metric displaying the metric lineage, team annotations, dashboards, and much more!)
Flexible slicing and dicing
Transform figures out the appropriate identifiers (and subsequently dimensions) available to you once your metric configurations are created. Explore your metrics without having to write lengthy SQL joins or interweaved CTEs.
Reproducibility with Saved Queries
If you have a specific query within a spreadsheet that you would like to keep handy for easy reproducibility, you can save your query and access them anytime under the “Saved Queries” tab! Even better - you can also run the saved query to refresh the spreadsheet data!
Quick cell references with Custom Functions
Notice the “Custom Function” toggle at the bottom of the connector menu. If you would like more flexibility and dynamic referencing based on cell references in your spreadsheet, toggle the “Custom Function” on to retrieve a pre-filled MQL (MetricFlow Query Language) query function!
Additionally, you may choose to use the MQL_QUERY function as a template, and add in your cell references (as seen below)
For a sneak peek check out our quick demo our Transform connector for Google Sheets:
Interested in Learning More? If you’d like to learn more about the Transform Google Sheets connector, or even just learn more about Transform in general, feel free to book a demo with one of our product specialists! We also love hearing feedback about the product, and Transform is committed to helping teams scale data with context, curiosity, and–most of all–success.
