Module 8: Homework – Looker Studio Marketing Sales and Spend

Sales DataDownload
Marketing SpendDownload

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

📦 Deliverables (what you must turn in)

1) File Upload (Required)
One document (Word, PDF, or CSV) with your answers to the Core questions (Q1–Q6) and, if you attempt them, any Bonus questions (Q7–Q10).

  • Clearly number your answers (Q1–Q6 for Core; Q7–Q10 only if you did them).

2) Dashboard Link (Required)
A shareable link to your Looker Studio dashboard with two pages:

  • Page 1: Sales Performance
  • Page 2: Marketing Efficiency
    Make sure instructors can view without requesting access.

3) Scheduled Email Proof (Required)
A screenshot or a short note confirming you scheduled daily email delivery of the dashboard.
(Attach the screenshot inside the same file as your answers or as page 2.)

📤 Submission Instructions

  1. Go to the “Drag and drop files to upload” section above.
  2. Upload your deliverable file (answers + screenshot).
  3. Paste your Looker Studio dashboard link inside the same file or in the submission text box.
  4. Double-check the file uploaded correctly and the link opens without permission issues.

🧰 What you’ll build (and why)

  • A two-page dashboard in Looker Studio that lets you answer simple business questions quickly.
  • Page 1 (Sales Performance) teaches time series, categories, and comparisons.
  • Page 2 (Marketing Efficiency) teaches blending monthly spend with monthly revenue for simple ROI-style analysis.
  • You’ll schedule a daily email to practice reporting automation.

0) Get the data

Use the download links provided above for:

FilePurposeKey Fields
sales_data.csvDaily product sales by region & channel (2 years)date (YYYY-MM-DD), region, product_category, sales_channel, units_sold, revenue_usd
marketing_spend.csvMonthly marketing spend by channel (2 years)month (YYYY-MM), marketing_channel, spend_usd

Save both files to Google Drive (recommended) or your computer. You’ll upload them into Looker Studio.

1) Create a new Looker Studio report & upload data

  1. Open Looker Studio → Blank report.
  2. When prompted to “Add data to report”, choose File upload → upload sales_data.csv.
    • Click Add to Report → Add.
  3. Add the second file: Resource → Manage added data sources → + Add a data source → File upload → upload marketing_spend.csv → Add.

Why: Putting both datasets in one report lets you build all charts in one place. Uploading as file sources is the simplest path for beginners.

2) Fix field types (avoid broken charts)

Open Resource → Manage added data sources → Edit each source and ensure:

For sales_data

  • date → Date (not Date & Time)
  • units_sold, revenue_usd → Number
  • region, product_category, sales_channel → Text

For marketing_spend

  • month is a text like YYYY-MM, so convert it to a real date:
    • Create a new field called month_date: PARSE_DATE('%Y-%m', month) Set its Type to Date.
  • spend_usd → Number
  • marketing_channel → Text

Why: Charts and joins depend on correct data types. If month stays a string, time series and blending won’t behave correctly.

3) Create helper fields (reusable & friendly)

Create these calculated fields in the sales_data source (Edit data source → Add a field):

  1. AOV (Average Order Value)
SUM(revenue_usd) / SUM(units_sold)

Type: Number

  1. yearmonth (to line up with monthly spend)
DATE_TRUNC(date, MONTH)

Type: Date (will display as Year-Month when used as a dimension)

  1. year
YEAR(date)

Type: Number

  1. year_quarter (nice for grouping Q1–Q4 by year)
FORMAT_DATE('%Y-Q%q', date)

Type: Text

Now create these calculated fields in the marketing_spend source:

5. yearmonth (matching the sales side)

    DATE_TRUNC(month_date, MONTH)

    Type: Date

    Why: These fields standardize time to month, enable AOV, and make grouping by Year/Quarter easy.

    4) (Optional, but recommended) Blend monthly spend with monthly sales

    This lets you compare monthly spend vs monthly revenue.

    1. Insert any chart → in the Data panel click Blend data.
    2. Left table:marketing_spend
      • Dimensions: yearmonth, marketing_channel
      • Metrics: SUM(spend_usd)
    3. Right table:sales_data
      • Dimensions: yearmonth
      • Metrics: SUM(revenue_usd), SUM(units_sold)
    4. Join keys: yearmonth ↔ yearmonth
    5. Join type: Left outer (keeps all months/channels from spend even if sales are missing)
    6. Add two new fields in the blend:
      • ROAS SUM(revenue_usd) / SUM(spend_usd)
      • cost_per_unit_proxy SUM(spend_usd) / SUM(units_sold)
    7. Name the blend Monthly Spend + Sales and click Save.

    Note: We’re not doing true channel attribution here (sales aren’t broken down by marketing channel). This is a simple association for practice.

    5) Build Page 1 — Sales Performance (core skills)

    At the top: Insert → Date range control

    • Style → Underline so students notice it.
      Why: One control syncs all charts to the selected period.

    A) Scorecards (4)

    Insert four Scorecards (they’ll inherit the page’s date range):

    1. Total Revenue → Metric: SUM(revenue_usd)
      • Comparison: Previous year (YoY %)
    2. Total Units → Metric: SUM(units_sold)
    3. Average Order Value (AOV) → Metric: AOV
    4. YoY Revenue Growth (quick way)
      • Duplicate Total Revenue and keep only the % comparison visible in Style.

    Why: Scorecards are the “health check” at a glance.

    B) Time-series line chart (trend & seasonality)

    • Insert → Time series
    • Dimension: date
    • Metric: SUM(revenue_usd)
    • Optional smoothing: add a second series using SUM(revenue_usd) and apply a trendline (or moving average if available in your Looker Studio version).

    Why: Time series reveal direction and seasonal spikes.

    C) Stacked area by product category (category momentum)

    Two easy setups (pick one):

    • Time series with Breakdown dimension = product_category, Metric = SUM(revenue_usd), or
    • Area chart (stacked) with Dimension = date, Metric = SUM(revenue_usd), Breakdown = product_category.

    Why: Shows how categories contribute over time.

    D) Bar chart by region (where volume happens)

    • Insert → Bar chart
    • Dimension: region
    • Metric: SUM(units_sold)
    • Sort: by metric descending
    • Style: turn on Data labels

    Why: Quick comparison of regional performance.

    E) Page-level controls

    • Drop-down (region): Control field = region (Apply to This page)
    • Checkbox (sales_channel): Control field = sales_channel (compare Online vs Store)
    • Slider (units_sold): Control field = units_sold (optional, for exploration)

    6) Build Page 2 — Marketing Efficiency (practice with blending)

    A) Dual-axis monthly line (Revenue vs Spend)

    • Insert → Time series
    • Data source: Monthly Spend + Sales
    • Dimension: yearmonth
    • Metrics: SUM(revenue_usd) and SUM(spend_usd)
    • Style: turn on Dual axis (Revenue left, Spend right)

    Why: Eye-level view of whether higher spend aligns with higher revenue.

    B) Scatter plot by channel (diminishing returns)

    • Insert → Scatter chart
    • Data source: Monthly Spend + Sales
    • Dimension: marketing_channel
    • Breakdown: yearmonth (gives multiple dots per channel over time)
    • X: SUM(spend_usd)
    • Y: SUM(revenue_usd)
    • Style: enable Trendline

    Why: Look for points where extra spend doesn’t lift revenue much.

    C) Pie chart (share of spend)

    • Data source: marketing_spend
    • Dimension: marketing_channel
    • Metric: SUM(spend_usd)
    • Style: show labels & %

    Why: Understand budget allocation.

    D) Table with conditional formatting (cost effectiveness)

    • Data source: Monthly Spend + Sales
    • Dimensions: yearmonth, marketing_channel
    • Metrics: SUM(spend_usd), SUM(units_sold), cost_per_unit_proxy
    • Style → Conditional formatting on cost_per_unit_proxy: red→green (green = lower cost is better)

    Why: A quick way to spot efficient months/channels.

    7) Make it look good (fast styling wins)

    • Theme & layout → Customize: pick 3–4 harmonious brand colors + neutral greys.
    • In each chart’s Style:
      • Turn Data labels ON for bars and pies (when categories ≤ 6).
      • Set Grid background opacity = 0% (clean “card” look).
      • Add a subtle shadow to charts/cards.

    8) Schedule daily email delivery (automation practice)

    1. Open your report → Share → Schedule email delivery.
    2. Recipients: your school email.
    3. Subject: “Daily Sales & Marketing Dashboard”.
    4. Frequency: Daily at 7:00 AM (your time).
    5. Attach PDF (optional message).
    6. Click Save.
    7. Take a screenshot of the schedule confirmation for your submission.

    Why: Executives often want automated digests. You’re practicing a real-world habit.

    ✅ CORE Homework Questions (Required: Q1–Q6)

    Answer these in your uploaded file. Use Page 1 unless noted.

    Q1. What is Total Revenue YTD 2024?
    How: Set Date range to This year to date. Read the Total Revenue scorecard.

    Q2. Which product category has the highest revenue in 2024 YTD?
    How: Use the stacked area (or add a table: Dimension = product_category, Metric = SUM(revenue_usd), sort by revenue desc).

    Q3. Which region sold the most units in 2024 YTD?
    How: Bar chart by region with SUM(units_sold) sorted descending.

    Q4. What is the AOV overall in 2024 YTD and for Online vs Store? Which is higher?
    How: Toggle the sales_channel checkbox and read the AOV scorecard (or add a quick table by sales_channel with AOV).

    Q5. Describe the revenue trend over the last 6 months in one sentence (e.g., rising, flat, seasonal peaks).
    How: Set the Date range to the last 6 months and read the time series (use trendline if helpful).

    Q6. Which month in 2024 had the highest marketing spend?
    How (Page 2): Dual-axis monthly chart (Spend is the right axis). Hover to find the peak month.

    ⭐ BONUS Questions (Optional: Q7–Q10)

    Attempt any you like (0–4). These stretch your blending and comparison skills.

    Q7. Which region had the fastest YoY revenue growth from 2023 → 2024?
    How: Add a table with Dimension = region, Metric = SUM(revenue_usd) and Comparison = Previous year (%), then sort by the YoY %.

    Q8. What % of total revenue came from Electronics in November 2023 (one decimal)?
    How: Set Date range to Nov 1–30, 2023; use a pie/table by product_category. Turn on Show value as percent of total.

    Q9. Looking at the scatter plot, is there evidence of diminishing returns at higher spend? Cite a specific month and channel.
    How: Page 2 scatter (Spend X, Revenue Y), enable trendline, hover to identify a high-spend month where revenue doesn’t rise much.

    Q10. If Social Media spend increases by 10% next month, estimate the expected revenue uplift using recent ROAS.
    How: In the blended table, filter to marketing_channel = Social Media. Compute recent ROAS = Revenue/Spend (e.g., last 3-month average).
    Uplift ≈ Current Spend × 10% × ROAS. Show your math.

    🧪 Troubleshooting & tips

    • My date looks wrong: Re-check types. date must be Date; month_date must be Date; yearmonth = DATE_TRUNC(…, MONTH).
    • Comparisons not showing YoY: On scorecards/tables, set Comparison to Previous year (not previous period).
    • Charts look noisy: Aggregate to Month or use a trendline.
    • Numbers look huge: In Style, set Number formatting to Compact (K, M).
    • Blend missing months: Ensure both sides use the same yearmonth Date field; join type should be Left outer.

    ✅ Final QA checklist before you submit

    Your dashboard link is shareable (no permission roadblocks).

    Two pages present: Sales Performance and Marketing Efficiency.

    Date controls work and charts update when you filter.

    Regional bar sorts descending by units.

    Labels visible on bar/pie where helpful.

    You scheduled the daily email and captured a screenshot.

    Your uploaded file clearly answers Q1–Q6 (Core) and any Bonus you attempted—numbered and easy to read.