EZ Exporter Grouping and Aggregation

The Grouping and Aggregation feature can be found in the Data Settings of EZ Exporter.

"Grouping and Aggregation" allows you to create summary reports of your Shopify data. Below is a sample list of possible reports you can generate with this feature:

  • Sales by Hour, Week, Day, Month, Year, Day of Week (which days have the highest sales, for example)
  • Sales by Customer, Customer Lifetime Value (CLTV), Average Order Value
  • Sales by Product or Variant
  • Sales by Product Type or Category
  • Average Ordered Quantity by Variant
  • New Customers by Day, Week, Month
  • Best Selling Products
  • Sales by City, State/Province, Country, Zip Code
  • Number of Customers by City, State/Province, Country, Zip Code

Note: These summary reports won't match the values in the Shopify Admin's Analytics reports as they're calculated differently (more info).

The basic idea is to "group" your data first based on a field, such as the "Created At" field, and then "aggregate" that grouped data using mathematical functions. Below are the aggregation functions that you can use:

  • COUNT
  • COUNT UNIQUE
  • MIN
  • MAX
  • MEAN (AVERAGE)
  • MEDIAN
  • SUM
  • LIST (returns a JSON list/array of items in the group, similar to GROUP_CONCAT() in MySQL or STRING_AGG() in PostgreSQL)

You can group by multiple fields and use multiple aggregation functions as well.  It's also important to note that only fields in the "Fields to Group By" or "Fields to Aggregate" will contain values when using this feature.

Below are a few examples to help get you started.

Example #1: Total Sales by Day

This is a pretty typical type of report to run. What we want to do here is "group" the order data by the order creation date and then "aggregate" the "Total Price" of all the orders for each day using the SUM() function.

Step 1: Set the "Date and Time" format to YYYY-MM-DD. This way, we can group the "Created At" field by the day. If you want to group the data by month, then you can simply choose the YYYY-MM format.

 

EZ Exporter Shopify App - Changing Date and Time Format

Step 2: Configure the "Grouping and Aggregation" options.

EZ Exporter Shopify App - Grouping and Aggregation Configuration - Sales by Day

As you can see in the screenshot, we first added "Created At" to the Fields to Group By and then configured the Fields to Aggregate using a combination of functions to get the results that we need.

The output of this report will look something like this:

EZ Exporter Shopify Apps - Grouping and Aggregation - Sales by Day - Output

Example #2: Product Variant Order Summary

EZ Exporter Shopify App - Grouping and Aggregation Configuration - Orders by Lineitem

As you can see from the configuration above, we grouped the data by "Lineitem Variant ID." We could've grouped by "Lineitem Name" as well, but using the Lineitem Variant ID is more reliable as it can't be changed, while the "Lineitem Name" can change.

The output will look something like this:

EZ Exporter Shopify App - Grouping and Aggregation - Orders by Lineitem Output

Example #3: Customers by Country

EZ Exporter - Grouping and Aggregation - Customers by Country

As you can see from this example, we're grouping the data by "Country" first, then grouping it again by whether the customer "Accepts Marketing."

The output will look something like this:

EZ Exporter - Grouping and Aggregation - Customers by Country Output

Example 4: Orders by SKU

Group the data by the Lineitem SKU and export a column containing a list of order names associated with each SKU.

The ouput will look something like this:

Using Aggregated Values in Custom and Calculated Fields

You can go even a step further and use the values of the aggregated fields in Custom or Calculated Fields. For example, you may want to take 50% off the aggregated total price. You can do so in a Calculated Field like this:

{{ total_price__sum }} * 0.50

You can see the main difference here is the field ID now has __sum appended to it to distinguish between the different aggregated fields.

For instance, you may want to add the average order value to the aggregated total price. In this case, there will be multiple total_price fields in use, one for __sum and the other for __mean. The formula will then look something like this:

{{ total_price__sum }} + {{ total_price__mean }}

To make it easier for you to figure out how to reference these field values, we've added a small text right below each aggregation field in the Data Settings as shown below:

EZ Exporter Shopify App - Referencing Aggregated Values

Please feel free to contact us if you need any assistance with using this feature.


App: EZ Exporter

Tags: advanced features