← back to Dw Yolo Loop

scripts/dup-title-scan/dup-title-scan.sh

21 lines

#!/bin/sh
# dup-title-scan (c46) — READ-ONLY. ACTIVE products sharing an EXACT title across
# distinct handles = SEO cannibalization + poor shopper differentiation. Finding:
# 3,285 collision-titles / 18,577 products; ~84% (2,771) are GENERIC/template
# titles (vendor+material only, e.g. "Versace Wallcovering" x99, "Roberto Cavalli
# Wallcovering" x149) — real distinct products never differentiated by pattern+
# color. Fix = title-enrichment (gated, customer-facing). $0 local mirror.
PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
DB="postgresql:///dw_unified?host=/tmp"
$PSQL "$DB" -c "
with t as (select trim(title) ti, count(distinct handle) h, string_agg(distinct vendor,', ') v
  from shopify_products where status='ACTIVE' and title is not null and trim(title)<>'' group by trim(title) having count(distinct handle)>1)
select count(*) collision_titles, sum(h) products,
  count(*) filter (where ti ~* '(wallcovering|wallpaper|durable|self adhesive)$' or ti !~ ' - ') generic_template
from t;"
echo '-- top collisions --'
$PSQL "$DB" -c "
select trim(title) title, count(distinct handle) handles, string_agg(distinct vendor,', ') vendors
from shopify_products where status='ACTIVE' and trim(title)<>'' group by trim(title)
having count(distinct handle)>1 order by 2 desc limit 15;"