How to Clean a Messy Stripe / Shopify / Gumroad CSV Before Google Sheets (or Make)

When you export data from platforms like Stripe, Shopify, or Gumroad and import it into Google Sheets, you often encounter problems such as duplicate entries, phone numbers in various formats, headers with spaces or mixed cases, and inconsistent email addresses. This guide provides a step-by-step process to clean such messy CSV files manually, ensuring they're ready for import into Google Sheets or Make without any issues.

The first step is to address the header names. Headers with spaces or title case (e.g., "Full Name") can cause issues with Make mappings and QUERY ranges in Sheets, so it's best to rename them in snake_case (e.g., "full_name"). After renaming, freeze the header row and rename columns by hand or using a helper row for consistency.

Next, focus on removing leading and trailing spaces in text columns. Use the TRIM function to clean up these spaces, then convert the data to values to avoid any further alterations. Additionally, remove any blank rows, which can result from the export process. You can filter and delete rows that contain only blanks or commas.

Another crucial step is normalizing email addresses. Lowercase all email addresses and trim any extra spaces. This ensures that different cases of the same email (e.g., "Jane.DOE@Example.com" and "jane.doe@example.com") are treated as the same. While heavy regex validation can be complex in Sheets, a simple lowercase and trim operation is usually sufficient for most cases.

For phone numbers, choose a consistent formatting strategy. For US-based files, a common approach is to remove all non-digit characters, leaving only the digits. If the resulting number is 11 digits and starts with a '1', remove the leading '1'. Ensure that the final phone number is 10 digits long and stored as text to prevent Sheets from misinterpreting it.

Dedupe entries based on a reliable key. For customer lists, email is the best key to dedupe on, whereas for transaction records, the order ID is more appropriate. In Google Sheets, go to Data > Data cleanup > Remove duplicates and ensure you're only checking the key column (email or order ID). This step helps eliminate duplicate records, ensuring each customer or transaction is correctly represented.

If your data requires splitting full names into separate first and last name columns for certain applications, do so carefully. While automation can help, human oversight is still essential to maintain accuracy, especially when dealing with multi-part names.

Finally, after cleaning the data, export it as a UTF-8 CSV file to ensure compatibility with Make and other tools. Open the file in a text editor and do a quick spot-check to verify that headers are in snake_case, emails are lowercased, phone numbers are consistent, and there are no remaining blank rows.

This manual cleaning process, while time-consuming for larger datasets, ensures that your CSV files are clean and ready for import into Google Sheets or Make. For larger files, consider using a small, repeatable cleaner like OpsCSV to automate these steps. Regardless of the method, maintaining a consistent workflow will prevent fragile dependencies in your Make and Sheets setups.