Aggregate ad group cost-per-click using REGEXMATCH

As a digital marketing professional, you are tasked with determining the effectiveness of the three ad groups - Mugs, Sweaters, and Tee Shirts. You know from experience that average cost-per-click (CPC) is a very good indicator of ad group performance.

Your team decides that they want to keep the new Mugs ad group no matter what, so you would like to see what the average CPC would be, depending on the ad groups chosen.

この演習はコースの一部です

Google Sheetsで学ぶマーケティング分析

コースを見る

演習の手順

Add regular expressions in cells H3 and H4 that will match the respective ad groups listed in cells G3 and G4.
- Your expressions should match the first or the second ad group.
Write a filter for the CPC column, with the condition for the filter using the regex in cell H3 (combined with REGEXMATCH()).
Wrap the filter with an AVERAGE() to find the average CPC for the Mugs and Tee Shirts ad groups.
Repeat steps 2 and 3 to get the average CPC for the Mugs and Sweaters ad groups.

実践的なインタラクティブ演習

理論を実践に変える、インタラクティブな演習のひとつをお試しください

演習を開始する

この演習はコースの一部です

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