Formulas are the backbone of Excel. We cover the SUM, COUNT, and AVERAGE formulas, as well as how to work out the true weighted average for bounce rate.
- [Instructor] The strength of Excel…is its ability to perform calculations with formulas.…We're going to go through a few examples of how to do that.…If you'd like to follow along,…go to exercise file 1.4,…how to use formulas for analysis.…What we're looking at is an export from Google Analytics,…showing traffic and behavior by page.…Now, let's do some basic analysis on this data.…First, let's try the count formula.…Let's see how many pages we're dealing with.…So if we hit equals count,…open brackets,…then we're going to select this row here.…
Command + Shift + Down.…Then we're going to close brackets.…So there we go.…4,705 pages.…We can also work out how much traffic we had in total.…So we're going to go sum,…page views,…Command + Shift + Down arrow.…Close brackets.…And there we go.…92,638.…Now, we can do some quick calculations…to figure out the average,…which is the sum divided by the count.…And that's 20 page views per page.…
But we can do that with one formula…which is just average.…And we can select this.…Command + Shift + Down arrow.…
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