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.
Step 2: Configure the "Grouping and Aggregation" options.
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:
Example #2: Product Variant Order Summary
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:
Example #3: 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:
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:
Please feel free to contact us if you need any assistance with using this feature.
App: EZ Exporter
Tags: advanced features