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:

• Editing custom data feeds versus managed data feeds
• Getting started
• Modifying data feed properties
• Modifying column properties
• Modifying data feeds using formulas
• Other editing options
• General editing actions
• Converting pivot table data to a list table format
• Some notes on editing data feeds

Notes:

• This article focuses on editing data feeds. Go here for an introduction to data feeds and to learn about viewing data feeds. Go here if you want to learn about creating data feeds.
• Users who only have the PowerMetrics Viewer role cannot access data feeds. Learn more about PowerMetrics roles and permissions.

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

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

• 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 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: 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.

• 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 formula bar you 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. The possibilities are endless.

Note: If a data feed was created using the query builder (for example, data from Google Analytics or HubSpot), the columns that get brought into the data feed cannot directly be manipulated using formulas. You can, however, add new columns to the data feed and write formulas that refer to the “locked” columns or to other new columns.

To open the formula bar:

• For data feeds created with the query builder, click the +button to add a new, empty column to the data feed. If you’ve already added one or more new columns, right-click one of those columns and select Edit formula. The formula bar will display above the data feed table.

• For all other data feeds, right-click any column, or click the drop-down arrow button in 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 opens below.

To close the formula bar:

• For data feeds created with the query builder, click the drop-down arrow button in an added column (not one of the columns that came from the query builder) and select Hide formula. You can also click the X in the upper-right corner above the formula bar.
• For all other data feeds, click the drop-down arrow button in any column and select Hide formula or click anywhere in the dark grey workspace surrounding your data feed. When you hide the formula bar, your raw data is also hidden.

Notes on writing formulas in the data feed editor:

• For data feeds created with the query builder, formulas can use results references but not datasource references. Formulas for other types of data feeds can use both results references and datasource references.
• 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.

Click the following links to get more detailed information on writing formulas:

• Learn more about using the formula bar.
• Learn more about using results references when writing formulas.
• See our master list of available functions.

Other editing options

This section describes general editing options for data feeds. These options can all be accessed via the toolbar. Learn more about toolbar actions.

General editing actions

You can add individual columns to your data feed using the + button (which displays beside the last column in your data) or via the drop-down arrow button for each column, or via the toolbar at the top of the data feed editor window. After adding a new column, you can populate it with data by clicking a column in the raw data (that displays underneath your data feed). (Not applicable to data feeds made using the query builder.) If your raw data source uses tree-structured data you must add each column individually.

For more options, click the drop-down arrow button on the right-side of a column name. (See below.)

Access the toolbar at the top of the screen to add, duplicate, move, and remove columns. Use the arrow icons to undo and redo actions. From the toolbar, you can also unpivot columns (see below), merge data from other data feeds, add all columns from the raw data, remove all columns from the raw data, and open the Properties panel.

Converting pivot table data to a list table format

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

Some notes on editing data feeds

Here are a few things to note about working with data feeds:

• If the source data includes more than 30 columns, only the first 30 will be automatically added to 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.)

• If your data feed is connected to a multi-sheet data source, for example, an Excel file or Google Sheet that has data in multiple sheets, 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.)