How can we help?

PowerMetrics: Preparing spreadsheets for data feeds

Before uploading a spreadsheet to use as a data feed, we recommend you organize and prepare your data. Not only does this make it faster and easier to create data feeds, it also ensures that your feeds will work well with metrics. 

Note: This article refers to data feeds. For customers who use data sources instead, the same general principles apply.

Following the tips and recommendations in this article will help you achieve the ideal spreadsheet, as shown below:

This article is divided into sections with recommendations and tips for before and after uploading spreadsheet data when creating data feeds.

Before uploading your spreadsheet

There are three areas to address before uploading a spreadsheet: data format, data structure, and file size.

Data format

  • Use a standard decimal format for your numerical data, for example, 1000000.29.
  • Include day, month, and year in your date references, if possible. This enables you to take advantage of the pre-built data filters in PowerMetrics.
  • Use one of the following date formats: 30/03/2023 (DD/MM/YYYY) or 03/30/2023 (MM/DD/YYYY). Use forward slashes (/) as separators, not hyphens (-), for example, don't format dates like this: 03-23-23.
  • Use consistent spelling and case for values you'll use as dimensions. For example, don't use a mix of "UK" and "United Kingdom" or a mix of “Complete”, “complete”, and “COMPLETE”.

Data structure

  • Organize your data into columns, instead of rows. This ensures new data gets added to your metrics as soon as the data feed refreshes. This is especially important for date values.

Don't do this: In the following example, dates and their associated values are spread across multiple columns:

Do this: Here's the same data split over two columns:

  • Ensure all date columns use a consistent format, for example, DD/MM/YYYY.
  • Ensure each column has the same number of rows.
  • Remove summary rows.
  • Remove external references, for example, VLOOKUPs and pivot tables.
  • For columns you'll use as dimensions, put all the members for each dimension into the same column. For example, for data that's segmented by country, all of the countries should be in a single "country" column.

File size

  • The maximum file size for any type of data source is 10 MB. If your file exceeds this limit, try the following: Separate the data into more than one file. Remove all external references (like VLOOKUPs). Remove hidden rows and columns.

After uploading your spreadsheet

After uploading your spreadsheet, you enter the data feed editor. A bit more preparation here will ensure your data feed works perfectly for custom metrics.

The relationship between data feeds and custom metrics

Metrics get new (and historical) data from columns in data feeds. As a data feed evolves over time (for example, when rows are added, removed, or modified) those changes are stored in the associated metric. This interaction ensures your metrics always give you a complete, historical view of your data.

Once you understand the relationship between data feeds and custom metrics, preparing your spreadsheet data in the data feed editor becomes mostly common sense. The following section includes a few pointers to help you out.

The columns every data feed should include

Data feeds should include:

  • Usually, at least one column of numbers. This is the value your metric will measure, for example, "Revenue". Remember - Each individual metric measures a single value and tracks how that single value changes over time. Metrics that measure a count of values or unique values in a text column don't require a column of numbers. The "Charges Count" metric is a good example. It counts rows in a text column to get the total number of charges made to your customers.
  • Usually, one or more columns that add dimensionality to your data. For example, if your measure is "Revenue", you may have columns for "Country" and "Product".
  • Usually, a column of dates. Metrics collect and store data history, so it makes sense to have a date column in your spreadsheet. Every time your data feed is updated, new data enters into the metric and either adds to or replaces the previous data. As time goes by, metrics evolve enabling you to do comparisons to previous time periods and gain insights from the trends in your data.
    There are some exceptions where your metric refers to the date and time when the data feed gets refreshed (sometimes called "date and time imported" or "current date"). The "Total Followers" metric is a good example. It tracks the latest values only (the most up-to-date results) and refers to the import time, not a specific date from a date column.

Data format tips and recommendations

  • Make sure every column has the correct data format applied (text, number, percentage, currency, date, or duration).
  • Metrics refer to single columns of data and, as such, require data to be in a list table format. If your data is in a pivot table format, you can use the unpivot feature in the data feed editor to convert it to a list table. However, it’s preferable to restructure your original spreadsheet data as a list table before uploading it.
  • After uploading your spreadsheet file, do not rearrange the columns in your data. New columns should only be added to the right-end of the table (after the last, populated column).

Handling large and multi-sheet data sources

  • 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. Learn more here.
  • You can combine data from different data feeds (provided they share at least one column in common) using the merge feature. Learn more here.

Modifying data feeds using formulas and functions

  • Columns from your original data source are "locked" in the data feed editor. This means you can't manipulate their data using the formula bar. However, you can add new, unlocked columns and then use results references (and formulas and functions) that refer to the data in your locked columns. Learn more here.

Related articles and videos

Updated
Have more questions? Submit a request