A Sell-Through Rate (STR) report is a powerful tool for understanding your sales performance and identifying where you need to focus your efforts for improvement. It typically helps you gauge how well your inventory is moving, how quickly products are selling, and how your sales compare to your inventory.
To optimize your sales strategy in real-time, combining historical data (from TAP Explore) and forecasting data (from Forecasting) is essential. This article shows you one way you can approach it.
The instructions below are based on showing STR by station, position and delivery method. If you want to see STR on other parameters, update your TAP Explore and forecasting queries accordingly (making sure added parameters are in both queries).
TAP Explore
Query
Sell-Thru-Rate report type.
Date range: beginning of current month to today.
Dimensions: Stations, Position, Delivery Method.
Format Excel Export
Rename the Report tab: DateRange_STR.
Add a tab and name it DateRange_Forecast.
Add a column to the right of Delivery Method column, and name it Aggregated.
In this column add the formula to aggregate Station Name, Position and Delivery Method into one. This will be used as the vlookup value when we combine the STR report with the Forecasting report.
Forecasting
Query
Date range: today to the end of the current month.
Filter: All positions (by default only the mid-roll is selected; you need to add the other two positions to the filter).
Dimensions: Station, Position, Delivery Method.
Format Excel Export
No need to include contending data in your export.
Copy/paste the report into your STR report and in the Tab named DateRange_Forecast.
Add a column to the right of Delivery Method column and name it Aggregated.
In this column add the formula to aggregate Station Name, Position, and Delivery Method into one. This will be used as the vlookup value when we combine the STR report with the Forecasting report.
Add a column at the right of Available Inventory and name it Contending.
Add the formula to subtract avails from total (
=total Inventory-Available inventory)
Combine Historical with Future Data
Steps
Make sure no lines are missing (data in STR nor in Forecasting and vice-versa).
Add a new tab after the DateRange_Forecast tab, and name it ALL month STR.
Copy/paste the STR table, including Station, to the Aggregated column.
At the end of it, copy the same data from the Forecast tab (station to aggregated).
If you copied the header of the Forecast data, delete it now.
Select the entire table and in the Data tab click Remove Duplicates.
In the pop-up window, make sure everything is selected.
We are now ready to combine the data.
Add seven columns to the right of Aggregated column
Early Mo Capacity (this is the Total from the STR report).
End Mo Capacity (this is the Total Inventory of the Forecast Report).
Mo Capacity as of date (sum of Early Mo Capacity and End Mo Capacity).
Early Mo PAID Imps (this is the Impressions Paid in STR).
Booked Imps as of date (this is the Contending of the Forecast Report).
Total PAID Imps as of date (sum of Early Mo Paid and Booked Imps as of date).
Mo STR as of date = Total PAID Imps as of date / Mo Capacity as of date.
Formulas
Early Mo Capacity: =vlookup( Aggregated cell, STR table from Aggregated to Total(absolute values),6 (column number the data we need is in),false)
End Mo Capacity: =vlookup( Aggregated cell, Forecast table from Aggregated to Total inventory(absolute values),2 (column number the data we need is in),false)
Mo Capacity as of date: = Early + End Mo capacity
Early Mo PAID Imps: =vlookup( Aggregated cell, STR table from Aggregated to Impressions Paid(absolute values),3 (column number the data we need is in),false)
Booked Imps as of date: =vlookup( Aggregated cell, Forecast table from Aggregated to Contending(absolute values),4 (column number the data we need is in),false)
Total PAID Imps as of date: (= Early Mo Paid + Booked Imps as of date)
Before moving to the final column/formula (Mo STR as of date), remove all #N/A from your cells and replace them by 0 (zero) (except the columns with sum formula, they will update as you remove the #n/a). These come from stations/position not in both reports.
Mo STR as of date: = Total PAID imps as of date / Mo Capacity (format the column to percentage)
Possible Pivots
By station (value in pivot should be an average).
By position (value in pivot should be an average).
By Delivery Method (value in pivot should be an average).
All three dimensions.
Final Recommendations
Save and pull weekly (make sure to update dates to always cover the full month).
Create a template so calculation will be automated to the most recent data.
Create Master document where you will copy weekly Month STR result (from the template).
Each week add to Master doc in new tab to see changes week after week.