How can we help?

Editing data feeds

 

Our recent redesign simplifies the data transfer experience by combining raw and modelled data sources into a single object - data feeds. We’re gradually releasing this new feature to our customers.
If you see Data Feeds in the left navigation sidebar, you’ll in the right place. If not, go here.

Refer to this article whenever you want to modify a data feed, for example, if you want to edit a standalone data feed or one that’s associated with a custom metric(s).

This article includes:

Notes:

Editing custom data feeds versus managed data feeds

When you create custom data feeds (those that will be used for custom metrics) you’re given full editing access, including all of the options described in this article.

Managed data feeds are created by Klipfolio when you add instant metrics. To ensure the integrity and accuracy of your instant metrics, the underlying data in these data feeds is managed by Klipfolio PowerMetrics and cannot be edited. As such, your editing options are limited to modifying the following attributes: the data feed name and description, the account the data feed is connected to, and the queue for refresh setting. You can also delete managed data feeds or share them with your team.

To see which of your data feeds are managed, in the data feed list (accessed by clicking Data Feeds in the left navigation sidebar), select Managed in the Display filter. (See below.)

Editing data feeds for JSON/XML source data

When your source data is in a JSON or XML format, the method of processing it in the data feed editor is slightly different than when dealing with source data that’s already in a tabular format. In the data feed editor, you can quickly populate columns with specific data elements from the tree-structured source data and then add context to your data using formulas and properties. Learn how to edit a data feed that uses JSON/XML source data.

Getting started

You’ll begin by opening your list of data feeds, selecting the data feed you want to modify, and entering its edit mode.

To edit an existing data feed:

  1. Click Data Feeds in the left navigation sidebar.
  2. In your list of data feeds, select the one you want to edit.
  3. Click the Edit button in the top navigation bar.
  4. When you’re done editing, click Save data feed.

Modifying data feed properties

Access the panel on the right to modify general settings for a data feed and the source data that the feed is connected to. (See below.)

Following is a list of possible editing options. These options vary depending on if the data feed is managed or not and where the data is coming from (for example, an uploaded file versus a data service, such as Google Analytics 4):

  • Name: Type a name for your data feed here. Alternatively, you can give it a name by hovering and clicking on the title (at the top-left of the page).

  • Description: To provide extra context for a data feed, you can enter a description here (optional).

  • Data service: Clicking the data service (for example, Google Analytics 4, as shown below) gives you access to, and allows you to modify, the source data that the data feed is connected to.

    The UI for modifying the source data differs depending on where the data is coming from:
    • If the data comes from a service that supports instant and custom metrics (like Google Analytics 4 or HubSpot), you’ll be taken to the "Choose data for your metric" page in the query builder, where you can alter the data query by choosing a different view, or selecting different columns, or applying different filters. In the query builder, you can also choose to connect to a different account by clicking your account tile in the top navigation bar (beside “Save”).

    • If the data comes from one of our core services (like file upload or REST/URL) or from a data service that only supports custom metrics (like Google Drive, SurveyMonkey, or Mixpanel), you’ll be taken to the “Configure data service” page where you can adjust the raw data, for example, by uploading a different set of data (file upload), or entering a different query URL (REST/URL), or connecting to a different account (Google Drive).

  • Queue for refresh: To change the data feed’s refresh frequency rate (and that of its associated metrics), click the drop-down beside your current refresh rate and select a different one from the list of available options. The choices available to you depend on your pricing plan. If you want to access more frequent refresh rates, click Upgrade to move to a different plan.
    Note: This setting is not applicable to data feeds that are connected to data from an uploaded file.

  • Refresh status: If the refresh status is OK, then your data feed is being updated as expected and no action is needed. (See below.) If you see: Warning (one or more refresh failures), the data feed is having trouble refreshing. If you see: Disabled due to multiple refresh failures, the data feed has failed to refresh 20 times and, as a result, has been disabled. Click the Re-enable button. If that doesn’t work, go here for a list of troubleshooting tips. If you see: Disabled due to connection not found, the data that the data feed was connected to is no longer available. This may be because it was deleted or is no longer shared with you.

    Note: This setting is not applicable for data feeds that are connected to data from an uploaded file.

  • Last refreshed: Click the link under Last refreshed to manually put the data feed into the queue to be refreshed. (See below.)

    Note: You can also manually queue one or more data feeds for refresh from your list of data feeds. Click Data Feeds in the left navigation sidebar to open your list and select the checkbox beside the name of each data feed you want to refresh. Then click the Queue for refresh button on the top-left of the page.
    For data feeds that are connected to an uploaded file you’ll see Last uploaded here instead.

  • Column names: Under Use text in row, click the drop-down list and choose the row number that contains your column headings.
    Note: This option is toggled off by default for data feeds that were created using the query builder, as it’s not applicable.

  • Exclude rows: Enable Exclude data before row to choose the row number you want your data to start with in the data feed. For example, if you choose 5, the data in your data feed will start with row 5 and onward. Refer to the digits that display on the left side of the rows to help you select the right number of rows to exclude.
    Note: This option is toggled off by default for data feeds that were created using the query builder, as it’s not applicable.

Modifying column properties

The Column properties panel displays on the right-side of the screen when a column in the data feed is selected.

Note on data format types: When automatically setting the format, we do our best to select the correct format type. However, we recommend you take a moment to verify all formats are set as you expected.

Set properties and align your data by choosing a Name and a Data format for each column.

  • Name: By default, column names are auto-set to match those from the source data (raw data or data query). If you want to use a custom name for your column(s) instead, toggle the Automatically set name option to “off”. This enables you to enter your own custom column names. You can either enter a new column name in the Column Properties panel or in the column header itself.

  • Data format: You can either let us automatically set the data format type for you or you can manually select one of the following data types for each column: Text, Number, Percentage, Currency, Date, or Duration.

    • Text: Textual data is indicated by ABC in the column header.

    • Number: Numeric data is indicated by 123 in the column header.

    • Percentage: Data formatted as a percent is indicated by a percentage symbol, %, in the column header.
      When choosing this data format, use the formula bar to convert percentage values to decimals between 0 and 1, if they are not already. For example, if your values are 23, 33, and 55, add “/100” to the formula in the formula bar so that they become 0.23, 0.33, and 0.55.

    • Currency: Data formatted as currency is indicated by a currency symbol, $, in the column header.

    • Date: Date data is indicated by DATE in the column header.
      Depending on the source of your data (not applicable to data feeds made using the query builder), you may need to set the Input Format for each column to match the format used in the corresponding column in the raw data. (To display the raw data, click the drop-down arrow in the column and select Edit formula). We do our best to auto-fill the Input Format for you but it’s a good idea to double-check that it aligns.
      For each date column, optionally, select a Display Format that differs from your Input Format. Your choice is reflected in the selected data column and will be used in your data feed.
      You can either use the default time zone for your account or select a different time zone from the drop-down list. Note: This setting is not applicable if your data feed doesn't include hourly data.

    • Duration: Duration data is indicated by DUR in the column header.
      Note: We’re working on adding auto-detection for “Duration”. In the meantime, you’ll need to manually set the format type for duration columns.
      Duration values are consumed in seconds. The Input Format is used to convert the duration values to their corresponding value in seconds. You must set the Input Format for each column to match the format used in the corresponding column in the raw data. (Not applicable to data feeds made using the query builder.) (To display the raw data, click the drop-down arrow in a column and select Edit formula).
      The display format for duration is “Days:hours:minutes:seconds”, depending on your data. For example, if the duration values in the column are all less than one day, then the display format will be “hours:minutes:seconds”.
      Note: To select from our pre-set input format options, the duration units in your raw data must be either colon-separated units or single units. For example, “dd:hh:mm:ss”, “d:h:m:s”, and “1234” (where the input format is minutes). You can, alternatively, select the Custom option.

Modifying data feeds using formulas

In the data feed editor formula bar you can build formulas, using functions and references, to manipulate, refine, and perform calculations on your data. For example, you can combine related information from separate columns into a single column or group your data to hide duplicate values.

This section includes:

Notes and tips

This section includes things to note and some tips for using the data feed editor formula bar.

Notes on editing data feeds using formulas:

Compared to other data feed types, the editing process is slightly different for data feeds that are created using the query builder and for data feeds that are created from spreadsheets with a CSV or Excel format. Data feeds that originate in the query builder are the ones you create for these services: Airtable, Facebook and Facebook Ads, Google Ads, Google Analytics 4, HubSpot, Instagram Business, LinkedIn Pages and LinkedIn Ads, Mailchimp, QuickBooks, Salesforce, Shopify, Stripe, X Ads (Twitter Ads), Xero, YouTube, and Zendesk.

Note: Unlike CSV and Excel formatted data, when editing data feeds created from JSON/XML formatted spreadsheets, you follow the same formula editing process as with other data feeds.

Columns that are brought into the data feed from spreadsheets and from the query builder are locked and cannot directly be manipulated using formulas. When working with this kind of data feed, you add new columns and populate them by creating formulas that reference either the locked columns or other new columns. See “Refining your data” for details.

For data feeds created from spreadsheets or the query builder, you write formulas using column references (also known as “results” references). For other types of data feeds you write formulas using results references or datasource references. See “Refining your data” for details.

When building formulas using results references, we recommend using consistent reference types within the formula, for example, results references (&) should be combined with results references, and datasource references (@) should be combined with datasource references. Otherwise, you may notice misalignment in your results. This is because datasource references include all rows of your raw data (including headers and leading blank rows), while result references are the final result of your data feed column (with the headers and excluded rows removed). If you want to combine datasource and results references, we suggest you create a data feed column that points to a column in the raw data and include a results reference to this column in your formula.

Tips on writing formulas:

  • Use the Function Help for assistance when adding functions to formulas. If you find it distracting, you can hide it by clicking the 3-dot menu at the end of the formula bar and selecting Hide Function Help. (See below.)
  • If you need more space to write your formula, grab and drag the formula bar field from the bottom-right corner. (See below.)
  • For help assessing complex formulas, use the Evaluate button. Highlight specific parts of the formula and click Evaluate to see how the data gets adjusted by the functions you’ve applied.

Opening and closing the formula bar

To open the formula bar:

  • For data feeds created from spreadsheets or the query builder, the formula bar is automatically opened when you edit a data feed.
    Columns that are brought into the data feed from spreadsheets or the query builder are locked and cannot directly be manipulated using formulas. To work in the formula bar, you first need to add a new (unlocked) column by clicking the + button (in the toolbar or in the data feed table). (See below.)

    With the new column selected, note that the formula bar is now active.
  • For all other data feeds, right-click any column and select Edit formula. (You can also open the formula bar by double-clicking any column.) When you open the formula bar, your raw data displays underneath. (See below.)

To close the formula bar:

  • For data feeds created from spreadsheets or the query builder, the formula bar is always open.

  • For all other data feeds, right-click any column and select Hide formula or click anywhere in the dark grey workspace surrounding your data feed table. When you hide the formula bar, your raw data is also hidden.

Refining your data

There are many ways to manipulate the data in your feed to refine it and make sure it includes everything you need to create your desired metrics. For example:

For data feeds created from spreadsheets or the query builder

As noted above, formulas for data feeds that were created from spreadsheets or the query builder use column (results) references (not datasource references). This means you add new columns to the data feed table and populate them by creating formulas that reference other columns in the feed. We’ll refer to these new columns as “derived” columns.

To create a derived column using column references:

  1. In an open data feed, click the Edit button in the top navigation bar.
  2. Add a new column by either clicking the + button in the toolbar or the + in the data feed table (located after the last column in the data feed).
  3. With the new column in focus, click inside the formula bar. Then, click the column you want to reference. Alternatively, you can enter an ampersand (&) in the formula bar or click Insert > Column Reference and select the desired column from the list. (See below.)


    The selected column is surrounded by a coloured, dashed line. The dashes indicate that the column has been selected and the line colour signifies the column’s data format type. (See below.) Green is used for number and percentage format types, orange for text, pink for currency, and blue for date and duration.
  4. Create the desired formula for your derived column by doing any or all of the following: referring to additional columns, using operators (+, -, *, /, <, >, <=. >=, =, !=), and inserting functions.
  5. To evaluate the formula and populate the derived column, either click Enter with your cursor in the formula bar (the formula bar remains open and you can, optionally, continue to work with the formula) or click anywhere outside the formula bar or table (to complete the formula).
  6. When you’re done editing, click Save data feed.
    Now that you’ve added a derived column, you can, optionally, organize and manipulate its data further using formulas and functions. To do this, select the column and work within the formula bar.

For all other data feeds

Add new columns to your data feed by clicking the + button in the toolbar or the + in the data feed table (located after the last column in the data feed).

After adding a new column, you can populate it using data from other columns with results references or by using data from columns in your raw source data with datasource references.

To add and populate a new column using results references:

  1. In an open data feed, click the Edit button in the top navigation bar.
  2. Add a new column by either clicking the + button in the toolbar or the + in the data feed table (located after the last column in the data feed).
  3. Enter an ampersand (&) in the formula bar or click Insert > Results Reference. (See below.)
  4. Select a column from the list to add it to the formula bar. (See below.)
  5. Create the desired formula for your new column by doing any or all of the following: referring to additional columns, using operators (+, -, *, /, <, >, <=. >=, =, !=), and inserting functions.
  6. To evaluate the formula and populate the new column, either click Enter with your cursor in the formula bar (the formula bar remains open) or click anywhere outside the formula bar or table (the formula bar closes).
  7. When you’re done editing, click Save data feed.

To add and populate a new column using datasource references:

  1. In an open data feed, click the Edit button in the top navigation bar.
  2. Add a new column by either clicking the + button in the toolbar or the + in the data feed table (located after the last column in the data feed).
  3. With your cursor in the formula bar, click column headers from your raw source data to add them to the formula. Note: Click the column header to make sure you get all of its rows. This also ensures your data stays aligned when it gets refreshed.
  4. Create the desired formula for your new column by doing any or all of the following: referring to additional columns, using operators (+, -, *, /, <, >, <=. >=, =, !=), and inserting functions.
  5. To evaluate the formula and populate the new column, either click Enter with your cursor in the formula bar (the formula bar remains open) or click anywhere outside the formula bar or table (the formula bar closes).
  6. When you’re done editing, click Save data feed.

Click the following links if you want to learn more:

Other editing options

This section includes a bit more information on editing and working with data feeds.

Accessing column options - the toolbar

Access the toolbar at the top of the screen to add, duplicate, and remove columns. Use the arrow icons to undo and redo actions. From the toolbar, you can also move a column to the left or right, merge data from other data feeds, unpivot columns, add all columns from the raw data, remove all columns from the raw data, and open the Properties panel. Note: The options you see depend on the type of data feed you’re editing and the column that’s currently selected.

To access options for a single column, right-click anywhere in the column.

Hiding and showing columns

When in edit mode, you can hide or show specific columns in your data feed table. To do this, click anywhere in a column and select Hide or Show. (See below.)

You can also hide and show specific columns by clicking the eye in the column’s header. (See below.)

After hiding one or more columns in your data feed, you can show them by clicking the Show hidden columns toggle, located at the top of the window. Mouse over this option to see how many columns in the data feed table are currently hidden. (See below.)

When shown, hidden columns will display with a “closed eye” icon in the column header. (See below.)

Notes on hidden columns:

  • Hidden columns won’t be available for selection when creating custom metrics. Make sure all columns you might want to use in metric building are shown in the data feed.
  • You won’t be able to select hidden columns when merging data feeds. Learn more about merging data feeds.
  • You can hide unpivoted columns but, once hidden, a pivoted column can’t be selected to be unpivoted. Columns must be shown for you to be able to unpivot them. Learn more about unpivoting.

Unpivoting - Converting pivot table data to list table data

In the data feed editor, you use the Unpivot button to convert data from a pivot table format to a list table format.

  • A pivot table format is two-dimensional with context along rows and columns and values at the intersections.
  • A list table format is one-dimensional where each row is distinct and contains both context and value.

Why a list table? Data feeds are used for metrics. Metrics refer to single columns of data and, as such, require data to be in a list table format.

If the data you want to track in a metric is spread across several columns, you can use unpivot to consolidate the data into two, single columns - one for “values” and one for “labels”.

See below for a common example, where dates and their associated values are spread across multiple columns:

When you select those columns and unpivot them, they become a single “Labels” column and a single “Values” column. (See below.)

How do I unpivot columns in my data feed?

You can either unpivot columns when you originally create the data feed, or later, by editing the data feed (as described below).

To unpivot columns in a data feed:

  1. Click Data Feeds in the left navigation sidebar to open your list of data feeds.
  2. Select the data feed you want to modify.
  3. Click the Edit button in the top navigation bar.
  4. Click the Unpivot button in the toolbar.
  5. Select the checkboxes for the columns in the data feed you want to unpivot and click Apply.
    Note: You must select at least two columns.
  6. The columns you selected are unpivoted into two columns - a Labels column and a Values column.
    Note: Your data feed now has more rows than before because each value in the unpivoted column now has its own row.
  7. Rename the columns (optional).
  8. Click Save data feed.
    Note: You cannot edit formulas for unpivoted columns. You also cannot duplicate or remove them.

Applying filters to columns

You can apply filters to columns that have a text, numeric, currency, or percentage format.

Notes and tips:

  • Remember, when summarizing data, you want to include all the values. Applying filters isn’t only a visual treatment. If you filter out values, they won’t be included in your metrics.
  • If there are blank rows in your data, you can use the “Is not empty” filter to remove them from your data feed.
  • When applying filters to percentage formatted columns, you’ll need to enter the filter values as decimals.
  • If unpivoting your data, you unpivot first before applying filters to the new labels and values columns.

To apply filters to columns:

  1. Click Data Feeds in the left navigation sidebar to open your list of data feeds.
  2. Select the data feed you want to modify.
  3. Click the Edit button in the top navigation bar.
  4. Click the drop-down arrow in a column header (see below) and select Filter.
  5. The filter dialog opens. Choose either Match all or Match any, then, select an operator, and enter a value to filter the data in the column. When you’re done, click Filter.

Modifying the applied filters

After applying a filter(s) to a column, the filter indicator displays. (See below.) Clicking it opens the filter dialog so you can modify or remove the existing filter(s) or add a new filter(s).

  • To remove all filters, click Clear all.
  • To remove some filters, click the Remove filter button beside the value you want to remove.
  • To edit an existing filter, select the filter, choose a different operator or enter a different value, then, click Update.
  • To add a new filter, choose either Match all or Match any, then, select an operator, and enter a value. When you’re done, click Filter.

Seeing all the data in your data feed

If your OneDrive, SharePoint, Box, and Dropbox source data includes more than 30 columns, only the first 30 will be automatically added to the data feed. If your uploaded local files and Google Sheets source data includes more than 100 columns, only the first 100 will be included in the data feed. Learn more here.

If a data feed contains many rows of data, all rows may not display at once. To see additional rows or to go to the last few rows or first few rows, click one of the links that display below the data feed table. For example, “Showing 75 of 352 rows". "Show 100 more.” or "Go to the last few rows". (See below.)

Working with multi-sheet source data

For uploaded local files and Google Sheets, you choose a single sheet to work with when you create a data feed - one data feed per sheet. To create a data feed that includes data from more than one sheet, you can create single data feeds and then merge them. Learn how here.

For files stored on OneDrive, SharePoint, Box, and Dropbox, you can access the data in the other sheets when editing the data feed. Double-click a column in the raw data to select it. Use the Sheet drop-down menu to select the sheet you want to access. You can use the drop-down to switch between sheets when writing formulas and creating data feeds. (See below.)

Updated
Have more questions? Submit a request