Grouping and Aggregating Your Shopify Data with EZ Exporter
Grouping and aggregating data is now available in EZ Exporter!
This feature allows you to generate all kinds of summary reports to get more insights from your Shopify data. Below is just small list of the different types of reports you can now create thanks to this new 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
- Sales by City, State/Province, Country, Zip Code
- Products by Type or Vendor
- Orders by Referring Site
- Orders by Shipping City, State/Province, Country, Zip Code
- Average Ordered Quantity by Variant
- New Customers by Day, Week, Month
- Top Selling Products
- Number of Customers by City, State/Province, Country, Zip Code
As you can see from the examples above, there's a lot of combinations you can do. We designed this feature to be pretty flexible, so you can play around with the different combinations to get your desired report output.
The basic idea here 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. There are 6 aggregation functions that you can use:
- COUNT
- COUNT UNIQUE
- MIN
- MAX
- MEAN (AVERAGE)
- MEDIAN
- SUM
You can group by multiple fields and use multiple aggregation functions as well. 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: Products 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:
We really hope this new feature will bring a lot of value to Shopify merchants. We've put quite a lot of effort into building this functionality.
There's of course always something that can be improved. We'd love to hear your feedback!
Tags: new features, shopify, csv export, ez exporter, reporting