Import your Metrics from Google Sheets

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. 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".
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 Metrics Data for import attachment where a sample spreadsheet is provided 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".
   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 - cumulative or non cumulative and summed up values/average of values/last updated value, timeframe (weekly, monthly, quarterly, etc.,), and the trend (higher is better or lower is better). In case of selecting sum or average of values, you can also select how the data can be aggregated - All times to include all the data in the sheet or YTD (year to date) to select the start month of your fiscal year. With YTD, the aggregation will reset to the value of the actual data on the selected month and start summing or averaging as of that date up to the next 12 months.
      If you wish to change the name of the metric, you can do so from the "Metric display settings" page - click into the "Metrics" name text box, and enter the metric name.
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 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.

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 > Library > + Create new metric > Connect to a source option,
• 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.

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.

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.

• Metrics - If you wish to change the name of the metrics, click into the Metrics name text box, and enter the metrics name.
• Unit - 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.

• My data is - Choose whether your data is set up cumulative or non cumulative in the spreadsheet. If you select Cumulative, then the Show total as will automatically default to Last value, and you'll not be able to edit it.
• Show total as - 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.
    If "sum of values" or "average of values" is chosen, then you can also select how the data can be aggregated - "All times" to include all the data in the sheet or "YTD" (year to date) to select the start month of your fiscal year. With YTD, the aggregation will reset to the value of the actual data on the selected month and start summing or averaging as of that date up to the next 12 months.
    
  • 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).
  If the spreadsheet is set up "monthly", and if the Time frame you choose while importing is "quarterly", then the values are grouped and the data points are shown quarter-wise. For example, you've values from January to July in your spreadsheet, the metric tile will display

  • Summed up values from January till July, if "sum of values" is chosen. The "Table view" tab in the imported metrics' sidebar will show grouped values - Q1: Sum of Jan to March; Q2: Sum of Jan to June; Q3: Sum of Jan to July.
  • Average of values from January till July, if "average of values" is chosen. The "Table view" tab in the imported metrics' sidebar will show grouped values - Q1: Average of Jan to March; Q2: Average of Jan to June; Q3: Average of Jan to July. 
  • Value in July month if "Last value" is chosen. The "Table view" tab in the imported metrics' sidebar will show grouped values - Q1: Value of March; Q2: Value of June; Q3: Value of July.

  If you wish to see the actual value that you entered against the exact date, then choose "Daily" in Time frame.
• 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 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.

Sample file to import:

Attached is a sample excel in this article (Sample Metrics Data for Import) which you can download and import to your Google Sheets (See Import Excel data into Sheets) and play around.

Or, you can download the sample Operating & Financial Model template from our website, input your metrics and import them into your Cascade workspace.

How to edit or delete a metric?

All the imported metrics will be available in the Metrics > Library tab. Click the metric tile to view its information from the Sidebar:

• Details - Displays a line graph of the metrics data against time. If YTD is selected then the aggregation and display will reset to the value of the raw data on the selected month and start summing or averaging as of that date up to the next 12 months.
  
• Measures - Here, you can either create a new measure from this metric, or you can view the measures that're connected with this metrics.
• Table view - Displays the exact values of the metrics over time, as available in your spreadsheets.

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.

What happens when my spreadsheet is set up "monthly", but I chose "quarterly" timeframe during import?

If the spreadsheet is set up "monthly", and if the Time frame you choose while importing is "quarterly", then the values are grouped and the data points are shown quarter-wise. For example, you've values from January to July in your spreadsheet, the metric tile will display

• Summed up values from January till July, if "sum of values" is chosen. The Table view tab in the imported metrics' sidebar will show grouped values - Q1: Sum of Jan to March; Q2: Sum of Jan to June; Q3: Sum of Jan to July.
• Average of values from January till July, if "average of values" is chosen. The Table view tab in the imported metrics' sidebar will show grouped values - Q1: Average of Jan to March; Q2: Average of Jan to June; Q3: Average of Jan to July. 
• Value in July month if "Last value" is chosen. The Table view tab in the imported metrics' sidebar will show grouped values - Q1: Value of March; Q2: Value of June; Q3: Value of July. 

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.

I get an error in Step 4 saying "Option selected is already assigned to a metric".

This happens when you've selected the row or column in which you had the dates in the previous step. Select only those metrics that you wish to import in the Mapping metrics step.

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.

I updated the progress in my spreadsheet, but the metric shows data points for a different date.

It might be because you would have chosen a different Time frame. If you choose a monthly time frame, then the actual data points will be grouped and your progress will be displayed against the last date of every month, and if quarterly is chosen, the progress will be displayed on the last date of the month in every quarter. If you wish to see the actual value that you entered against the exact date, then choose "Daily" in the Time frame.

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.

I get a "Failed to sync" error.

Go to Integrations > Metrics & measures > Google Sheets, and click Manage. It'll list all the connections. See if the connection that you created while connecting to Google sheets account is still available here. If its not, then you may need to reconnect the sheet and reimport the metrics.
If the connection is available, click the three dots against it, and click Refresh connection to re-establish the connection. You can then click Sync now in the metric tile and the issue must be resolved. If the issue still persists, contact us.

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 value from a pivot table, or obtained from formula does not make any difference as long as the value and the date format is available and 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.