Import your Metrics from Google Sheets
  • 09 Jun 2023
  • 13 Minutes to read
  • Contributors

Import your Metrics from Google Sheets


Article Summary

Whether you're a strategic leader responsible for your organization's growth, or an operational leader driving the metrics, bringing your business metrics closer to your strategy will help you drive faster results and improve the efficacy! If you're tracking your revenue metrics like ARR, MRR, net profit, EBITDA, or financial metrics like accounts payable and receivable, quick ratio, or sales and marketing metrics like NPS, CSAT, customer churn, CAC, LTV, or supply chain metrics like throughout, cycle time, perfect order rate, and so on in Google Sheets, you can import them into Cascade, and track their progress and performance here!

Eliminate the need to go back and forth between Cascade and one hundred spreadsheets. Keep your data up-to-date, accurate, and right where you need it. Save time spent on reporting and monitoring your strategy!

Let's see how the import works before going into the details:

  1. Connect your Google Sheets with Cascade:
    1. Establish the connection between the Google Sheets and Cascade.
  2. Import your metrics in Google Sheets into Cascade:
    1. Choose the file and the sheet in which you have your metrics stored. See the Sample file to import section later in this article where a sample spreadsheet is attached for reference!
    2. Select the format of your spreadsheet layout - if its set up horizontally or vertically.
    3. Choose the metrics that you wish to import. Please remember not to select the column or row that has "Dates". If you do not find the relevant unit in the dropdown, you can select "No unit". If you do not find the unit that you're looking for, you can add your own custom units!
    4. Select the row or column that has the Dates. Remember that it should be set in date format (Number > Format > Date). Also, if you've specified mm/dd/yyyy format, make sure you're entering the dates in the same format too!
    5. Select how you need the imported metrics to display as - summed up values/average of values/last updated value, timeframe (weekly, monthly, quarterly, etc.,), and the trend (higher is better or lower is better).
  3. Connect your imported metrics to measures in Cascade:
    1. Once imported into Cascade, you can build a "Performance of Single Metric" widget to look at how an imported metric has progressed over time.
    2. You can then connect them to a new measure or an already existing measure to track them with targets and due dates! Note that you can connect the same metrics to any number of measures to track for your specific requirements! This step is done within Cascade since the measure does not live in your sheets! See Connect your Imported Metrics to Measures for details!

Prerequisites:

Before you start with the import, it is important that these prerequisites are met:

Cascade:

  • A valid account and workspace in Cascade.

Google Sheets:

  • A valid Google Drive account.
  • A spreadsheet in your Google Drive account with date and metric data.

Please note that you cannot pinpoint to a single cell, but rather to columns or rows where you're updating progress.

Step 1: Connect your Google Sheets with Cascade

It is important to establish a connection between Google Sheets and Cascade to start importing your metrics. This connection can then be used by all the other metrics in that workspace which have their progress in Google Sheets.

You can establish the connection in either of these ways. Click Connect in Google Sheets

  • either from the Integrations > Metrics & measures tab,
  • or, from the Metrics > Connect metrics tab.

  1. Give the connection a name, and click Connect.
    1. Follow Google's instructions to connect your Google account with Cascade. When you've finished, click Continue.
  2. Select the folder or file from your Google drive that you want the connection to access, set it up as private or shared connection, and click Finish.

What is a private and shared connection?

When a connection is set to "public", everyone in the workspace can use the same connection while connecting with the same app, i.e., while setting up tracking, public connections will be listed in the dropdown! For example, if a connection to Google Sheet is set as public by the owner,

  • everyone in that workspace can use the same connection whenever they're trying to connect with Google Sheets to import metrics. 
  • if the sheet is also publicly viewable or editable, then anyone using this connection can access that sheet to update progress. However, if you do not have access to that sheet, then irrespective of having a public connection, you'll not be able to update progress.
    Once the connection to google is approved, you can select a specific file or folder or the entire drive to access. Now, even when a connection is set to public, you can either have access to the entire drive or restrict the access to the contents in the specific folder or file.
  • anyone can manage these connections, i.e., edit its name, or remove the connection or delete them, or change it to private.

When a connection is set to "private", none of the above points is valid! Only the owner can use that connection and manage it! Others will not see this connection listed in the dropdown while setting up the tracking!

We recommend setting up connection as "private", unless you use shared drive accounts where everyone accesses the files or updates progress in which case you can set up the connection as "shared"!

Step 2: Connect your metrics from Google Sheets to Cascade

Now that you've successfully established the connection between Google Sheets and Cascade, you can start importing the metrics by providing the spreadsheet details.

1. Choose the spreadsheet from the Google drive:

  1. Select the Google Sheet connection from the list or create a new one.
  2. Choose the spreadsheet file which has the metrics data from the list. This list will display all the spreadsheet files in your Google Drive.
    If you had restricted access to a specific file or folder, then only those files in that folder or that particular file will be listed here!
  3. Choose the appropriate sheet that you wish to import metrics from, and click Continue.

2. Specify the spreadsheet layout:

Select the format in which your spreadsheet is set-up - either horizontal or vertical!

It is important that you mention this right for the import function to recognize the set-up and the values in your spreadsheet! Click Next to continue.

3. Choose the metrics to import:

Choose the column in which your metrics are available, and select the metrics that you wish to import!

Do not select the column in which your date resides, i.e., do not select the check-box against the date values while mapping metrics. Select only those boxes against metrics that you wish to import!

Specify the unit for each metric! If all the metrics that you wish to import have the same unit, then you can apply them universally from the Select unit dropdown at the top-right!

In case you do not find the unit you're looking for, you can input your own custom unit - just type it, click + Add "unit" as a new option, and it'll get added! For example, Machines, Sessions, Tickets, Responses, Numbers, and so on. This custom unit will be stored and available throughout this import flow, and if you wish to have the same unit for some other metric, you can add this from the list.

However, they won’t be available in the dropdown for other metrics (a new import workflow), and you'll need to manually “create” them again!

Click Next to continue!

4. Select the date format:

This is another important step where you need to select the date format specified in your spreadsheet! Please note that the cells in the date column or row in your spreadsheet must be set to "date format", i.e., choose the date column or row, and select Format > Number > Date! Also, if you've specified mm/dd/yyyy format, make sure you're entering the dates in the same format too!

Without dates, we'll not be able to read or import metrics! Only based on the dates, the values are imported and displayed. This will also enable you to understand the progress made over time!

Click Next!

5. Choose the format of data set-up and timeframe:

Select the way the data is set-up in your spreadsheet!

  • Unit - If you've not selected unit in the previous steps, you can select them from the dropdown list here! You can add your own custom units from here as well!
  • Display with - the option that you choose here will decide the way the value is displayed, i.e., if you choose
    • Sum of values, then we'll add the values in that metrics column or row, and display the summed up value for the time frame that you choose!
    • Avg (Average) of values, then we'll display the average of values in that metrics column or row for the time frame that you choose. i.e., if you choose the timeframe as "Quarterly", then the average of the values in those months of that quarter will be displayed.
    • Last value, then we'll just display the latest value available in the spreadsheet for that metric. Remember that, any value entered against a future date will not be considered, and only that value which is available latest (as on the current date or before the current date) is displayed!
  • Time frame - depending on the frequency or time frame that is set-up for the metric in the spreadsheet, the data will be displayed. Note that, selecting a time frame that is smaller than what is in the spreadsheet will show the same value over and over (i.e., selecting "daily" when the data is set up "monthly", will display the same value every day until the month changes, and so on)!
  • Trend - depending on the trend that you choose, the color will change, i.e., if you choose "Higher is better" or "Lower is better", then when the metric value matches this criteria, the color will be green. If the metric value is opposite to the trend that's set, then the color will be red.

Select the check-boxes against the metrics, if you wish to assign the Display with or Time frame or Trend globally from the top-right! Click Clear All or the trash icon to undo the selections!

Click Connect metrics to finish the import! On successful import, you'll see a message prompt on that! 

The successfully imported metrics will now be displayed in the Metrics > Library tab! Every time, the value in the spreadsheet changes, the relevant metrics will automatically be updated here in Cascade! You can also click by hovering the mouse over the metric tile, or click Sync now in the Metric sidebar to sync the values or updates!

You can then analyze the performance of an imported metric from building a "Performance of Single Metric" widget or you can now connect these metrics to measures, and report accurate progress on your strategic objectives associated with these measures. See Connect your Imported Metrics to Measures for details!

You can search for your metrics or choose options from the dropdown to display in alphabetical order, or make the recent ones to appear first, and so on.

If you go to some other page or click Back in the browser, or do a browser refresh during import operation, then you'll lose all the changes that you made! If you wish to go back to the previous pages to do some changes, please click Go back!

Sample file to import:

Here is a sample excel which you can download and import to your Google Sheets (See Import Excel data into Sheets) and play around:

How to edit or delete a metric?

All the imported metrics will be available in the Metrics > Library tab. Click the three dots in the metric tile that you wish to edit or delete, and choose the relevant option!

  • Click Edit, if you wish to change the name of the metric, unit, display as or time frame, and click Save.
  • Click Delete - this'll permanently delete the metric from the library. If you wish to have it later, then you'll need to re-import it!

FAQs

I have connected my measures to Google sheets and tracking it already, but now this functionality seems interesting, how do I transition to this? Will this mean, I'll lose the data and progress that was made till date?

You can simply import the metrics using the same spreadsheet, and then hook the measures up to those metrics. And, from the tracking section, choose tracking type as Metric library, and select the metric. See Connect your Imported Metrics to Measures for the steps!

The spreadsheet is the source of truth, so if you've the historical data there, it'll translate to the measure once connected.

I have metrics in different files, will that mean I need to do separate import every time for all these files?

Yes, but the best way is to unify them in a single spreadsheet and import in one go.

I do not find my unit in the drop-down during import, what needs to be done?

You can add your own custom units. Just type the text, click + Add "custom unit" as a new option, and it'll get added to the list for this metric flow!

What is the data polling frequency?

Whenever there is an update made in your Google Sheets, the data is pulled into Cascade immediately! You do not need to manually sync the updates!

What happens if I make a change in the value for a date in the past, i.e., historical data point?

You'll just notice that the value gets over written for that date!

Will I see expected progress graph and numbers if I connect to metrics?

Yes, when metrics are hooked up to measures, they'll provide the historical and current values to track the measures. The initial and target values that you set while connecting a metric to a measure will define its expected progress.

Who can do this import workflow?

Anyone that has a Cascade account and workspace, and also a google drive account and a spreadsheet with metrics in it!

Why can’t I update the values of a metric from Cascade?

We consider the data coming from Google Sheets as the source of truth to avoid having misaligned information in the metric. This is a one-way connection: from Google Sheets to Cascade. Nothing you do in Cascade will change spreadsheet values.

Why aren’t my metrics being updated automatically?

Metrics bring in whatever is there in the spreadsheet! If you do not update or add anything there, then no new values will be added in the metric.

What happens if I change the Google Password used in a public connection or how can I change/adapt the Cascade - Google connection in case of a password update?

Navigate to Integrations > Metrics & measures > Google Sheets, and click Manage. It'll list all the connections with Google Sheets in your workspace. From the three dots against your connection, click Refresh connection, and follow Google's instructions to reconnect your Google account with Cascade.

I've formula in my spreadsheet cells, how is this handled?

The import fetches only the values from the spreadsheet howsoever it is set-up! Be it a pivoted value, or obtained from formula does not make any difference as long as the value is valid!

What happens if the data is set up "weekly", but I choose "monthly" in timeframe on import?

This'll not display any error, however, it'll show the same value over and over every week until the month changes!

I chose the wrong unit for my metric while importing!

This'll just show the incorrect unit, but it won’t affect the actual number!

My data is not showing up!

The column or row that you mention during import can have only numbers and related characters, like percent and currency signs. Check them to make sure there are no letters or special characters.

Do a browser refresh to check for intermittent issues. Please note:

  • Cells for the date column/row need to have a date format in the spreadsheet (Format > Number > Date).
  • Make sure commas or full stops are used correctly, especially in currency, according to the spreadsheet format.  Some countries use “,” for decimals and others “.”! Depending on the spreadsheet set up, this might cause wrong data coming in as it will consider it as “text” and not “currency”.  Cascade will detect the spreadsheet’s set-up and use that as a guide.
  • Columns/rows selected for metrics will only bring in data that is in number format!
  • Ensure there're no empty columns or rows in your sheet.

Was this article helpful?