EZ Exporter Update: Grouping and Aggregation Now Supports a LIST() Function

In addition to the MIN, MAX, SUM, MEAN, COUNT, and UNIQUE COUNT functions, EZ Exporter also now supports a LIST function.

This new function allows you to combine a group of field values in a JSON list/array.  If you're familiar with SQL, this is similar to MySQL's GROUP_CONCAT() function or PostgreSQL's STRING_AGG() function.

This enables creating summary reports where you can include a column including a comma-separated list of values from a grouped data.

Some examples:

  • Group orders by product and export a comma-separated list of countries each product is being shipped to.
  • Group orders by product and export a comma-separated list of email addresses of customers who ordered each product.
  • Group orders by product and export a list of order numbers associated with the purchased product.
  • Group customers by country and export a list of email addresses for each country.
  • Group product variants by the first variant option and export a list of SKUs for each option.

Let's look at how this works in the app directly by setting up the first example.

In the Grouping and Aggregation section, group the data by the Lineitem Name and aggregate the Shipping Country field using the LIST function as shown below:

The output will look something like this:

As you can see, there are many duplicates in the list since many orders were being shipped to the same country.  To only export unique countries, we can use the Calculated Fields feature with this formula:

", ".join(set(load_json_data({{ shipping_address.country__list }})))

The set() function in the formula removes the duplicates in the list.  Let's take it a step further and also sort the final output alphabetically:

", ".join(sorted(set(load_json_data({{ shipping_address.country__list }}))))

Below is the final output:

As you can see, it's now much cleaner. Since Calculated Fields are quite flexible, you can do a lot of additional manipulation to the grouped data with it.

While our "Grouping and Aggregation" freature still won't be as flexible as the Pivot Table feature in Excel or Google Sheets, this new addition should hopefully be able to handle the majority of use cases of our users when creating summary reports.

Tags: new features, ez exporter, shopify data export, shopify summary reports