I have a fairly complex metric that I'm trying to pull and I keep hitting dead ends. I'm also somewhat new to Tableau so I'm not great with formula syntax yet.
Backstory: My company has a few YouTube Channels. Much like a TV Channel, we have multiple series within each channel. We would like to study the initial performance of each series. My product team has already set up an API call to pull metrics by video, by day and it is stored in a SQL server. I've pulled the data into Tableau and have done the vertical, channel, and series mapping. Lifetime total views were easily calculated.
Metric that I'm having an issue pulling: I'm looking for total and average views on Day 1, 3, 7, 30, 60, 90 after a video is uploaded. So, whether video was published 3 years ago or 3 months ago, I'm looking for total and average views on that first day, first three days, first seven days, etc.
My issue: Aside from just being able to pull those metric by series, I have one other issue. (This is where the condition comes in.) YouTube has a staging area. A content manager will upload a video, mark it as private, and view it a few times to make sure everything looks right. By doing that, YouTube will now show that this video exists and has recorded views whether or not this video is made public yet. When the video is officially published a few days later, it will have considerably higher views. YouTube reporting does not have any field that shows actual publish date. So, if I just pull from the first day that metrics are available, the true number will be incorrect and will skew averages and totals. The production team generated a calculated field that generates a "Video Date" which returns the first date that a video reaches at least 500 views. That needs to be Day 1.
Also, if the video is less than 90 days old, does anything need to be done regarding this calculation or would it automatically ignore it?
Thoughts? Is this possible? What is the best way to calculate the sum across the time spans?
I've also attached a small sample file.
Your input and help is greatly appreciated!
Series Sample Set.twbx 138.3 KB