Spreadsheet Analysis for Marketers: Skills That Pay Off

Dashboards are nice, but many real marketing questions start with a CSV export and a spreadsheet. If you can slice that data confidently, you will answer questions faster than a team waiting for a custom report.

Set up the data properly

Before any formulas, shape the sheet so it behaves.

• One header row, no merged cells, no blank rows inside the data.
• One row per record, such as one row per campaign per day.
• One type of value per column; do not mix text and numbers.
• Dates stored as real dates, not text.
• Keep a raw copy of the export untouched on a separate tab.

Formulas worth learning first

• SUM, AVERAGE, COUNT: the basics.
• SUMIF and SUMIFS: total spend or conversions that meet conditions, such as a campaign name or date range.
• COUNTIF and COUNTIFS: count rows by category.
• VLOOKUP or XLOOKUP (INDEX and MATCH in older versions): attach information from another table, such as joining ad spend to a campaign owner.
• IF and IFERROR: handle edge cases, such as dividing by zero.
• TEXT, LEFT, RIGHT, SPLIT: pull values out of campaign names or URLs.

Calculated metrics like CTR, conversion rate and cost per lead should be formulas, not typed numbers, so they update when the data does.

Pivot tables

A pivot table summarises thousands of rows in seconds.

1. Select the data range and insert a pivot table.
2. Drag channel or campaign into rows.
3. Drag spend, clicks and conversions into values.
4. Add a date or month to columns to see trends.
5. Add calculated fields for ratios, or compute them next to the pivot.

Pivots answer questions like "which campaigns spent more than they returned this month?" without writing formulas for every case.

A hypothetical example

A freelancer receives a monthly ad export with campaign names such as "IN_Search_Brand_Oct". She splits the name into country, channel, type and month using text functions, builds a pivot by channel and type, and adds cost per lead beside it. In an hour she has a clean summary for the client, and the same sheet is reusable next month by pasting new data.

Habits that prevent mistakes

• Label units and currency in headings.
• Avoid hard-coded numbers inside formulas; point to cells.
• Use consistent naming, ideally aligned with UTM conventions.
• Check totals against the platform's own interface.
• Add a notes tab describing sources and date pulled.
• Lock or protect formula cells if others will edit.

Turning analysis into reporting

Once the numbers are correct, present them well. Use a few focused charts, as suggested in our data visualisation for reports guide, and feed stable tables into a Looker Studio dashboard when you want automation.