build_marketing_dashboard.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
from openpyxl.chart import BarChart, PieChart, Reference
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName


def build_marketing_dashboard_excel(filename="advertising_mini_crm_dashboard.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")
    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):
        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, 28)

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

    # ---------- Hidden Lists sheet for dropdowns ----------
    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"]

    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

    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"))

    # ---------- 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, 4200],
        [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, 1200],
    ]
    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")
    ws1.add_data_validation(dv_status)
    ws1.add_data_validation(dv_funnel)
    dv_status.add("J2:J200")
    dv_funnel.add("E2:E200")

    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"
    ])
    media_rows = [
        [1, "Instagram Ads", "Carousel", "Awareness", "2026-05-01", "2026-06-15", 2200, 18000, 42000, 2100, None, None, 180, None],
        [1, "Influencer", "Video", "Awareness", "2026-05-10", "2026-06-10", 1800, 12000, 25000, 950, None, None, 120, None],
        [1, "Outdoor", "Billboard", "Awareness", "2026-05-01", "2026-05-31", 1000, 30000, 30000, 300, None, None, 20, None],
        [2, "Google Search", "Search Ad", "Conversion", "2026-04-10", "2026-05-10", 2000, 15000, 32000, 2600, None, None, 310, None],
        [2, "Email", "Newsletter", "Conversion", "2026-04-15", "2026-05-10", 600, 8000, 12000, 900, None, None, 150, None],
        [2, "TikTok", "Short Video", "Conversion", "2026-04-12", "2026-05-05", 900, 20000, 50000, 1700, None, None, 140, None],
        [3, "Email", "Newsletter", "Sales", "2026-08-15", "2026-09-15", 1500, 10000, 16000, 1200, None, None, 190, None],
        [3, "Instagram Ads", "Story", "Sales", "2026-08-20", "2026-09-10", 1800, 14000, 30000, 1800, None, None, 220, None],
        [3, "Google Search", "Search Ad", "Sales", "2026-08-15", "2026-09-15", 1200, 11000, 18000, 1400, None, None, 200, None],
        [4, "Influencer", "Reel", "Consideration", "2026-06-01", "2026-07-15", 2500, 20000, 40000, 1600, None, None, 130, None],
        [4, "Instagram Ads", "Post", "Consideration", "2026-06-01", "2026-07-10", 2200, 17000, 35000, 1500, None, None, 110, None],
        [4, "Outdoor", "Poster", "Consideration", "2026-06-10", "2026-06-30", 1300, 25000, 25000, 250, None, None, 18, None],
    ]
    for row in media_rows:
        ws3.append(row)
    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})"

    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 col in ["K", "L", "N"]:
        for r in range(2, ws3.max_row + 1):
            ws3[f"{col}{r}"].number_format = "0.00"
    for r in range(2, ws3.max_row + 1):
        ws3[f"K{r}"].number_format = "0.00%"

    # ---------- 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_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)
    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})"

    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))

    # ---------- 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-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-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", "Total_Impressions", "Total_Clicks", "Total_Conversions",
        "Total_Spend", "Total_Revenue", "CTR", "Conversion_Rate", "CPC", "CPA",
        "ROI", "ROAS", "Engagement_Rate"
    ])
    for r in range(2, 6):
        cid = r - 1
        ws7.append([cid, f"=INDEX(Campaign_Overview!B:B,MATCH(A{r},Campaign_Overview!A:A,0))", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0])
        ws7[f"C{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!D:D)'
        ws7[f"D{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!F:F)'
        ws7[f"E{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!G:G)'
        ws7[f"F{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!I:I)'
        ws7[f"G{r}"] = f'=SUMIF(KPI_Raw_Data!B:B,A{r},KPI_Raw_Data!H:H)'
        ws7[f"H{r}"] = f'=IF(C{r}=0,0,D{r}/C{r})'
        ws7[f"I{r}"] = f'=IF(D{r}=0,0,E{r}/D{r})'
        ws7[f"J{r}"] = f'=IF(D{r}=0,0,F{r}/D{r})'
        ws7[f"K{r}"] = f'=IF(E{r}=0,0,F{r}/E{r})'
        ws7[f"L{r}"] = f'=IF(F{r}=0,0,(G{r}-F{r})/F{r})'
        ws7[f"M{r}"] = f'=IF(F{r}=0,0,G{r}/F{r})'
        # engagements / reach
        ws7[f"N{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))'

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

    ws7.conditional_formatting.add("L2:L100", CellIsRule(operator="lessThan", formula=["0"], fill=red_fill))
    ws7.conditional_formatting.add("L2:L100", CellIsRule(operator="between", formula=["0", "0.2"], fill=yellow_fill))
    ws7.conditional_formatting.add("L2:L100", CellIsRule(operator="greaterThan", formula=["0.2"], 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 CPA 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)

    # ---------- Sheet 9: Executive_Dashboard ----------
    dash = wb.create_sheet("Executive_Dashboard")
    dash["A1"] = "ADVANCED MARKETING DASHBOARD"
    dash["A1"].font = Font(size=16, bold=True)
    dash["A3"] = "KPI"
    dash["B3"] = "Value"
    dash["D3"] = "Top Performer"
    dash["E3"] = "Value"
    for cell in ["A3", "B3", "D3", "E3"]:
        dash[cell].font = Font(bold=True, color="FFFFFF")
        dash[cell].fill = header_fill
        dash[cell].border = border
        dash[cell].alignment = Alignment(horizontal="center")

    kpi_cards = [
        ("Total Budget", "=SUM(Campaign_Overview!K:K)"),
        ("Total Spend", "=SUM(Campaign_Overview!L:L)"),
        ("Total Revenue", "=SUM(KPI_Calculations!G:G)"),
        ("Total Conversions", "=SUM(KPI_Calculations!E:E)"),
        ("Avg CTR", "=AVERAGE(KPI_Calculations!H:H)"),
        ("Avg CPA", "=AVERAGE(KPI_Calculations!K:K)"),
        ("Avg ROI", "=AVERAGE(KPI_Calculations!L:L)"),
        ("Avg ROAS", "=AVERAGE(KPI_Calculations!M:M)"),
    ]
    row = 4
    for label, formula in kpi_cards:
        dash[f"A{row}"] = label
        dash[f"B{row}"] = formula
        dash[f"A{row}"].fill = sub_fill
        dash[f"A{row}"].border = border
        dash[f"B{row}"].border = border
        row += 1

    dash["D4"] = "Best ROI Campaign"
    dash["E4"] = '=INDEX(KPI_Calculations!B2:B5,MATCH(MAX(KPI_Calculations!L2:L5),KPI_Calculations!L2:L5,0))'
    dash["D5"] = "Best CTR Campaign"
    dash["E5"] = '=INDEX(KPI_Calculations!B2:B5,MATCH(MAX(KPI_Calculations!H2:H5),KPI_Calculations!H2:H5,0))'
    dash["D6"] = "Best Conversion Campaign"
    dash["E6"] = '=INDEX(KPI_Calculations!B2:B5,MATCH(MAX(KPI_Calculations!E2:E5),KPI_Calculations!E2:E5,0))'
    for r in range(4, 7):
        dash[f"D{r}"].fill = sub_fill
        dash[f"D{r}"].border = border
        dash[f"E{r}"].border = border

    for r in range(4, 12):
        dash[f"B{r}"].border = border

    # Formats
    for r in [8]:
        pass
    dash["B8"].number_format = "0.00%"  # Avg CTR
    dash["B10"].number_format = "0.00%"  # Avg ROI
    dash["B11"].number_format = "0.00"   # Avg ROAS
    dash["B9"].number_format = "0.00"    # Avg CPA

    # Charts
    # 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=6, max_col=7, 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, "G2")

    # 2. CTR by Campaign
    chart2 = BarChart()
    chart2.title = "CTR by Campaign"
    data = Reference(ws7, min_col=8, max_col=8, 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, "G18")

    # 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, "T2")

    # 4. Conversions share pie chart
    chart4 = PieChart()
    chart4.title = "Conversions Share"
    data = Reference(ws7, min_col=5, 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, "T18")

    # Filters note
    dash["A14"] = "Suggested filters:"
    dash["A15"] = "Campaign | Channel | Status | Funnel Stage | Date"
    dash["A14"].font = Font(bold=True)
    dash["A15"].font = Font(italic=True)

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

    # ---------- Save ----------
    if "Sheet" in wb.sheetnames:
        del wb["Sheet"]

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


if __name__ == "__main__":
    build_marketing_dashboard_excel()