Lightspeed X-Series Product Performance Reporting – Google Apps Script, BigQuery & Sheets

Summary We are looking for an experienced Google Apps Script developer to build a custom product-performance reporting system using the Lightspeed Retail X-Series API, BigQuery, and Google Sheets. Lightspeed's standard reporting does not accurately show which of our products are truly performing best or worst because: Bulk inventory skews sell-through rates. We may buy large quantities of a strong seller, making its traditional sell-through percentage appear artificially low. Out-of-stock periods skew items-sold-per-day. A product that sells 10 units while available for only 10 of 30 days should not be evaluated as though it had 30 days of selling opportunity. We currently have approximately 7,000+ SKUs and sell roughly 17,000 items per year. What We Need The system should use: Lightspeed X-Series → Google Apps Script → BigQuery → Google Sheets BigQuery should store the historical/raw data, while Google Sheets should be the searchable/filterable reporting interface. 1. OOS-Adjusted Sales Velocity The primary metric should be: Items Sold Per In-Stock Day = Units Sold ÷ Days Available for Sale Days when inventory is considered out of stock should be excluded from the denominator. We also need a configurable Out-of-Stock Margin because inventory is not always perfectly accurate due to theft/shrinkage. Example: if a SKU has an OOS margin of 3, an inventory level of 3 or below should be treated as out of stock for reporting purposes. The margin should be configurable by product/SKU. 2. Sales and Inventory Must Be Tracked Separately Actual Lightspeed sales data should determine units sold and returns. Inventory changes should determine product availability/OOS status. Inventory changes caused by things such as: Receiving deliveries Theft/shrinkage Inventory counts Manual corrections Transfers must not accidentally be counted as sales or returns. 3. Historical Data We want historical sales imported into the new system before launch. Use the Lightspeed API where possible, with Lightspeed CSV exports as a fallback if necessary. We understand historical sales may be available even if historical daily inventory levels are not. The developer should determine what inventory history Lightspeed exposes and clearly identify the earliest date for which the new OOS-adjusted calculation can be considered reliable. 4. Product/Supplier SKU Grouping Our internal Lightspeed SKUs are generally numerical. Multiple internal SKUs may share the same supplier SKU because they represent different colors/sizes of the same product. Example: Supplier SKU XYZ Internal SKU 123 – Small Internal SKU 124 – Medium Internal SKU 125 – Large Searching XYZ should allow us to see either: The combined performance of the product family, or The performance of each individual SKU/variant. Some supplier SKUs also contain size/color suffixes, so searching a root such as KA664 should return related variants such as KA664-SM, KA664-MD, etc. We prefer predictable root/group matching over broad fuzzy matching that may generate incorrect SKU relationships. 5. Google Sheets Report The final user-facing report must remain inside Google Sheets. Users should be able to use multiple filters simultaneously, including: Date range Internal SKU Supplier SKU/product family Vendor/Supplier Category Product Size/Color where applicable A Google Sheets sidebar or similar Apps Script interface is acceptable if it provides a better experience than standard Sheets filters. The report should show key fields such as: SKU / Supplier SKU Product Vendor Category Units sold In-stock days OOS days OOS-adjusted items sold per day Current inventory OOS margin Performance ranking 6. Discount Context We also need an average discount metric for the selected reporting period. This is important because a poorly performing product placed on clearance may suddenly appear to be a strong seller. A quantity-weighted average discount is preferred if the Lightspeed data supports it. Technical Requirements Use bulk/paginated API calls, not individual API calls for thousands of SKUs. Design around Google Apps Script execution limits using chunked/resumable processing where needed. Avoid duplicate data when jobs retry or resume. Store API credentials securely in Apps Script Properties or another agreed secure location—not spreadsheet cells. Log API/sync errors so failures do not happen silently. Keep BigQuery as the historical data store rather than allowing raw logs to make the Google Sheet increasingly slow. The normal integration should be read-only with respect to Lightspeed. Testing The freelancer is responsible for setting up or using an appropriate Lightspeed test/development environment, credentials, and dummy data necessary to validate the system before connecting it to production. Testing should confirm that: Sales count as sales. Returns count correctly. Receiving inventory does not count as a return. Shrinkage/manual inventory reductions do not count as sales. OOS thresholds affect available selling days correctly. Related SKU variants can be viewed individually or together. Documentation Provide concise documentation suitable for our internal RAG system covering: System Overview & Links What the system does Google Sheet Apps Script project BigQuery resources Other required operational links Users/Managers How to search/filter reports How to interpret the metrics How to change OOS margins Developer/IT Architecture API/authentication BigQuery structure Sync/pagination/resume logic OOS calculation SKU grouping Error handling Triggers and maintenance Deliverables Working Lightspeed → BigQuery automated data pipeline Historical sales backfill Ongoing inventory/OOS tracking OOS-adjusted sales-performance calculations Supplier SKU/product-family grouping Discount metric Filterable/searchable Google Sheets report Source code Testing Documentation