Data set with events on some, but not all dates - need to generate rows for all dates

[andreasledet]

Hi there, 

This issue is a bit difficult to explain, but I'll try my best. 

I'm building a Google Data Studio dashboard with a graph combining digital ad data and Spotify streaming data. The purpose is to see whether streams of a certain track increases on the days where we have run ad campaigns for the track in question. 

I get the streaming data from Big Query and the marketing data comes from various sources and is then put together in Alteryx. 

Now here is the problem. When combining two data sources in Google Data Studio in a graph, the data is being joined by date. This means that for the data join to work, there has to be a date match between the data. Right now, a track released in May will have streaming data for all dates since the release, but we may only have run ads on a total of 10 days. Which means that on the 10 dates that have both streaming and ad data, the graph works, but for the remaining dates, it's blank. 

The fix for this would be to generate rows for all campaigns for every date YTD, which will have the campaign name and date but then a 0 in the spend column. 

Here's an example of how the ad data might look now:

Campaign	Spend	Impressions	Date
Track xyz	100	75000	2018-05-06
Track xyz	100	72000	2018-05-07
Track xyz	50	40000	2018-05-11
Track xyz	200	12000	2018-05-13
Track xyz	200	13000	2018-05-14

And this is what it should look like:

Campaign	Spend	Impressions	Date
Track xyz	100	75000	2018-05-06
Track xyz	100	72000	2018-05-07
Track xyz	0	0	2018-05-08
Track xyz	0	0	2018-05-09
Track xyz	0	0	2018-05-10
Track xyz	50	40000	2018-05-11
Track xyz	0	0	2018-05-12
Track xyz	200	12000	2018-05-13
Track xyz	200	13000	2018-05-14

And then preferably with all dates YTD for each campaign. I have a couple hundred different campaigns. They are all stacked in the same dataset. 

[DavidP]

Hi there,

I think this is what you're looking for. When combining it all, you just need to assign the right campaign to every set.

[andreasledet]

Thanks! Looks very promising.

But do I need to manually enter each campaign in the Formula tool after the Join tool? With several hundred campaigns and new ones being added every week, it would be nice to have the campaign column fetch the names automatically in some way. 

[DavidP · Accepted solution]

Ok, here's a slightly updated version. I modified the sample data slightly to represent 3 different campaigns. Using the unique tool, we can get a list of all campaigns from the data and then use the append field to create a record for every date for every campaign. We now use this to join to the known campaigns (using both date and campaign as your join condition) and then you just write the zero values for Spend and Impressions.

Let me know if this is more what you want.

[andreasledet]

This one worked. Had some trouble getting the joining just right due to some messed up formatting between the different data sources, but it works very well now. 

Thanks a lot!