from datetime import datetime from typing import List import numpy as np import pandas as pd import streamlit as st from snowflake.snowpark import Session from snowflake.snowpark.context import get_active_session from snowflake.snowpark.exceptions import SnowparkSessionException from common.main_metrics import by_day, top_products, overall_raw, vs_last_week try: user = st.experimental_user.user_name except AttributeError: user = "user" st.set_page_config(layout="wide") st.write(f"Hello {user}!") st.subheader("Merch data overview.") try: session = get_active_session() except SnowparkSessionException: from common.local_connection import get_connection_parameters connection_params = get_connection_parameters("sme_merch") session = Session.builder.configs(connection_params).create() @st.cache_data(show_spinner="downloading data, please wait...") def preparing_environment() -> tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame]: raw_by_artist_table = "merch_artist_raw_metrics" raw_sp = session.table(raw_by_artist_table) raw_df_ = raw_sp.to_pandas() totals_by_artist_table = "merch_artist_metrics" metrics_sp = session.table(totals_by_artist_table) metrics_df_ = metrics_sp.to_pandas() artist_daily_table = "merch_daily_metrics" daily_sp = session.table(artist_daily_table) daily_metrics_df_ = daily_sp.to_pandas() basket_assoc_table = "BASKET_ANALYSIS_OUTPUT_ASSOC" basket_assoc_sp = session.table(basket_assoc_table) basket_assoc_df_ = basket_assoc_sp.to_pandas() basket_assoc_item_table = "BASKET_ANALYSIS_OUTPUT_ASSOC_ITEM" basket_assoc_item_sp = session.table(basket_assoc_item_table) basket_assoc_item_df_ = basket_assoc_item_sp.to_pandas() product_metrics_table = "MERCH_BASKET_METRICS" product_sp = session.table(product_metrics_table) product_metrics_df_ = product_sp.to_pandas() product_metrics_df_["PRODUCT_TYPE"] = np.where( product_metrics_df_["PRODUCT_TYPE"] == "", "unknown product type", product_metrics_df_["PRODUCT_TYPE"], ) product_name_metrics_table = "MERCH_NAME_BASKET_METRICS" product_name_sp = session.table(product_name_metrics_table) product_name_metrics_df_ = product_name_sp.to_pandas() product_age_metrics_table = "MERCH_BASKET_METRICS_AGE" product_age_sp = session.table(product_age_metrics_table) product_age_metrics_df_ = product_age_sp.to_pandas() product_age_metrics_df_['AGE_RANGE_C'] = product_age_metrics_df_['AGE_RANGE_C'].apply( lambda x: x[:-2] + '-' + x[-2:] if x is not None else x) product_gender_metrics_table = "MERCH_BASKET_METRICS_GENDER" product_gender_sp = session.table(product_gender_metrics_table) product_gender_metrics_df_ = product_gender_sp.to_pandas() fansifter_ratio_table = "MERCH_ARTIST_FANSIFTER_PERCENTAGE" fansifter_ratio_table_sp = session.table(fansifter_ratio_table) fansifter_ratio_table_df_ = fansifter_ratio_table_sp.to_pandas() fansifter_segments_table = "MERCH_ARTIST_SEGMENT_PERCENTAGE" fansifter_segments_table_sp = session.table(fansifter_segments_table) fansifter_segments_table_df_ = fansifter_segments_table_sp.to_pandas() return ( metrics_df_, daily_metrics_df_, basket_assoc_df_, basket_assoc_item_df_, product_metrics_df_, product_name_metrics_df_, raw_df_, fansifter_ratio_table_df_, fansifter_segments_table_df_, product_age_metrics_df_, product_gender_metrics_df_ ) ( metrics_df, daily_metrics_df, basket_assoc_df, basket_assoc_item_df, product_metrics_df, product_name_metrics_df, raw_df, fansifter_ratio_table_df, fansifter_segments_table_df, product_age_metrics_df, product_gender_metrics_df ) = preparing_environment() raw_key = "raw_df" metrics_key = "metrics_df" rfm_key = "rfm_df" daily_key = "daily_metrics_df" cluster_key = "df_clusters" basket_key = "basket_assoc_df" basket_item_key = "basket_assoc_item_df" product_item_key = "product_metrics_df" product_name_item_key = "product_name_metrics_df" fansifter_ratio_item_key = "fansifter_ratio_table_df" fansifter_segments_item_key = "fansifter_segments_table_df" basket_age_key = "product_age_metrics_df" basket_gender_key = "product_gender_metrics_df" if metrics_key not in st.session_state: st.session_state[raw_key] = raw_df st.session_state[metrics_key] = metrics_df st.session_state[daily_key] = daily_metrics_df st.session_state[product_item_key] = product_metrics_df st.session_state[product_name_item_key] = product_name_metrics_df st.session_state[basket_key] = basket_assoc_df st.session_state[basket_item_key] = basket_assoc_item_df st.session_state[fansifter_ratio_item_key] = fansifter_ratio_table_df st.session_state[fansifter_segments_item_key] = fansifter_segments_table_df st.session_state[basket_age_key] = product_age_metrics_df st.session_state[basket_gender_key] = product_gender_metrics_df # Check if session state object exists if "index" not in st.session_state: st.session_state["index"] = 0 if "artists" not in st.session_state: st.session_state["artists"] = metrics_df["ARTIST"].drop_duplicates().to_list() with st.sidebar: selected_artist = st.selectbox( label="Choose artist", options=st.session_state["artists"], index=st.session_state["index"], key="artist", ) if selected_artist: main_territory_multiselect: List[str] = st.multiselect( "Choose territories", metrics_df[metrics_df["ARTIST"] == selected_artist]["TERRITORY"].unique(), ) time_period = st.slider( "Select a date range", min_value=datetime(2019, 9, 19), max_value=datetime(2025, 9, 1), value=(datetime(2019, 9, 19), datetime(2025, 9, 1)), format="MM/DD/YYYY", ) # Update the index. It is used in the select box. st.session_state["index"] = st.session_state["artists"].index( st.session_state["artist"] ) if main_territory_multiselect: overall_raw(raw_df, st.session_state["artist"], main_territory_multiselect, time_period) st.divider() by_day(daily_metrics_df, time_period, main_territory_multiselect) vs_last_week(raw_df, main_territory_multiselect) st.divider() top_products(product_metrics_df, product_name_metrics_df, time_period, main_territory_multiselect) st.divider() else: st.write("Please choose artist and territory from left menu.")