How to Clean Data with AI: A Salesforce / MySQL Example

Last Modified: August 21, 2023 - 4 min read

Julian Alvarado

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.’

Install the Coefficient add-on

Input “Coefficient” in the Google Workspace Marketplace search menu and select the Coefficient app.

Search for the coefficient data connector from the menu

Click ‘Allow’ to grant Coefficient access to your Google account.  

Click allow to grant Coefficient permissions

Wait a few moments for the install to complete, then return to your spreadsheet menu.

Click on Extensions -> Coefficient -> Launch.

Launch coefficient from the extensions menu

Coefficient will open on the right side of your spreadsheet.

Now let’s import your Salesforce data.

Click ‘Import from…’ in the Coefficient menu.

Click import from... on the coefficient menu

Select ‘Salesforce’ as your data source.

Select salesforce as your data source

Click ‘From Object & Fields.’

Select from objects and fields

Choose ‘Account’ as your object and click next.

select account as your object

Click ‘Select Fields.’

Click select fields...

Choose the following fields: ‘Account ID,’ ‘ Owner Email,’ and ‘Full Name.’

select account id, email, and full name as your fields

Click ‘Import’ to populate your spreadsheet with your live data.

wait for your live salesforce data to be imported

Now it’s time to clean our data!

Your data will now appear in your spreadsheet

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.

Manually create two new columns for the first and last names

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 GPT Copilot

Select ‘Formula Builder.’

select formula builder from the gpt copilot menu

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.’

describe the formula you want to build

Copy the formula.

copy the output from the formula builder

Paste the formula into the appropriate cell.

Paste the formula in the cell

Select the cell range and drag it down to apply it throughout your spreadsheet.

Coefficient Excel Google Sheets Connectors
314,000 Pros Sync Live Data from Their Business Systems into 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.

Paste the formula in the cell and drag it down to apply to each row

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…’

Return to the coefficient menu and select export to...

Select ‘MySQL.’

select mysql to export your data

Now select your source data: your current tab and the header row (row 2).

Click ‘Next.’

select your source data header row

Choose the opportunities table.

choose opportunities as your destination

Then select ‘Update’ to update your existing records.

Select update data for existing records

Click ‘Next.’

Now, let’s map our fields.

Click ‘Account ID.’

select account ID and map to a field

Select ‘Opportunity ID.’

select opporunity as your field

‘First Name’

map a field to the first name column

Click ‘owner_first_name.’

select opportunity ID to map it to first name

Repeat to map the last name accordingly.

Check to see if your mappings are correct, and click ‘Export…’

Repeat the steps above for last name

Update your entire sheet by selecting ‘All rows on sheet.’ Click next.

select the rows you want to update

Finally, confirm your update.

press confirm to push the 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.

Exported with Wordable

Set Spreadsheet Data on Refresh

Try the Spreadsheet Automation Tool Over 300,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 300,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Julian Alvarado Content Marketing
Julian is a dynamic B2B marketer with 8+ years of experience creating full-funnel marketing journeys, leveraging an analytical background in biological sciences to examine customer needs.
Google icon
300,000+ users on Google Marketplace
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies