build_marketing_dashboard_v2.py

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.chart import BarChart, PieChart, LineChart, Reference
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName


def build_marketing_dashboard_excel_v2(filename="advertising_mini_crm_dashboard_v2.xlsx"):
    wb = Workbook()

    # ---------- Styles ----------
    header_fill = PatternFill("solid", fgColor="1F4E78")
    sub_fill = PatternFill("solid", fgColor="D9EAF7")
    green_fill = PatternFill("solid", fgColor="C6EFCE")
    red_fill = PatternFill("solid", fgColor="FFC7CE")
    yellow_fill = PatternFill("solid", fgColor="FFEB9C")
    orange_fill = PatternFill("solid", fgColor="FCE4D6")
    blue_fill = PatternFill("solid", fgColor="EAF2F8")

    thin = Side(style="thin", color="B7B7B7")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    def style_header(ws, row=1):
        for cell in ws[row]:
            cell.font = Font(bold=True, color="FFFFFF")
            cell.fill = header_fill
            cell.border = border
            cell.alignment = Alignment(horizontal="center", vertical="center")

    def style_table(ws):
        for row in ws.iter_rows():
            for cell in row:
                cell.border = border
                if cell.row > 1:
                    cell.alignment = Alignment(vertical="center")

    def auto_width(ws, max_width=28):
        for col_cells in ws.columns:
            max_len = 0
            col_letter = get_column_letter(col_cells[0].column)
            for c in col_cells:
                value = "" if c.value is None else str(c.value)
                max_len = max(max_len, len(value))
            ws.column_dimensions[col_letter].width = min(max_len + 2, max_width)

    def freeze(ws, cell="A2"):
        ws.freeze_panes = cell

    # ---------- Hidden Lists sheet ----------
    lists = wb.active
    lists.title = "_Lists"
    lists.sheet_state = "hidden"

    status_values = ["Planned", "Active", "Paused", "Completed"]
    funnel_values = ["Awareness", "Consideration", "Conversion", "Retention"]
    creative_type_values = ["Banner", "Post", "Story", "Video", "Email", "Landing Page"]
    creative_status_values = ["Draft", "Approved", "Live", "Rejected"]
    channel_values = ["Instagram Ads", "Google Search", "TikTok", "Email", "Influencer", "Outdoor"]
    owner_values = ["Maria", "John", "Eleni", "Nikos"]
    brands = ["CoffeeLab", "FitNow", "PaperHub", "GreenWear", "All"]

    for i, val in enumerate(status_values, start=1):
        lists[f"A{i}"] = val
    for i, val in enumerate(funnel_values, start=1):
        lists[f"B{i}"] = val
    for i, val in enumerate(creative_type_values, start=1):
        lists[f"C{i}"] = val
    for i, val in enumerate(creative_status_values, start=1):
        lists[f"D{i}"] = val
    for i, val in enumerate(channel_values, start=1):
        lists[f"E{i}"] = val
    for i, val in enumerate(owner_values, start=1):
        lists[f"F{i}"] = val
    for i, val in enumerate(brands, start=1):
        lists[f"G{i}"] = val

    wb.defined_names.add(DefinedName("StatusList", attr_text="_Lists!$A$1:$A$4"))
    wb.defined_names.add(DefinedName("FunnelList", attr_text="_Lists!$B$1:$B$4"))
    wb.defined_names.add(DefinedName("CreativeTypeList", attr_text="_Lists!$C$1:$C$6"))
    wb.defined_names.add(DefinedName("CreativeStatusList", attr_text="_Lists!$D$1:$D$4"))
    wb.defined_names.add(DefinedName("ChannelList", attr_text="_Lists!$E$1:$E$6"))
    wb.defined_names.add(DefinedName("OwnerList", attr_text="_Lists!$F$1:$F$4"))
    wb.defined_names.add(DefinedName("BrandFilterList", attr_text="_Lists!$G$1:$G$5"))

    # ---------- Sheet 1: Campaign_Overview ----------
    ws1 = wb.create_sheet("Campaign_Overview")
    ws1.append([
        "Campaign_ID", "Campaign_Name", "Brand", "Objective", "Funnel_Stage",
        "Target_Audience", "Start_Date", "End_Date", "Owner", "Status",
        "Planned_Budget", "Actual_Spend", "Variance"
    ])
    campaigns = [
        [1, "Summer Coffee Launch", "CoffeeLab", "Brand Awareness", "Awareness",
         "18-35 urban coffee drinkers", "2026-05-01", "2026-06-15", "Maria", "Active", 5000, 4700],
        [2, "Fitness Subscription Promo", "FitNow", "Lead Generation", "Conversion",
         "20-40 fitness audience", "2026-04-10", "2026-05-10", "John", "Completed", 3500, 3600],
        [3, "Back to School Stationery", "PaperHub", "Sales Increase", "Conversion",
         "Parents & students", "2026-08-15", "2026-09-15", "Eleni", "Planned", 4500, 0],
        [4, "Eco Clothing Awareness", "GreenWear", "Positioning", "Consideration",
         "Eco-conscious 22-40", "2026-06-01", "2026-07-15", "Nikos", "Paused", 6000, 2200],
    ]
    for row in campaigns:
        ws1.append(row + [f"=K{ws1.max_row+1}-L{ws1.max_row+1}"])
    style_header(ws1)
    style_table(ws1)
    freeze(ws1)

    dv_status = DataValidation(type="list", formula1="=StatusList")
    dv_funnel = DataValidation(type="list", formula1="=FunnelList")
    dv_owner = DataValidation(type="list", formula1="=OwnerList")
    ws1.add_data_validation(dv_status)
    ws1.add_data_validation(dv_funnel)
    ws1.add_data_validation(dv_owner)
    dv_status.add("J2:J200")
    dv_funnel.add("E2:E200")
    dv_owner.add("I2:I200")

    ws1.conditional_formatting.add("M2:M200", CellIsRule(operator="lessThan", formula=["0"], fill=red_fill))
    ws1.conditional_formatting.add("M2:M200", CellIsRule(operator="greaterThanOrEqual", formula=["0"], fill=green_fill))
    auto_width(ws1)

    # ---------- Sheet 2: Audience_Personas ----------
    ws2 = wb.create_sheet("Audience_Personas")
    ws2.append([
        "Persona_ID", "Persona_Name", "Age_Range", "Gender", "Interests",
        "Needs", "Pain_Points", "Preferred_Channel", "Buying_Motivation"
    ])
    personas = [
        [1, "Maria", "22-30", "F", "Coffee, productivity, social media", "Energy, convenience",
         "Busy mornings", "Instagram Ads", "Feel productive and stylish"],
        [2, "John", "28-40", "M", "Fitness, apps, nutrition", "Results, efficiency",
         "Lack of time", "Google Search", "Improve health"],
        [3, "Anna", "30-45", "F", "School supplies, family budgeting", "Value, reliability",
         "Back-to-school stress", "Email", "Save money and time"],
        [4, "Kostas", "24-38", "M", "Sustainability, fashion", "Identity, ethics",
         "Fast fashion guilt", "Influencer", "Buy values, not just clothes"],
    ]
    for row in personas:
        ws2.append(row)
    style_header(ws2)
    style_table(ws2)
    freeze(ws2)
    auto_width(ws2)

    dv_channel = DataValidation(type="list", formula1="=ChannelList")
    ws2.add_data_validation(dv_channel)
    dv_channel.add("H2:H200")

    # ---------- Sheet 3: Media_Plan ----------
    ws3 = wb.create_sheet("Media_Plan")
    ws3.append([
        "Campaign_ID", "Channel", "Format", "Objective", "Start_Date", "End_Date",
        "Planned_Budget", "Reach", "Impressions", "Clicks", "CTR", "CPC",
        "Conversions", "CPA", "CTR_Benchmark", "CPA_Target", "Alert"
    ])
    media_rows = [
        [1, "Instagram Ads", "Carousel", "Awareness", "2026-05-01", "2026-06-15", 2200, 18000, 42000, 2100, None, None, 180, None, 0.04, 12],
        [1, "Influencer", "Video", "Awareness", "2026-05-10", "2026-06-10", 1800, 12000, 25000, 950, None, None, 120, None, 0.03, 15],
        [1, "Outdoor", "Billboard", "Awareness", "2026-05-01", "2026-05-31", 1000, 30000, 30000, 300, None, None, 20, None, 0.01, 20],
        [2, "Google Search", "Search Ad", "Conversion", "2026-04-10", "2026-05-10", 2000, 15000, 32000, 2600, None, None, 310, None, 0.05, 10],
        [2, "Email", "Newsletter", "Conversion", "2026-04-15", "2026-05-10", 600, 8000, 12000, 900, None, None, 150, None, 0.05, 8],
        [2, "TikTok", "Short Video", "Conversion", "2026-04-12", "2026-05-05", 900, 20000, 50000, 1700, None, None, 140, None, 0.03, 10],
        [3, "Email", "Newsletter", "Sales", "2026-08-15", "2026-09-15", 1500, 10000, 16000, 1200, None, None, 190, None, 0.05, 8],
        [3, "Instagram Ads", "Story", "Sales", "2026-08-20", "2026-09-10", 1800, 14000, 30000, 1800, None, None, 220, None, 0.04, 10],
        [3, "Google Search", "Search Ad", "Sales", "2026-08-15", "2026-09-15", 1200, 11000, 18000, 1400, None, None, 200, None, 0.05, 8],
        [4, "Influencer", "Reel", "Consideration", "2026-06-01", "2026-07-15", 2500, 20000, 40000, 1600, None, None, 130, None, 0.03, 15],
        [4, "Instagram Ads", "Post", "Consideration", "2026-06-01", "2026-07-10", 2200, 17000, 35000, 1500, None, None, 110, None, 0.04, 14],
        [4, "Outdoor", "Poster", "Consideration", "2026-06-10", "2026-06-30", 1300, 25000, 25000, 250, None, None, 18, None, 0.01, 20],
    ]
    for row in media_rows:
        ws3.append(row + [None])

    for r in range(2, ws3.max_row + 1):
        ws3[f"K{r}"] = f"=IF(I{r}=0,0,J{r}/I{r})"
        ws3[f"L{r}"] = f"=IF(J{r}=0,0,G{r}/J{r})"
        ws3[f"N{r}"] = f"=IF(M{r}=0,0,G{r}/M{r})"
        ws3[f"Q{r}"] = (
            f'=IF(AND(K{r}<O{r},N{r}>P{r}),"Low CTR + High CPA",'
            f'IF(K{r}<O{r},"Low CTR",IF(N{r}>P{r},"High CPA","OK")))'
        )

    style_header(ws3)
    style_table(ws3)
    freeze(ws3)
    auto_width(ws3)

    dv_channel2 = DataValidation(type="list", formula1="=ChannelList")
    ws3.add_data_validation(dv_channel2)
    dv_channel2.add("B2:B500")

    for r in range(2, ws3.max_row + 1):
        ws3[f"K{r}"].number_format = "0.00%"
        ws3[f"O{r}"].number_format = "0.00%"
        ws3[f"L{r}"].number_format = "0.00"
        ws3[f"N{r}"].number_format = "0.00"
        ws3[f"P{r}"].number_format = "0.00"

    ws3.conditional_formatting.add("Q2:Q500", FormulaRule(formula=['$Q2="Low CTR"'], fill=yellow_fill))
    ws3.conditional_formatting.add("Q2:Q500", FormulaRule(formula=['$Q2="High CPA"'], fill=orange_fill))
    ws3.conditional_formatting.add("Q2:Q500", FormulaRule(formula=['$Q2="Low CTR + High CPA"'], fill=red_fill))
    ws3.conditional_formatting.add("Q2:Q500", FormulaRule(formula=['$Q2="OK"'], fill=green_fill))

    # ---------- Sheet 4: Creatives ----------
    ws4 = wb.create_sheet("Creatives")
    ws4.append([
        "Creative_ID", "Campaign_ID", "Type", "Headline", "Message", "Slogan",
        "CTA", "USP", "Status", "Approval_Date"
    ])
    creative_rows = [
        [1, 1, "Post", "Start Your Day Strong", "Coffee that powers your morning", "Energy in every sip", "Order Now", "Fast premium coffee", "Live", "2026-04-28"],
        [2, 1, "Video", "Morning Routine Upgrade", "Fuel your city pace", "Stay in the flow", "Try Today", "Coffee for busy lives", "Approved", "2026-04-27"],
        [3, 2, "Banner", "Train Smarter", "Join the app that fits your schedule", "Results on your time", "Start Free Trial", "Flexible fitness plans", "Live", "2026-04-08"],
        [4, 3, "Email", "Back to School Savings", "Smart stationery packs for families", "Everything ready", "Shop the bundle", "Affordable school bundles", "Draft", ""],
        [5, 4, "Story", "Wear What You Believe", "Sustainable fashion for real people", "Style with purpose", "Explore Collection", "Eco clothing with style", "Approved", "2026-05-25"],
    ]
    for row in creative_rows:
        ws4.append(row)
    style_header(ws4)
    style_table(ws4)
    freeze(ws4)
    auto_width(ws4)

    dv_ctype = DataValidation(type="list", formula1="=CreativeTypeList")
    dv_cstatus = DataValidation(type="list", formula1="=CreativeStatusList")
    ws4.add_data_validation(dv_ctype)
    ws4.add_data_validation(dv_cstatus)
    dv_ctype.add("C2:C500")
    dv_cstatus.add("I2:I500")

    # ---------- Sheet 5: Budget_Tracking ----------
    ws5 = wb.create_sheet("Budget_Tracking")
    ws5.append([
        "Campaign_ID", "Channel", "Planned", "Actual", "Difference", "Difference_%", "Notes", "Budget_Alert"
    ])
    budget_rows = [
        [1, "Instagram Ads", 2200, 2100, None, None, "Under budget"],
        [1, "Influencer", 1800, 1700, None, None, "Negotiated lower fee"],
        [1, "Outdoor", 1000, 900, None, None, "Reduced printing"],
        [2, "Google Search", 2000, 2200, None, None, "Higher click costs"],
        [2, "Email", 600, 500, None, None, "Automation discount"],
        [2, "TikTok", 900, 900, None, None, "On plan"],
        [3, "Email", 1500, 0, None, None, "Not launched yet"],
        [3, "Instagram Ads", 1800, 0, None, None, "Not launched yet"],
        [3, "Google Search", 1200, 0, None, None, "Not launched yet"],
        [4, "Influencer", 2500, 1100, None, None, "Paused campaign"],
        [4, "Instagram Ads", 2200, 100, None, None, "Paused campaign"],
        [4, "Outdoor", 1300, 0, None, None, "Paused campaign"],
    ]
    for row in budget_rows:
        ws5.append(row + [None])

    for r in range(2, ws5.max_row + 1):
        ws5[f"E{r}"] = f"=C{r}-D{r}"
        ws5[f"F{r}"] = f"=IF(C{r}=0,0,E{r}/C{r})"
        ws5[f"H{r}"] = f'=IF(E{r}<0,"Overspend",IF(E{r}=0,"On Budget","Under Budget"))'

    style_header(ws5)
    style_table(ws5)
    freeze(ws5)
    auto_width(ws5)
    for r in range(2, ws5.max_row + 1):
        ws5[f"F{r}"].number_format = "0.00%"

    ws5.conditional_formatting.add("E2:E500", CellIsRule(operator="lessThan", formula=["0"], fill=red_fill))
    ws5.conditional_formatting.add("E2:E500", CellIsRule(operator="greaterThanOrEqual", formula=["0"], fill=green_fill))
    ws5.conditional_formatting.add("H2:H500", FormulaRule(formula=['$H2="Overspend"'], fill=red_fill))
    ws5.conditional_formatting.add("H2:H500", FormulaRule(formula=['$H2="Under Budget"'], fill=green_fill))
    ws5.conditional_formatting.add("H2:H500", FormulaRule(formula=['$H2="On Budget"'], fill=yellow_fill))

    # ---------- Sheet 6: KPI_Raw_Data ----------
    ws6 = wb.create_sheet("KPI_Raw_Data")
    ws6.append([
        "Date", "Campaign_ID", "Channel", "Impressions", "Reach", "Clicks",
        "Conversions", "Revenue", "Spend", "Engagements", "Video_Views"
    ])
    raw_rows = [
        ["2026-05-05", 1, "Instagram Ads", 12000, 7000, 620, 55, 1650, 700, 950, 0],
        ["2026-05-20", 1, "Instagram Ads", 15000, 8500, 780, 70, 2100, 800, 1200, 0],
        ["2026-05-15", 1, "Influencer", 25000, 12000, 950, 120, 3600, 1700, 2000, 18000],
        ["2026-05-25", 1, "Outdoor", 5000, 4500, 120, 10, 300, 900, 100, 0],
        ["2026-04-20", 2, "Google Search", 18000, 11000, 1450, 170, 5100, 1200, 0, 0],
        ["2026-05-02", 2, "Google Search", 14000, 9000, 1150, 140, 4200, 1000, 0, 0],
        ["2026-04-25", 2, "Email", 12000, 8000, 900, 150, 3000, 500, 600, 0],
        ["2026-04-28", 2, "TikTok", 50000, 20000, 1700, 140, 2800, 900, 1900, 28000],
        ["2026-06-10", 4, "Influencer", 40000, 20000, 1600, 130, 5200, 1100, 3000, 25000],
        ["2026-06-18", 4, "Instagram Ads", 35000, 17000, 1500, 110, 3300, 100, 2400, 0],
    ]
    for row in raw_rows:
        ws6.append(row)
    style_header(ws6)
    style_table(ws6)
    freeze(ws6)
    auto_width(ws6)

    # ---------- Sheet 7: KPI_Calculations ----------
    ws7 = wb.create_sheet("KPI_Calculations")
    ws7.append([
        "Campaign_ID", "Campaign_Name", "Brand", "Total_Impressions", "Total_Clicks", "Total_Conversions",
        "Total_Spend", "Total_Revenue", "CTR", "Conversion_Rate", "CPC", "CPA",
        "ROI", "ROAS", "Engagement_Rate", "CTR_Target", "CPA_Target", "ROI_Target", "Alerts"
    ])
    targets = {
        1: {"ctr": 0.035, "cpa": 15, "roi": 0.20},
        2: {"ctr": 0.050, "cpa": 10, "roi": 0.25},
        3: {"ctr": 0.040, "cpa": 10, "roi": 0.20},
        4: {"ctr": 0.030, "cpa": 16, "roi": 0.15},
    }
    for cid in [1, 2, 3, 4]:
        ws7.append([cid, None, None, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, targets[cid]["ctr"], targets[cid]["cpa"], targets[cid]["roi"], None])

    for r in range(2, ws7.max_row + 1):
        ws7[f"B{r}"] = f'=INDEX(Campaign_Overview!B:B,MATCH(A{r},Campaign_Overview!A:A,0))'
        ws7[f"C{r}"] = f'=INDEX(Campaign_Overview!C:C,MATCH(A{r},Campaign_Overview!A:A,0))'
        ws7[f"D{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!D:D)'
        ws7[f"E{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!F:F)'
        ws7[f"F{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!G:G)'
        ws7[f"G{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!I:I)'
        ws7[f"H{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!H:H)'
        ws7[f"I{r}"] = f'=IF(D{r}=0,0,E{r}/D{r})'
        ws7[f"J{r}"] = f'=IF(E{r}=0,0,F{r}/E{r})'
        ws7[f"K{r}"] = f'=IF(E{r}=0,0,G{r}/E{r})'
        ws7[f"L{r}"] = f'=IF(F{r}=0,0,G{r}/F{r})'
        ws7[f"M{r}"] = f'=IF(G{r}=0,0,(H{r}-G{r})/G{r})'
        ws7[f"N{r}"] = f'=IF(G{r}=0,0,H{r}/G{r})'
        ws7[f"O{r}"] = f'=IF(SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!E:E)=0,0,SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!J:J)/SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!E:E))'
        ws7[f"S{r}"] = (
            f'=IF(AND(I{r}<P{r},L{r}>Q{r},M{r}<R{r}),"Low CTR + High CPA + Low ROI",'
            f'IF(I{r}<P{r},"Low CTR",IF(L{r}>Q{r},"High CPA",IF(M{r}<R{r},"Low ROI","OK"))))'
        )

    style_header(ws7)
    style_table(ws7)
    freeze(ws7)
    auto_width(ws7)
    for r in range(2, ws7.max_row + 1):
        for col in ["I", "J", "M", "O", "P", "R"]:
            ws7[f"{col}{r}"].number_format = "0.00%"
        for col in ["K", "L", "N", "Q"]:
            ws7[f"{col}{r}"].number_format = "0.00"

    ws7.conditional_formatting.add("S2:S100", FormulaRule(formula=['$S2="Low CTR"'], fill=yellow_fill))
    ws7.conditional_formatting.add("S2:S100", FormulaRule(formula=['$S2="High CPA"'], fill=orange_fill))
    ws7.conditional_formatting.add("S2:S100", FormulaRule(formula=['$S2="Low ROI"'], fill=yellow_fill))
    ws7.conditional_formatting.add("S2:S100", FormulaRule(formula=['$S2="Low CTR + High CPA + Low ROI"'], fill=red_fill))
    ws7.conditional_formatting.add("S2:S100", FormulaRule(formula=['$S2="OK"'], fill=green_fill))

    # ---------- Sheet 8: Evaluation ----------
    ws8 = wb.create_sheet("Evaluation")
    ws8.append([
        "Campaign_ID", "Goal_Achieved", "KPI_Result", "Insights",
        "What_Worked", "What_Did_Not_Work", "Recommendation", "Next_Action"
    ])
    eval_rows = [
        [1, "Yes", "Strong CTR and solid ROAS", "Awareness + conversions performed well", "Influencer + Instagram synergy", "Outdoor weak on conversions", "Increase creator content", "Test retargeting"],
        [2, "Yes", "Best conversion efficiency", "Search captured high intent", "Google Ads", "TikTok lower efficiency", "Increase search budget", "Refine TikTok creative"],
        [3, "Pending", "Not launched", "No data yet", "N/A", "N/A", "Launch test batch", "Monitor CTR weekly"],
        [4, "Partial", "Good reach, moderate ROI", "Strong engagement but paused early", "Influencer engagement", "Pause reduced momentum", "Restart with tighter audience", "Rebuild funnel"],
    ]
    for row in eval_rows:
        ws8.append(row)
    style_header(ws8)
    style_table(ws8)
    freeze(ws8)
    auto_width(ws8, 36)

    # ---------- Sheet 9: Executive_Dashboard ----------
    dash = wb.create_sheet("Executive_Dashboard")

    # Title
    dash["A1"] = "ADVANCED MARKETING DASHBOARD v2"
    dash["A1"].font = Font(size=16, bold=True)
    dash["A1"].fill = blue_fill

    # Slicer-like filters
    dash["A3"] = "Dashboard Filters"
    dash["A3"].font = Font(bold=True, color="FFFFFF")
    dash["A3"].fill = header_fill

    filter_labels = ["Brand", "Status", "Funnel Stage", "Owner", "Channel"]
    for idx, label in enumerate(filter_labels, start=4):
        dash[f"A{idx}"] = label
        dash[f"A{idx}"].fill = sub_fill
        dash[f"A{idx}"].border = border
        dash[f"B{idx}"] = "All"
        dash[f"B{idx}"].border = border

    # add hidden lists "All" prefixed for filter use
    filter_lists = wb["_Lists"]
    all_status = ["All"] + status_values
    all_funnel = ["All"] + funnel_values
    all_owner = ["All"] + owner_values
    all_channel = ["All"] + channel_values

    for i, val in enumerate(all_status, start=1):
        filter_lists[f"H{i}"] = val
    for i, val in enumerate(all_funnel, start=1):
        filter_lists[f"I{i}"] = val
    for i, val in enumerate(all_owner, start=1):
        filter_lists[f"J{i}"] = val
    for i, val in enumerate(all_channel, start=1):
        filter_lists[f"K{i}"] = val

    wb.defined_names.add(DefinedName("AllStatusList", attr_text="_Lists!$H$1:$H$5"))
    wb.defined_names.add(DefinedName("AllFunnelList", attr_text="_Lists!$I$1:$I$5"))
    wb.defined_names.add(DefinedName("AllOwnerList", attr_text="_Lists!$J$1:$J$5"))
    wb.defined_names.add(DefinedName("AllChannelList", attr_text="_Lists!$K$1:$K$7"))

    dv_brand = DataValidation(type="list", formula1="=BrandFilterList")
    dv_status_filter = DataValidation(type="list", formula1="=AllStatusList")
    dv_funnel_filter = DataValidation(type="list", formula1="=AllFunnelList")
    dv_owner_filter = DataValidation(type="list", formula1="=AllOwnerList")
    dv_channel_filter = DataValidation(type="list", formula1="=AllChannelList")
    dash.add_data_validation(dv_brand)
    dash.add_data_validation(dv_status_filter)
    dash.add_data_validation(dv_funnel_filter)
    dash.add_data_validation(dv_owner_filter)
    dash.add_data_validation(dv_channel_filter)
    dv_brand.add("B4")
    dv_status_filter.add("B5")
    dv_funnel_filter.add("B6")
    dv_owner_filter.add("B7")
    dv_channel_filter.add("B8")

    # KPI summary section
    dash["D3"] = "KPI"
    dash["E3"] = "Value"
    dash["G3"] = "Benchmarks / Alerts"
    dash["H3"] = "Value"
    for cell in ["D3", "E3", "G3", "H3"]:
        dash[cell].font = Font(bold=True, color="FFFFFF")
        dash[cell].fill = header_fill
        dash[cell].border = border
        dash[cell].alignment = Alignment(horizontal="center")

    # Filter-aware formulas using SUMPRODUCT
    kpis = [
        ("Filtered Campaigns", '=SUMPRODUCT((IF($B$4="All",1,--(Campaign_Overview!C2:C200=$B$4)))*(IF($B$5="All",1,--(Campaign_Overview!J2:J200=$B$5)))*(IF($B$6="All",1,--(Campaign_Overview!E2:E200=$B$6)))*(IF($B$7="All",1,--(Campaign_Overview!I2:I200=$B$7))))'),
        ("Total Budget", '=SUMPRODUCT(Campaign_Overview!K2:K200*(IF($B$4="All",1,--(Campaign_Overview!C2:C200=$B$4)))*(IF($B$5="All",1,--(Campaign_Overview!J2:J200=$B$5)))*(IF($B$6="All",1,--(Campaign_Overview!E2:E200=$B$6)))*(IF($B$7="All",1,--(Campaign_Overview!I2:I200=$B$7))))'),
        ("Total Spend", '=SUMPRODUCT(Campaign_Overview!L2:L200*(IF($B$4="All",1,--(Campaign_Overview!C2:C200=$B$4)))*(IF($B$5="All",1,--(Campaign_Overview!J2:J200=$B$5)))*(IF($B$6="All",1,--(Campaign_Overview!E2:E200=$B$6)))*(IF($B$7="All",1,--(Campaign_Overview!I2:I200=$B$7))))'),
        ("Total Revenue", '=SUMPRODUCT(KPI_Calculations!H2:H100*(IF($B$4="All",1,--(KPI_Calculations!C2:C100=$B$4))))'),
        ("Total Conversions", '=SUMPRODUCT(KPI_Calculations!F2:F100*(IF($B$4="All",1,--(KPI_Calculations!C2:C100=$B$4))))'),
        ("Avg CTR", '=AVERAGE(KPI_Calculations!I2:I5)'),
        ("Avg CPA", '=AVERAGE(KPI_Calculations!L2:L5)'),
        ("Avg ROI", '=AVERAGE(KPI_Calculations!M2:M5)'),
        ("Avg ROAS", '=AVERAGE(KPI_Calculations!N2:N5)'),
    ]
    start_row = 4
    for i, (label, formula) in enumerate(kpis, start=start_row):
        dash[f"D{i}"] = label
        dash[f"E{i}"] = formula
        dash[f"D{i}"].fill = sub_fill
        dash[f"D{i}"].border = border
        dash[f"E{i}"].border = border

    dash["G4"] = "Low CTR Campaigns"
    dash["H4"] = '=COUNTIF(KPI_Calculations!S2:S5,"Low CTR")'
    dash["G5"] = "High CPA Campaigns"
    dash["H5"] = '=COUNTIF(KPI_Calculations!S2:S5,"High CPA")'
    dash["G6"] = "Low ROI Campaigns"
    dash["H6"] = '=COUNTIF(KPI_Calculations!S2:S5,"Low ROI")'
    dash["G7"] = "Severe Alerts"
    dash["H7"] = '=COUNTIF(KPI_Calculations!S2:S5,"Low CTR + High CPA + Low ROI")'
    dash["G8"] = "Best ROI Campaign"
    dash["H8"] = '=INDEX(KPI_Calculations!B2:B5,MATCH(MAX(KPI_Calculations!M2:M5),KPI_Calculations!M2:M5,0))'
    dash["G9"] = "Best CTR Campaign"
    dash["H9"] = '=INDEX(KPI_Calculations!B2:B5,MATCH(MAX(KPI_Calculations!I2:I5),KPI_Calculations!I2:I5,0))'

    for r in range(4, 10):
        dash[f"G{r}"].fill = sub_fill
        dash[f"G{r}"].border = border
        dash[f"H{r}"].border = border

    dash["E9"].number_format = "0.00"
    dash["E10"].number_format = "0.00%"
    dash["E11"].number_format = "0.00"
    dash["E12"].number_format = "0.00%"
    dash["E13"].number_format = "0.00"

    # Alert panel
    dash["A11"] = "Automatic Alerts"
    dash["A11"].font = Font(bold=True, color="FFFFFF")
    dash["A11"].fill = header_fill
    dash["A12"] = "CTR below benchmark"
    dash["B12"] = '=COUNTIF(Media_Plan!Q2:Q500,"Low CTR")'
    dash["A13"] = "CPA above target"
    dash["B13"] = '=COUNTIF(Media_Plan!Q2:Q500,"High CPA")'
    dash["A14"] = "Low CTR + High CPA"
    dash["B14"] = '=COUNTIF(Media_Plan!Q2:Q500,"Low CTR + High CPA")'
    for r in range(12, 15):
        dash[f"A{r}"].fill = sub_fill
        dash[f"A{r}"].border = border
        dash[f"B{r}"].border = border

    dash.conditional_formatting.add("B12:B14", CellIsRule(operator="greaterThan", formula=["0"], fill=red_fill))

    # Chart 1: Spend vs Revenue by Campaign
    chart1 = BarChart()
    chart1.type = "bar"
    chart1.style = 10
    chart1.title = "Spend vs Revenue by Campaign"
    chart1.y_axis.title = "Campaign"
    chart1.x_axis.title = "Amount"
    data = Reference(ws7, min_col=7, max_col=8, min_row=1, max_row=5)
    cats = Reference(ws7, min_col=2, min_row=2, max_row=5)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.height = 7
    chart1.width = 12
    dash.add_chart(chart1, "J2")

    # Chart 2: CTR by Campaign
    chart2 = BarChart()
    chart2.title = "CTR by Campaign"
    data = Reference(ws7, min_col=9, max_col=9, min_row=1, max_row=5)
    cats = Reference(ws7, min_col=2, min_row=2, max_row=5)
    chart2.add_data(data, titles_from_data=True)
    chart2.set_categories(cats)
    chart2.height = 7
    chart2.width = 12
    dash.add_chart(chart2, "J18")

    # Chart 3: Budget Variance by Channel
    chart3 = BarChart()
    chart3.title = "Budget Variance by Channel"
    data = Reference(ws5, min_col=5, max_col=5, min_row=1, max_row=13)
    cats = Reference(ws5, min_col=2, min_row=2, max_row=13)
    chart3.add_data(data, titles_from_data=True)
    chart3.set_categories(cats)
    chart3.height = 7
    chart3.width = 12
    dash.add_chart(chart3, "W2")

    # Chart 4: Conversion share
    chart4 = PieChart()
    chart4.title = "Conversions Share"
    data = Reference(ws7, min_col=6, min_row=1, max_row=5)
    labels = Reference(ws7, min_col=2, min_row=2, max_row=5)
    chart4.add_data(data, titles_from_data=True)
    chart4.set_categories(labels)
    chart4.height = 7
    chart4.width = 10
    dash.add_chart(chart4, "W18")

    # Chart 5: Trend line using raw spend/revenue
    trend = LineChart()
    trend.title = "Revenue Trend (Raw Data Order)"
    trend.y_axis.title = "Revenue"
    data = Reference(ws6, min_col=8, max_col=8, min_row=1, max_row=ws6.max_row)
    cats = Reference(ws6, min_col=1, min_row=2, max_row=ws6.max_row)
    trend.add_data(data, titles_from_data=True)
    trend.set_categories(cats)
    trend.height = 7
    trend.width = 12
    dash.add_chart(trend, "J34")

    for col in range(1, 40):
        dash.column_dimensions[get_column_letter(col)].width = 16

    freeze(dash, "A4")

    # ---------- Remove default sheet ----------
    if "Sheet" in wb.sheetnames:
        del wb["Sheet"]

    # ---------- Save ----------
    wb.save(filename)
    print(f"Workbook created: {filename}")


if __name__ == "__main__":
    build_marketing_dashboard_excel_v2()