It's common to have multiple sources of marketing data you need to join together. VLOOKUP and SUMIF are two of the most popular formulas for marketers in Excel. Learn how to use them.
- [Narrator] Quite often, you'll be using Excel…to join multiple data sets together.…For example, if you have conversions in one report,…and Ad Spend in another, you'll need to match them together…to report on cost per conversion.…Let's walk through how to do that.…You can see in this example, you have an advertising report…here which is showing you spend, clicks,…cost per click by campaign,…and we have a conversion report over here,…which is showing you conversions by campaign.…Now these could be in different sheets…but we just put them side by side so you can see.…
The way to bring the conversions across…into the advertising report is using V Lookup.…So we're going to type in VLOOKUP…and to choose the lookup value,…which is the campaign name which is campaign one…right here, you're going to choose the table array…which is the conversion report that we have.…Now we're going to make this an absolute reference…by pressing Command T because we want to be able…to drag this formula down, we don't want the box to move.…
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