mfergis

-- Key Order Drivers [2026 Growth Goal]
-- https://docs.google.com/spreadsheets/d/1bUA23J2e8Y7yoGhTmV9vOSBENHGQ1OoLVVCKMkuf-cA

-- DEFINITIONS
-- New = Buyer placed first order within the past year
-- Reactivated = Buyer placed last order over a year ago, maintains status for a year
-- Existing = All other buyers

-- NOTES
-- Buyer is only counted in the month if they placed an order in that month

mfergis

-- Amazon Sales & Revenue by Price [Feb 2025]
-- https://docs.google.com/spreadsheets/d/1GLxx3D5FaO0iDcrEQSzvBYi53uOiKcxpU8kpp8JmdsQ

-- Get Etsy price data
-- https://github.com/etsy-dev/pbucchianeri/blob/main/inventory/emerging_inventory/inventory_et_offsite_analytics_support.sql
-- https://github.com/etsy-dev/pbucchianeri/blob/main/inventory/emerging_inventory/inventory_et_offsite_analytics_comparison_sheet.sql
CREATE OR REPLACE TABLE `etsy-data-warehouse-dev.mfergis.listing_segments_with_price_buckets` AS (
	WITH most_recent_listing_prices AS (
		SELECT 
			listing_id, 

mfergis

-- State of Inventory: How are our categories performing? [Jan 2025]
-- https://docs.google.com/spreadsheets/d/1wWTuYD0_d6jJnN4ILyLB3cSWQftZxSZeFnP2Zk-j1j8

-- Classify listings by category, "production segment", and craftsmanship score
create or replace table `etsy-data-warehouse-dev.mfergis.listing_segments` as (  
  with c_score as (
    select
      cast(listing_id as int64) as listing_id,
      max(craftsmanship_score) as craftsmanship_score
    from `etsy-data-warehouse-prod.gen_ai.craftsmanship_score`

mfergis

-- POD Fast Facts
-- https://docs.google.com/spreadsheets/d/1cZSDyVZJK3lsQEQiGpCH8bxYNpo0PpAAHA_ECE6TAwg/edit?gid=0#gid=0
-- https://docs.google.com/presentation/d/1eiWsPTLq9YO-MfZ66m5bs8EDGO1zS_KyESHjFJCo1Dc/edit#slide=id.g312924ad13b_0_168


-- POD GMS YTD
select
  sum(case when c.print_on_demand.is_pod = 1 then gms_net end) as pod_gms_ytd,
  sum(gms_net) as total_gms_ytd
from `etsy-data-warehouse-prod.transaction_mart.all_transactions` a

mfergis

-- Inventory: Category Segment Strategy + 2025 Focus Areas
-- https://docs.google.com/presentation/d/1jmcPWGpCmtGaFFGsexY3U5vYN-oB6k24rrT8rOQvKJc
-- https://docs.google.com/spreadsheets/d/1eQmHBnP-9i4RZ99oNAxMIc6ztDFRilEezvbxYXgg4OA


-- Kids & Baby Stats by Category

-- L1
with purchases as (
  select

mfergis

-- Inventory Attribute Coverage [Q4 2024]
-- https://docs.google.com/spreadsheets/d/1-mM8t2RaGwJDCIBIZrQX8IVpuDfglDOCHjYh02sOgpo

create or replace table `etsy-data-warehouse-dev.mfergis.inventory_attribute_coverage` as (
with listing_views as (
  select
    listing_id,
    count(*) AS views
  from `etsy-data-warehouse-prod.analytics.listing_views`
  where _date >= date_sub(current_date, interval 12 month)

mfergis

-- Inventory Strategy LCS
-- https://docs.google.com/spreadsheets/d/1k76q__bZoL9oX79Ypzk1jdhgifxTkS_Rz0yqMB7G4TQ/

-- TTM TY, LY, Y/Y
select 
  ifnull(sum(case when date >= date_sub(current_date, interval 12 month) then accounting_gms_net end), 0) as gms_ttm_ty,
  ifnull(sum(case when date >= date_sub(current_date, interval 24 month) and date < date_sub(current_date, interval 12 month) then accounting_gms_net end), 0) as gms_ttm_ly
from `etsy-data-warehouse-prod.rollups.gms_daily_mart_regional_yy`
where date >= "2022-01-01"
;