Spreadsheets to BigQuery: 8 steps to move Excel data into BigQuery using Supermetrics’ Custom Data Import

Excel is great for ad-hoc analysis and daily campaign reporting. However, if you reach your spreadsheet limit and/or want to do some advanced data analysis, moving Excel data into a marketing data warehouse like Google BigQuery is a better option.

In this article, I’ll show you how to do just that with Supermetrics’ new feature, Custom Data Import.

Psst: looking to move data from a Google Sheet to BigQuery? You can use the Google Sheets Supermetrics connector.

Step 1: Access Custom Data Import

Log in to Supermetrics Hub and go to the sidebar to manage data connections. Click ‘Data Sources’, and find ‘Custom Data Import’. The Custom Data Import feature allows you to bring any kind of data—zero-party data like surveys, out-of-home data, seasonal marketing data, etc.—into Supermetrics and then into your reporting tools.

Step 2: Import your data

Select ‘New Import’ to start. At this stage, you’ll need to upload a sample of the spreadsheet or file format you intend to use for your data transfer. Supermetrics supports a variety of common file types, including .csv, .xls, or .xlsx formats.

Tip: Uploading a sample file allows Supermetrics to analyze and provide a detailed preview of your data's structure, giving you the opportunity to make important adjustments before finalizing the import.

Step 3: Review and customize your data

After uploading your data, you’ll see a preview of how your data is organized. This gives you the flexibility to customize the import process according to your specific requirements.

For instance, you can choose what you want to import by specifying the row. If your file contains header rows or unnecessary information at the top, you can exclude these rows from the import. If there are unwanted rows at the end of the file, such as totals, summaries, or any other non-essential data, you can remove them as well.

Step 4: Complete column configuration view

Once you’re happy with how your data looks, move to the column configuration view. This section is critical for ensuring that your spreadsheet data is properly organized and formatted for your chosen reporting tools in this case, it’s BigQuery.

In the column configuration view, you can assign specific data types to each column in your file. For example, you can choose whether a particular column contains text, numerical values, dates, or other data types. Properly assigning data types is important because it makes sure that your data is interpreted correctly once it's imported into BigQuery.

In addition to setting data types, Custom Data Import provides advanced users with the option to create custom transformation rules using regular expressions (REGEX). This is useful if your data contains inconsistencies or requires advanced manipulation before analysis. For example, you could use regex to standardize phone number formats or remove unwanted characters from text fields.

Tip: if you’re not familiar with REGEX or find it daunting, you can still apply transformations later using Custom Fields. Custom Fields offer a more user-friendly way to modify and clean your data after it has been imported, giving you the flexibility to make changes as needed without requiring advanced technical knowledge.

Step 5: Set data update frequency

Next, you can choose how often you want your data to be updated.

One option is to do a full refresh and completely replace all existing data with each new update. This is useful if you want to ensure that only the most current data is made queryable in Supermetrics, as it overwrites the previous dataset entirely. However, it may not be ideal if you need to retain historical data for querying or tracking changes over time.

Another option is to simply append new data to the existing dataset. This adds new records to your data without affecting the existing ones.

The last option is append and deduplicate, which updates specific records based on a unique identifier, such as an ID number or another unique field within your dataset. By doing so, you can make sure that the records corresponding to the unique identifier are updated while the rest of the dataset remains unchanged. It’s great for scenarios where you need to continuously update certain fields—like prices, inventory levels, or customer information—without altering the entire dataset.

Step 7: Test file

Upload a test file to verify the configuration. This step allows you to verify that the data is being transferred correctly and that all configurations, such as column mappings and data type assignments, are accurate.

For a more efficient and hands-off approach, set up an automated data export from your source system. Automation allows your data to flow continuously and without interruption, saving you time and reducing the risk of human error. For example, if you’re pulling data from your CRM, you can configure it to automatically generate and send export files on a regular basis. Many CRMs and other marketing platforms offer built-in scheduling features that allow you to export data daily, weekly, or at whatever frequency suits your needs.

Step 8: Source configuration

To fully automate your data transfers, you can configure your CRM or source system to send these export files directly to the email address provided by Supermetrics' Custom Data Import tool.

Tip: if you need to perform additional transformations, calculations, or modifications to your data before it is transferred to BigQuery, Supermetrics offers the option to create Custom Fields. Custom Fields allow you to manipulate the data in various ways, such as combining multiple columns, applying mathematical formulas, or even reformatting data to meet specific requirements. Here’s a guide to Custom Fields for more info.

That’s it! That’s how you can automatically bring Excel data inBigQuery. But don’t stop there! Book a demo and see how you can easily integrate any type of data—online and offline—into your reporting tools with Custom Data Import.