Track your weekly marketing campaign metrics using Dax in Excel — Part 1 — Introduction

In the realm of data analysis, DAX has emerged as a game-changer, amplifying Excel’s capabilities with robust time intelligence.

While Excel simplifies the process of data aggregation, I faced a unique challenge at work: the necessity to consolidate data on a weekly basis and compare metrics from one week to the next.

Throughout this article and the subsequent ones, I’ll delve into the challenge I faced, present the data, and reveal how I adeptly employed DAX to tackle this issue.

Our dataset provides insights into the performance of specific keywords used in an advertising campaign. You can skip this part if you are familiar with digital marketing terms.
Let’s break it down:

• keyword_text: This represents the actual keyword being targeted.
• match_type: Describes how the keyword is matched with user searches. “exact” means the search term must match the keyword.
• keyword_status: Indicates whether the keyword is currently active (“enabled”) or not.
• current_bid: This shows the bid amount for the keyword.
• adgroup_name & adgroup_status: Keywords are grouped under ad groups for better organization and tracking.
• campaign_name & campaign_status: Ad groups further roll up under campaigns.
• target_acos: This would typically indicate the target Advertising Cost of Sales.
• report_date: Date when the data was reported. This is crucial for our weekly aggregation. The data showcases metrics from different days.
• ad_impressions: Number of times the ad was displayed.
• ad_clicks: Counts the times the ad was clicked by users.
• ad_spend: The amount spent on the ad.
• ad_revenue: Revenue generated from the ad.
• ad_conversions: Indicates the number of conversions (like sign-ups, purchases, etc.) resulting from the ad.
• ad_orders: Number of orders placed due to the ad.

The calendar table is a cornerstone for robust time intelligence in DAX. It’s structured as follows:

• Date: A sequential list of dates.
• Year: The year corresponding to each date.
• Month Num: Numerical representation of the month, where January is 1 and December is 12.
• Month: Full name of the month.
• MMM-YYYY: A compact representation of the month and year.
• Day Number: Numerical representation of the day of the week, with Sunday typically represented as 1 and Saturday as 7.
• Day Name: The full name of the weekday.
• Quarter: The fiscal quarter the date belongs to.
• WeekNumAcc: An incremental number representing the week, essential for making week-to-week comparisons.
• Week Name: Combines the year and week number, useful for providing a clear reference to a specific week.

In our analysis, the “Week Name” column will be pivotal for creating slicers, allowing users to filter data by specific weeks. Meanwhile, “WeekNumAcc” will be our tool for drawing week-to-week comparisons, ensuring we can observe trends and performance changes over time seamlessly.

To wrap up our session today, we’ll transition our datasets into dynamic Excel tables. Subsequently, we’ll integrate these tables into Excel’s data model, laying the groundwork for more advanced analytics.

For those eager to follow along, you can download the dataset from this link. Having the data on hand will provide a practical experience as we navigate through each step together.