Add a campaign dropdown filter

As the dashboard is almost complete, the marketing director at your agency reminds you that the initial request asked for the data to be filtered by both the Source and the Campaign Name. You know that all you need to do is modify the current formula to filter for both dropdown menus.

As the formula is already quite complex at this stage, we have formatted it within the cell so that you can see the different elements of the two IF() functions. If you are struggling with the formula, you can review the relevant slide in the Slide Reference tab!

本练习是课程的一部分

在 Google Sheets 中进行营销分析

练习说明

In cell H19, add another FILTER() function as an 'else' statement to the inner IF() function in the existing formula (replacing #insert your else statement here!)`.

This FILTER() function should filter the range A1:F29.
Add a REGEXMATCH() function as the first condition, which will filter B1:B29 based on the campaign dropdown H3.
Add another REGEXMATCH() function as the second condition to the FILTER() function in cell H19 to filter A1:A29 based on the source dropdown H2.

Note: Don't change the dropdown values in cells H2 and H3!

动手互动练习

通过我们的互动练习之一，将理论转化为实践

本练习是课程的一部分

在 Google Sheets 中进行营销分析

SkillTag.level.intermediateSkillTag.label

4.8+

免费开始课程

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

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