Excel Template Guide

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.

Template Overview

What You'll Create

A properly structured Excel file with:

Time periods in the first column (Observation)
KPI variable (your dependent variable: sales, revenue, conversions)
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:

A1: Observation
A2: 2023-01-01
A3: 2023-01-08
A4: 2023-01-15
A5: 2023-01-22
...continue for 52+ weeks

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

A1: Observation
A2: 2023-01-01
A3: 2023-02-01
A4: 2023-03-01
...continue for 24+ months

Option 3: Custom Period Labels

A1: Observation
A2: Week 1
A3: Week 2
A4: Week 3
...continue

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

A2: 2023-01-01
A3: =A2+7

Copy A3 down to auto-increment by 7 days

Monthly Dates:

excel

A2: 2023-01-01
A3: =EDATE(A2,1)

Copy A3 down to auto-increment by 1 month

Create Month Dummies Automatically

Formula for January (Month = 1):

excel

=IF(MONTH($A2)=1,1,0)

Formula for February (Month = 2):

excel

=IF(MONTH($A2)=2,1,0)

Continue for all 12 months, changing the month number in the formula.

Calculate Rolling Averages (Advanced)

4-Week Moving Average:

excel

=AVERAGE(B2:B5)

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

=IF(COUNTIF(HolidayList,A2)>0,1,0)

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

=COUNTIF($A$2:$A$100,A2)>1

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

First column named exactly Observation
All other columns have descriptive names
No special characters in column names
No merged cells
No hidden columns or rows
Data starts in Row 1 (headers) and Row 2 (data)

Data Quality

All data values are numeric (no text in data cells)
No currency symbols ($, €, £)
No percentage symbols (%)
No blank cells (use 0 if needed)
No formulas (values only)
Dates in chronological order
No duplicate time periods

Content

At least 26 observations (52+ recommended)
KPI variable included
Major marketing channels included
Seasonality variables (if relevant)
All critical variables present

File

Saved as .xlsx format
File size under subscription limit
Backup copy created
Ready to upload

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!

PreviousData Upload & Format Requirements NextData Quality Best Practices

Last updated 1 month ago