Putting it all together

Let's clean up a digital marketing data set using Data Validation!

In this exercise, we will take this table, which reports on two different digital marketing sources, and apply Data Validation to fix any issues. This data set was filled in by multiple digital marketing professionals.

As we learned earlier, this means that there may be a few issues, as data entry is almost never perfect. The challenge here will be to apply different types of Data Validation that we have learned throughout this chapter to pin-point any issues with the data.

Deze oefening maakt deel uit van de cursus

Marketing Analytics in Google Sheets

Cursus bekijken

Oefeninstructies

Create a list of items dropdown for the Source column, with Google and Bing (in this order) as the dropdown values.
Fix the Source column errors.

Praktische interactieve oefening

Zet theorie om in actie met een van onze interactieve oefeningen.

Begin met trainen

Deze oefening maakt deel uit van de cursus

Marketing Analytics in Google Sheets

SkillTag.level.intermediateSkillTag.label

4.8+

Begin de cursus gratis

In 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.

Exercise 1: The importance of clean data entry Exercise 2: Test your knowledge Exercise 3: Fix the errors Exercise 4: Contribute to the data set Exercise 5: Create dropdowns from lists Exercise 6: Mitigating campaign name errors Exercise 7: List from a range Exercise 8: List of items Exercise 9: Validations using cell criteria and checkboxes Exercise 10: Text validation Exercise 11: Check the checkboxes Exercise 12: Putting it all together

Huidige oefening

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 <code>REGEXMATCH()</code>, <code>REGEXREPLACE()</code>, and <code>REGEXEXTRACT()</code>, to reorganize and aggregate data with ease.

Exercise 1: What are regular expressions?Exercise 2: Using regular expressions Exercise 3: Writing regular expressions Exercise 4: Test a string using REGEXMATCH Exercise 5: Filter table using REGEXMATCH Exercise 6: Aggregate campaign cost using REGEXMATCH Exercise 7: Aggregate ad group cost-per-click using REGEXMATCH Exercise 8: Modify a string using REGEXEXTRACT and REGEXREPLACE Exercise 9: Rename brand campaigns using REGEXREPLACE()Exercise 10: Rename all ad groups using REGEXREPLACE()Exercise 11: Extract brand campaign names using REGEXEXTRACT()Exercise 12: Create campaign IDs using REGEXEXTRACT()Exercise 13: Cleaning Campaign Names Exercise 14: Modify the campaign ID to include source Exercise 15: How much did each source spend?Exercise 16: Sum up campaigns with RegEx

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.

Exercise 1: Analyzing paid-search trends with line & area charts Exercise 2: Analyze cost data with a stacked area chart Exercise 3: Plotting campaign click-through-rates with a line chart Exercise 4: Visualizing ad group performance with column & bar charts Exercise 5: Using a bar chart to visualize total ad group spend Exercise 6: Assessing campaign performance with a column chart Exercise 7: Ad group overview with 100% stacked bar chart Exercise 8: Evaluating campaigns with pie & scatter plots Exercise 9: Assessing campaign goal completions with a pie chart Exercise 10: Understanding click through rate with a scatter plot Exercise 11: Visualizing goal completions with a bubble chart Exercise 12: Build a digital marketing dashboard Exercise 13: Visualizing impressions with a doughnut chart Exercise 14: Using a bubble chart to determine the better source Exercise 15: Source performance using a standard stacked bar chart

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!

Exercise 1: Preparing the data Exercise 2: Dropdowns for source and campaign name Exercise 3: Create a filtered table Exercise 4: Prep for charts with a regex driven table Exercise 5: Visualize the data Exercise 6: Ad group impression share with a doughnut chart Exercise 7: Ad group metrics with a bar chart Exercise 8: Campaign and ad group analysis with a bubble chart Exercise 9: Putting it all together Exercise 10: Who are the dashboards for?Exercise 11: Add a campaign dropdown filter Exercise 12: Apply dynamic filter to dashboard Exercise 13: Wrap-up