If you’re running an online store on WordPress, chances are you’re using WooCommerce to manage customer data and orders. With the holiday season approaching, you may want to send special discount codes to existing customers or analyze store data to assess performance across different regions.
WooCommerce provides a built-in export feature that allows you to export customer data to a CSV file, which you can then upload to Google Sheets. Simply go to your WooCommerce dashboard, navigate to the Customers section, and select the option to download the customer list as a CSV file.
For a more automated and efficient solution, you can use Google Apps Script to create a custom script that directly exports your WooCommerce customer data into Google Sheets. Here’s how to do it:
Step 1: Create an API Key in WooCommerce
Start by generating an API key in WooCommerce. In your WooCommerce dashboard, go to the Settings section, click on the Advanced tab, and then navigate to Rest API. Click the Create API Key button. Give your key a name, such as “Import Customers to Google Sheets.” Set the API key permissions to “Read Only,” as you will only need access to view the customer data, not modify it. WooCommerce will generate a consumer key and consumer secret—make sure to save the secret key, as it won’t be accessible later.
Step 2: Create a Google Sheet
Next, create a Google Sheet to store the customer data. In your browser’s address bar, type sheets.new to create a new spreadsheet. Then, go to Extensions > Apps Script to open the Google Apps Script editor for the spreadsheet.
const MAX_PER_PAGE = 100;
const CONSUMER_KEY = '<>';
const CONSUMER_SECRET = '<>';
const WORDPRESS_DOMAIN = '<>';
const fetchWooCommerceCustomers = () => {
const bearerToken = Utilities.base64Encode(`${CONSUMER_KEY}:${CONSUMER_SECRET}`);
const getQueryString = (options) => {
return Object.keys(options)
.map((key) => `${key}=${options[key]}`)
.join('&');
};
const getApiUrl = (pageNum) => {
const options = {
context: 'view',
page: pageNum,
per_page: MAX_PER_PAGE,
order: 'desc',
orderby: 'id',
role: 'customer',
};
return `${WORDPRESS_DOMAIN}/wp-json/wc/v3/customers?${getQueryString(options)}`;
};
// Fetches a single page of customer data.
const fetchPage = (pageNum) => {
const url = getApiUrl(pageNum);
const response = UrlFetchApp.fetch(url, {
headers: {
'Content-Type': 'application/json',
Authorization: `Basic ${bearerToken}`,
},
});
return JSON.parse(response.getContentText());
};
let page = 1;
let allCustomers = [];
let hasMore = true;
do {
const customers = fetchPage(page);
allCustomers = allCustomers.concat(customers);
page += 1;
hasMore = customers.length === MAX_PER_PAGE;
} while (hasMore === true);
return allCustomers;
};
Step 3: Add the Script to Fetch WooCommerce Customer Data
In the Apps Script editor, paste the provided script, replacing the WooCommerce consumer key, consumer secret, and WordPress domain with your own details (don’t include a slash at the end of the WordPress domain). The script will fetch all customer data from your WooCommerce store.
const parseCustomer = (customer) => {
const { id, first_name, last_name, email, billing = {} } = customer;
return {
customer_id: id,
first_name,
last_name,
customer_email: email,
billing_first_name: billing.first_name,
billing_last_name: billing.last_name,
billing_email: billing.email,
billing_phone: billing.phone,
billing_address_1: billing.address_1,
billing_address_2: billing.address_2,
billing_city: billing.city,
billing_state: billing.state,
billing_postcode: billing.postcode,
billing_country: billing.country,
};
};
Step 4: Flatten the Customer Data
To format the data properly, add a function to the script that flattens the customer data. This ensures that the data is structured correctly for easy storage in the Google Sheet.
const exportCustomersToGoogleSheet = () => {
const wooData = fetchWooCommerceCustomers();
const customers = wooData.map(parseCustomer);
const headers = Object.keys(customers[0]);
const rows = customers.map((c) => headers.map((header) => c[header] || ''));
const data = [headers, ...rows];
const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
const message = rows.length + ' customers exported to sheet ' + sheet.getName();
SpreadsheetApp.getUi().alert(message);
};
Step 5: Store the Customer Data
Now, add another function to the script to store the customer data in your Google Sheet. This will automatically transfer the data into the sheet for easy access.
Step 6: Run the Export Function
In the Apps Script editor, select the exportCustomersToGoogleSheet function and click Run. Authorize the script to access your Google Sheet and WooCommerce account. Watch as the customer data from WooCommerce is imported directly into your Google Sheet.
You can also use Gmail Mail Merge to send personalized emails to your customers directly from the Google Sheet. This method provides a simple and efficient way to manage your WooCommerce customer data and reach out to your audience.
