Frknrg/RgReport
0
1import requests2import pandas as pd3import os4import json5try:6 import streamlit as st7 _HAS_STREAMLIT = True8except ImportError:9 _HAS_STREAMLIT = False10import time11import random12 13def get_config():14 token = os.environ.get("SHOPIFY_ACCESS_TOKEN")15 store_domain = os.environ.get("SHOPIFY_STORE_DOMAIN")16 api_version = "2026-01"17 18 if _HAS_STREAMLIT:19 try:20 # Try to get from st.secrets if not in environ or to override21 if not token:22 token = st.secrets.get("SHOPIFY_ACCESS_TOKEN")23 if not store_domain:24 store_domain = st.secrets.get("SHOPIFY_STORE_DOMAIN")25 except Exception:26 # Silently fallback to environ if st.secrets fails (common in bg threads)27 pass28 29 return token, store_domain, api_version30 31def execute_shopifyql(query):32 """33 Executes a ShopifyQL query via the GraphQL Admin API (version 2026-01).34 35 Args:36 query (str): The ShopifyQL query string37 38 Returns:39 pd.DataFrame: A DataFrame containing the query results, or empty DataFrame on error.40 """41 token, store_domain, api_version = get_config()42 43 if not token or not store_domain:44 print("❌ Error: SHOPIFY_ACCESS_TOKEN or SHOPIFY_STORE_DOMAIN not set.")45 return pd.DataFrame()46 47 url = f"https://{store_domain}/admin/api/{api_version}/graphql.json"48 49 headers = {50 "Content-Type": "application/json",51 "X-Shopify-Access-Token": token52 }53 54 graphql_query = '''55 query ShopifyQLQuery($query: String!) {56 shopifyqlQuery(query: $query) {57 tableData {58 rows59 columns {60 name61 dataType62 displayName63 }64 }65 parseErrors66 }67 }68 '''69 70 payload = {71 "query": graphql_query,72 "variables": {73 "query": query74 }75 }76 77 max_retries = 878 base_delay = 30 # Shopify Plus: generous base delay79 80 for attempt in range(max_retries):81 try:82 response = requests.post(url, headers=headers, json=payload, timeout=60)83 84 # Check HTTP-level rate limit (429)85 if response.status_code == 429:86 retry_after = int(response.headers.get("Retry-After", 60))87 print(f"⏳ HTTP 429 Rate Limited. Waiting {retry_after}s (Retry-After header)...", flush=True)88 time.sleep(retry_after)89 continue90 91 if response.status_code != 200:92 wait_time = base_delay * (attempt + 1)93 print(f"⚠️ HTTP {response.status_code}. Waiting {wait_time}s...", flush=True)94 time.sleep(wait_time)95 continue96 97 data = response.json()98 99 # Read GraphQL throttle cost info from extensions100 extensions = data.get("extensions", {})101 cost_info = extensions.get("cost", {})102 throttle_status = cost_info.get("throttleStatus", {})103 104 if throttle_status:105 currently_available = throttle_status.get("currentlyAvailable", 0)106 max_available = throttle_status.get("maximumAvailable", 1000)107 restore_rate = throttle_status.get("restoreRate", 50)108 requested_cost = cost_info.get("requestedQueryCost", 0)109 actual_cost = cost_info.get("actualQueryCost", 0)110 print(f" 💰 API Budget: {currently_available}/{max_available} (restore: {restore_rate}/s, query cost: {actual_cost})", flush=True)111 112 if "errors" in data:113 # Check for THROTTLED error114 is_throttled = False115 for error in data["errors"]:116 if error.get("extensions", {}).get("code") == "THROTTLED" or "Rate limited" in error.get("message", ""):117 is_throttled = True118 break119 120 if is_throttled:121 # ShopifyQL has server-side throttle SEPARATE from token bucket.122 # Even with full budget (19999/20000), queries get throttled server-side.123 # Minimum 60s wait is needed between retries.124 wait_time = min(60 * (attempt + 1), 300) # 60s, 120s, 180s... max 300s125 print(f"⏳ Throttled. Waiting {wait_time:.0f}s before retry {attempt+1}/{max_retries}...", flush=True)126 time.sleep(wait_time)127 continue128 129 print(f"❌ GraphQL Error: {json.dumps(data['errors'], indent=2)}", flush=True)130 return pd.DataFrame()131 132 result = data.get("data", {}).get("shopifyqlQuery", {})133 134 if result.get("parseErrors"):135 print(f"❌ ShopifyQL Parse Error: {result['parseErrors']}")136 return pd.DataFrame()137 138 table_data = result.get("tableData")139 if not table_data:140 return pd.DataFrame()141 142 columns_info = table_data.get("columns", [])143 column_names = [col["name"] for col in columns_info]144 rows = table_data.get("rows", [])145 146 df = pd.DataFrame(rows, columns=column_names)147 148 # Convert data types149 for col in columns_info:150 col_name = col["name"]151 dtype = col["dataType"]152 153 if dtype in ["MONEY", "INTEGER", "DECIMAL"]:154 df[col_name] = pd.to_numeric(df[col_name], errors='coerce')155 elif dtype in ["DATE", "DAY_TIMESTAMP"]:156 df[col_name] = pd.to_datetime(df[col_name], errors='coerce')157 158 return df159 160 except Exception as e:161 print(f"❌ Request Error: {e}")162 if attempt < max_retries - 1:163 time.sleep(base_delay)164 continue165 return pd.DataFrame()166 167 print("❌ Max retries exceeded.")168 return pd.DataFrame()169 170# --- Report Functions (From User's Shopify Reports) ---171 172def get_unified_sales_data(start_date, end_date):173 """174 Unified sales query - Combines multiple sales queries into one175 Reduces API calls from 4+ to 1176 Returns all sales metrics in a single DataFrame177 """178 query = f"""179 FROM sales180 SHOW 181 gross_sales,182 net_sales,183 total_sales,184 discounts,185 orders,186 average_order_value,187 quantity_ordered_per_order,188 returning_customers,189 customers,190 returning_customer_rate191 WHERE excludes_post_order_adjustments = true192 TIMESERIES day193 WITH TOTALS, PERCENT_CHANGE194 SINCE {start_date} UNTIL {end_date}195 COMPARE TO previous_period196 ORDER BY day ASC197 LIMIT 1000198 """199 return execute_shopifyql(query)200 201 202def get_total_sales_breakdown(start_date, end_date):203 """Uses unified query for better performance"""204 unified_data = get_unified_sales_data(start_date, end_date)205 if not unified_data.empty:206 return unified_data[['day', 'gross_sales', 'net_sales', 'total_sales', 'discounts', 'orders', 'average_order_value']]207 return unified_data208 209def get_average_order_value_trend(start_date, end_date):210 """Uses unified query for better performance"""211 unified_data = get_unified_sales_data(start_date, end_date)212 if not unified_data.empty:213 return unified_data[['day', 'average_order_value', 'orders', 'gross_sales']]214 return unified_data215 216def get_quantity_per_order_trend(start_date, end_date):217 """Uses unified query for better performance"""218 unified_data = get_unified_sales_data(start_date, end_date)219 if not unified_data.empty:220 return unified_data[['day', 'quantity_ordered_per_order']]221 return unified_data222 223def get_visitors_over_time(start_date, end_date):224 """Zamana göre ziyaretçiler"""225 query = f"""226 FROM sessions227 SHOW online_store_visitors, sessions228 WHERE human_or_bot_session IN ('human', 'bot')229 TIMESERIES day230 WITH TOTALS, PERCENT_CHANGE231 SINCE {start_date} UNTIL {end_date}232 COMPARE TO previous_period233 ORDER BY day ASC234 LIMIT 1000235 """236 return execute_shopifyql(query)237 238def get_marketing_attributed_sales(start_date, end_date):239 """Pazarlamayla ilişkilendirilen satışlar"""240 query = f"""241 FROM sales242 SHOW orders, total_sales, gross_sales, net_sales243 WHERE referring_channel IS NOT NULL244 AND referring_channel != 'direct'245 GROUP BY referring_channel, referring_medium246 WITH TOTALS247 SINCE {start_date} UNTIL {end_date}248 COMPARE TO previous_period249 ORDER BY total_sales DESC250 LIMIT 1000251 """252 return execute_shopifyql(query)253 254def get_sessions_by_device(start_date, end_date):255 """Cihaz türüne göre oturumlar"""256 query = f"""257 FROM sessions258 SHOW online_store_visitors, sessions259 WHERE session_device_type IS NOT NULL260 AND human_or_bot_session IN ('human', 'bot')261 GROUP BY session_device_type262 WITH TOTALS263 SINCE {start_date} UNTIL {end_date}264 ORDER BY sessions DESC265 LIMIT 1000266 """267 return execute_shopifyql(query)268 269def get_current_visitors():270 """Şu anki ziyaretçiler (real-time) - Son 60 dakika"""271 query = """272 FROM sessions273 SHOW online_store_visitors274 WHERE human_or_bot_session IN ('human', 'bot')275 SINCE startOfMinute(-60min) UNTIL now276 LIMIT 100277 """278 return execute_shopifyql(query)279 280def get_conversion_rate_monitoring(date="today"):281 """Conversion rate monitoring (hourly for US)"""282 query = f"""283 FROM sessions284 SHOW sessions, sessions_with_cart_additions, sessions_that_reached_checkout,285 sessions_that_completed_checkout, conversion_rate286 WHERE human_or_bot_session IN ('human')287 AND session_country = 'United States'288 TIMESERIES hour289 WITH TOTALS, CURRENCY 'USD'290 DURING {date}291 ORDER BY hour ASC292 LIMIT 1000293 """294 return execute_shopifyql(query)295 296def get_conversion_rate_over_time(start_date, end_date):297 """Zamana göre dönüşüm oranı - United States, Human sessions only"""298 query = f"""299 FROM sessions300 SHOW sessions, sessions_with_cart_additions, sessions_that_reached_checkout,301 sessions_that_completed_checkout, conversion_rate302 WHERE human_or_bot_session IN ('human')303 AND session_country = 'United States'304 TIMESERIES day305 WITH TOTALS, PERCENT_CHANGE, CURRENCY 'USD'306 SINCE {start_date} UNTIL {end_date}307 COMPARE TO previous_period308 ORDER BY day ASC309 LIMIT 1000310 """311 return execute_shopifyql(query)312 313def get_birthstone_conversion_rate(date="today"):314 """Birthstone conversion rate monitoring"""315 query = f"""316 FROM sessions317 SHOW sessions, sessions_with_cart_additions, sessions_that_reached_checkout,318 sessions_that_completed_checkout, conversion_rate319 WHERE human_or_bot_session IN ('human')320 AND session_country = 'United States'321 AND landing_page_url CONTAINS 'birthstone'322 TIMESERIES hour323 WITH TOTALS, CURRENCY 'USD'324 DURING {date}325 ORDER BY hour ASC326 LIMIT 1000327 """328 return execute_shopifyql(query)329 330def get_returning_customer_rate(start_date, end_date):331 """Uses unified query for better performance"""332 unified_data = get_unified_sales_data(start_date, end_date)333 if not unified_data.empty:334 return unified_data[['day', 'returning_customers', 'customers', 'returning_customer_rate']]335 return unified_data336 337def get_returning_customer_rate_excluding_try_on(months_back=12):338 # Returning Customer Rate (Excluding Home Try On & Exchanges)339 query = f"""340 FROM sales341 SHOW sales, orders, returning_customer_sales, returning_customer_orders342 WHERE line_item_requires_shipping == true343 AND product_id NOT IN (4582606004304, 6639950553168)344 AND total_discounts_set_amount < (total_price_set_amount + discount_applications_value)345 AND transaction_kind IN ('sale')346 SINCE -{months_back}month UNTIL today347 """348 return execute_shopifyql(query)349 350def get_rfm_customer_analysis(start_date=None, end_date=None):351 """352 RFM Müşteri Analizi - Parçalı sorgu yaklaşımı.353 Shopify, customers tablosunda çok alanlı sorguları dahili throttle ile engelliyor.354 Bu yüzden 3 ayrı basit sorgu yapıp birleştiriyoruz.355 """356 import time as _time357 358 base = "FROM customers"359 group_order = "GROUP BY rfm_group ORDER BY new_customer_records DESC LIMIT 20"360 361 # Part 1: Counts and percentages362 q1 = f"{base} SHOW percent_of_customers, new_customer_records {group_order}"363 df1 = execute_shopifyql(q1)364 365 if df1 is None or df1.empty:366 return pd.DataFrame()367 368 _time.sleep(30) # Let Shopify's internal throttle cool down369 370 # Part 2: Order counts371 q2 = f"{base} SHOW new_customer_records, total_number_of_orders {group_order}"372 df2 = execute_shopifyql(q2)373 374 _time.sleep(30)375 376 # Part 3: Amount spent377 q3 = f"{base} SHOW new_customer_records, total_amount_spent {group_order}"378 df3 = execute_shopifyql(q3)379 380 # Merge results381 combined = df1.copy()382 if df2 is not None and not df2.empty and 'total_number_of_orders' in df2.columns:383 combined = combined.merge(df2[['rfm_group', 'total_number_of_orders']], on='rfm_group', how='left')384 if df3 is not None and not df3.empty and 'total_amount_spent' in df3.columns:385 combined = combined.merge(df3[['rfm_group', 'total_amount_spent']], on='rfm_group', how='left')386 387 return combined388 389 390# Backward compatibility aliases391get_sales_report = get_total_sales_breakdown392get_visits_report = get_visitors_over_time393get_top_channels_report = get_marketing_attributed_sales394get_device_report = get_sessions_by_device395get_conversion_report = get_conversion_rate_over_time396 397if __name__ == "__main__":398 print("Testing ShopifyQL with corrected field names...")399 400 # Test device report401 print("\n1. Testing Device Report...")402 df_devices = get_sessions_by_device("-7d", "today")403 if not df_devices.empty:404 print(f"✅ Device Report: {len(df_devices)} rows")405 print(df_devices)406 407 # Test marketing channels408 print("\n2. Testing Marketing Channels...")409 df_channels = get_marketing_attributed_sales("-30d", "today")410 if not df_channels.empty:411 print(f"✅ Marketing Channels: {len(df_channels)} rows")412 print(df_channels.head())413