Managing your data source size

To ensure the stability and performance of your Klipfolio dashboards, there is a 10 MB limit on Klipfolio data sources. Learn more about data source limits here.

This article provides advice on how to limit the size of your data sources to ensure optimum dashboard performance for:

Notes:

  • A data source’s size and required memory are what matter most. Klipfolio does not specify limits for the number of rows and columns in Excel or CSV files or for the number of arrays and objects in XML and JSON data sources.
  • It’s important to remember that your data source size can change over time. Make sure to monitor your data source on a regular basis to ensure that it remains within the limits.

Common factors that impact data source size

Some of the most common things that impact the size of your data sources include:

  • Number of metrics in your data source: For example, if your data source includes metrics for Revenue, Accounts, and Leads but you are only interested in Revenue, you should remove the other metrics from your data to reduce its size.
  • Number of records in your data source: For example, if you have daily data for the last five years, you can divide your data into two data sources - one that includes your historical data and one that includes your current data.

Managing query-based static data source sizes

If you are using query-based data sources, you can apply filters to your query to reduce the size of the data set returned. If the data source exceeds the 10 MB limit, create multiple data sources grouped by a supported filter (for example, a time period filter may be applied).

A static data source query without a filter applied may look like the following query.

In this example, a large amount of data will be returned with this static query:

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,ga:country&metrics=ga:users,ga:sessions&start-date=365daysAgo&end-date=today

In your query you could change the granularity of the data in the data source (for example, changing from daily to monthly aggregation of the data).

To reduce the size of the data source, we created a static data source query with a filter applied to limit the amount of data returned. In this case, we are creating one data source per month. Each one of the following queries is a separate data source:

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,ga:country&metrics=ga:users,ga:sessions&start-date={date.addMonths(-12).startOfMonth.format()}&end-date={date.addMonths(-12).endOfMonth.format()}

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,ga:country&metrics=ga:users,ga:sessions&start-date={date.addMonths(-11).startOfMonth.format()}&end-date={date.addMonths(-11).endOfMonth.format()}

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,ga:country&metrics=ga:users,ga:sessions&start-date=date={date.addMonths(-1).startOfMonth.format()}&end-date={date.addMonths(-1).endOfMonth.format()}

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,ga:country&metrics=ga:users,ga:sessions&start-date= {date.addMonths().startOfMonth.format()}&end-date={date.addMonths().endOfMonth.format()}&end-date=today

Managing query-based dynamic data source sizes

Variables are used to help reduce the size of your dynamic data sources instead of using hard-coded filter parameters. This results in one data source definition that will generate multiple instances based on the values provided via the variable. Learn more about variables here.

For example, rather than one large data source that needs filtering using formulas, include a drop-down user input control indicating the various time periods important to you. The value is used to populate the variable in the dynamic data source query definition and will create an individual instance for the relevant time period. Each instance can be significantly smaller than one large data source.

In the Query URI, a variable is used in place of a hard-coded value, using the format {props.varname}.

For example, in this query:

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,ga:country&metrics=ga:users,ga:sessions&start-date=date.startOfYear&end-date={date.today}

replace ga:country with {props:country} and date.startOfYear with {props.vardate}

Your new query will look like this:

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:Your-Profile-ID&dimensions=ga:isMobile,{props.country}&metrics=ga:users,ga:sessions&start-date={props.vardate}&end-date={date.today} 

Managing uploaded data source sizes

If you are using XLS or CSV files, you will need to segment the files if they are near to or exceed the 10 MB limit. See below for instructions on how to segment your data source file.

You can upload files from the following sources:

  • Local storage
  • FTP or SFTP
  • Email attachments
  • Cloud file sharing services

There are a few different ways you can segment and realign your uploaded data to reduce size:

Spreadsheets with multiple sheets or workbooks

Spreadsheets with multiple worksheets can be reduced in size by converting the worksheets into individual files.

Using LOOKUP to align segmented CSV or XLS data sources

If your data source has too many columns, you can split your data source into multiple data sources as needed and use LOOKUP to combine the data.

LOOKUP uses a key column to align data. This means that all the data sources you want to combine should have a column of the same key data with all values in the same position.

In these two data sources the Client ID is the key.

To align the data, use the LOOKUP function as shown below:

LOOKUP(SLICE( A:A ), SLICE( A:A ), SLICE( C:C ))

where A:A is the Client ID column from sample data 1

and A:A and C:C are the Client ID and Category columns selected from sample data 2

The image below displays the Category column from sample data 2 in the same Table component as all the data from sample data 1.

See LOOKUP and SLICE for further instructions on using the functions.

Using ARRAY to combine segmented data sources

If your data source has too many rows, you can split your data source into multiple data sources as needed and use ARRAY to combine the data. It is recommended to split the data into logical groupings. In this example we use January data and February data.

Use the ARRAY function to combine your data sources: 

ARRAY(SLICE( B:B ), SLICE( B:B ))

where B:B is selected from January data

and B:B is selected from February data

See ARRAY and SLICE for further instructions on using the functions.

Related Links

Have more questions? Submit a request