⚙️ Scripts & Outputs
HOW PRICE LISTS ARE BUILT:
1. Fetches ALL orders for a given month from Shopify GraphQL.
2. Excludes subscription orders and wholesale orders — only uses one-time DTC purchases.
3. For each SKU, collects every originalUnitPrice observed across all qualifying orders.
4. Calculates the reference price as the mode (most common price). Falls back to median if no clear mode.
5. Saves as PriceList/Monthly/prices_YYYY-MM.json — one file per month.
Why mode? During promos, some orders have discounted prices. The mode captures the 'normal' retail price since most orders in any given month are non-promo.
Coverage: Jan 2024 → Feb 2026 (26 months). Also has 'derived' folder with extended price observations.
⚠️ Not automated daily. Run manually when new months need prices or when price changes happen.
📥 Inputs: Shopify GraphQL API (historical order line items)
Queries Shopify GraphQL for all orders created yesterday. Filters to PAID/PARTIALLY_REFUNDED only. Separates DTC vs Wholesale (Walmart/Target/Faire by tag).
Per-Order Fields from Shopify: Financial Status, Current Total Price (Net), Total Discounts, Total Shipping (customer-paid), Subtotal, Order Tags, Customer Order Index (1st order = New).
Per-Line-Item Fields from Shopify: SKU, Quantity, Original Unit Price, Discount Allocations (type + amount per allocation).
Calculated Columns (NOT from Shopify — computed by this script):
• dtc_gross = Units × Monthly Price List from PriceList/Monthly/prices_YYYY-MM.json (see Script #0)
• dtc_subscription_discounts = For subscription orders: (Price List price − originalUnitPrice) × quantity. This captures the 15% Stay.ai discount that Shopify bakes into the line item price instead of tracking as a discount.
• dtc_automatic_discounts = Sum of all AutomaticDiscountApplication allocations on line items
• dtc_code_discounts = Sum of all DiscountCodeApplication allocations (e.g. WELCOME15)
• dtc_manual_discounts = Sum of all ManualDiscountApplication allocations (CS/rewards)
• dtc_shipping_discounts = Total Discounts − (subscription + automatic + code + manual). This is the remainder — captures free shipping value.
• dtc_free_item_value/count/cogs = Line items where originalUnitPrice = $0. Value calculated using Price List. COGS from COGS/Cogs.xlsx.
• dtc_refund_value/units/cogs = Separate REST API call for refunds processed on that date (not the order date).
CSV has 67 columns per day. This is the foundation for ALL downstream reports.
📥 Inputs: Shopify GraphQL API, PriceList/Monthly/*.json (for True Gross), COGS/Cogs.xlsx (for cost tracking)
Independently fetches all orders from Shopify REST API. Excludes wholesale. Breaks down units sold per SKU into New vs Returning customers.
Per-SKU Output: Product Name, New Units, New Revenue, Returning Units, Returning Revenue.
Note: This script does NOT calculate True Gross. It outputs raw Shopify revenue per SKU. True Gross (units × price list) is calculated downstream in generate_monthly_matrices.py.
📥 Inputs: Shopify REST API (separate fetch, does NOT read from Daily Shopify Orders CSV)
Queries the ShipStation API for all shipments created yesterday. Calculates the actual postage cost Teeccino paid per shipment.
Output Columns: Date, Total Cost (what we paid), Shipment Count, Avg Cost Per Shipment.
This is NOT what the customer paid for shipping (that comes from Shopify's totalShippingPriceSet in script #1). This is what Teeccino actually paid carriers (DHL, USPS) via ShipStation.
📥 Inputs: ShipStation API
Fetches account-level insights from Meta Ads API.
Fields: Spend, Impressions, Clicks, CPC, CPM, Reach, Frequency, Actions (purchases, add-to-carts, etc.), Action Values (conversion revenue), Cost Per Action.
📥 Inputs: Meta Marketing API (Ad Account 559530661221525)
Fetches campaign-level metrics from Google Ads API using GAQL query.
Fields: Cost (micros → dollars), Impressions, Clicks, Conversions, Conversion Value.
📥 Inputs: Google Ads API (via googleads Python client)
Reads the CSV files generated by scripts 1, 3a, and 3b and inserts/updates them into the local SQLite database (teeccino_kpi.db).
Tables Updated:
• daily_shopify — All 67 columns from Daily Shopify Orders CSV
• daily_meta_ads — Meta spend/impressions/clicks/conversions
• daily_google_ads — Google spend/impressions/clicks/conversions
This is a data warehouse step — it does not recalculate anything. It just loads the flat files into SQL for easier querying downstream.
📥 Inputs: Daily Shopify Orders.csv (from #1), Daily Meta Ads.csv (from #3a), Daily Google Ads.csv (from #3b)
The big one. Aggregates all data sources into monthly summaries across multiple Excel sheets.
True Gross Calculation (happens HERE):
DTC Gross Sales = (SKU Units × Monthly Price List) − Free Item Value − Refund Value
Sheets Generated in Monthly Matrices:
• WebSalesMatrix — DTC orders, gross, net, units, AOV, new/returning, subscriptions
• ChannelMatrix — Walmart/Target/Faire wholesale breakdown
• DiscountMatrix — Subscription/automatic/code/manual/shipping/price-reduction discounts
• ShippingMatrix — Customer-paid shipping vs actual ShipStation costs
• Profit Waterfall — Gross → Discounts → Shipping → COGS → Ad Spend → Platform → Tools → Contribution Margin
Shopify KPI 2026 pulls from these matrices and adds Forecast targets (from 2026 Forecast v3.xlsx) and sessions/CVR data.
📥 Inputs: Daily SKU Sales.xlsx (from #2), PriceList/Monthly/*.json, COGS/Cogs.xlsx, teeccino_kpi.db (from #4), Billing/CreditCard/master_transactions.csv
Reads the finalized KPI Excel files and generates the visual MTD HTML dashboard with cards for Revenue, Orders, AOV, Ad Spend, MER, and Shipping.
📥 Inputs: Monthly Matrices.xlsx (from #5), Shopify KPI 2026.xlsx (from #5)
Breaks down shipping into Free vs Paid. For each shipment, queries ShipStation for actual postage cost, then matches against Shopify order to see if the customer paid for shipping or got free shipping.
Output Columns: Total Shipments, Total Units, Avg Units/Order, Avg Weight, Free Shipping Count/Cost, Paid Shipping Count/Revenue/Cost, Total Cost, Total Revenue, Net (revenue minus cost).
Key Insight: 'Net' is almost always negative — Teeccino subsidizes shipping. This feeds into the Shipping Matrix in Monthly Matrices.
📥 Inputs: ShipStation API, Shopify REST API (to match shipping revenue per order)
Measures warehouse speed. Pulls all orders from the last 30 days and calculates how long it took from order placement to fulfillment.
Key Metrics: Orders Fulfilled, Avg Fulfillment Time (hours), Median Fulfillment Time, % fulfilled within 24h/48h/72h, Current Backlog count, Backlog over 24h/48h.
Also outputs: A per-order detail table showing each order's creation time, fulfillment hours, and total value.
📥 Inputs: Shopify REST API (last 30 days of orders + fulfillment events)
Reshapes the Daily Shipping Analysis CSV into a clean Excel report. Shows daily fulfillment counts, units per order, avg package weight, free vs paid shipping split, and total shipping cost.
Note: This does NOT fetch any new data — it just reformats the ShipStation data from script 2c into a more readable Excel format.
📥 Inputs: Daily Shipping Analysis CSV (from #2c — does NOT hit any API directly)
Standalone. Tracks Instagram follower growth, reach, impressions, profile views, and engagement daily. Not connected to the KPI pipeline.
📥 Inputs: Instagram Graph API
Standalone. Scans Slack for new ECH ad creative Drive links, downloads them, matches creatives to Meta ad performance data via image hashing, and rebuilds the visual Creatives Library HTML page.
📥 Inputs: Slack API (ECH channel), Google Drive (folder downloads), teeccino_kpi.db (Meta performance matching)