#imports import os import sys parent_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) utils_dir = os.path.join(parent_dir, "utils") sys.path.append(utils_dir) import pandas as pd import numpy as np pd.options.mode.chained_assignment = None import sys import datetime as dt import re from flask import render_template, request, jsonify, session from . import main_bp from .views import TrackReviewView, CardInfoView, First35StandardView, First35BenchmarkView, First35ProView, First35AlbumsView from utils.config import Config from utils.sme_ds_utilities import DatabaseUtils smeds = DatabaseUtils(Config.DB_SOURCE) # Track Review related views. main_bp.add_url_rule('/', view_func=TrackReviewView.as_view('index'), methods=['GET', 'POST']) main_bp.add_url_rule('/track_review', view_func=TrackReviewView.as_view('track_review'), methods=['GET', 'POST']) main_bp.add_url_rule('/card_info/', view_func=CardInfoView.as_view('card_info'), methods=['GET', 'POST']) # First 35 related views. main_bp.add_url_rule('/first_35', view_func=First35StandardView.as_view('anomaly_detection'), methods=['GET', 'POST']) main_bp.add_url_rule('/first_35_pro', view_func=First35ProView.as_view('anomaly_detection_pro'), methods=['GET']) main_bp.add_url_rule('/first_35/benchmarks', view_func=First35BenchmarkView.as_view('benchmark_assignment'), methods=['GET']) main_bp.add_url_rule('/albums', view_func=First35AlbumsView.as_view('albums')) # TODO: Move to the views file. @main_bp.route('/first_35_curated') def first_35_curated(): geo_country = request.args.get('geo_country', 'US') query = f""" WITH latest_scores AS ( SELECT isrc_cd, product_name, artist_name, fin_label_parent_name, company_brand_name, source_of_stream, anomaly_score, artwork_url, streams, ROW_NUMBER() OVER ( PARTITION BY isrc_cd, source_of_stream ORDER BY decay_day DESC ) AS rank FROM first_35.current_events WHERE source_of_stream IN ('spotify', 'spotify_lean_back', 'apple', 'apple_lean_back', 'total', 'total_lean_back') AND anomaly_score IS NOT NULL AND geo_country = '{geo_country}' ), pivoted_scores AS ( SELECT isrc_cd, product_name, artist_name, MAX(fin_label_parent_name) as label_parent_name, MAX(company_brand_name) as label_filter_name, MAX(artwork_url) as artwork_url, SUM(CASE WHEN source_of_stream IN ('total', 'total_lean_forward', 'total_lean_back') THEN streams ELSE 0 END) as total_streams, MAX(CASE WHEN source_of_stream = 'spotify' THEN anomaly_score END) as spotify_lf, MAX(CASE WHEN source_of_stream = 'spotify_lean_back' THEN anomaly_score END) as spotify_lb, MAX(CASE WHEN source_of_stream = 'apple' THEN anomaly_score END) as apple_lf, MAX(CASE WHEN source_of_stream = 'apple_lean_back' THEN anomaly_score END) as apple_lb, MAX(CASE WHEN source_of_stream = 'total' THEN anomaly_score END) as total_lf, MAX(CASE WHEN source_of_stream = 'total_lean_back' THEN anomaly_score END) as total_lb FROM latest_scores WHERE rank = 1 GROUP BY isrc_cd, product_name, artist_name ), scored_tracks AS ( SELECT isrc_cd as id, product_name as track_name, artist_name, label_parent_name, label_filter_name, artwork_url, total_streams, (COALESCE(spotify_lf, 0) + COALESCE(spotify_lb, 0) + COALESCE(apple_lf, 0) + COALESCE(apple_lb, 0) + COALESCE(total_lf, 0) + COALESCE(total_lb, 0)) / NULLIF((CASE WHEN spotify_lf IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN spotify_lb IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN apple_lf IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN apple_lb IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN total_lf IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN total_lb IS NOT NULL THEN 1 ELSE 0 END), 0) as interesting_score FROM pivoted_scores ) SELECT * FROM scored_tracks ORDER BY ABS(interesting_score) DESC """ df = smeds.query_db(query, 'main') song_dict = df.to_dict('records') return render_template('first_35_curated.html', song_dict=song_dict, geo_country=geo_country) # TODO: Bring Glossary back, but update it for 2026. # @main_bp.route('/glossary') # def glossary(): # return render_template('glossary.html') @main_bp.route('/tutorial') def tutorial(): return render_template('track_review_tutorial.html') # TODO: Bring Usage Report back, but update it for 2026. # @main_bp.route('/usage_report') # def usage_report(): # return render_template('usage_report.html') @main_bp.route('/track_action', methods=['POST']) def track_action(): data = request.get_json() print(data) user_email = data['user_email'] artist = data['artist'] track = data['track'] tadas_score = data['tadas_score'] pfn_geo = data['pfn_geo'] action = data['action'] # "approve", "decline", or "maybe" rep_owner = data['rep_owner'] is_business = data['is_business'] comment = data['comment'] comment = re.sub(r'[^a-zA-Z0-9\s]', '', comment) artist = re.sub(r'[^a-zA-Z0-9\s]', '', artist) track = re.sub(r'[^a-zA-Z0-9\s]', '', track) rep_owner = re.sub(r'[^a-zA-Z0-9\s]', '', rep_owner) today_date = dt.datetime.now().strftime("%Y-%m-%d") def escape_single_quotes(track): return track.replace("'", "''") track = escape_single_quotes(track) # Your query with parameterized values insert_query = f""" insert INTO tadas.user_dashboard_v2 (user_email, artist, track, tadas_score, pfn_geo, action, action_date, rep_owner, budget, comment, tadas_score_now, is_trending_now, tadas_update_date) VALUES ('{user_email}', '{artist}', '{track}', {tadas_score}, '{pfn_geo}', '{action}', '{today_date}', '{rep_owner}', {is_business}, '{comment}', {tadas_score}, 1, '{today_date}'); """ smeds.update_db(insert_query, 'main') last_week = (dt.date.today() - dt.timedelta(days=9)).strftime("%Y-%m-%d") update_query = """update iago.track_iago SET num_reactions = num_reactions + 1 WHERE pfn_geo = '""" + pfn_geo + """' and report_date > '""" + last_week + """';""" smeds.update_db(update_query, 'main') return jsonify({'message': 'Action recorded successfully'}), 200 @main_bp.route('/remove_artist', methods=['POST']) def remove_artist(): data = request.get_json() print(data) user_email = data['user_email'] artist = data['artist_name'] today_date = dt.datetime.now().strftime("%m/%d/%Y, %H:%M:%S") # Your query with parameterized values insert_query = f""" INSERT INTO iago.remove_artist (user_email, artist_name, removal_date) VALUES ('{user_email}', '{artist}', '{today_date}'); """ smeds.update_db(insert_query, 'main') return jsonify({'message': 'Action recorded successfully'}), 200 # TODO: This is a view, move it there. @main_bp.route('/signup', methods=['GET', 'POST']) def signup(): user_email = session.get('email', '') current_endpoint = request.endpoint if request.method == 'POST': # do stuff. rep_owner = request.form['repOwnerSelect'] era = request.form['eraSelect'] country = request.form['countrySelect'] streamrange = request.form['streamRangev2'] max_id_df = smeds.query_db('select max(request_id) as max_id from iago.email_notification_preferences') new_id = max_id_df['max_id'].max() + 1 notification_query = f""" INSERT INTO iago.email_notification_preferences (email, rep_owner, era, date_logged, geo_country, streamrange, request_id) VALUES ('{user_email}', '{rep_owner}', '{era}', NOW(), '{country}', '{streamrange}', {new_id}); """ smeds.update_db(notification_query, 'main') congrats = 1 else: congrats = 0 q = """select * from tadas.saved_filters where user_email = '""" + user_email + """';""" user_settings_df = smeds.query_db(q, 'main') if len(user_settings_df) > 0: selected_filters = {'rep_owner_choice': user_settings_df['rep_owner_choice'].max(), 'geo_country_choice': user_settings_df['geo_country_choice'].max(), 'era_choice': user_settings_df['era_choice'].max(), 'streamRange_choice': user_settings_df['streamrange_choice'].astype(str).max()} else: selected_filters = {'rep_owner_choice': 'ALL', 'geo_country_choice': 'ALL', 'era_choice': 'ALL', 'streamRange_choice': '0'} # Render the user dashboard with the filtered tables return render_template( 'track_review_signup.html', current_endpoint=current_endpoint, filter_status=selected_filters, user_email=user_email, congrats=congrats ) # TODO: Move these to the first 35 specific areas. @main_bp.route('/first_35/get_suggestions', methods=['POST']) def get_benchmark_suggestions(): data = request.get_json() target_isrc = data.get('target_isrc') geo_country = data.get('geo_country', 'US') q = f""" SELECT artist_name FROM first_35.current_events WHERE isrc_cd = '{target_isrc}' AND geo_country = '{geo_country}' LIMIT 1 """ artist_df = smeds.query_db(q, 'main') if artist_df.empty: return jsonify({'suggestions': []}) artist_name = artist_df['artist_name'].iloc[0] q = f""" SELECT DISTINCT product_family_no, project_artist_name as artist_name, product_name, month_pulled, day_35 as total_streams FROM first_35.artist_track_streams WHERE project_artist_name = '{artist_name}' AND geo_country = '{geo_country}' AND product_name IS NOT NULL ORDER BY day_35 DESC LIMIT 10 """ suggestions_df = smeds.query_db(q, 'main') suggestions_df['display_text'] = suggestions_df['product_name'] + ' - PFN: ' + suggestions_df['product_family_no'].astype(str) suggestions_df['pfn'] = suggestions_df['product_family_no'].astype(str) suggestions_df['month_pulled'] = suggestions_df['month_pulled'].astype(str) suggestions_df['total_streams'] = suggestions_df['total_streams'].fillna(0).astype(int) suggestions_list = suggestions_df[['pfn', 'artist_name', 'product_name', 'display_text', 'month_pulled', 'total_streams']].to_dict('records') return jsonify({'suggestions': suggestions_list}) @main_bp.route('/first_35/assign_benchmark', methods=['POST']) def assign_benchmark(): data = request.get_json() target_isrc = data.get('target_isrc') benchmark_identifier = data.get('benchmark_identifier') identifier_type = data.get('identifier_type') geo_country = data.get('geo_country', 'US') user_email = session.get('email', 'unknown') if not target_isrc or not benchmark_identifier: return jsonify({'success': False, 'error': 'missing required fields'}), 400 benchmark_isrc = benchmark_identifier scaling_factor = 1.0 # apparently upsert is a way to insert or update in a query so trying it here upsert_query = f""" INSERT INTO day_0_35.first_35_benchmarks (target_isrc, geo_country, benchmark_isrc, assigned_by, scaling_factor) VALUES ('{target_isrc}', '{geo_country}', '{benchmark_isrc}', '{user_email}', {scaling_factor}) ON CONFLICT (target_isrc, geo_country) DO UPDATE SET benchmark_isrc = EXCLUDED.benchmark_isrc, assigned_by = EXCLUDED.assigned_by, scaling_factor = EXCLUDED.scaling_factor, assigned_at = CURRENT_TIMESTAMP """ try: smeds.update_db(upsert_query, 'main') return jsonify({'success': True}) except Exception as e: print(f"error assigning benchmark: {e}") return jsonify({'success': False, 'error': str(e)}), 500 @main_bp.route('/first_35/usage', methods=['GET', 'POST']) def first_35_usage(): dev_exclusion = """AND user_email NOT IN ( 'abhiram.vadali@sonymusic.com', 'soomin.kim@sonymusic.com', 'travis.stowe@sonymusic.com' )""" end_date = dt.date.today().strftime('%Y-%m-%d') start_date = (dt.date.today() - dt.timedelta(days=30)).strftime('%Y-%m-%d') endpoint_filter = 'all' if request.method == 'POST': start_date = request.form.get('start_date', start_date) end_date = request.form.get('end_date', end_date) endpoint_filter = request.form.get('endpoint_filter', 'all') endpoint_clause = "" if endpoint_filter != 'all': endpoint_clause = f" AND endpoint = '{endpoint_filter}'" stats_q = f""" SELECT COUNT(*) AS total_visits, COUNT(DISTINCT user_email) AS unique_users FROM first_35.endpoint_logs WHERE visit_ts::date BETWEEN '{start_date}' AND '{end_date}' {endpoint_clause} {dev_exclusion} """ stats_df = smeds.query_db(stats_q, 'main') total_visits = int(stats_df['total_visits'].iloc[0]) if not stats_df.empty else 0 unique_users = int(stats_df['unique_users'].iloc[0]) if not stats_df.empty else 0 avg_visits_per_user = ( round(total_visits / unique_users, 1) if unique_users > 0 else 0 ) active_day_q = f""" SELECT visit_ts::date AS visit_date, COUNT(*) AS cnt FROM first_35.endpoint_logs WHERE visit_ts::date BETWEEN '{start_date}' AND '{end_date}' {endpoint_clause} {dev_exclusion} GROUP BY 1 ORDER BY cnt DESC LIMIT 1 """ active_day_df = smeds.query_db(active_day_q, 'main') most_active_day = str(active_day_df['visit_date'].iloc[0]) if not active_day_df.empty else '—' most_active_day_count = int(active_day_df['cnt'].iloc[0]) if not active_day_df.empty else 0 daily_q = f""" SELECT visit_ts::date AS visit_date, COUNT(*) AS visit_count FROM first_35.endpoint_logs WHERE visit_ts::date BETWEEN '{start_date}' AND '{end_date}' {endpoint_clause} {dev_exclusion} GROUP BY 1 ORDER BY 1 """ daily_df = smeds.query_db(daily_q, 'main') daily_df['visit_date'] = daily_df['visit_date'].astype(str) daily_counts = daily_df.to_dict('records') endpoint_q = f""" SELECT endpoint, COUNT(*) AS visit_count FROM first_35.endpoint_logs WHERE visit_ts::date BETWEEN '{start_date}' AND '{end_date}' {dev_exclusion} GROUP BY 1 ORDER BY visit_count DESC """ endpoint_df = smeds.query_db(endpoint_q, 'main') endpoint_counts = endpoint_df.to_dict('records') top_users_q = f""" SELECT user_email, COUNT(*) AS visit_count FROM first_35.endpoint_logs WHERE visit_ts::date BETWEEN '{start_date}' AND '{end_date}' {endpoint_clause} {dev_exclusion} GROUP BY 1 ORDER BY visit_count DESC LIMIT 10 """ top_users_df = smeds.query_db(top_users_q, 'main') top_users = top_users_df.to_dict('records') log_q = f""" SELECT TO_CHAR(visit_ts, 'YYYY-MM-DD HH24:MI') AS visit_ts, user_email, endpoint, geo_country FROM first_35.endpoint_logs WHERE visit_ts::date BETWEEN '{start_date}' AND '{end_date}' {endpoint_clause} {dev_exclusion} ORDER BY visit_ts DESC LIMIT 500 """ log_df = smeds.query_db(log_q, 'main') visit_log = log_df.to_dict('records') return render_template( 'day0_35/first_35_usage.html', start_date = start_date, end_date = end_date, endpoint_filter = endpoint_filter, total_visits = total_visits, unique_users = unique_users, avg_visits_per_user = avg_visits_per_user, most_active_day = most_active_day, most_active_day_count= most_active_day_count, daily_counts = daily_counts, endpoint_counts = endpoint_counts, top_users = top_users, visit_log = visit_log, ) # TODO: Clean up this ROAS Stuff. DATE_FMT = "%Y-%m-%d" DATE_TODAY = '2025-09-11' DASH_COLS = [ "pfn_geo", "artist_name", "product_name", "label_name", "geo_country", "spend_start", "spend_stop", "status" ] def _load_dashboard_df() -> pd.DataFrame: q = "SELECT * FROM roas.campaign_agg_by_platform" df = smeds.query_db(q, 'main') df.columns = [c.strip().lower() for c in df.columns] # keep only the columns we show missing = [c for c in DASH_COLS if c not in df.columns] if missing: raise KeyError(f"Missing columns in dashboard CSV: {missing}") df = df[DASH_COLS].copy() df = df.drop_duplicates().drop_duplicates(subset=["pfn_geo"], keep="first") # parse dates for filtering + display df["spend_start"] = pd.to_datetime(df["spend_start"], errors="coerce") df["spend_stop"] = pd.to_datetime(df["spend_stop"], errors="coerce") df["spend_start_str"] = df["spend_start"].dt.strftime(DATE_FMT) df["spend_stop_str"] = df["spend_stop"].dt.strftime(DATE_FMT) df["spend_stop_str"] = np.where(df["spend_stop_str"].isna(), '-', df["spend_stop_str"]) # sort by start desc for initial view consistency df = df.sort_values(["spend_start", "spend_stop"], ascending=[False, False], na_position="last") return df # --- Prep Platform Analytics Timeseries --- def _prep_platform_series(df): """ Given a per-adset daily df (already filtered to this pfn_geo), return: - labels: list of dates (YYYY-MM-DD) - series: dict metric -> list of floats aligned to labels """ # group by date (sum all adsets - gender, age) grouped_df = df.groupby(df['report_date']).sum(numeric_only=True).reset_index() # labels labels = grouped_df['report_date'].dt.strftime(DATE_FMT).tolist() # numeric metrics (exclude ids etc.) drop_cols = {"pfn_geo", "product_family_no", "treatment", "campaign_id", "ad_set", "adset", "campaign", "account_id"} numeric_cols = grouped_df.select_dtypes(include="number").columns metrics = [c for c in numeric_cols if c not in drop_cols] series = {m: grouped_df[m].astype(float).fillna(0).tolist() for m in metrics} return labels, series def _common_metrics(tt_series: dict[str, list[float]], meta_series: dict[str, list[float]]) -> list[str]: """Return a sorted list of metrics available in BOTH platforms; if empty, return union.""" tt_keys = set(tt_series.keys()) mt_keys = set(meta_series.keys()) inter = sorted(tt_keys & mt_keys) if inter: return inter return sorted(tt_keys | mt_keys) # ---------- Filters ---------- def _filter_text(df, q): if not q: return df ql = q.lower() mask = ( df["artist_name"].astype(str).str.lower().str.contains(ql, na=False) | df["product_name"].astype(str).str.lower().str.contains(ql, na=False) ) return df[mask] def _filter_markets(df, markets): if not markets: return df selected = [m for m in (m.strip() for m in markets) if m] selected_lower = {m.lower() for m in selected} if not selected_lower: return df return df[df["geo_country"].astype(str).str.lower().isin(selected_lower)] # Make one for label filtering def _filter_labels(df, labels): if not labels: return df selected = [l for l in (l.strip() for l in labels) if l] selected_lower = {l.lower() for l in selected} if not selected_lower: return df return df[df["label_name"].astype(str).str.lower().isin(selected_lower)] def _filter_dates(df, start, stop): if not start and not stop: return df start_dt = pd.to_datetime(start, errors="coerce") if start else None stop_dt = pd.to_datetime(stop, errors="coerce") if stop else None out = df if start_dt is not None: out = out[(out["spend_stop"].isna()) | (out["spend_stop"] >= start_dt)] if stop_dt is not None: out = out[(out["spend_start"].isna()) | (out["spend_start"] <= stop_dt)] return out def _apply_filters(df, q, markets, labels, start, stop): df = _filter_text(df, q) df = _filter_markets(df, markets) df = _filter_labels(df, labels) df = _filter_dates(df, start, stop) return df def _markets_list(df): vals = df["geo_country"].dropna().astype(str).str.strip() vals = vals[vals != ""].unique().tolist() return sorted(vals) def _labels_list(df): vals = df["label_name"].dropna().astype(str).str.strip() vals = vals[vals != ""].unique().tolist() return sorted(vals) # ---------- Routes ---------- @main_bp.route("/roas_dashboard", methods=["GET"]) def roas_dashboard(): """ /roas — Dashboard table with search (artist/track), multi-market select, date range. """ q = (request.args.get("q") or "").strip() or None markets = request.args.getlist("market") labels = request.args.getlist("label") start = (request.args.get("start") or "").strip() or None stop = (request.args.get("stop") or "").strip() or None df_all = _load_dashboard_df() markets_all = _markets_list(df_all) labels_all = _labels_list(df_all) df = _apply_filters(df_all, q, markets, labels, start, stop) rows = df.to_dict(orient="records") return render_template( "roas_dashboard.html", rows=rows, markets_all=markets_all, markets_selected=markets, labels_all=labels_all, labels_selected=labels, q=q or "", start=start or "", stop=stop or "", ) @main_bp.route("/roas_analytics/", methods=["GET"]) def roas_analytics(pfn_geo: str): """ /roas/analytics/ Pulls: - agg campaign meta and results - streaming uplifts comparison - timeseries attribution by campaign - did results - blurbs - tiktok daily ad set - meta daily ad set """ # --- campaign meta --- q = f"select * FROM roas.campaign_agg_by_platform WHERE pfn_geo = '{pfn_geo}'" ca_row = smeds.query_db(q, 'main') r = ca_row.iloc[0] # --- streaming uplift table --- q = f"select * FROM roas.uplift_comparisons WHERE treatment = '{pfn_geo}'" uplift_row = smeds.query_db(q, 'main') # safe gets per your request title_artist = r.get("artist_name", "") title_track = r.get("product_name", "") budget = r.get("campaign_budget", "") audience_target = r.get("audience_target", "") objective = r.get("objective", "") status = r.get("status", "") # dates start_dt = pd.to_datetime(r.get("spend_start"), errors="coerce") stop_dt = pd.to_datetime(r.get("spend_stop"), errors="coerce") start_str = start_dt.strftime(DATE_FMT) if pd.notna(start_dt) else "" stop_str = stop_dt.strftime(DATE_FMT) if pd.notna(stop_dt) else "" recommended_date = pd.to_datetime(r.get("recommended_date"), errors="coerce") recommended_date = recommended_date.strftime(DATE_FMT) # TEMP: Handle live campaign edge cases that don't have stop_dt yet stop_dt = pd.to_datetime(DATE_TODAY) if not pd.notna(stop_dt) else stop_dt stop_str = DATE_TODAY if stop_str=="" else stop_str # --- did result box --- q = f"SELECT * FROM roas.did_results WHERE treatment = '{pfn_geo}'" did_results_df = smeds.query_db(q, 'main') did_coef = did_results_df.coef.iloc[0] did_pval = did_results_df.p_value.iloc[0] did_stat_sig = did_pval < 0.05 if pd.notna(did_pval) else False # --- blurb --- q = f"SELECT * FROM roas.blurbs_by_campaign WHERE pfn_geo = '{pfn_geo}'" blurb_df = smeds.query_db(q, 'main') summary_text = blurb_df.summary.iloc[0] driver_text = blurb_df.drivers.iloc[0] # recommended actions section for live campaigns: def process_driver_text(text): if ':' in text: parts = text.split(":", 1) intro = parts[0].strip() after = parts[1] if len(parts) > 1 else "" return {"intro": intro, "after": after} else: pass blurb_df["driver_text_parts"] = blurb_df["drivers"].apply(process_driver_text) driver_text_parts = blurb_df.driver_text_parts.iloc[0] # --- timeseries --- q = f"SELECT * FROM roas.ts_attribution WHERE treatment = '{pfn_geo}'" ts = smeds.query_db(q, 'main') ts.columns = [c.strip().lower() for c in ts.columns] ts_labels = [] ts_treat = [] ts_control = [] if not ts.empty and {"report_date", "days_from_event", "treatment_streams", "top_avg_control"}.issubset(ts.columns): # ts = ts.dropna(subset=["days_from_event"]).sort_values("days_from_event") # ts_labels = ts["days_from_event"].astype(int).tolist() ts["report_date"] = pd.to_datetime(ts["report_date"], errors="coerce") ts = ts.dropna(subset=["report_date"]).sort_values("report_date") ts_labels = ts["report_date"].dt.strftime(DATE_FMT).tolist() ts_treat = pd.to_numeric(ts["treatment_streams"], errors="coerce").fillna(0).astype(int).tolist() ts_control = pd.to_numeric(ts["top_avg_control"], errors="coerce").fillna(0).astype(int).tolist() # TEMP: Remove treatment streams after today's date temp_live_ts = ts[ts['report_date']<=pd.to_datetime(DATE_TODAY)] ts_treat = pd.to_numeric(temp_live_ts["treatment_streams"], errors="coerce").fillna(0).astype(int).tolist() if stop_str == DATE_TODAY else ts_treat # # TODO Add Tiktok Creations & Views Timeseries # tiktok_ts = pd.read_csv('/Users/kim0045/Desktop/smeds-tadas-ui/blueprints/roas/tiktok_treatment_control.csv') # tiktok_ts["report_date"] = pd.to_datetime(tiktok_ts["report_date"], errors="coerce") # tiktok_ts = tiktok_ts.dropna(subset=["report_date"]).sort_values("report_date") # tt_treatment = tiktok_ts[tiktok_ts['type']=='treatment'] # tt_control = tiktok_ts[tiktok_ts['type']=='control'] # tt_c_labels = tt_treatment["report_date"].dt.strftime(DATE_FMT).tolist() # tt_c_treat = pd.to_numeric(tt_treatment["t_creations_today"], errors="coerce").fillna(0).astype(int).tolist() # tt_c_control = pd.to_numeric(tt_control["t_creations_today"], errors="coerce").fillna(0).astype(int).tolist() # --- TikTok & Meta daily ad set level timeseries q = f"SELECT * FROM roas.tiktok_daily_ad_set WHERE pfn_geo = '{pfn_geo}'" tiktok_ts = smeds.query_db(q, 'main') tiktok_ts.columns = [c.strip().lower() for c in tiktok_ts.columns] tiktok_ts["report_date"] = pd.to_datetime(tiktok_ts["report_date"], errors="coerce") tiktok_ts = tiktok_ts.dropna(subset=["report_date"]).sort_values("report_date") q = f"SELECT * FROM roas.meta_daily_ad_set WHERE pfn_geo = '{pfn_geo}'" meta_ts = smeds.query_db(q, 'main') meta_ts.columns = [c.strip().lower() for c in meta_ts.columns] meta_ts["report_date"] = pd.to_datetime(meta_ts["report_date"], errors="coerce") meta_ts = meta_ts.dropna(subset=["report_date"]).sort_values("report_date") # TEMP: Handle live campaigns (cut off later date metrics) if stop_str == DATE_TODAY: cutoff = pd.to_datetime(DATE_TODAY) tiktok_ts = tiktok_ts[tiktok_ts["report_date"] <= cutoff] meta_ts = meta_ts[meta_ts["report_date"] <= cutoff] tt_labels, tt_series = _prep_platform_series(tiktok_ts) mt_labels, mt_series = _prep_platform_series(meta_ts) metrics_all = _common_metrics(tt_series, mt_series) # default checked set # TEMP: Update stop_str to "" for no campaign stop date display stop_str = "" if stop_str == DATE_TODAY else stop_str # --- analytics table --- ca_columns = ['platform', 'spend_start', 'spend_stop', 'audience_target', 'objective', 'impressions', 'views', 'vtr', 'cost_per_view', 'vs_bmk'] ca_table = ca_row[ca_columns] # update values to integers, percetages, or 2-decimal floats as appropriate ca_table['impressions'] = pd.to_numeric(ca_table['impressions'], errors='coerce').apply( lambda x: f"{int(x):,}" if pd.notna(x) else "N/A" ) ca_table['views'] = pd.to_numeric(ca_table['views'], errors='coerce').apply( lambda x: f"{int(x):,}" if pd.notna(x) else "N/A" ) # just add '%' at the end to vtr values ca_table['vtr'] = ca_table['vtr'].apply(lambda x: f"{x:.0f}%" if pd.notna(x) else "N/A") ca_table['vs_bmk'] = ca_table['vs_bmk'].apply(lambda x: f"{x:.0f}%" if pd.notna(x) else "N/A") ca_table.sort_values(by='platform', ascending=False, inplace=True) ca_rows = ca_table.to_dict(orient="records") # --- uplift comparison table --- uplift_row.rename(columns={'label': '', 'agg_streams_lift': 'Total Streams', 's_streams_lift': 'Spotify Streams', 's_lf_streams_lift': 'Spotify LF', 's_search_streams_lift': 'Spotify Search', 's_collection_streams_lift': 'Spotify Collection', 's_artist_streams_lift': 'Spotify Artist', 's_album_streams_lift': 'Spotify Album', 's_radio_streams_lift': 'Spotify Radio'}, inplace=True) uplift_columns = ['', 'Total Streams', 'Spotify Streams', 'Spotify LF', 'Spotify Search', 'Spotify Collection', 'Spotify Artist', 'Spotify Album', 'Spotify Radio'] uplift_table = uplift_row[uplift_columns] # convert values to whole number percentages except non-numeric column uplift_table[uplift_table.select_dtypes(include='number').columns] = uplift_table.select_dtypes(include='number').applymap(lambda x: f"{x:.0%}" if pd.notna(x) else "N/A") uplift_rows = uplift_table.to_dict(orient="records") # markets for the universal search bar try: markets_all = _markets_list(_load_dashboard_df()) labels_all = _labels_list(_load_dashboard_df()) except Exception: markets_all = [] labels_all = [] return render_template( "roas_analytics.html", # nav/search context (kept empty so analytics URL stays clean) q="", start="", stop="", markets_all=markets_all, markets_selected=[], labels_all=labels_all, labels_selected=[], campaign={str(pfn_geo)}, # headline + meta pfno=str(pfn_geo), title_track=title_track or "Unknown Track", title_artist=title_artist or "Unknown Artist", start_str=start_str, stop_str=stop_str, start_dt = start_dt, stop_dt = stop_dt, status = status, budget='$'+str(int(budget)) if (budget is not None and str(budget) != "nan") else "", recommended_date=str(recommended_date) if (recommended_date is not None and str(recommended_date) != "nan") else "", audience_target=str(audience_target) if (audience_target is not None and str(audience_target) != "nan") else "", objective=str(objective) if (objective is not None and str(objective) != "nan") else "", ### stat + chart + table did_coef=did_coef, did_stat_sig=did_stat_sig, summary_text=summary_text, driver_text=driver_text, driver_text_parts=driver_text_parts, ts_labels=ts_labels, ts_treat=ts_treat, ts_control=ts_control, # # tiktok creations timeseries # tt_c_labels=tt_c_labels, # tt_c_treat=tt_c_treat, # tt_c_control=tt_c_control, ca_columns=ca_columns, ca_rows=ca_rows, uplift_columns=uplift_columns, uplift_rows=uplift_rows, # campaign metric timeseries tt_labels=tt_labels, tt_series=tt_series, mt_labels=mt_labels, mt_series=mt_series, metrics_all=metrics_all )