Sheet Optimization

Section header image mobile

As Coefficient empowers you to extract data from diverse sources, facilitating robust analysis, it's essential to address potential lags that may arise as your data grows. These slowdowns are often a result of Google Sheets's limitations in handling larger datasets. In this article, we provide valuable suggestions to help you optimize your imports and enhance the overall performance of your data analysis in Google Sheets. Explore practical tips to make the most of Coefficient and overcome potential hurdles associated with data scale. 😎

Ways to Optimize your Coefficient Imports

Filters

Row Limits

Cloud Pivots

Using Filters, Sorts, Row Limits, and Cloud Pivots on a Coefficient Import

Filters

Sorts

Limits

Cloud Pivots

Optimizing Scheduled Imports (Refreshes)

Offloading Old Sheets

Removing Extra/Empty Rows and/or Columns

Split “Data-heavy” Imports

FAQs for Sheet Optimization

Ways to Optimize your Coefficient Imports

ℹ️ NOTE: Depending on the integration that you use, some of these features may not be possible. If you need some guidance on your specific scenario please reach out to the Support Team (support@coefficient.io).

  • Filters
  • Row Limits
  • Cloud Pivots

Filters

Putting a filter on your Coefficient import is a great way to keep the data focused on the objective and lighter in weight to reduce load lag.

Row Limits

Row limits are often helpful for ad-hoc analysis as you view the top “X” amount of results based on some criteria. This can give you insights into trends and if the query you are using in the import is pulling what you are expecting.

Cloud Pivots

Cloud pivots with Coefficient are AMAZING! With the Coefficient Cloud Pivot, you can group the data from the source so that the import contains the results and not the raw data. This differs from the Google Sheets pivot table as the native Sheets pivot requires all the raw data to be included in the import first and then the pivot is created. Coefficient Cloud pivots do not require all the raw data to be imported - only the pivot table is created with the raw data essentially “behind it”. This is a fantastic way to keep your Sheet focused and light in data.

Using Filters, Sorts, Row Limits, and Cloud Pivots on a Coefficient Import

Let’s go through how each of these features will help you with your Coefficient imports.

Example: Pulling in “Sales.Customer” data from MS SQL resulting in 20K rows of raw data. We will add a filter on the Territory ID, Sort by Customer ID, set the row limit to the first 1K records, and group the results in a cloud pivot table.*

Filters

1. From the Coefficient sidebar, locate the import that we want to edit, “Sales.Customer”, and click the 3-vertical dots button beside your import name. This will reveal options for your import, select “Edit”.

Screenshot 2023-11-21 at 12.51.53 AM.png

2. In the “Filters section, select the “edit” link. 

Screenshot 2023-11-21 at 12.53.09 AM.png

3. The “Import Preview” window will open up and from here you can see the options to “Filter”, “Sort”, “Limit” and “Pivot”. Screenshot 2023-11-21 at 12.54.08 AM.png

4. Select the “Filter” option and click “Add Filter”.

Screen Shot 2022-10-11 at 5.26.38 PM.png

5. From the fields listed, select the one you would like to filter by. In this case, we will filter on, “TerritoryID”. You can also type in the field if you know the name or keyword(s).

Screen Shot 2022-10-11 at 5.29.45 PM.png

6. Next, you will set the logic for the filter (or conditions for the results). In this example, we will select“is equal to” because we are looking for an exact match here for the results.

Screen Shot 2022-10-11 at 5.30.50 PM.png

 

7. Now we need to include the value that our import results should match. In this example, we want “Territory ID 4”. So we will input “4” into the value box. Then select, “Done”.

Screen Shot 2022-10-11 at 5.33.00 PM.png

 

 

8. The “Import Preview” window will update the sample results to reflect the filter we just created. As you can see all of the results are now from “Territory ID 4”.Screenshot 2023-11-21 at 1.01.56 AM.png

Sorts

9. Sorting your results can help you see your data orientated in order by value (ascending/descending). To Sort your query results, select “Sort” and “Add sort”.

Screen Shot 2022-10-11 at 5.50.12 PM.png

10. Select the field you would like your data sorted by. In this case, we want to sort by “CustomerID. Then select how you want the sorting to be done. (Ascending or A→Z)

Screen Shot 2022-10-12 at 9.39.01 PM.png

Limits

11. To set a limit to the amount of data within this import, select “Limit” and input the number of rows you would like in your query results. In this example, we are setting the limit to “1,000” rows.

Screen Shot 2022-10-11 at 6.05.24 PM.png

12. Once you have set your filters, sorted the data, and updated your limit, click “Done” to import the results of your query into Google Sheets. The Import Preview window will close. Screenshot 2023-11-21 at 1.09.50 AM.png

ℹ️ NOTE: Filter, sort, and limits set will appear in the import details. Click “Save & Run” to apply these updates to your existing import.

Screenshot 2023-11-21 at 1.10.44 AM.png

13. Your import is now down from 20K to 1K rows of the customer records from Territory 4 sorted by the Customer ID. ⭐️

ℹ️ NOTE: The yellow warning message appears to indicate that you are pulling the first 1,000 rows of data from your records only.Screenshot 2023-12-05 at 7.58.07 PM.png

Cloud Pivots

1. In this import, we want to use the Cloud Pivot option to group/count the number of customers in each territory. From the Import Preview, toggle the “Pivot” to enable this feature.Screenshot 2023-11-21 at 1.20.48 AM.png

2. A new screen will load allowing you to select the rows, columns, and values for your pivot table. For this example, we will add “Territory ID” to the Rows and “count”/”Customer ID” for the values.Screenshot 2023-11-21 at 1.23.48 AM.png

3. The resulting import will show your data grouped as expected in the previous step. Should you want to make adjustments, you can select “Change Fields” in the import details/sidebar and make changes as needed. The Cloud Pivot will update/refresh (if scheduled) as the source data changes. Screen Shot 2022-10-12 at 9.26.18 PM.png

ℹ️ More on Coefficient Cloud Pivots here

Optimizing Scheduled Imports (Refreshes)

Setting your Coefficient imports to refresh on a schedule is a great way to keep your data current, with that you may not always notice how quickly your imports will expand and how multiple scheduled imports within a sheet affect the performance of the workbook (all the sheets and imports together). Here is a great suggestion from the team that will help keep your workbooks → Sheets → Imports all running smoothly and nimbly.

Stagger the refresh schedule of the imports within your workbook. If you have all your imports refreshing hourly all at the same time that may take some time resulting in multiple API requests to the various data sources to pull in the updated data.

Screen Shot 2022-10-14 at 6.33.34 PM.png

ℹ️  Click here to learn more about scheduled Import Refreshes. 💡

Offloading Old Sheets

It is easy to create all your imports in a single workbook and set up Snapshots in that workbook. This will result in more tabs being created and more and more data coming in. This can cause some lag in your workbook/sheets/imports loading. Here is another suggestion from the team:

Remove/Delete any old or outdated tabs/sheets/imports from your sheet. Deleting a Sheet can be done by clicking the tab and selecting “delete” from the options. Deleting a Coefficient import is a bit different - check out this article for the details.Screenshot 2023-11-21 at 1.16.13 AM.png

Removing Extra/Empty Rows and/or Columns

Each cell → row → column → Sheet/Import counts to the overall total weight of a workbook. We want to make sure that each one serves a purpose and that our workbooks are as efficient as possible. Having empty or unused rows/columns (cells) within a Sheet/Import may be taking up space that could be used elsewhere in the workbook. To remove rows/columns from your Sheets/Imports highlight the rows/columns you want to remove right-click inside the highlighted area(s) and select the option to “Delete columns….” or “Delete rows…”.Screen Shot 2022-10-14 at 6.59.43 PM.png

ℹ️  Click here to learn more about optimizing your Sheets. 😎

Split “Data-heavy” Imports

Sometimes you may have a very large Coefficient import that is growing rapidly and is starting to cause the refresh to lag. Splitting the import into 2 (or more) separate imports COULD be a way to still pull in all the data to your workbook but speed up the lagging refreshes on this import.
Example: This “Sales.Customer” import is pulling in 20K rows and 7 columns (taking about 140 cells total). (This is not hitting the upper limits by any means - but being proactive is a great place to be.)Screen Shot 2022-10-21 at 5.51.51 PM.png

One way to set this up is to create imports based on “TerritoryID”. We have 5 imports now pulling in the data, but the imports are all smaller in size and can be refreshed more quickly. (If you need all this data compiled for analysis - that can be achieved with a formula or two.) 😎Screen Shot 2022-10-21 at 5.50.02 PM.png

FAQs for Sheet Optimization

Why should I optimize my Sheets/Imports with Coefficient?

Sheet optimization can differ depending on the user and the objectives for each spreadsheet you create and maintain. These tips below are helpful suggestions the team has made to users in the past - please use the ones that best suit your needs. If you have a suggestion that is not on this list - please let us know; we would LOVE to add it here to help other users.

  • Keep only active and relevant sheets/imports in your workbooks. Off-load/delete any Sheets/Imports that are no longer needed.
  • Avoid scheduling your imports to refresh all at the same time. No one likes a traffic jam.
  • Use filters, sorts, and cloud pivots to keep the data light, applicable, and focused on your objectives.
Was this article helpful? 1 out of 1 found this helpful