Cleaning data for analysis and consistency across systems is a daunting task, especially when you don’t know how to code.
It’s not just about the technical skills required but also the time and effort involved in manually refreshing spreadsheets and disparate systems, such as CRMs and databases, when using traditional data cleaning techniques.
Why struggle when AI and no-code automations can do the heavy lifting? Fortunately, there are ways to combine the power of AI and automation with the flexibility of spreadsheets to transform your data cleaning process.
In this blog, we’ll demonstrate an example by pulling in Salesforce data, cleaning it using AI, and pushing it back to MySQL for a mass update – without ever leaving your spreadsheet with whatever systems you use. It’s like having a personal data scientist at your fingertips!
Prefer a visual guide? Check out the video tutorial below!
Step-by-Step Guide: Salesforce / MySQL Data Cleaning Made Easy with Coefficient
Coefficient is a free Google Sheets add-on that allows you to connect your business systems to Google Sheets and sync real-time data into your spreadsheet.
Before you start importing your data, install Coefficient.
Open Google Sheets. Click Extensions in the top ribbon and select ‘Add-ons’ -> ‘Get add-ons.’
Input “Coefficient” in the Google Workspace Marketplace search menu and select the Coefficient app.
Click ‘Allow’ to grant Coefficient access to your Google account.
Wait a few moments for the install to complete, then return to your spreadsheet menu.
Click on Extensions -> Coefficient -> Launch.
Coefficient will open on the right side of your spreadsheet.
Now let’s import your Salesforce data.
Click ‘Import from…’ in the Coefficient menu.
Select ‘Salesforce’ as your data source.
Click ‘From Object & Fields.’
Choose ‘Account’ as your object and click next.
Click ‘Select Fields.’
Choose the following fields: ‘Account ID,’ ‘ Owner Email,’ and ‘Full Name.’
Click ‘Import’ to populate your spreadsheet with your live data.
Now it’s time to clean our data!
For this example, we’ll separate the ‘Full Name’ column into separate ‘First Name’ and ‘Last Name’ columns.
First, create those labels in columns D and E.
Now, use Coefficient’s GPT Copilot Formula Builder to generate a formula that will separate the ‘Full Name’ column into unique ‘First Name’ and ‘Last Name’ columns.
Return to the Coefficient menu and select ‘GPT Copilot.’
Select ‘Formula Builder.’
Now describe the formula you wish to build, here: “Column c starting at row 3 contains full name. I want to separate full name into first name column in row d and last name column in row e.”
Click ‘Build.’
Copy the formula.
Paste the formula into the appropriate cell.
Select the cell range and drag it down to apply it throughout your spreadsheet.
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.
After manipulating the data, it’s time to write data back to MySQL.
This is done using the ‘Export to MySQL’ function in the Coefficient sidebar. The data is exported with just a few clicks in the Coefficient sidebar, allowing for a seamless transition from data cleaning to data exportation.
Return to the Coefficient menu and select ‘Export to…’
Select ‘MySQL.’
Now select your source data: your current tab and the header row (row 2).
Click ‘Next.’
Choose the opportunities table.
Then select ‘Update’ to update your existing records.
Click ‘Next.’
Now, let’s map our fields.
Click ‘Account ID.’
Select ‘Opportunity ID.’
‘First Name’
Click ‘owner_first_name.’
Repeat to map the last name accordingly.
Check to see if your mappings are correct, and click ‘Export…’
Update your entire sheet by selecting ‘All rows on sheet.’ Click next.
Finally, confirm your update.
Clean Salesforce Data and Export it Back to MySQL in Minutes
And just like that, you’ve imported live Salesforce data into Google Sheets, cleaned it in your spreadsheet, and exported it back to MySQL without using a single line of code!
With a simple but robust data cleaning process using today’s best practices, you can finally bring consistency and reproducibility to the table.
Get started with Coefficient for free today and start streamlining your data preparation and analysis directly in Google Sheets with more accuracy, reliability, and efficiency than ever before.
