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:
And we want to calculate the duration of campaigns, regardless of how many are run simultaneously:
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:
- spot length
- start date
- end date
It looks a bit like the following:
The solution involves parameter-based filtering, joining the data to a full set of calendar dates, and layered Table Calculations.
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:
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.
- 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
- 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:
And here it is in Sheet5 in the workbook:
- 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
- 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.
- 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.
- 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.
- A combination dashboard is then used to display the Gantt chart for clarity as well as the final result.