7 Replies Latest reply on Dec 21, 2018 1:29 PM by Jonathan Drummey

    The Advertising Campaigns Dashboard Conundrum

    Nathanael Underwood

      How to calculate the duration of overlapping campaigns




      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




      Twitter @ngunderwood

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



          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!




          • 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])


              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


              (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.



              • 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!




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

                  Very interesting. Thanks for sharing!

                  • 6. Re: The Advertising Campaigns Dashboard Conundrum
                    sandra cuentas

                    Hi Jonathan,


                    First, thanks for sharing this solution! I followed the steps and I was able to apply it succesfully in a similar problem. However, i'm having an issue i hope you can help me with. My problem involved the calculation of overlapping times in jobs done by a single agent. And then i calculated that value for every month. 


                    Basically i have an extra partition by month, so I have a table similar to the following one for every month:


                    Now i want to change the granularity of the view to show a line graph with the last value (filter=true) for every month in a year

                    Let's suppose that the table corresponds to January 2018, i would like to have a line graph showing January 2018 (x axis)-58 (y axis), February 2018 (x axis)-41 (y axis) and so on.


                    Is there a way to remove the dimensions of the view that are not neccesary while keeping the true filter value? LODs do not seem to be helpful in this case.


                    Thank you for your help!

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

                      Hi Sandra,


                      I'm just getting to this...in general, yes, this is possible. We'd need to keep the dimensions & table calculations in place and then add additional nested table calculations that use the prior results. I'd be glad to help if you're still looking for assistance, however given the complexity of the view & table calcs please put together a packaged workbook with some sample data and your work so far and I can take a look.


                      Level of Detail expressions don't lend themselves to this kind of analysis. We need to be looking across different values and carrying forward results i.e. LOOKUP() and PREVIOUS_VALUE() and at present (as of Tableau v2018.3) LOD expressions are doing a different kind of operation, namely aggregating within a partition.