Grades

Resources
Module 1: Welcome to Analytics Engineering!
Module 1: Lesson
Module 9: ChatGPT Mastery
Module 10: Analytics Engineering Capstone Project
Capstone Intro
Accounts and Access

Module 8: Homework – Looker Studio Marketing Sales and Spend

1  Download the datasets

FilePurposeRowsDimensionsMetrics
sales_data.csvDaily product sales by region & channel (2 years)≈ 8,760date (YYYY‑MM‑DD), region, product_category, sales_channelunits_sold, revenue_usd
marketing_spend.csvMonthly marketing spend by channel (2 years)96month (YYYY‑MM), marketing_channelspend_usd
Sales DataDownload
Marketing SpendDownload

Save the files to Google Drive (or your computer) before you begin the exercise.

2  Importing data into Looker Studio

  1. Open Looker Studio and click ➕ Blank Report.
  2. When prompted to “Add data to report”, choose File upload ▸ Upload from computer (or Google Drive if you stored the CSVs there).
  3. Select sales_data.csv. Leave the default “Automatically detect field types”. Click Add to Report ➜ Add.
  4. Repeat Resource ▸ Manage added data sources ▸ + Add a data source to upload marketing_spend.csv.
  5. Verify field types:
    • date → Date (YYYYMMDD)
    • month → Date (YearMonth)
    • Metrics (units_sold, revenue_usd, spend_usd) → Number
    • Others → Text / Dimension

Optional join (“data blend”)
If you want to relate spend to sales, create a blend: Resource ▸ Manage blends ▸ + Create blend.
Left table = sales_data (aggregated by month), Right table = marketing_spend, join key = month.

3  Building a beautiful dashboard

Page 1 — Sales Performance

ElementConfigurationWhy
Date range controlStyle ▸ UnderlineLets students pick any period
Scorecards (4)Metric: Total Revenue, Total Units, Avg Order Value (revenue_usd/units_sold), YoY Revenue GrowthQuick health check
Time‑series line chartDim =date, Metric =revenue_usd, Apply smooth 7‑day moving averageTrend & seasonality
Area chartBreak down by product_category stacked on revenueCategory momentum
Bar chartDim=region, Metric=units_sold, sort = descendingRegional volume

Styling tips

  • Use a harmonious palette: pick 3–4 vibrant brand colors plus neutral greys.
  • Activate “Show data labels” on bars & pie slices ≤ 6 categories.
  • In Chart style ▸ Grid set background to 0 % opacity for a clean, “card” look.

Page 2 — Marketing Efficiency

  1. Blended data line chart: month vs Revenue vs Spend (dual‑axis).
  2. Scatter plot: spend_usd (X) vs revenue_usd (Y) colored by marketing_channel.
  3. Pie chart: Share of spend_usd by channel.
  4. Table with conditional formatting: columns month, marketing_channel, CPM ≈ spend_usd/units_sold. Use red‑green color scale to highlight best CPA months.

Filters & Controls

  • Drop‑down list for region (applies to Page 1).
  • Checkbox for sales_channel to compare Online vs Store.
  • Slider for units_sold (advanced).

4  Homework questions

  1. Identify the top‑performing product category by revenue in Q4 2024.
  2. Which region experienced the fastest revenue growth between 2023 and 2024? Support with YoY %.
  3. In which month did marketing spend peak, and did revenue react proportionally? Explain.
  4. Calculate the average order value (AOV) for Online vs Store channels. Which is higher and why might that be?
  5. Inspect seasonal patterns: what two spikes do you observe in the revenue time series? Relate them to retail events.
  6. Using the blended chart, is there evidence of diminishing returns at higher spend levels? Cite a specific month.
  7. Filter to Clothing. Which region shows the lowest units sold but highest revenue per unit?
  8. What % share of total revenue came from Electronics in November 2023? Round to one decimal.
  9. Propose one actionable insight for the marketing team based on the scatter plot.
  10. If the company increases marketing spend by 10 % in Social Media, estimate the expected revenue uplift, using historical CPA.

5  Schedule daily email delivery

  1. While viewing the report, click Share ▸ Schedule email delivery.
  2. Recipients: add your school email.
  3. Subject: “Daily Sales & Marketing Dashboard”.
  4. Frequency: Daily at 7 AM (your time).
  5. Select PDF attachment, optional message.
  6. Click Save.

The report will arrive automatically each morning—confirm receipt after 24 hours.