Theme Store dashboard

published_themes_by_active_merchants.sql

SELECT COUNT(*) active_shops,
       "theme name",
       COALESCE(theme_dimension."current theme name", 'UNKNOWN') theme_name,
       "current theme price",
       "current theme state"
FROM partnerships.theme_publish_lookup
JOIN starscream.shop_dimension USING ("shop id")
LEFT JOIN support.theme_dimension
  ON ("theme store id" = "theme id")
WHERE "current funnel state" = 'Active customer'
GROUP BY 
         "theme name",
         theme_dimension."current theme name",
         "current theme price",
         "current theme state"
ORDER BY 1 DESC
LIMIT 50

theme_sales_by_week.sql

WITH distinct_shop_installs AS (
  SELECT DISTINCT "_shop_key", "_theme_key", "theme fulfilled at (est)"
  FROM partnerships.theme_store_installation_facts
  WHERE theme_store_installation_facts."theme fulfilled at (est)" >= DATEADD(MONTH, -3, GETDATE())
)
SELECT DATE_TRUNC('week', distinct_shop_installs."theme fulfilled at (est)") AS "date", COUNT(*) AS "count", "current theme name" AS "theme name", "current theme support url" AS "support url", "current theme support style" AS "support style"
FROM distinct_shop_installs
JOIN support.theme_dimension ON (support.theme_dimension."_theme_key" = distinct_shop_installs."_theme_key")
WHERE "current theme state" = 'Published'
AND "support style" != 'Shopify'
GROUP BY "date", "current theme name", "current theme support style", "current theme support url"
ORDER BY 1 DESC

themes_sales_last_three_months.sql

WITH distinct_shop_installs AS (
  SELECT DISTINCT "_shop_key", "_theme_key"
  FROM partnerships.theme_store_installation_facts
  WHERE theme_store_installation_facts."theme fulfilled at (est)" >= DATEADD(MONTH, -3, GETDATE())
)
SELECT COUNT(*) AS "count", "current theme name" AS "theme name", "current theme support style" AS "support style"
FROM distinct_shop_installs
JOIN support.theme_dimension ON (support.theme_dimension."_theme_key" = distinct_shop_installs."_theme_key")
WHERE "current theme state" = 'Published'
GROUP BY "current theme name", "current theme support style"
ORDER BY 1 DESC