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.
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 date filters in PowerMetrics.
- Use one of the following date formats: 30/03/2023 (DD/MM/YYYY) or 03/30/2023 (MM/DD/YYYY). We recommend using forward slashes (/) as separators, not hyphens (-), for example, 03/23/23 is preferable to 03-23-23.
- Use consistent date formats. If you see the error "Unable to get ingestion map" when saving your custom metric, the most likely cause is inconsistent date formatting within a column in the associated spreadsheet. For example, don't change the order of date units (dd-mm-yyyy) and (yyyy-mm-dd) or change the way years are referred to (yyyy-mm-dd) and (yy-mm-dd) within a single column.
- Each column header name should be unique. If your source spreadsheet data includes duplicated column header names, we recommend you modify it and replace duplicated names with unique ones before using it to create a data feed.
- 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 source data 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).
- For uploaded files and Google Sheets, PowerMetrics uses the column header names in your spreadsheet source data to uniquely identify columns in the data feed. This enables your data feed and custom metrics to update correctly, even when the content and order of columns in the raw source data changes. Note: To enable this feature, when creating your data feed and selecting the row to use as the “row with column headers”, you must choose a numbered row – not the “None. Use letters (A, B, C, D)” option.
We recommend you:- Use unique column header names in your spreadsheets. Duplicated column headers will be automatically renamed in the data feed. For example, if your source spreadsheet has 3 columns named “Profit”, they’ll be updated in the data feed to “Profit”, “Profit 1”, and “Profit 2”. If desired, you can rename these automatically named columns in the data feed editor.
- Don’t change the column header names in your source data after creating your data feed. Also, please note that column header row names are case sensitive. For example, if you change a name from “Revenue” to “revenue”, PowerMetrics will identify “revenue” as a new column and custom metrics that were pointing to “Revenue” will break.
Handling large and 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. For files stored on OneDrive, SharePoint, Box, and Dropbox, you can access the data in the other sheets when editing the data feed.
- For uploaded local files and Google Sheets, only the first 100 columns from your source data will be included in the data feed. We recommend using smaller data sets for your data feeds and metrics. If you need to create a data feed that includes > 100 columns, please contact our Support Team to discuss increasing your limit.
- 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 source data 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
- Editing data feeds - Go here if you want to learn more about editing data feeds.
- Data shapes and aggregation (video) - This video includes some helpful tips on organizing and preparing spreadsheet data.
- Creating metrics from Excel spreadsheets (video) and Creating metrics from Google Sheets (video) - These videos describe refining data in the data feed editor.
- Uploading spreadsheet files for data feeds and custom metrics
- Creating Google Drive data feeds and custom metrics
- Creating Microsoft OneDrive data feeds and custom metrics
- Creating Microsoft SharePoint data feeds and custom metrics
- Creating Google BigQuery data feeds and custom metrics
- Creating Dropbox data feeds and custom metrics
- Creating Box data feeds and custom metrics