Reporting & Analysis
Oct 5, 2023
TLDR
This is not a calculator to determine how much to mark down your prices; there are plenty of those out there. This is how you analyze your store sales data to determine what the sell-through rates of markdowns are compared to full price items.
One of the ways to clear out inventory or attract price-sensitive customers is through price reductions, also known as markdowns. In retail terminology, markdowns are permanent reductions in price on items that are moving too slowly off the shelves, either due to end of season, changes in trends, lack of inventory space, and so on. Because markdowns are permanent, they differ from sales, where prices are reduced temporarily.
Analyzing the results of your retail markdown strategy can provide insights into consumer buying behavior and the effectiveness of your pricing tactics. These insights have applications in:
Acquisition: Understanding whether new customers tend to be attracted by markdowns.
Customer LTV: Understanding whether customers acquired through markdowns eventually move on to buy items at full price.
Merchandising: Understanding whether new season launch campaigns should also show shoppers marked down items.
Pricing strategy: Understanding the optimal timing and depth of markdowns to maximize sales and profit.
Marketing: Understanding what marketing messages may resonate better with your audience, for ad creative and copy testing.
There are two ways in which you can compare the sales of marked down items against full price sales: manually using Shopify data exports, or automatically using Airboxr. Let's look at both methods.
Manual analysis from Shopify data exports
You can create a report to analyze your marked-down sales performance by manually downloading and working on your data in spreadsheets. Here’s how:
Step 1: Export your inventory data
Step 2: Export your orders data
Step 3: Create a column in the inventory spreadsheet
In your inventory data spreadsheet, create a column called “Price” to indicate if a product is either discounted or is selling at full price. This doesn't exist in the Shopify export, so it has to be added manually.
Step 4: Create a pivot table with the orders spreadsheet
From your orders data spreadsheet, create a pivot table to get a summary of Subtotal and Quantity ordered by SKU.
Step 5: Use XLOOKUP to confirm price status
Use XLOOKUP to confirm if the products sold were markdowns or full price by looking up the SKUs in the inventory table.
Step 6: Create another pivot table to get a summary of your marked down vs. full price sales report
Create another pivot table to get a summary of markdowns vs. full price sales.
Automated analysis using Airboxr
Airboxr creates a marked-down vs. full-price report in a matter of seconds using your Shopify data. It analyzes which orders are discounted by comparing the original price of the product and the price the product was sold. If the original price is greater than the selling price, then it is calculated as a marked down sale. The report also calculates the sell-through rates for markdowns vs. full priced items sold, as well as the percentage of their total sales.
Here’s how you can create your own report using Airboxr:
Step one: Select the hop
Access the hop from our Hops Marketplace. You can also open a spreadsheet in Airboxr and search for the hop from the sidebar.
Improve your DTC game. Sign up for weekly tips.
Step two: Customize your report (Optional)
You have the option to adjust the time period as you wish. You can also customize the report to only include data from a particular sales channel of your choice. To view which sales channels your orders are coming from, run the Order Export Hop and view the column named Sales Channel.
Step three: Run your hop
Click Run and watch the magic happen, as your report is prepared in a spreadsheet in a matter of seconds.
Bonus: Schedule your Markdown vs. Full-price analysis report
You can easily schedule your report on your sheet so it updates automatically. Simply click on the Schedule button to set up a schedule. You can also enable notifications via email.
It is important to emphasize that this report does not include discounted sales through coupons. To check your coupon code performance, run our Coupon Code Performance Hop.
If you need a custom report built, feel free to reach out to us via our in-app chat.
