Create a filtered table

Now that the dropdowns are created, the next step is to create a separate table. This table will be present in any future dashboard, as the marketing manager at your agency and the clients you work for like to see the filtered data.

本练习是课程的一部分

在 Google Sheets 中进行营销分析

练习说明

In cell H19:

Write a REGEXMATCH() function to match the column A1:A29 with the value in the source dropdown, H2.
Working in cell H19 again, wrap the REGEXMATCH() function from the previous step with a FILTER() function, filtering for range A1:F29.
Finally, still in cell H19, wrap the FILTER() function with an IF() statement, where LEN(H2) > 1 as the condition, the FILTER() function as the value if True, and range A2:F29 if False.

动手互动练习

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

本练习是课程的一部分

在 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