Data formatting guidelines
This guide is designed to assist users in preparing their data to ensure seamless integration into our application. Proper data preparation is crucial for efficient and error-free import, particularly when dealing with spreadsheets and BigQuery databases. Here, you will learn how to format your tables and determine the appropriate format for various columns to facilitate easy and accurate data integration.
Wide vs. Long table format
In data analysis and reporting, it is essential to understand the different ways data can be structured within tables. The two primary formats used are Wide Format and Long Format. Understanding whether your data is in wide or long format is crucial when orchestrating your data into channels for modeling purposes.
Wide format
In the wide format, each column represents a different variable or metric, and each row corresponds to a single observation. This format is akin to a pivot table and is particularly useful for comparative analysis across multiple metrics simultaneously.
| Date | FB Spend | Gads Spend | TikTok Spend | Revenue |
|-------------|----------|------------|--------------|----------|
| 01.08.2024 | 100 | 200 | 400 | 1000 |
| 02.08.2024 | 300 | 200 | 200 | 1050 |
Forvio demo data template exampleLong format
The long format, also known as "stacked" or "tidy" format, organizes data such that each row represents a single metric for an observation. It typically includes columns for quantitative values and their corresponding identifiers, like the data source or campaign.
| Date | Spend | Source | Campaign |
|-------------|----------|------------|--------------|
| 01.08.2024 | 100 | FB | |
| 01.08.2024 | 200 | Gads | |
| 01.08.2024 | 300 | Tiktok | C1 |
| 01.08.2024 | 100 | Tiktok | C2 |
| 02.08.2024 | 300 | FB | |
| 02.08.2024 | 200 | Gads | |
| 02.08.2024 | 100 | Tiktok | C1 |
| 02.08.2024 | 100 | Tiktok | C2 |
Date formatting
One of the most common problems encountered when importing data in tabular form is incorrect formatting of date columns. To ensure that the data is imported correctly and efficiently, it is essential to verify that the date entries adhere to one of the following recognized formats:
By ensuring your date data is formatted according to one of these standards, you can greatly reduce the likelihood of errors during the data import process.
Handling zero values in data tables
In the context of data preparation for our system, it is important to address how numerical values, especially zero values, are managed within your tables. Our system is designed to automatically remap tables where numerical entries are missing; however, to minimize potential errors and enhance data integrity, it is advisable to explicitly fill these cells with a zero value ('0').
Best practices for zero values:
- Preparation: When preparing your data tables, ensure that all cells intended to represent a value of zero are explicitly filled with '0'. This helps prevent any ambiguity that might arise during data processing.
- Consistency: Maintaining consistency in how zero values are represented across your datasets ensures that the data is accurately interpreted by the system. This consistency is crucial for accurate analysis and modeling.
- Data Integrity: Explicitly defining zero values enhances the overall integrity of your data. It prevents the system from misinterpreting empty cells as missing data, which could affect the outcomes of data analysis.
Correct
| Date | FB Spend | Gads Spend | TikTok Spend | Revenue |
|-------------|----------|------------|--------------|----------|
| 01.08.2024 | 100 | 0 | 400 | 1000 |
| 02.08.2024 | 300 | 200 | 0 | 1050 |
Incorrect
| Date | FB Spend | Gads Spend | TikTok Spend | Revenue |
|-------------|----------|------------|--------------|----------|
| 01.08.2024 | 100 | | 400 | 1000 |
| 02.08.2024 | 300 | 200 | | 1050 |