44 lines
1.8 KiB
Python
44 lines
1.8 KiB
Python
import sqlite3
|
|
import pandas as pd
|
|
import re
|
|
|
|
conn = sqlite3.connect('data/hiffi_catalog.db')
|
|
query = """
|
|
SELECT
|
|
a.artist_id, a.artist_name,
|
|
a.youtube_url, a.spotify_url, a.instagram_url,
|
|
t.subscribers_count, t.spotify_monthly_listeners, t.instagram_followers
|
|
FROM artists a
|
|
JOIN telemetry_metrics t ON a.artist_id = t.artist_id
|
|
WHERE a.image_verified_flag = 1
|
|
"""
|
|
df = pd.read_sql_query(query, conn)
|
|
conn.close()
|
|
|
|
# Pattern checks
|
|
yt_search = df[df['youtube_url'].astype(str).str.contains('results\\?search_query', case=False, na=False)]
|
|
yt_invalid = df[~df['youtube_url'].astype(str).str.contains(r'youtube\.com/(channel/|c/|user/|@)', regex=True, na=False)]
|
|
sp_invalid = df[~df['spotify_url'].astype(str).str.contains(r'open\.spotify\.com/artist/[a-zA-Z0-9]{22}', regex=True, na=False)]
|
|
ig_invalid = df[~df['instagram_url'].astype(str).str.contains(r'instagram\.com/[a-zA-Z0-9_\.]+', regex=True, na=False)]
|
|
|
|
print('=== TOTAL COHORT PLATFORM URL HEALTH (500 ARTISTS) ===')
|
|
print(f'YouTube Search Fallback URLs: {len(yt_search)}')
|
|
print(f'YouTube Non-Canonical URLs: {len(yt_invalid)}')
|
|
print(f'Spotify Malformed URLs: {len(sp_invalid)}')
|
|
print(f'Instagram Malformed URLs: {len(ig_invalid)}')
|
|
|
|
if len(yt_search) > 0:
|
|
print('\nArtists with YouTube Search Fallbacks:')
|
|
for _, r in yt_search.iterrows():
|
|
print(f" - {r['artist_name']} (ID: {r['artist_id']}) -> {r['youtube_url']}")
|
|
|
|
if len(sp_invalid) > 0:
|
|
print('\nArtists with Malformed Spotify URLs:')
|
|
for _, r in sp_invalid.iterrows():
|
|
print(f" - {r['artist_name']} (ID: {r['artist_id']}) -> {r['spotify_url']}")
|
|
|
|
if len(ig_invalid) > 0:
|
|
print('\nArtists with Malformed Instagram URLs:')
|
|
for _, r in ig_invalid.iterrows():
|
|
print(f" - {r['artist_name']} (ID: {r['artist_id']}) -> {r['instagram_url']}")
|