About
Generates royalty reports in the BrandManager360 (BM360) format by pulling order/line-item data from Shopify and appending one row per sale to a Google Sheet that mirrors the BM360 royalty report template. Populates key fields including UPI/SKU, product category, retailer, quantity, unit price, net sales, and royalty amount. Use this skill whenever the user asks to build, update, or refresh a royalty report, submit sales data to BM360, or reconcile Shopify orders against a royalty period.
What it does
- Fetch Shopify orders for a specified reporting period (date range, any order status)
- Resolve licensing fields (License_Code, Institution_Short_Code, Category_SubCategory_Code, Associated_Inst) by looking up each line item's UPI against a local Google Sheet submissions log first, before hitting BM360
- On first run, prompt the user to connect or create a submissions log sheet; offer to seed it by pulling all current artwork submissions from BM360 Web (or API if available)
- When a UPI is not found in the local sheet, fall back to BM360 Web (or API) and append the resolved submission to the log so future runs don't need to re-query
- For unmatched line items, analyze available signals — product title, tags/metafields, and product image — to assess whether the product is likely licensed, possibly licensed, or likely unbranded, and flag accordingly
- Default Retailer_Name to 'Direct to Consumer' and CLC_Retailer_Code to 'DTC' for all Shopify orders, using the store's configured address
- Map all 21 BM360 royalty report columns per line item and append rows to the report Google Sheet
- Write resolved rows first, then append unmatched rows at the bottom with blank licensing fields and a confidence-tiered review flag
- Summarize the run: rows written, duplicates skipped, local hits, BM360 fallback lookups, and unresolved UPIs broken down by confidence tier
What it won't do
- Won't submit the royalty report to BM360 — that step stays human-reviewed
- Won't calculate final royalty dollar amounts — net sales (Royalty_Sales) is the base; BM360 applies the rate
- Won't modify or delete existing rows in the report sheet
- Won't pull sales data from channels other than Shopify (e.g. Amazon, wholesale EDI)
How it works
1. Ask for the reporting period and the Google Sheet URL (or ID) if not provided. 2. Fetch all Shopify orders in that period using SHOPIFY_GET_ORDERS_WITH_FILTERS, paginating until complete. 3. For each order, expand line items and resolve product/variant details (type, tags, SKU) via SHOPIFY_GET_PRODUCT_VARIANT. 4. Map each line item to BM360 columns using the field guide below. 5. Read the sheet's existing rows (GOOGLESHEETS_VALUES_GET) to collect already-logged order+line-item IDs and skip duplicates. 6. Append one row per new line item via GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND. 7. Report the summary.