Track your weekly marketing campaign metrics using Dax in Excel — Part 5 — Dynamic Keywords Ranking

As we progress in our series, the need to extract insights from our data becomes more nuanced. After successfully setting up dynamic KPIs in the previous segment, now we’re turning our focus on the evolution of keyword performance. Specifically, understanding how keyword rankings fluctuate from one week to the next can unveil patterns, anomalies, and strategic opportunities.

Laying the Groundwork with a Calculated Column

First, let’s enhance our tb_keywords table. By adding a calculated column named RelatedWeekNumAcc, we create a link with our calendar table. This allows our model to easily reference the corresponding week number for each keyword's performance data. Here's the DAX formula for this calculated column:

=RELATED(Tb_Calendar[WeekNumAcc])

Shining a Spotlight on Keyword Rankings

Ranking keywords based on our dynamic KPIs is paramount. Let’s craft measures that not only rank keywords for the selected week but also contrast their performance with the previous week.

Keyword Rank for the Selected KPI Measure:

KeywordRankOverSelectedKpi:=
VAR wk = MIN(Tb_Calendar[WeekNumAcc]) 
Var rnk = CALCULATE( 
                   RANKX( 
                        FILTER(all(tb_keywords[keyword_text],tb_keywords[RelatedWeekNumAcc] ) , 
                                    tb_keywords[RelatedWeekNumAcc] = wk 
                                     )
      , [SelectedKPI],,DESC) )

Var rnkAllWeeks = CALCULATE( 
                   RANKX( 
                        FILTER(all(tb_keywords[keyword_text]) , TRUE() ) 
      , [SelectedKPI],,DESC) )

VAR Result =   if( [SingleWeekSelected], rnk, rnkAllWeeks )
RETURN Result

With KeywordRankOverSelectedKpi, we rank keywords based on the currently selected KPI. If a single week is chosen, the ranking corresponds to that specific week. In scenarios where multiple weeks are in view, the measure provides an aggregate rank.

Keyword Rank from the Previous Week Measure:

KeywordRankPrevWeekOverSelectedKpi:=VAR wk = MIN(Tb_Calendar[WeekNumAcc])  -1
VAR rnk = CALCULATE( 
                       RANKX( 
                            FILTER(all(tb_keywords[keyword_text],tb_keywords[RelatedWeekNumAcc] ) , 
                                         tb_keywords[RelatedWeekNumAcc] = wk 
                                         )
          , [SelectedKpiPrevWeek],,DESC) )
 VAR result =  IF([SelectedKpiPrevWeek]=BLANK(),BLANK(),  if ([SingleWeekSelected], rnk, BLANK() ) )
RETURN result

KeywordRankPrevWeekOverSelectedKpi does a similar job but for the week preceding the current selection. This measure is crucial for our subsequent comparison. Should there be no data for the previous week, it thoughtfully returns a blank, ensuring clarity in our visuals.

Rank Change Between Weeks Measure:

RankChangeFromPrevWeekForSelectedKpi:=
 IF([SelectedKpiPrevWeek] = BLANK(), 
    BLANK(), 
    [KeywordRankPrevWeekOverSelectedKpi] - [KeywordRankOverSelectedKpi])

RankChangeFromPrevWeekForSelectedKpi is where the magic happens. By subtracting our current week's rank from the previous week's, we get a clear picture of a keyword's trajectory. A negative value indicates a rise in rank, and vice versa.

Putting It All Together — A Snapshot View

With our newly added DAX measures and calculated columns, our PowerPivot data model is now capable of delivering insightful, week-over-week comparisons. By integrating this intelligence into a Pivot Table, we’re better equipped to make informed decisions based on the dynamic nature of our advertising data.

A detailed view of a digital dashboard displaying weekly keyword performance metrics. The data showcases ad clicks, ad spend, and associated campaign names, alongside rankings and changes for each keyword over a specified timeframe.

From the image above, you can observe the PivotTable detailing keyword ranks, changes from the previous week, and the associated KPI metrics. Furthermore, the use of slicers greatly augments user interaction, enabling the filtering of data based on specific keywords or campaign names. This not only gives a comprehensive overview of individual campaigns and keywords but also serves as an intuitive and user-friendly interface.

In the subsequent steps, our primary focus will be on enhancing the visual aesthetics of the Pivot Table. We aim to create a presentation that is not just informative but also visually appealing, ensuring clarity, ease of use, and actionable insights for end-users.

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.