Table Structures and Columns with Data Types

The template contains five sheets, each structured as a named Excel Table (Ctrl+T) to enable dynamic range expansion and easy formula referencing.

1. Lead Tracker Sheet

2. Campaign Log Sheet

3. Sales Funnel Sheet

This sheet visually represents pipeline progression using stage-based counts. It’s not for data entry but dynamically pulls from Lead Tracker.

4. Dashboard Sheet

This sheet features charts and KPIs pulled from the data sheets using formulas and pivot tables.

Formulas Required

• Campaign ROI (%): =IF([@[Actual Spend ($)]]>0, ([@[Total Revenue ($)]] - [@[Actual Spend ($)]]) / [@[Actual Spend ($)]] * 100, 0)
• Sales Funnel Count: =COUNTIFS(Lead Tracker[Status], "New") — replicated for each stage.
• Avg. Deal Size: =AVERAGEIFS(Lead Tracker[Estimated Value], Lead Tracker[Status], "Closed Won")
• Conversion Rate (%): =IF([@[Impressions]]>0, [@[Leads Generated]] / [@[Impressions]], 0)
• Total Leads: =COUNTA(Lead Tracker[ID]) — excludes blanks via structured reference.

Conditional Formatting

• Lead Status: Red for “Closed Lost,” Green for “Closed Won,” Yellow for “Proposal Sent.”
• Campaign ROI: Green if >50%, Orange if 10-49%, Red if negative.
• Last Contacted: Highlight in red if more than 7 days since last contact.
• Sales Funnel: Bar charts auto-color-coded by stage volume.

Instructions for the User

To use this template:

1. Open and save as a new file to avoid overwriting the template.
2. In “Settings & Instructions,” review dropdown lists and customize campaign types or statuses if needed.
3. Enter leads under “Lead Tracker” — ensure all fields are filled, especially Source and Status.
4. Log each marketing campaign in “Campaign Log.” Update Actual Spend and Leads Generated as data becomes available.
5. The Dashboard automatically updates. Refresh PivotTables via right-click > Refresh if data changes don’t auto-update (rare).
6. Update “Last Contacted” dates manually after reaching out to leads. Use the built-in reminder system (conditional formatting) to identify stale leads.

Example Rows

Lead Tracker Example:
ID: 1 | Name: Sarah Johnson | Email: [email protected] | Phone: +1-555-0199 | Source: Website | Date Added: 04/03/2024 | Status: Qualified | Assigned To: Alex R. | Last Contacted: 04/12/2024

Campaign Log Example:
Campaign ID: CAM-101 | Campaign Name: Spring Email Blitz | Type: Email | Start Date: 03/15/2024 | End Date: 03/31/2024 | Budget ($): $850 | Actual Spend ($): $795 | Leads Generated: 86 | Conversion Rate (%): 17.8% | Total Revenue ($): $9,460

Recommended Charts and Dashboards

• Pie Chart: Show distribution of lead sources.
• Clustered Column Chart: Compare campaign ROI across different marketing channels.
• Stacked Bar Chart: Visualize Sales Funnel stage counts — ideal for pipeline health assessment.
• KPI Cards (text boxes with large numbers): Total Leads, Closed Deals, Revenue This Month, Average Conversion Rate. These are dynamically linked to formulas in the Dashboard sheet using simple =SUM() and =COUNTIFS() references.

This Simple Marketing Plan CRM Tracker offers an ideal balance of functionality and accessibility. It requires no programming or advanced Excel skills, yet delivers actionable insights to refine campaigns, nurture leads effectively, and measure marketing ROI — making it indispensable for any team aiming to execute a data-driven yet straightforward marketing plan.