In order to fully take advantage of Excel's PivotTable functionality, you need to be able to split data into smaller segments. Learn how to use Text to Columns, LEFT, MID, and RIGHT to extract data from cells.
- [Instructor] Do you want to segment your data,…but don't have it in the right format?…Excel extraction techniques can help you…break out your data to better analyze it.…To follow along, go to Exercise File 3.8…Cell Extraction for further segmentation.…This is an example of an internal report I received…showing the source, medium, and campaign,…and how many signups that drove.…If you see here, the report is delimited by pipes.…So what that means is that if the source was Google,…it has a pipe separating, then the medium, which is CPC,…and then a pipe separating, and then the campaign.…
So if we want do any serious analysis on this,…we need to be able to split this out.…So one way to do this is text columns.…So I'm just going to select the source here,…Command + Shift + Down.…I'm going to copy that, Command + C; go back up,…and then click into C here and go Command + V.…Now I'm going to hit in the tab data text to columns.…I'm going to choose the delimiter.…So I'm going to go Next, and I'm going to tick Other,…
Michael begins with the basics, showing how to work with marketing data sources and explaining basic formulas for analysis. Next, he walks through how to build a data-driven marketing plan, including mapping out your marketing funnel, reviewing volume vs. cost tradeoff, and running A/B testing. Michael shows how to analyze a campaign's performance, working with PivotTables, charts, and dashboards. Finally, he demonstrates how to set up marketing campaigns in bulk, including manipulating text for ad copy generation and automating the most boring, repetitive tasks with macros.
- Cleaning up data formatting
- Using formulas for analysis
- Building a data-driven marketing plan
- Top-down vs. bottom-up marketing plans
- Running A/B tests
- Analyzing campaign performance
- Plotting data visually
- Building report templates and dashboards
- Building bulksheets for bulk campaigns on AdWords and Facebook