Managing your data sources

Overview

To ensure the stability and performance of your Klipfolio dashboards, there is a 10 MB limit on Klipfolio data sources. A Klipfolio data source is a subset of data for one or more metrics from a service like Google Analytics that is used in a Klip or multiple Klips. Surpassing the limit results in data sources becoming unavailable. There are steps you can take to configure your data source to be less than 10 MB and avoid disrupting your dashboard.

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. The overall size and memory required for the data source is what is important.

If your data source is increasing in size over time, it is advisable to proactively take action to maintain your data source below the 10 MB limit. At a high level, some of the most common things that impact the size of your data sources include: number of metrics included (are all the metrics required for the associated Klip(s)?), time-frame (are all time periods required for this source at the same time or can they be divided into historical versus current or by time period). Below, we provide some options for reducing or segmenting your data source depending on the data source type.

Query-based static data sources

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 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).

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

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

Query-based dynamic data sources

Variables are used to facilitate reducing the size of your dynamic data sources instead of hard-coded filter parameters. This results in one data source definition that will generate multiple instances based on the values provided via the variable. For example, rather than one large data source that needs filtering using formulas in your Klips, include a drop down user input control indicating the various time periods important to your Klips. 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}

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} 

About uploadable data sources

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.

Uploadable data sources supported by Klipfolio include:

  • Upload a File
  • Google Drive
  • Email Attachment
  • Dropbox
  • Box
  • FTP/SFTP

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.

For further instructions on using the LOOKUP and SLICE functions, see the following articles:

LOOKUP function

SLICE function

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

For further instructions on using the SLICE and ARRAY functions, see the following articles:

Spreadsheets with multiple sheets or workbooks

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

Important concepts