5 Replies Latest reply on Jul 21, 2014 9:05 PM by Andrew Drinkwater

    The Advertising Campaigns Dashboard Conundrum

    Nathanael Underwood

      How to calculate the duration of overlapping campaigns

       

       

      Issue:

      An advertising agency wants to know how many days a given campaign has been run for in a given country, regardless of how many advertisement lengths (15, 20, 30, 60 or 90 seconds) have been contracted out simultaneously. Some of these campaigns and spots will overlap.

       

      Here’s the Gantt chart of campaigns:

       

      fig 1.png

       

      And we want to calculate the duration of campaigns, regardless of how many are run simultaneously:

      fig 2.png

       

      Given how easy it is to display the data on a Gantt chart, this problem is a deceptively tricky one to resolve.

       

      The data provides, for every line, identifiers for the following:

      - saleID

      - country

      - campaign

      - spot length

      - start date

      - end date

       

      It looks a bit like the following:

      fig 3.png

       

      The solution involves parameter-based filtering, joining the data to a full set of calendar dates, and layered Table Calculations.

       

      Step 1

       

      There must be a complete calendar of dates in the data source. For every date in the calendar there must be at least one entry. Some days have no campaign start or finish events – there needs to be a line of data for these days, even if the country/campaign/saleID columns are NULL.

      In order to get around this, I implemented a left join on a data source with calendar dates extending beyond the range of the campaign dates, using the Start Date as a join clause with the date column of the calendar table. If there are enough dates in your data base to start with this is not necessary, but it might be an idea to find a way to do this, just to make sure that all bases are covered.

       

      Here is what the data connection looks like in Tableau Desktop:

      fig 4.png


      Step 2

      We are filtering per campaign and per country using parameters. It would be possible to implement the filter otherwise, but for the sake of obtaining a result in this demonstration, I used a couple of Parameters to return a Start and End value.

      fig 5.png


      Step 3

      • The number of days can then be computed for each campaign length. It is important to take the MAX() of the number of days as opposed to the SUM(). Taking the MAX() returns the longest campaign that occurs on a given date if multiple campaigns start on that date. This is shown in Sheet5 in red

       

      Step 4

      • Basing yourself on the above, a Table Calculation can be used to subtract days from the starting point of each campaign. When this count of remaining days reaches 0, the campaign is no longer running. (see attached – sheet 5 – Calculation1orange)

       

      Here is the Calculated Field window for Calculation1:

      fig 6.png


      And here it is in Sheet5 in the workbook:

      fig 7.png


      • A value of 1 is returned for values of Calculation1 above 0. This yields a 1 for each day that a campaign is active going down the table Calculation2 green

      fig 8.png


      • Calculation2 is then added across the table and a 1 is returned for values greater than 0, so that if multiple campaigns occur on a given day, only one day is counted. This is Calculation3  blue. This table calculation is implemented across the table.

      fig 9.png

      • Calculation3 is then summed down the table, yielding the total number of days that a given campaign has been run in a given country, skipping any interruptions. This is Calculation4 in black.

      fig 10.png


      • The table now needs to be tidied up using INDEX()functions across and down the table. These are used to filter the data being displayed without interfering with the underlying data query.

      fig 11.png


      • A combination dashboard is then used to display the Gantt chart for clarity as well as the final result.

      fig 12.png

       

      https://public.tableausoftware.com/views/campaignlength3/CampaignsDashboard

       

      Twitter @ngunderwood

        • 1. Re: The Advertising Campaigns Dashboard Conundrum
          Joshua Milligan

          Nathanael,

           

          Overall a good tutorial.  Thanks for sharing!  I love to see how others tackle these kinds of problems!

           

          A couple of thoughts:

           

          You shouldn't have to pad the data at the source.  That is, you don't need to join all the Dates table.  Tableau can pad that data in the cache (use the Show Missing Dates option).

           

          I may be missing something, but it seems that this could be simplified to two table calculations.  One would determine a 0 or 1 for each date if there was an active ad on that day -- it would work table down.  The other would work across the table and add up the first calc.  It would be partitioned by campaign.  Does this make sense?  Or did I miss a requirement?

           

          Would you be able to post a packaged workbook?  I'd love to see it in action!

           

          Regards,

          Joshua

          • 2. Re: The Advertising Campaigns Dashboard Conundrum
            Jonathan Drummey

            FYI Joshua, there's a link to the Tableau Public workbook at the bottom of the post. (I dunno if it was there initially or not).

             

            I agree that Show Missing Values could be used here to avoid the need for manual padding to a full list of dates. However, I don't think padding is necessary at all, I'll work on a solution when I can and post it here.

            • 3. Re: The Advertising Campaigns Dashboard Conundrum
              Jonathan Drummey

              Ok, I went at it.

               

              Here's a dashboard using no parameters (just scoped quick filters) so the list of campaigns and countries is totally dynamic, no domain padding aka Show Missing Values, and no padding outside Tableau:

               

              2014-07-21 13_51_10-Tableau - campaign length 3.png

               

              Besides being a lunchtime intellectual exercise and a little practice in table calcs, plus a demonstration of an alternative technique, another reason for me to post this is to hopefully provide some education on what can be done in Tableau when we approach it from a compositional perspective where we look at the absolutely necessary components of the view and take advantage of Tableau's features. Any time we can avoid padding a view, we are simplifying the setup and maintenance of the view. Any time we can swap out a parameter for a filter, we are enabling a more dynamic view and one that could very well be faster (Tableau won't cache calculations and maybe even entire views that involve parameters).

               

              So the start of my approach was to use scoped filters for Country and Campaign. To get rid of the padded rows, I added a Campaign (copy) dimension and used that to Exclude the Null values, then made the other filters Only Relevant Values.

               

              Looking at the data, the campaign days calculation doesn't actually care about the Spot Length dimension, all it needs to look at is the start date and end date of the campaigns, and it doesn't even need the individual campaigns in the view, just the Start Dates and End Dates. So only two dimensions are used in the view, and there are three calcs used to build this view, all have a Compute Using on Start Date & End Date (in that order). So this view only requires as many rows (addresses) as there are start & end date combinations in the data, whereas the other view requires (number of days in data) * 6 addresses. Having Tableau compute over fewer marks almost always results in performance increases.

               

              The Current Max End Date is used to carry forward the max end date through the rows:

               

              IF FIRST()==0 THEN
                  MIN([End Date])
              ELSEIF MIN([End Date]) >= LOOKUP(MIN([End Date]),-1) THEN
                  MIN([End Date])
              ELSE
                  PREVIOUS_VALUE(#1/1/1900#)
              END
              

               

              The Campaign Days calc is more complicated. Instead of using the padded data and a table calc that flags a given date with a 1 as being present or not in the data, then summing up those flags, this calc is looking at the start date, end date, and carried forward end date to determine whether to add to the total of Campaign Days. Given that the dates are always sorted by the Start Date, End Date because that's how the addressing is done, there are only 3 evaluations required. Whether we're in the first row of the partition/address, whether the start date is more than the prior carried forward end date, and whether there's a partial overlap.

               

              IF FIRST()==0 THEN
                  //first row
                  INT(MIN([End Date])-MIN([Start Date]))
              ELSEIF MIN([Start Date]) >= LOOKUP([Current Max End Date],-1) THEN
                  //current row starts on or after prior end date, so add the entire time
                  PREVIOUS_VALUE(0) + (MIN([End Date])-MIN([Start Date]))
              ELSEIF MIN([Start Date]) < LOOKUP([Current Max End Date],-1) AND MIN([End Date]) >= LOOKUP([Current Max End Date],-1) THEN
                  //start date is less than prior max end date, but this row extends the max end date, so add some more time
                  PREVIOUS_VALUE(0) + (MIN([End Date])-LOOKUP([Current Max End Date],-1))
              ELSE //just repeat the values
                  PREVIOUS_VALUE(0) 
              END
              

               

              (I'd previously had to do this kind of calc for a transit agency tracking individual passenger trips, so one bus run (campaign) could include many passengers (trips), so the logic was fairly fresh in my mind).

               

              This calc is essentially a variation on a running sum calc, and the last Start Date/End Date combo has the total Campaign Days. So the Last Filter calc with the formula LAST()==0 is used to filter for only that date.

               

              So that gets the # of Days view. The Gantt chart can then be built using Start Date as a continuous dimension on Columns, an INT(MIN([End Date])-MIN([Start Date])) on the Size Shelf, and sale ID to get the level of detail set:

               

              2014-07-21 14_05_18-Tableau - campaign length 3.png

               

              I also prepared a second Gantt using Show Empty Rows to show all the Spot Lengths in the data no matter what, I had to change how Start Date was used to turn off densification for that, you can see all that in the Gantt w/Show Empty Rows worksheet.

               

              Jonathan

              • 4. Re: The Advertising Campaigns Dashboard Conundrum
                Joshua Milligan

                Jonathan Drummey wrote:

                 

                ...approach it from a compositional perspective where we look at the absolutely necessary components of the view and take advantage of Tableau's features.

                Thanks Jonathan!  I love it when someone takes what was a fuzzy, semi-intuitive, "it-seems-right" kind of feeling that I have and gives it a concrete definition.  Great lesson!

                 

                Regards,

                Joshua

                • 5. Re: The Advertising Campaigns Dashboard Conundrum
                  Andrew Drinkwater

                  Very interesting. Thanks for sharing!