Dashboard creation for marketing KPIs in Digital Marketing - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ visualization

intermediate

Identify the best chart type for monthly website visits

You have monthly website visits data for the past year. Which chart type best shows trends over time clearly?

A Pie chart showing percentage of visits per month

B Scatter plot with visits on both axes

C Bar chart with months on the Y-axis and visits on the X-axis

D Line chart showing visits on the Y-axis and months on the X-axis

💡 Hint

Think about which chart type best shows changes over time.

A line chart is best for showing trends over time because it connects data points in order, making it easy to see increases or decreases month to month.

❓ dax_lod_result

intermediate

Calculate total conversions for a specific campaign

Given a table 'CampaignData' with columns 'CampaignName', 'Date', and 'Conversions', what is the total conversions for the campaign named 'Spring Sale'?

Use DAX to create a measure that returns this total.

Digital Marketing

TotalConversionsSpringSale = CALCULATE(SUM(CampaignData[Conversions]), CampaignData[CampaignName] = "Spring Sale")

A TotalConversionsSpringSale = SUM(CampaignData[Conversions])

B TotalConversionsSpringSale = CALCULATE(SUM(CampaignData[Conversions]), CampaignData[CampaignName] = "Spring Sale")

C TotalConversionsSpringSale = CALCULATE(SUM(CampaignData[Conversions]), CampaignData[Date] = "Spring Sale")

D TotalConversionsSpringSale = SUMX(FILTER(CampaignData, CampaignData[CampaignName] = "Spring Sale"), CampaignData[Conversions])

💡 Hint

Use CALCULATE to filter the table by campaign name before summing conversions.

Option B correctly filters the 'CampaignData' table to only rows where 'CampaignName' is 'Spring Sale' and sums the 'Conversions' column. Option B also works but is less efficient. Option B sums all conversions without filtering. Option B filters by date incorrectly.

❓ data_modeling

advanced

Design a data model for multi-channel marketing analysis

You want to analyze marketing performance across channels like Email, Social Media, and Paid Ads. Which data model design is best to support flexible reporting?

A Multiple unrelated tables for metrics, channels, and dates without keys

B Separate tables for each channel with no relationships between them

C A star schema with a fact table for marketing metrics and dimension tables for Channel, Date, and Campaign

D One flat table with all marketing data combined, including channel, date, and metrics columns

💡 Hint

Think about how to organize data for easy filtering and aggregation.

A star schema with a fact table and dimension tables allows flexible slicing and dicing of data by channel, date, and campaign. Flat tables or unrelated tables make reporting harder and less efficient.

🎯 Scenario

advanced

Optimize dashboard performance with large marketing datasets

Your marketing dashboard is slow because it uses a large dataset with daily data for multiple years. What is the best approach to improve performance without losing important insights?

A Aggregate data to monthly level and keep detailed daily data only for the last 3 months

B Keep all data at daily level and add more visuals to spread the load

C Remove all historical data older than one month

D Use only raw data tables without any pre-aggregation or indexing

💡 Hint

Consider balancing detail and performance by summarizing older data.

Option A balances performance and detail by aggregating older data monthly while keeping recent data detailed. Removing all old data loses insights. Keeping all data detailed slows performance. Using raw tables without optimization is inefficient.

🔍 Analysis

expert

Find the error in this DAX measure for calculating ROI

Given the measure below, what error will it cause when used in a report?

ROI = DIVIDE(SUM(Marketing[Revenue]) - SUM(Marketing[Cost]), SUM(Marketing[Cost]))

Digital Marketing

ROI = DIVIDE(SUM(Marketing[Revenue]) - SUM(Marketing[Cost]), SUM(Marketing[Cost]))

A Division by zero error if SUM(Marketing[Cost]) is zero

B No error; the measure calculates ROI correctly

C Syntax error due to missing parentheses

D Type error because SUM cannot be used inside DIVIDE

💡 Hint

Think about what happens if the denominator is zero.

DIVIDE function in DAX handles division safely, but if the denominator SUM(Marketing[Cost]) is zero, it returns blank or error depending on context. The formula itself is syntactically correct. SUM can be used inside DIVIDE. So the main issue is potential division by zero.

Practice

1. What is the main purpose of a marketing KPI dashboard?

easy

A. To create complex formulas for data analysis only

B. To store raw marketing data without visualization

C. To display key marketing numbers clearly in one place

D. To replace all marketing reports with text summaries

Solution

1. Step 1: Understand the role of a dashboard

   A dashboard is designed to show important information clearly and quickly.

2. Step 2: Identify the main function in marketing context

   Marketing KPI dashboards focus on showing key numbers in one place for easy review.

3. Final Answer:

   To display key marketing numbers clearly in one place -> Option C

4. Quick Check:

   Dashboard = Clear key numbers [OK]

Hint: Dashboards summarize key info visually [OK]

Common Mistakes:

• Confusing dashboards with raw data storage
• Thinking dashboards only store data without visuals
• Assuming dashboards replace all reports with text

2. Which of the following is the correct way to add a filter for 'Date' in a marketing dashboard tool?

easy

A. Create a text box with date values

B. Write a SQL query to delete dates from the data

C. Use a pie chart to show dates

D. Add a slicer visual and connect it to the Date field

Solution

1. Step 1: Identify how filters work in dashboards

   Filters let users select data subsets, often using slicers connected to fields.

2. Step 2: Choose the correct method for date filtering

   Adding a slicer visual connected to the Date field allows interactive filtering by date.

3. Final Answer:

   Add a slicer visual and connect it to the Date field -> Option D

4. Quick Check:

   Filter by slicer on Date [OK]

Hint: Use slicers for interactive filters [OK]

Common Mistakes:

• Deleting data instead of filtering
• Using pie charts to filter data
• Using text boxes instead of filter controls

3. Given a dashboard showing total website visits by channel, if the data is: Organic=5000, Paid=3000, Referral=2000, what will the pie chart show for Paid visits percentage?

medium

A. 30%

B. 25%

C. 50%

D. 20%

Solution

1. Step 1: Calculate total visits

   Total = 5000 (Organic) + 3000 (Paid) + 2000 (Referral) = 10000 visits.

2. Step 2: Calculate Paid visits percentage

   Paid % = (3000 / 10000) * 100 = 30%.

3. Final Answer:

   30% -> Option A

4. Quick Check:

   Paid visits = 30% of total [OK]

Hint: Divide part by total, multiply by 100 [OK]

Common Mistakes:

• Adding percentages instead of calculating
• Using wrong total for percentage
• Confusing counts with percentages

4. You created a dashboard but the filter for 'Campaign' does not change the charts. What is the most likely cause?

medium

A. The data source is offline

B. The filter is not connected to the data model

C. The dashboard has too many visuals

D. The charts are using the wrong color scheme

Solution

1. Step 1: Understand filter connections

   Filters must be linked to the data model fields used in visuals to affect them.

2. Step 2: Identify why filter has no effect

   If the filter is not connected properly, selecting values won't update charts.

3. Final Answer:

   The filter is not connected to the data model -> Option B

4. Quick Check:

   Filter connection missing = no effect [OK]

Hint: Check filter connections to data fields [OK]

Common Mistakes:

• Blaming colors for filter issues
• Assuming too many visuals cause filter failure
• Ignoring filter-data model link

5. You want to create a dashboard showing monthly revenue and conversion rate by marketing channel with a filter for last 6 months. Which combination is best?

hard

A. Use a line chart for monthly revenue, a card for conversion rate, and a date slicer for last 6 months

B. Use a pie chart for monthly revenue, a table for conversion rate, and no filter

C. Use a bar chart for total revenue only, a text box for conversion rate, and a filter for all time

D. Use a scatter plot for revenue and conversion, and a slicer for last year

Solution

1. Step 1: Choose visuals matching data types

   Line charts show trends over time well; cards highlight single KPIs like conversion rate.

2. Step 2: Select appropriate filter for time range

   A date slicer filtered to last 6 months allows focused analysis on recent data.

3. Final Answer:

   Use a line chart for monthly revenue, a card for conversion rate, and a date slicer for last 6 months -> Option A

4. Quick Check:

   Visuals + filter match data needs [OK]

Hint: Match chart type to data and add time slicer [OK]

Common Mistakes:

• Using pie charts for time series data
• Not adding time filters
• Choosing visuals that don't highlight KPIs clearly