Marketing Analytics in Spreadsheets
Learn how to ensure clean data entry and build dynamic dashboards to display your marketing data.
Start Course for Free4 Hours15 Videos56 Exercises
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.Loved by learners at thousands of companies
Course Description
Spreadsheets are an essential tool for any marketing professional, but how does one keep these spreadsheets clean and accurate - especially when multiple parties contribute data? Data validation and regular expressions are powerful tools for marketing analysts, but having clean data is only half the battle. After we learn how to clean the data, we will visualize it by building charts! Throughout the course, we will explore a dataset that includes the kind of information you will encounter in the world of digital marketing. We will spot errors in metrics using data validation, use regular expressions to aggregate campaign metrics, build charts to analyze campaign performance, and use everything we've learned to build a dynamic dashboard!
- 1
Data Validation for Clean Data Entry
FreeIn this chapter, you will explore the data validation options that Google Sheets offers to aid in clean data entry. You will also learn about the Bing and Google Ads paid advertising data you will explore throughout the course. After this chapter, you will be able to create spreadsheets that can be used by any number of people, without having to worry about disorganization.
The importance of clean data entry50 xpTest your knowledge50 xpFix the errors100 xpContribute to the data set100 xpCreate dropdowns from lists50 xpMitigating campaign name errors50 xpList from a range100 xpList of items100 xpValidations using cell criteria and checkboxes50 xpText validation100 xpCheck the checkboxes100 xpPutting it all together100 xp - 2
Regular Expressions
In the digital marketing world, naming conventions may differ among paid advertising campaigns or ad groups, which poses a problem when the user wants to analyze campaign performance. Regular expressions can help match certain strings, replace parts of strings, or extract a portion of a string. In this chapter, you will learn to use regular expressions, along with Google Sheets' built-in functions
REGEXMATCH(),REGEXREPLACE(), andREGEXEXTRACT(), to reorganize and aggregate data with ease.What are regular expressions?50 xpUsing regular expressions50 xpWriting regular expressions100 xpTest a string using REGEXMATCH50 xpFilter table using REGEXMATCH100 xpAggregate campaign cost using REGEXMATCH100 xpAggregate ad group cost-per-click using REGEXMATCH100 xpModify a string using REGEXEXTRACT and REGEXREPLACE50 xpRename brand campaigns using REGEXREPLACE()100 xpRename all ad groups using REGEXREPLACE()100 xpExtract brand campaign names using REGEXEXTRACT()100 xpCreate campaign IDs using REGEXEXTRACT()100 xpCleaning Campaign Names50 xpModify the campaign ID to include source100 xpHow much did each source spend?100 xpSum up campaigns with RegEx100 xp - 3
Visualize the Data with Charts
In this chapter, you will explore Google and Bing Ad campaigns and ad group data. In addition to a refresher on some basic charts, you will explore new ways to use these charts and experiment with the chart editor settings to create both informative and visually appealing charts. You will learn to explain paid advertising data through visualizations, which is an important task in the fast-paced digital advertising world.
Analyzing paid-search trends with line & area charts50 xpAnalyze cost data with a stacked area chart100 xpPlotting campaign click-through-rates with a line chart100 xpVisualizing ad group performance with column & bar charts50 xpUsing a bar chart to visualize total ad group spend100 xpAssessing campaign performance with a column chart100 xpAd group overview with 100% stacked bar chart100 xpEvaluating campaigns with pie & scatter plots50 xpAssessing campaign goal completions with a pie chart100 xpUnderstanding click through rate with a scatter plot100 xpVisualizing goal completions with a bubble chart100 xpBuild a digital marketing dashboard50 xpVisualizing impressions with a doughnut chart100 xpUsing a bubble chart to determine the better source100 xpSource performance using a standard stacked bar chart100 xp - 4
Build a Paid Search Campaign Dashboard
In the final chapter, you will be tasked with building a paid advertising dashboard that can be dynamically filtered by both source and campaign name. After completing the chapter, you should be able to tackle almost any data mitigation or dashboard creation project that you, or your boss, may think of!
Preparing the data50 xpDropdowns for source and campaign name100 xpCreate a filtered table100 xpPrep for charts with a regex driven table100 xpVisualize the data50 xpAd group impression share with a doughnut chart100 xpAd group metrics with a bar chart100 xpCampaign and ad group analysis with a bubble chart100 xpPutting it all together50 xpWho are the dashboards for?50 xpAdd a campaign dropdown filter100 xpApply dynamic filter to dashboard100 xpWrap-up50 xp
In the following tracks
Intermediate SpreadsheetsCollaborators
Prerequisites
Intermediate SpreadsheetsLuke Pajer
See More
Digital Marketing Specialist
Luke is a part of the Digital Marketing Services team at Beacon - a digital marketing agency located in Greensboro, North Carolina. His passion for spreadsheets and data science with python developed during his studies as a Geology major at Baylor University, which helped him find his niche as a Google Analytics expert at Beacon. Beyond business-related projects, Luke enjoys working on projects using JupyterLab - such as developing his own Fantasy Football player rankings or generating March Madness brackets.
What do other learners have to say?
Join over 12 million learners and start Marketing Analytics in Spreadsheets today!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.