Step-by-Step Guide to Preparing Your MMM Data in Excel
This comprehensive guide walks you through creating a properly formatted Excel file for MixModeler, from blank spreadsheet to upload-ready dataset. Follow these step-by-step instructions to ensure your data is structured correctly for immediate use.
Marketing variables (spend, impressions, GRPs by channel)
Control variables (seasonality, pricing, external factors)
Clean numeric data ready for modeling
Step-by-Step Template Creation
Step 1: Open New Excel Workbook
Instructions:
Open Microsoft Excel
Create new blank workbook
Work on Sheet1 (default first sheet)
Delete any extra sheets (Sheet2, Sheet3) - optional but cleaner
Step 2: Set Up Column Headers
Row 1 - Column Headers:
Critical Requirements: ✅ First column MUST be named exactly Observation (case-sensitive) ✅ Use descriptive, clear names for all variables ✅ Use underscores instead of spaces (TV_Spend not TV Spend) ✅ Avoid special characters (@, #, $, %, &, *)
Step 3: Enter Time Periods (Column A)
Option 1: Weekly Data (Recommended)
Enter dates for each week:
Quick Method:
Enter first date (A2): 2023-01-01
Enter second date (A3): 2023-01-08
Select both cells A2:A3
Drag fill handle down to auto-increment by 7 days
Continue until you have 52+ rows
Option 2: Monthly Data
Option 3: Custom Period Labels
Step 4: Enter Your KPI Data (Column B)
Instructions:
Column B header: Sales (or Revenue, Conversions, etc.)
Enter your actual KPI values for each time period
Ensure all values are numeric (no currency symbols)
Example:
Formatting Tips:
Format cells as "Number" (not "Currency")
No decimal places needed if whole numbers
No commas in the actual cell value (Excel displays them, but value should be 150000)
Step 5: Add Marketing Variables (Columns C+)
For Each Marketing Channel:
Create column with descriptive name
Enter spend, impressions, or GRPs for each period
Use consistent units (e.g., all spend in dollars, all impressions in thousands)
Example Marketing Columns:
Naming Conventions:
Step 6: Add Seasonality Variables (Optional but Recommended)
Month Dummy Variables:
Create binary (0/1) columns for each month:
Quick Creation Method:
Create 12 columns (Month_Jan through Month_Dec)
Use Excel formula: =IF(MONTH($A2)=1,1,0) for January
Adjust formula for each month (1=Jan, 2=Feb, etc.)
Copy formula down all rows
Holiday Indicator:
Single binary column for holidays/special events:
Step 7: Add Control Variables (Optional)
Price Variable:
Promotion Indicator:
Competitor Activity:
Example Complete Template
Here's what your complete spreadsheet should look like:
Excel Formulas and Functions
Auto-Fill Date Sequences
Weekly Dates:
excel
Copy A3 down to auto-increment by 7 days
Monthly Dates:
excel
Copy A3 down to auto-increment by 1 month
Create Month Dummies Automatically
Formula for January (Month = 1):
excel
Formula for February (Month = 2):
excel
Continue for all 12 months, changing the month number in the formula.
Calculate Rolling Averages (Advanced)
4-Week Moving Average:
excel
Use Case: Smooth volatile data
Flag Holiday Weeks
Manual Method: Enter 1 for holiday weeks, 0 otherwise
Formula Method (if you have holiday dates):
excel
Data Quality Checks in Excel
Check 1: No Blank Cells
Instructions:
Select all data range (A1:Z100)
Press F5 (Go To)
Click "Special"
Select "Blanks"
If blanks found → fill with 0 or appropriate value
Check 2: All Numbers Valid
Instructions:
Select data columns (B2:Z100)
Look for left-aligned numbers (indicates text)
Look for green corner triangles (number stored as text)
Fix: Data → Text to Columns → Finish
Check 3: No Duplicate Dates
Formula to Check:
excel
Place in column next to Observation. If returns TRUE, you have duplicates.
Check 4: Dates in Chronological Order
Instructions:
Select Observation column
Data → Sort → Oldest to Newest
Verify order makes sense
Formatting Best Practices
Number Formatting
For Financial Data (Spend, Sales):
Format: Number
Decimal places: 0 (for whole numbers)
Use thousands separator: Yes (for display only)
Example display: 150,000 (stored as 150000)
For Percentages:
Store as decimals: 0.15 not 15%
Format can show percentage in display
Example: 15% displayed, 0.15 stored
For Dates:
Format: Short Date or Custom
Example: 1/1/2023 or 2023-01-01
Ensure all dates use same format
Column Width
Set Appropriate Widths:
Select all columns
Home → Format → AutoFit Column Width
Or double-click between column headers
Benefits:
Easy to review data
Spot errors quickly
Professional appearance
Freeze Header Row
Instructions:
Click on Row 2 (first data row)
View → Freeze Panes → Freeze Panes
Now header row stays visible when scrolling
Benefits:
Always see column names
Easier data entry
Less confusion
Common Excel Mistakes and Fixes
Mistake 1: Numbers Stored as Text
Symptom:
Numbers aligned left
Green triangle in corner
Formulas don't work on these cells
Fix:
Select affected cells
Click warning icon
"Convert to Number" OR
Data → Text to Columns → Finish
Mistake 2: Hidden Columns
Symptom:
Column letters skip (A, B, D, E - missing C)
Model might miss variables
Fix:
Select columns on both sides
Right-click → Unhide
Mistake 3: Merged Cells
Symptom:
Cells span multiple rows/columns
Upload will fail
Fix:
Home → Merge & Center → Unmerge Cells
Split data appropriately
Mistake 4: Formulas Instead of Values
Symptom:
Cells contain =SUM(...) or other formulas
Can cause upload issues
Fix:
Select all data
Copy (Ctrl+C)
Paste Special → Values
Now cells contain results, not formulas
Saving Your Template
Step 1: Save As Excel Format
Instructions:
File → Save As
Choose location
File name:MMM_Data_2023.xlsx
File type: Excel Workbook (*.xlsx)
Click Save
Why .xlsx:
Native Excel format
Preserves formatting
Best compatibility with MixModeler
Step 2: Create Backup Copy
Best Practice:
Save original: MMM_Data_2023_ORIGINAL.xlsx
Save working copy: MMM_Data_2023_Working.xlsx
Upload working copy to MixModeler
Benefits:
Preserve original data
Can revert if needed
Safe experimentation
Pre-Upload Checklist
Before uploading to MixModeler, verify:
Structure
Data Quality
Content
File
Advanced Template Features
Multiple KPIs
You can include multiple KPIs in one file:
Use Case: Build separate models for different KPIs using same marketing data
Channel Breakdowns
Break channels into sub-channels:
Use Case: Detailed attribution within channel types
Geographic Splits
Include regional data:
Use Case: Regional MMM models or national model with regional controls
Template Download Resources
Starter Template (Simple)
Includes:
52 weekly observations
Sales (KPI)
3 marketing channels
Holiday indicator
Download: Available in MixModeler under Templates
Professional Template (Comprehensive)
Includes:
104 weekly observations
Multiple KPIs
10 marketing channels
12 month dummies
Control variables
Download: Available in MixModeler under Templates
Custom Template Builder
Instructions:
Start with blank template
Add your specific channels
Include your control variables
Save as master template
Reuse for future updates
Summary
Key Takeaways:
📊 First column = "Observation" - exact name, case-sensitive
🔢 Numeric data only - remove all symbols and formatting
📅 Chronological order - earliest to latest
✅ Use checklist - verify before upload
💾 Save as .xlsx - best format for MixModeler
📋 Include essentials - KPI + marketing + seasonality
🔄 Create template once - reuse for data updates
📁 Keep backups - preserve original data
Following this guide ensures your Excel file is perfectly formatted for immediate use in MixModeler, saving time and avoiding upload errors!