import streamlit as st import pandas as pd from snowflake.snowpark.context import get_active_session from datetime import datetime, timedelta st.set_page_config( page_title="E-commerce Analytics Dashboard", page_icon="🛒", layout="wide" ) @st.cache_resource def get_snowpark_session(): """Get active Snowpark session""" return get_active_session() @st.cache_data(ttl=300) # Cache for 5 minutes def get_active_stores(): """Get list of active stores""" session = get_snowpark_session() query = """ SELECT STORE_ID, SNOWFLAKE_SCHEMA, STORE_NAME, PLATFORM, TERRITORY, ARTIST, SNOWFLAKE_DB FROM CRM_ECOMMERCE_DATA.CONSOLIDATION_DATA.ECOMMERCE_STORES WHERE ACTIVE = TRUE ORDER BY STORE_NAME """ return session.sql(query).to_pandas() @st.cache_data(ttl=300) def get_orders_data(selected_stores, date_range): """Get orders data from selected stores""" if not selected_stores: return pd.DataFrame() session = get_snowpark_session() stores_df = get_active_stores() filtered_stores = stores_df[stores_df['STORE_ID'].isin(selected_stores)] # Get data from each store separately and combine with pandas all_data = [] for _, store in filtered_stores.iterrows(): try: db = store['SNOWFLAKE_DB'] schema = store['SNOWFLAKE_SCHEMA'] store_name = store['STORE_NAME'] query = f""" SELECT * FROM {db}.{schema}."ORDER" WHERE CREATED_AT >= '{date_range[0]}' AND CREATED_AT <= '{date_range[1]}' """ store_data = session.sql(query).to_pandas() if not store_data.empty: store_data['source_schema'] = schema store_data['store_name'] = store_name all_data.append(store_data) except Exception as e: st.warning(f"Could not load data from {store_name}: {str(e)}") continue if all_data: return pd.concat(all_data, ignore_index=True, sort=False) else: return pd.DataFrame() @st.cache_data(ttl=300) def get_order_url_tags(selected_stores, date_range): """Get order URL tags data from selected stores""" if not selected_stores: return pd.DataFrame() session = get_snowpark_session() stores_df = get_active_stores() filtered_stores = stores_df[stores_df['STORE_ID'].isin(selected_stores)] # Get data from each store separately and combine with pandas all_data = [] for _, store in filtered_stores.iterrows(): try: db = store['SNOWFLAKE_DB'] schema = store['SNOWFLAKE_SCHEMA'] store_name = store['STORE_NAME'] query = f""" SELECT * FROM {db}.{schema}.ORDER_URL_TAG """ store_data = session.sql(query).to_pandas() if not store_data.empty: store_data['source_schema'] = schema store_data['store_name'] = store_name all_data.append(store_data) except Exception as e: st.warning(f"Could not load URL tags from {store_name}: {str(e)}") continue if all_data: return pd.concat(all_data, ignore_index=True, sort=False) else: return pd.DataFrame() def parse_utm_tags(url_tags_df): """Parse UTM parameters from URL tags""" # Check for different possible column name combinations key_col = None value_col = None if 'KEY' in url_tags_df.columns and 'VALUE' in url_tags_df.columns: key_col, value_col = 'KEY', 'VALUE' elif 'TAG_NAME' in url_tags_df.columns and 'TAG_VALUE' in url_tags_df.columns: key_col, value_col = 'TAG_NAME', 'TAG_VALUE' else: st.warning("Could not find UTM tag columns (looking for KEY/VALUE or TAG_NAME/TAG_VALUE)") return pd.DataFrame() if url_tags_df.empty: return pd.DataFrame() # Filter for UTM tags utm_tags = url_tags_df[url_tags_df[key_col].str.startswith('utm_', na=False)].copy() if utm_tags.empty: return pd.DataFrame() # Check if we have ORDER_ID column id_col = 'ORDER_ID' if 'ORDER_ID' in utm_tags.columns else 'order_id' if id_col not in utm_tags.columns: st.warning("Could not find ORDER_ID column for UTM analysis") return pd.DataFrame() # Pivot to get UTM parameters as columns utm_pivot = utm_tags.pivot_table( index=[id_col, 'store_name'], columns=key_col, values=value_col, aggfunc='first' ).reset_index() # Clean column names utm_pivot.columns.name = None # Rename ORDER_ID column to be consistent if id_col != 'ORDER_ID': utm_pivot = utm_pivot.rename(columns={id_col: 'ORDER_ID'}) return utm_pivot def main(): st.title("🛒 E-commerce Analytics Dashboard") st.markdown("Analyze ORDER and ORDER_URL_TAG data across all Shopify stores") # Sidebar for all filters with st.sidebar: st.header("🔍 Filters") # Load stores try: stores_df = get_active_stores() # Add cache clear button if st.button("🔄 Clear Cache & Reload Stores"): st.cache_data.clear() st.rerun() st.subheader("Store Selection") # Store name filter store_name_filter = st.text_input( "Search stores by name", placeholder="Type to search stores...", help="Enter part of a store name to filter the list" ) # Filter stores based on search if store_name_filter: filtered_stores_df = stores_df[ stores_df['STORE_NAME'].str.contains(store_name_filter, case=False, na=False) ] else: filtered_stores_df = stores_df # Additional filters col1, col2 = st.columns(2) with col1: # Platform filter platforms = ['All'] + sorted(stores_df['PLATFORM'].dropna().unique().tolist()) selected_platform = st.selectbox("Platform", platforms) with col2: # Territory filter territories = ['All'] + sorted(stores_df['TERRITORY'].dropna().unique().tolist()) selected_territory = st.selectbox("Territory", territories) # Apply additional filters if selected_platform != 'All': filtered_stores_df = filtered_stores_df[filtered_stores_df['PLATFORM'] == selected_platform] if selected_territory != 'All': filtered_stores_df = filtered_stores_df[filtered_stores_df['TERRITORY'] == selected_territory] # Store selection store_options = dict(zip(filtered_stores_df['STORE_NAME'], filtered_stores_df['STORE_ID'])) if not store_options: st.warning("No stores found matching the filter criteria") return # Store selection options col1, col2, col3 = st.columns(3) with col1: if st.button("Select All"): st.session_state.selected_store_names = list(store_options.keys()) with col2: if st.button("Select First 10"): st.session_state.selected_store_names = list(store_options.keys())[:10] with col3: if st.button("Deselect All"): st.session_state.selected_store_names = [] # Initialize session state if not exists or filter valid options if 'selected_store_names' not in st.session_state: st.session_state.selected_store_names = list(store_options.keys())[:10] else: # Filter out any selected stores that are no longer in the current options st.session_state.selected_store_names = [ store for store in st.session_state.selected_store_names if store in store_options.keys() ] selected_store_names = st.multiselect( f"Select Stores ({len(store_options)} available)", options=list(store_options.keys()), default=st.session_state.selected_store_names, key="store_multiselect" ) # Update session state st.session_state.selected_store_names = selected_store_names selected_stores = [store_options[name] for name in selected_store_names] # Show selected stores info if selected_stores: st.success(f"✅ {len(selected_stores)} store(s) selected") if len(selected_stores) > 50: st.warning("⚠️ Loading data from many stores may take longer.") else: st.warning("No stores selected") # Date range selection st.subheader("Date Range") default_end = datetime.now() default_start = default_end - timedelta(days=30) date_range = st.date_input( "Select date range", value=(default_start, default_end), max_value=datetime.now(), help="Initial date range for loading data" ) if len(date_range) != 2: st.error("Please select both start and end dates") return # Additional data filters (will be applied after data is loaded) st.subheader("Data Refinement") # Store search for loaded data st.write("**🏪 Filter by Specific Stores**") data_store_search = st.text_input( "Search within loaded stores", placeholder="Filter loaded data by store name...", help="Further filter the loaded data by store name", key="data_store_search" ) # Date refinement st.write("**📅 Refine Date Range**") refine_date_range = st.checkbox( "Enable date range refinement", help="Further narrow the date range after data is loaded" ) # Order value range st.write("**💰 Order Value Range**") enable_price_filter = st.checkbox( "Enable order value filtering", help="Filter orders by total price range" ) # UTM filters (will be populated dynamically after data loads) st.subheader("UTM Filters") st.info("UTM filter options will appear here after data is loaded") except Exception as e: st.error(f"Error loading stores: {str(e)}") st.info("Please check your Snowflake permissions and table access") return if not selected_stores: st.warning("Please select at least one store to analyze") return # Main dashboard try: # Load data with st.spinner("Loading orders data..."): orders_df = get_orders_data(selected_stores, date_range) with st.spinner("Loading URL tags data..."): url_tags_df = get_order_url_tags(selected_stores, date_range) if orders_df.empty: st.warning("No orders data found for the selected criteria") return # Apply data refinement filters from sidebar filtered_orders_df = orders_df.copy() available_stores_in_data = sorted(orders_df['store_name'].unique()) # Apply store name filter from sidebar if data_store_search: matching_stores = [ store for store in available_stores_in_data if data_store_search.lower() in store.lower() ] if matching_stores: filtered_orders_df = filtered_orders_df[filtered_orders_df['store_name'].isin(matching_stores)] # Apply refined date range filter if enabled if refine_date_range and 'CREATED_AT' in filtered_orders_df.columns: filtered_orders_df['CREATED_AT'] = pd.to_datetime(filtered_orders_df['CREATED_AT']) min_date = filtered_orders_df['CREATED_AT'].min().date() max_date = filtered_orders_df['CREATED_AT'].max().date() # Create dynamic date range selector in sidebar with st.sidebar: if min_date and max_date: refined_date_range = st.date_input( "Select refined date range", value=(min_date, max_date), min_value=min_date, max_value=max_date, help="Further filter the loaded data by date" ) if len(refined_date_range) == 2: start_date, end_date = refined_date_range filtered_orders_df = filtered_orders_df[ (filtered_orders_df['CREATED_AT'].dt.date >= start_date) & (filtered_orders_df['CREATED_AT'].dt.date <= end_date) ] # Apply price range filter if enabled if enable_price_filter and 'TOTAL_PRICE' in filtered_orders_df.columns: min_price = float(filtered_orders_df['TOTAL_PRICE'].min()) if filtered_orders_df['TOTAL_PRICE'].notna().any() else 0.0 max_price = float(filtered_orders_df['TOTAL_PRICE'].max()) if filtered_orders_df['TOTAL_PRICE'].notna().any() else 1000.0 # Create dynamic price range selector in sidebar with st.sidebar: price_range = st.slider( "Filter by order value ($)", min_value=min_price, max_value=max_price, value=(min_price, max_price), step=1.0, help="Filter orders by total price range" ) filtered_orders_df = filtered_orders_df[ (filtered_orders_df['TOTAL_PRICE'] >= price_range[0]) & (filtered_orders_df['TOTAL_PRICE'] <= price_range[1]) ] # Show filtered data info if len(filtered_orders_df) != len(orders_df): st.info(f"📊 Showing {len(filtered_orders_df):,} orders (filtered from {len(orders_df):,} total)") # Use filtered data for all subsequent analysis orders_df = filtered_orders_df if orders_df.empty: st.warning("No data matches the selected filters") return # Overview metrics st.header("📊 Overview") col1, col2, col3, col4 = st.columns(4) with col1: total_orders = len(orders_df) st.metric("Total Orders", f"{total_orders:,}") with col2: if 'TOTAL_PRICE' in orders_df.columns: total_revenue = orders_df['TOTAL_PRICE'].sum() st.metric("Total Revenue", f"${total_revenue:,.2f}") else: st.metric("Total Revenue", "N/A") with col3: unique_stores = orders_df['store_name'].nunique() st.metric("Active Stores", unique_stores) with col4: if 'TOTAL_PRICE' in orders_df.columns and total_orders > 0: avg_order = orders_df['TOTAL_PRICE'].mean() st.metric("Avg Order Value", f"${avg_order:.2f}") else: st.metric("Avg Order Value", "N/A") # Charts st.header("📈 Analytics") # Orders by store col1, col2 = st.columns(2) with col1: st.subheader("Orders by Store") store_orders = orders_df.groupby('store_name').size().reset_index(name='orders') store_orders = store_orders.set_index('store_name') st.bar_chart(store_orders, color='#FF8C00') with col2: if 'TOTAL_PRICE' in orders_df.columns: st.subheader("Revenue by Store") store_revenue = orders_df.groupby('store_name')['TOTAL_PRICE'].sum().reset_index() store_revenue = store_revenue.set_index('store_name') st.bar_chart(store_revenue, color='#FF8C00') # Time series if 'CREATED_AT' in orders_df.columns: st.subheader("Orders Over Time") orders_df['CREATED_AT'] = pd.to_datetime(orders_df['CREATED_AT']) daily_orders = orders_df.groupby(orders_df['CREATED_AT'].dt.date).size().reset_index(name='orders') daily_orders.columns = ['date', 'orders'] daily_orders = daily_orders.set_index('date') st.line_chart(daily_orders, color='#FF8C00') # UTM Performance Analysis if not url_tags_df.empty: st.header("📊 UTM Performance Analysis") # Apply the same filtering to URL tags data for UTM analysis filtered_url_tags_df = url_tags_df.copy() # Apply store name search filter to URL tags if data_store_search: matching_stores = [ store for store in available_stores_in_data if data_store_search.lower() in store.lower() ] if matching_stores: filtered_url_tags_df = filtered_url_tags_df[filtered_url_tags_df['store_name'].isin(matching_stores)] # Apply date range filter to URL tags by matching with filtered orders if not orders_df.empty: # Get order IDs from filtered orders order_id_col = 'ID' if 'ID' in orders_df.columns else 'ORDER_ID' if order_id_col in orders_df.columns: filtered_order_ids = orders_df[order_id_col].unique() url_order_id_col = 'ORDER_ID' if 'ORDER_ID' in filtered_url_tags_df.columns else 'order_id' if url_order_id_col in filtered_url_tags_df.columns: filtered_url_tags_df = filtered_url_tags_df[filtered_url_tags_df[url_order_id_col].isin(filtered_order_ids)] # Parse UTM tags from filtered data utm_df = parse_utm_tags(filtered_url_tags_df) if not utm_df.empty: # Merge with orders data to get revenue info utm_orders = utm_df.copy() # Try to merge with filtered orders data for revenue info if not orders_df.empty: order_id_col = 'ID' if 'ID' in orders_df.columns else None if order_id_col and 'ORDER_ID' in utm_df.columns: # Select columns that actually exist merge_cols = [order_id_col, 'store_name'] optional_cols = ['TOTAL_PRICE', 'CREATED_AT'] for col in optional_cols: if col in orders_df.columns: merge_cols.append(col) # Perform merge utm_orders = utm_df.merge( orders_df[merge_cols].rename(columns={order_id_col: 'ORDER_ID'}), on=['ORDER_ID', 'store_name'], how='left' ) # Update UTM filter options in sidebar based on loaded data if 'utm_source' in utm_orders.columns: source_options = ['All'] + sorted(utm_orders['utm_source'].dropna().unique().tolist()) with st.sidebar: utm_source_filter = st.selectbox( "UTM Source", options=source_options, help="Filter UTM analysis by specific source", key="utm_source_select" ) if 'utm_medium' in utm_orders.columns: medium_options = ['All'] + sorted(utm_orders['utm_medium'].dropna().unique().tolist()) with st.sidebar: utm_medium_filter = st.selectbox( "UTM Medium", options=medium_options, help="Filter UTM analysis by specific medium", key="utm_medium_select" ) # Apply UTM filters filtered_utm_orders = utm_orders.copy() if utm_source_filter != 'All' and 'utm_source' in filtered_utm_orders.columns: filtered_utm_orders = filtered_utm_orders[filtered_utm_orders['utm_source'] == utm_source_filter] if utm_medium_filter != 'All' and 'utm_medium' in filtered_utm_orders.columns: filtered_utm_orders = filtered_utm_orders[filtered_utm_orders['utm_medium'] == utm_medium_filter] # Show filter results info if len(filtered_utm_orders) != len(utm_orders): st.info(f"🔍 Showing {len(filtered_utm_orders):,} UTM orders (filtered from {len(utm_orders):,} total)") # UTM overview metrics (using filtered data) col1, col2, col3, col4 = st.columns(4) with col1: utm_orders_count = len(filtered_utm_orders) st.metric("Orders with UTM", f"{utm_orders_count:,}") with col2: if 'TOTAL_PRICE' in filtered_utm_orders.columns: utm_revenue = filtered_utm_orders['TOTAL_PRICE'].sum() st.metric("UTM Revenue", f"${utm_revenue:,.2f}") else: st.metric("UTM Revenue", "N/A") with col3: utm_sources = filtered_utm_orders['utm_source'].nunique() if 'utm_source' in filtered_utm_orders.columns else 0 st.metric("Unique Sources", utm_sources) with col4: utm_campaigns = filtered_utm_orders['utm_campaign'].nunique() if 'utm_campaign' in filtered_utm_orders.columns else 0 st.metric("Unique Campaigns", utm_campaigns) # UTM Performance Summary Tables st.subheader("UTM Performance Summary") # Display performance tables with filtered data if not filtered_utm_orders.empty: # Create UTM segments (combination of source + medium) if 'utm_source' in filtered_utm_orders.columns and 'utm_medium' in filtered_utm_orders.columns: filtered_utm_orders['utm_segment'] = filtered_utm_orders['utm_source'].astype(str) + ' / ' + filtered_utm_orders['utm_medium'].astype(str) for param in ['utm_source', 'utm_medium', 'utm_segment']: if param in filtered_utm_orders.columns: # Skip if we filtered by this parameter and there's only one value if (param == 'utm_source' and utm_source_filter != 'All') or \ (param == 'utm_medium' and utm_medium_filter != 'All'): if filtered_utm_orders[param].nunique() <= 1: continue # Create aggregation dict based on available columns if 'TOTAL_PRICE' in filtered_utm_orders.columns: agg_dict = { 'ORDER_ID': 'count', 'TOTAL_PRICE': ['sum', 'mean'] } else: agg_dict = { 'ORDER_ID': 'count' } summary = filtered_utm_orders.groupby(param).agg(agg_dict).round(2) # Set column names based on what we aggregated if 'TOTAL_PRICE' in filtered_utm_orders.columns: summary.columns = ['Orders', 'Total Revenue', 'Avg Order Value'] summary = summary.sort_values('Total Revenue', ascending=False) else: summary.columns = ['Orders'] summary = summary.sort_values('Orders', ascending=False) # Format the display name display_name = param.upper().replace('_', ' ') if param == 'utm_segment': display_name = 'UTM SEGMENT' st.write(f"**{display_name} Performance**") # Create visualizations alongside tables col1, col2 = st.columns([1, 1]) with col1: # Show the data table st.dataframe(summary.head(10), use_container_width=True) with col2: # Create visualizations based on available data if len(summary) > 0: if 'Total Revenue' in summary.columns: # Revenue chart st.write("**Revenue Chart**") top_performers = summary.head(8) # Limit to top 8 for readability revenue_chart_data = pd.DataFrame({ 'revenue': top_performers['Total Revenue'].values }, index=top_performers.index) st.bar_chart(revenue_chart_data, color='#FF8C00') else: # Orders chart (when no revenue data) st.write("**Orders Chart**") top_performers = summary.head(8) orders_chart_data = pd.DataFrame({ 'orders': top_performers['Orders'].values }, index=top_performers.index) st.bar_chart(orders_chart_data, color='#FF8C00') else: st.write("No data to visualize") # Add some spacing between sections st.write("") else: st.warning("No UTM data matches the selected filters") else: st.info("No UTM tags found in the data - ensure UTM parameters are being captured") # Data tables st.header("📋 Data Tables") tab1, tab2 = st.tabs(["Orders Data", "UTM Analysis Data"]) with tab1: st.subheader("Recent Orders") # Define privacy-sensitive columns to remove privacy_columns = [ 'EMAIL', 'PHONE', 'BILLING_ADDRESS_PHONE', 'SHIPPING_ADDRESS_PHONE', 'BILLING_ADDRESS_ADDRESS1', 'SHIPPING_ADDRESS_ADDRESS1', 'ADDRESS1', 'BILLING_ADDRESS_ADDRESS2', 'SHIPPING_ADDRESS_ADDRESS2', 'ADDRESS2', 'BILLING_ADDRESS_ADDRESS_1', 'SHIPPING_ADDRESS_ADDRESS_1', 'BILLING_ADDRESS_ADDRESS_2', 'SHIPPING_ADDRESS_ADDRESS_2', 'NAME', 'BILLING_ADDRESS_NAME', 'SHIPPING_ADDRESS_NAME', 'BILLING_ADDRESS_FIRST_NAME', 'SHIPPING_ADDRESS_FIRST_NAME', 'BILLING_ADDRESS_LAST_NAME', 'SHIPPING_ADDRESS_LAST_NAME', 'BILLING_ADDRESS_COMPANY', 'SHIPPING_ADDRESS_COMPANY', 'LATITUDE', 'LONGITUDE', 'BILLING_ADDRESS_LATITUDE', 'BILLING_ADDRESS_LONGITUDE', 'SHIPPING_ADDRESS_LATITUDE', 'SHIPPING_ADDRESS_LONGITUDE' ] # Remove privacy-sensitive columns from display display_orders = orders_df.head(100).copy() columns_to_drop = [col for col in privacy_columns if col in display_orders.columns] if columns_to_drop: display_orders = display_orders.drop(columns=columns_to_drop) st.info(f"🔒 Privacy protection: {len(columns_to_drop)} sensitive columns hidden from display") st.dataframe(display_orders, use_container_width=True) # Download data without privacy-sensitive columns download_orders = orders_df.copy() columns_to_drop_download = [col for col in privacy_columns if col in download_orders.columns] if columns_to_drop_download: download_orders = download_orders.drop(columns=columns_to_drop_download) csv_data = download_orders.to_csv(index=False) st.download_button( label="📥 Download Orders Data as CSV", data=csv_data, file_name=f"orders_data_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv", mime='text/csv', help="Privacy-sensitive columns are excluded from download" ) with tab2: if not url_tags_df.empty: # Use the same filtered UTM data from analysis section if 'filtered_utm_orders' in locals() and not filtered_utm_orders.empty: st.subheader("UTM Analysis Data") st.dataframe(filtered_utm_orders.head(100), use_container_width=True) # Download button csv = filtered_utm_orders.to_csv(index=False) st.download_button( label="📥 Download UTM Analysis Data as CSV", data=csv, file_name=f"utm_analysis_data_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv", mime='text/csv' ) else: st.info("No UTM tags found matching the current filters") else: st.info("No URL tags data available for UTM analysis") except Exception as e: st.error(f"Error loading data: {str(e)}") st.info("Please check your Snowflake permissions and table access") if __name__ == "__main__": main()