Top SKU Sales Analytics Macro - User Guide

Overview

This macro generates and emails a dynamic "Top Products" sales report. It is designed to help you identify which products are driving your revenue, how their performance compares to previous periods, and what their current inventory status is.

The final output is a clean, table-based HTML email sent to a specified client, complete with actionable alerts for marketing and restocking.

How It Works

The macro's power comes from its ability to compare three different time periods. When you run the macro, you define the length (in days) for these three periods.

1. Report Period: This is the main period for the report. The macro finds the Top X SKUs based on revenue from this period. (e.g., the last 7 days)
2. Comparison Period: This is the period immediately before the Report Period. The macro calculates the "vs. Prior Period %" by comparing sales in the Report Period to this period. (e.g., the 7 days before the Report Period)
3. Benchmark Period: This is the period before the Comparison Period. It is used to calculate a stable daily sales average. This average is used for two key metrics:
   • The "vs. Benchmark Avg %" (to see if a product is trending).
   • The "Days of Stock Left" calculation (to provide stable restocking alerts).
   (e.g., the 30 days before the Comparison Period)

Based on this analysis, the macro generates two types of alerts for the Top X products:

• Marketing Alerts: Triggered if a product's sales are significantly higher (e.g., +50%) than its long-term benchmark average.
• Restock Alerts: Triggered if a product's "Days of Stock Left" (based on its benchmark average) falls below a set threshold (e.g., 3 days).

Email Report Preview

This is how the final email looks to the client, showing key performance indicators and actionable alerts based on the defined periods.

Setup & Configuration

To set up this macro, you must configure it in the Linnworks script execution settings. The Execute method requires 11 parameters to be passed in.

Parameter Name	Type	Description	Example
Report Parameters
reportPeriodDays	Integer	The main period for the report (e.g., "Top 5 from last 7 days").	7
comparisonPeriodDays	Integer	The period to compare against (e.g., "vs. the 7 days before that").	7
benchmarkPeriodDays	Integer	The period to use for the long-term sales average (e.g., "30-day avg").	30
topAmount	Integer	The number of top products to show (e.g., Top 5, Top 10).	5
Email Sender (Your) Details
senderEmail	String	The "From" email address (e.g., your report's email).	reports@mycompany.com
senderName	String	The "From" name (e.g., "MyCompany Reports").	Patternica Reports
smtpHost	String	The SMTP server host.	smtp.gmail.com
smtpPort	Integer	The SMTP port (e.g., 587 for TLS).	587
smtpPassword	String	The password for the senderEmail account. (See note below on App Passwords)	YourAppPassword
Email Recipient (Client) Details
clientEmail	String	The client's email address to send the report to.	client@example.com
clientName	String	The client's name, used in the email greeting (e.g., "Hello John,").	John

Example Use Cases

Here is how you would combine the parameters to generate different types of reports: