HKPC 2026 AI Workshop Resources

How many traffic and users does Facebook and it's related channel has brought us in May?

I have uploaded three datasets. One is my Facebook post performance, and the other two is my website traffic from GA4. Please merge these 3 datasets. Use the 'Source' and 'Campaign Name' (or whatever they used for their UTMs) in the GA4 file and match it with the corresponding identifier in the Facebook file. Which specific Facebook post drove the highest number of website sessions?

Generate bar chart top 10 FB posts by estimated GA sessions, using Post Title instead of Post ID. Cleaning: first line without URLs/hashtags, strip all emojis/variation selectors/ZWJ, keep only Traditional Chinese + English + numbers. For Y-axis use 20-char short title + PostID last 4 digits (avoid duplicate "母親節限定" titles). For hover show full Traditional Chinese title. Use Plotly, set font to Noto Sans TC / Microsoft JhengHei, HTML meta UTF-8. Save CSV utf-8-sig.

[CONTEXT]
You are an expert retail data analyst advising the executive team of a 3-store Hong Kong bakery chain ("Central Flagship", "Tsim Sha Tsui Ocean", "Mong Kok Broadway"). 
You are provided with 5 relational CSV tables covering 26 ISO weeks of POS transaction logs:

sales_transactions_final.csv (Transaction line items with timestamps)
customer_master.csv (App registered member demographics & tiers)
product_master.csv (SKU prices, costs, and designated substitute items)
store_master.csv (Store locations, district profiles, and price sensitivities)
inventory_oos_log.csv (Out-of-stock event logs with start/end times)

[OBJECTIVE]
Perform an end-to-end retail performance analysis on the attached datasets. Analyze sales performance, profitability, product drivers across districts, hourly basket co-purchasing patterns, demographic preferences, and substitution impacts during stockout (OOS) events. Finally, evaluate time-series forecasting accuracy by holding out the last 4 ISO weeks before predicting total unit demand for the next 4 weeks.

[DELIVERABLE]
Provide a structured executive briefing containing:

Data Quality & Schema Summary: Total row counts, date ranges, null checks, and duplicate validation.
Weekly Revenue & Gross Profit Trend: ISO week-level summary table and visualization description.
Ranked Product Profitability Table: Products ranked by total Gross Profit (HKD), Revenue, Volume, and Margin %.
Pattern Analysis Insights: Peak hour cross-sell patterns (basket correlations), district price sensitivity, and OOS demand displacement.
4-Week Holdout Forecast & Validation: Model error metrics (MAE/MAPE) on the holdout period, followed by refitted weekly total unit forecasts for the next 4 weeks with confidence intervals.
5 Evidence-Based Insights: Data-backed findings on store, product, time, and promotional drivers.
3 Strategic Actions for Next Week: Clear, high-impact operational recommendations.

[APPROACH]

Schema & Validation: Validate primary/foreign keys across tables (store_id, product_id, customer_id). Clean duplicates and handle GUEST transactions.
Calculation: Compute Gross Profit at line level as Gross Profit = revenue_hkd - total_cost_hkd. Calculate Gross Margin % as (Gross Profit / revenue_hkd) * 100.
Aggregation: Aggregate transactional timestamps into ISO calendar weeks and hourly time slots (Morning: 07-09, Afternoon: 14-16, Evening: 17-19, Off-peak).
OOS & Basket Correlation: Cross-reference stockout timestamps with transaction logs to measure sales lift in substitute SKUs. Identify items frequently bought together in the same transaction_id.
Promotional Analysis: Compare promotion vs. non-promotion periods by store district, treating lift as association rather than direct causation.
Time-Series Holdout & Forecast: Reserve the last 4 ISO weeks. Fit a forecasting baseline, calculate MAPE on the holdout period, then refit using all 26 weeks to forecast unit demand for the upcoming 4 weeks.

[DO & DON'TS]

DO express all monetary values strictly in Hong Kong Dollars (HKD).
DO label model uncertainty and provide upper/lower confidence bounds for forecasts.
DO state all explicit assumptions, equations, and record counts used in aggregations.
DON'T confuse correlation with causation, especially regarding promotional discounts or weather/seasonal patterns.
DON'T expose row-level customer PII or unaggregated individual transaction records.
DON'T invent missing facts or assume variables outside the provided 5 master CSV files.