HomeTechnologyHow to Create an Excel File that Pulls Customer Data with WooCommerce...

How to Create an Excel File that Pulls Customer Data with WooCommerce and Python

“Hey, could you please send me our clients’ emails? We’re about to launch a new marketing campaign and…we need them ASAP.”

If you work in the Web department on a corporate level, you’ve probably heard this sentence hundreds of times. So, today I want to share how I solved this issue for a company I worked at.

What Was the Request?

We had an e-commerce app constructed with WooCommerce, that got thousands of orders every day worldwide. To register, users had to add their first name, last name, and email address.

Our marketing department requested my team to provide them with the email address and first names of all our clients to launch a new campaign.

I anticipated them to ask for a CSV file to upload contacts massively to their marketing platform. Instead, they told me they desired an Excel file since they had to edit it before going ahead with the campaign launch.

Once they assigned me the Jira issue, I was ready to start.

Analyzing the Problem

WooCommerce provides users with the export functionality to get clients’ data but it doesn’t generate an Excel file. It generates a CSV or an XML file.

When you work with a tool like WordPress, the first option you think about is using a plugin and get what you need.

I found a few options on the WordPress plugin directory, but there is a lot to consider every time you install a new plugin in your instance. You have to think about maintenance costs (consider every time a developer works on it, they log hours that affect your budget), security vulnerabilities, and – last but not the least – purchase approval can take a lengthy time.  

I also considered a second option: downloading the CSV via the user interface and turning it into an Excel file by using 1 of the thousand services you can find online.

But using third events services could break critical security and privacy policies and the final result is not always reliable. So I decided to go no further in this path.

In the end, I thought developing a script was the best and quickest option to solve this task.  

WordPress provides developers with APIs. Many other plugins from the WordPress system provide APIs as well, and WooCommerce is not an exception. The documentation is robust and it offers hyperlinks to libraries for the most used languages such as Node.js, Python, PHP, and Ruby.

I decided to go with Python and use this technology to develop a script that generates an Excel file with the following structure:

  • Two columns: first name and email
  • One row per customer

I chose Python for several causes: there are hundreds of libraries that can help you get get the job done, it is flexible, and it is very useful when it comes to handling data.

I also decided to use Pandas and Openpyxl to handle data and create the Excel file.

Before coding, let’s get what we need

To work with the WooCommerce API, I need to generate the APIs keys to work with. You can get more information on WooCommerce official documentation.

First, you need to log into your WooCommerce instance, and then go to WooCommerce > Settings > Advanced > REST API.

How to Create an Excel File that Pulls Customer Data

Then I’ll give it a brief description, choose the user I want to generate the API for, choose the permissions granted (Read/Write or both), and then hit “Generate API key”.

Next, I’ll get the Consumer Key and the Consumer Secret:

1633710066 970 How to Create an Excel File that Pulls Customer Data

How to Prepare the Environment

The very first thing I did was add the necessary libraries to develop my script. I initiated with the Python library for WooCommerce. To install it, I ran this command:

pip install woocommerce

Good. Now I’ll go ahead and install Pandas, a data analysis and manipulation tool for Python:

pip install pandas

After that, I installed Openpyxl, a Python library to read and write Excel files:

pip install openpyxl

Let’s code

I made the API call using the API function provided by the WooCommerce Python library and I stored it in a variable. Then I passed the function the base URL of my WooCommerce website, the consumer key, the consumer secret, and the version.

wcdata = API(
    url="<BASE_URL>",
    consumer_key='ck_XXXXXXXXXXXXXXXXXXXX',
    consumer_secret="cs_XXXXXXXXXXXXXXXXXXXX",
    version='wc/v3'
)

Then I used the GET function to call the “clients” endpoint and I created regionally a JSON file (“contacts.json”) with the data I got from the endpoint I called right before:

newJson = wcdata.get('clients').json()
with open('contacts.json', 'w') as f:
    json.dump(newJson, f, ensure_ascii=False, indent=4)

I transformed it to a Pandas object and stored it in the “df_json” variable:

I used the to_excel() function to turn the object into an Excel file. I passed the function 3 arguments:

  • The name of the file I was about to create
  • The index, set to “untrue” since I didn’t want the record id to be printed on my file
  • The columns I needed to print on my file (first_name and email)
df_json.to_excel('customers_contacts.xlsx', index=False, columns=('first_name', 'email'))

I ran the script and I got this:

1633710066 323 How to Create an Excel File that Pulls Customer Data

That’s it. This is how I created an Excel file with clients’ emails and first names with WooCommerce APIs and Python in less than 20 lines of code.

Of course, it is a script you can run via the command line when desired or you can also automate it to regularly generate reviews about the e-commerce you and your team are operating.

Final thoughts

I also needed to share some other content I found on the web while studying to develop this script.

The first 1 is a Stack Overflow question. It helped me optimize my code while creating the JSON file. I really appreciated the chosen question, especially when it suggested how you can write a “nicer” JSON file on a modern system.

The second 1 is about Pandas. If you write Python code, 1 day or another, you’ll have to deal with data and their manipulation. This article by Erik Marsja explains really well how you can convert your JSON file to Excel with Pandas. It provides readers with several tips on how they can use this powerful library to display the data they want in an efficient and efficient way.

Feel free to share this post if you found it useful! You can also find the full code on this Github repo.

Source

Most Popular