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/Excel 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/Excel. Explore practical tips to make the most of Coefficient and overcome potential hurdles associated with data scale. 😎
Ways to Optimize Your Coefficient Imports
Using Filters, Sorts, Row Limits, and Cloud Pivots on a Coefficient Import
Optimizing Scheduled Imports (Refreshes)
Removing Extra/Empty Rows and/or Columns
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 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” results based on some criteria. This can give you insights into trends and if the query you are using in the import pulls what you expect.
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 on data.
Using Filters, Sorts, Row Limits, and Cloud Pivots on a Coefficient Import
Let’s review how 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 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”. |
2. In the “Filters section, select the “edit” link. |
3. The “Import Preview” window will open up and from here you can see the options to “Filter”, “Sort”, “Limit” and “Pivot”.
|
4. Select the “Filter” option and click “Add Filter”. |
5. Select the one you would like to filter by from the fields listed. In this case, we will filter on “TerritoryID”. You can also type in the field if you know the name or keyword(s). |
|
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.
|
7. We need to include the value that our import results should match. In this example, we want “Territory ID 4”. We will input “4” into the value box. Then select “Done”.
|
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”.
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”. |
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) |
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.
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/Excel. The Import Preview window will close.
ℹ️ NOTE: Filter, sort, and limits set will appear in the import details. Click “Save & Run” to apply these updates to your existing import.
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.
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.
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.
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.
ℹ️ 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 workbook's performance (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.
ℹ️ 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, which 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.
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…”.
ℹ️ 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.)
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.) 😎
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.