Tin can product history

1. What Tin Can sells

Tin Can makes screen-free, Wi-Fi-enabled landline-style phones for kids. Two commercial devices and one subscription plan drive essentially all revenue:

Product	Plain-English description	Primary identifier in the warehouse
Tin Can Flashback	Retro corded handset; the first product.	Shopify line-item `title ILIKE 'Tin Can Flashback%'`
Tin Can (the "Tin Can phone")	Modern flagship handset. Dominant product by volume from May 2025 onward.	Shopify line-item `title = 'Tin Can'` (exact match)
Party Line Plan	Subscription that lets a Tin Can call non-Tin-Can phone numbers. Billed monthly or annually, and typically starts with a 30-day free trial — so the first paid invoice generally lands ~30 days after sign-up, not immediately.	Stripe subscriptions; Shopify line-item `title ILIKE '%Party Line%'` or `title ILIKE 'Tin Can Plan'`

Everything else sold through Shopify (WiFi Bridge, HT801, AT&T Trimline, cord/cable replacements, tissue paper, boxes, "Refund Cancellation", test items) is either an internal component SKU, a replacement part, or an accounting artifact.

2. Timeline of the key events

Date	Event	Why it matters for analysis
2025-02-12	First Flashback pre-orders trickle in (~1–5 units/day).	Any analysis that treats "Tin Can's first sale ever" should use this date, not the product's listing date.
2025-03-13	Flashback's first high-volume sales day (224 units).	Use this as the effective Flashback public launch.
2025-04-28	First sale of the Tin Can phone.	Use this as the effective Tin Can phone launch.
2025-05	Phone overtakes Flashback ~12:1 in monthly units, and never looks back.	When comparing products, expect the phone to dominate from May 2025 onward.
2025-08-18	Viral TikTok spike. Daily orders step from ~215/day → 561 → 669 → 894 peak on 2025-08-20.	Demand never returns to pre-spike levels. Treat Aug 18, 2025 as a regime change.
Late Aug 2025 → present (2026-04)	Inventory exhausted and phones have remained on continuous backorder ever since. Quoted lead times stretched from days → weeks → months and have never recovered. As of April 2026, a phone ordered today is quoted to ship in June 2026 (~2 months out).	Orders ≠ shipments for the Tin Can phone at any point from 2025-08-18 onward. Use `FULFILLMENTS`, not `ORDERS.created_at`, whenever the question is about delivery timing, and never assume recent orders have shipped.
2025-12-25 → 26	Christmas activation crash. Call attempts spike ~8× vs. Christmas Eve; answer rate collapses ~8–20pp.	Any service-quality metric needs to call out these two days explicitly.
2025-12-29 → 31 and 2026-01-03 → 05	Post-Christmas aftershocks — multiple days of degraded answer rate.	"Service recovered in a week" is roughly correct for the worst of it, but full stability doesn't return until ~2026-01-10. Customers who were on free trials for Party Line plans during htis period had their trials extended by 30 days
2026-01-10 → 11	Answer rate stabilizes in the 37–38% range and stays there.	Use this as the effective "post-Christmas steady state" for baselines.

3. How to identify each product in the warehouse

Shopify ORDERS.line_items is a VARIANT array — you must LATERAL FLATTEN it and cast extracted fields with ::type. Always filter test = FALSE.

Flashback vs. Tin Can phone vs. everything else

-- Canonical product-bucketing CTE
WITH line_items_flat AS (
  SELECT
    o.id                               AS order_id,
    o.created_at                       AS order_ts,
    o.created_at::date                 AS order_date,
    value:title::varchar               AS product_title,
    value:quantity::number             AS qty,
    value:price::number                AS unit_price,
    CASE
      WHEN value:title::varchar ILIKE 'Tin Can Flashback%' THEN 'Flashback'
      WHEN value:title::varchar = 'Tin Can'                THEN 'Tin Can Phone'
      WHEN value:title::varchar ILIKE '%Party Line%'
        OR value:title::varchar ILIKE 'Tin Can Plan'       THEN 'Party Line Plan'
      ELSE 'Other'
    END AS product_bucket
  FROM RAW_DB.SHOPIFY.ORDERS o,
       LATERAL FLATTEN(input => o.line_items)
  WHERE o.test = FALSE
)
SELECT product_bucket, SUM(qty) AS units
FROM line_items_flat
GROUP BY product_bucket;

Gotchas worth hard-coding into every analysis:

ILIKE 'Tin Can Flashback%' (not =) — there are historical variants: Tin Can Flashback, Tin Can Flashback with WiFi Module, Tin Can Flashback (Wired, No WiFi).
= 'Tin Can' (exact, case-sensitive-ish) — anything more permissive (e.g. ILIKE 'Tin Can%') will sweep in the Flashback and everything else.
Ancillary titles you will see and should usually exclude: WiFi Bridge, WiFi Bridge (Component), HT801 (Component), AT&T Trimline (Component), any cord/cable/power-block name, Refund Cancellation, Paid Shipping Label, TEST ITEM, TEST $1 Tin Can Plan.
For subscription economics, don't use Shopify line items for the subscription — use RAW_DB.STRIPE.SUBSCRIPTIONS / INVOICES. Shopify only captures the initial sign-up touchpoint.

Identifying a product from `PRODUCTS` (not line items)

SELECT id, title, created_at, published_at
FROM RAW_DB.SHOPIFY.PRODUCTS
WHERE title IN ('Tin Can', 'Tin Can Flashback');
-- Tin Can:           id = 14660544102765, published 2025-03-08
-- Tin Can Flashback: id = 14655294996845, published 2025-02-17

Product-level IDs are stable; variant IDs live on PRODUCT_VARIANTS and inside the line_items VARIANT.

4. Where each kind of question should be answered from

Question type	Authoritative table(s)	Notes
Units sold per product / day / month	`RAW_DB.SHOPIFY.ORDERS` (flatten `line_items`)	Filter `test = FALSE`. Use `created_at::date`.
Revenue (gross)	`RAW_DB.SHOPIFY.ORDERS.total_price` or the flattened `value:price * value:quantity`	Prefer `ANALYTICS_DB.ANALYTICS.DAILY_SALES` for pre-aggregated totals — but note it is not broken down by product.
Pre-aggregated daily sales	`ANALYTICS_DB.ANALYTICS.DAILY_SALES`	Columns: `date`, `items_sold`, `items_refunded`, `net_items_sold`, `gross_sales`, `discounts`, `refunds`, `net_sales_revenue`. No product dimension.
Shipment / delivery timing	`RAW_DB.SHOPIFY.FULFILLMENTS`	`ORDERS.created_at` is when the customer paid, not when they received it. Phones have been on continuous backorder since 2025-08-18 and still are as of April 2026 — orders placed today are quoted to ship in June 2026. Treat any phone-delivery question as fulfillment-driven, not order-driven, for the entire period from Aug 2025 onward.
Subscription revenue / churn	`RAW_DB.STRIPE.SUBSCRIPTIONS`, `INVOICES`, `CHARGES`	Stripe amounts are in cents — divide by 100. Stripe timestamps are Unix — use `to_timestamp(created)`. Party Line Plans have monthly and annual billing cadences and typically include a 30-day free trial, so count a subscriber from `trial_start` / subscription creation if you want total signups, but from `trial_end` (or the first paid invoice) if you want paying subscribers.
Call activity per day	`ANALYTICS_DB.ANALYTICS.CALL_LOGS`	Grain = one row per call attempt. Use `call_date::date`.
Per-device / per-kid activity	`ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY`	Grain = one row per device per day.
Service disruption proxy	`CALL_LOGS` answer rate: `AVG(IFF(is_call_answered, 1, 0))` grouped by day	Relative drops > ~8pp from a trailing baseline are meaningful; absolute values are not a clean SLA.

5. Canonical windows to use for period-over-period analysis

When Hex AI needs to compare "before vs. after" for something, these are the right breakpoints:

-- Product-era segmentation. Note: the back-order regime that began on 2025-08-18
-- is STILL ONGOING as of April 2026 — every era from 'Post-viral back-order'
-- onward is a supply-constrained period, not a steady-state one.
CASE
  WHEN order_date <  DATE '2025-04-28' THEN 'Flashback-only era'
  WHEN order_date <  DATE '2025-08-18' THEN 'Phone launched, pre-viral (supply OK)'
  WHEN order_date <  DATE '2025-12-25' THEN 'Post-viral back-order (supply constrained)'
  WHEN order_date <  DATE '2026-01-11' THEN 'Christmas crash / recovery (supply constrained)'
  ELSE                                       'Ongoing back-order (supply constrained)'
END AS era

For service-quality studies:

-- Pre-rush baseline:        2025-12-01 .. 2025-12-10
-- Ramp into Christmas:      2025-12-11 .. 2025-12-24
-- Crash:                    2025-12-25 .. 2025-12-26
-- Acute recovery:           2025-12-27 .. 2026-01-02
-- Aftershocks:              2026-01-03 .. 2026-01-09
-- Post-recovery steady:     2026-01-10 onward

6. Frequent pitfalls

Forgetting test = FALSE. RAW_DB.SHOPIFY.ORDERS contains internal test orders. Always filter.
Treating published_at as the launch date. Flashback was published_at = 2025-02-17 but didn't meaningfully sell until 2025-03-13. The Tin Can phone was published_at = 2025-03-08 but first sold 2025-04-28. Use first-significant-sales-day for "launch".
Using ILIKE 'Tin Can%' to mean "the phone". That sweeps in Flashback and the subscription. Use = 'Tin Can' for the phone.
Mixing Shopify and Stripe for subscriptions. Shopify captures device purchases + initial plan sign-up. Stripe is the source of truth for recurring revenue.
Comparing answer rates across eras without calling out the post-Christmas baseline shift. The "new normal" answer rate (~37–38%) is lower than the pre-viral baseline (~48%); that reflects a larger/noisier user base, not ongoing degraded service.
Ordering date ≠ delivery date, continuously from 2025-08-18 onward. Phones have been on backorder the entire time from the viral spike through today (April 2026) — quoted lead times currently sit around 2 months (order now → ships June 2026). This is not a closed-out historical window; it is the ongoing state of the business. If a question is about what the customer experienced (when they got a working phone, when they could first place a call, etc.), join to FULFILLMENTS and never use ORDERS.created_at as a delivery proxy.