Marketing Analytics Mastery: From Strategy to Application

Overview

In the previous lecture, Case Study #1: Metrics Identified, we shortlisted five KPIs based on the Etsy case study. You can now test what you've learned by trying to calculate the campaign performance for the five KPIs using a real dataset. To recap, the five KPIs, benchmarks and targets are as follows:

1. Total revenue | Benchmark = $18,000 | Target = $20,000

2. Conversion rate | Benchmark = 1.3% | Target = 1.6%

3. Clickthrough rate | Benchmark = 2.3% | Target = 2.5%

4. Cost per click | Benchmark = $0.5 | Target = $0.4

5. Post engagement rate | Benchmark = 2.1% | Target = 2.4%

The attached Excel (XLS) file contains three worksheets (i.e. google analytics, instagram, and facebook). These three worksheets provide sample data for the Etsy case study.

What you need to do

1. Download the XLS file from Resources titled Etsy_campaign_dataset - ACTIVITY-BRIEF.xlsx

2. Using this dataset, try to calculate the actual performance for the five KPIs, and establish how far above or below the benchmark and target this campaign was. It's ok if you don't know how at this stage. Just try your best.

3. Optionally, try analyzing the dataset and come up with some interesting observations. Here are some questions you can consider answering:

   1. Was this campaign a success? i.e. Were the KPI targets achieved?

   2. What sources drove the highest conversion rate (e.g. Facebook, Instagram, etc)?

   3. On average, do video posts perform better than image posts on Instagram?

   4. Did the link posts on Facebook generate higher click-through rates than the photo posts?

When you're finished, you can check your answers by watching the next lecture in the next lecture.

[Update] Since filming this lecture Meta has rolled out an update to the Meta Business Suite insights tool that now includes a 'people-who-engaged' metric, called Engaged Users. You can refer to the next lecture, Engaged Users Metric in Facebook Insights, for more information.

Overview

In the previous lecture, Case Study #2: Metrics Identified, we shortlisted five KPIs based on the Akamai case study. You can now test what you've learned by trying to calculate the campaign performance for the KPIs using a real dataset. However, I've added a few additional KPIs to the list to make the activity a little more interesting. There are 10 KPIs in total, and the benchmarks and targets are as follows:

1. Website Pageviews | Benchmark = 100,000 | Target = 140,000

2. Website Users | Benchmark = 60,000 | Target = 70,000

3. Display ad Impressions | Benchmark = 1,000,000 | Target = 2,000,000

4. Display ad CPM | Benchmark = $8.0 | Target = $7.5

5. LinkedIn ad Impressions | Benchmark = 4,000,000 | Target = 5,500,000

6. LinkedIn ad CPM | Benchmark = $10 | Target = $9.5

7. Google Search Average Keyword Position | Benchmark = 5 | Target = 3

8. PR Coverage / Clips | Benchmark = 100 | Target = 150

9. Website Conversion Rate | Benchmark = 2.5% | Target = 3%

10. LinkedIn Lead Form Completion Rate | Benchmark = 0.8% | Target = 0.9%

The attached Excel (XLS) file contains six worksheets (i.e. microsite, display ads, LinkedIn ads, Google search console, PR, and CRM). These six worksheets provide sample data for the Akamai case study. Note that compared to the first data analysis activity with Etsy campaign, this activity features a much larger dataset, and will be more difficult to analyze. It's ok if you can find all of the answers. You can refer to the second attached resource to see the answers as well as how the calculations are done.

What you need to do

1. Download the XLS file from Resources titled akamai_campaign_dataset_BRIEFING.xls

2. Using this dataset, try to calculate the actual performance for the KPIs, and establish how far above or below the benchmark and target this campaign was. It's ok if you don't know how at this stage. Just try your best.

3. Optionally, try analyzing the dataset and come up with some interesting observations. Here are some questions you can consider answering:

   1. Overall, how well do you think this campaign did? Did it achieve the business objectives? Why or why not?

   2. Between the display ads and LinkedIn ads, which had a better CPM?

   3. Did traffic quality to the website improve, worsen or stay the same throughout the campaign?

   4. Was there anything interesting or notable about the top ranking keywords?

   5. Of the leads received via the website lead form (i.e. CRM), how many of these are hot leads interested in the product?

When finished, you can check your answers by watching the next lecture.

[Correction] There's a minor error in this lecture in column K (Length Category), which appears at 14:32. Due to an error in the vlookup function of the spreadsheet, the contents of this column are not correctly referencing the values in column J, so please take note.

Google recently rebranded Google Data Studio to Google Looker Studio. At this point in time, there are no material changes to the product and user interface, so everything I teach in this video is still relevant. I will continue to monitor updates to Looker Studio and will update this video if there are any updates to the product in the future.