14 Replies Latest reply on Mar 9, 2016 2:43 PM by Gerry Zakharian

    Aggregate over a group with a Condition

    Gerry Zakharian

      Hello Everyone,

       

      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!

        • 1. Re: Aggregate over a group with a Condition
          Adam Crahen

          Hi Gerry-

           

          First I calculated day 1, 3, 7, 30 and 90 for every vertical/channel

           

          Day 1

          {fixed [Vertical], [Channel Name] : min([Metric Date])}

           

          DAY 3

          {fixed [Vertical], [Channel Name] : min([Metric Date])} + 2

           

          etc.

          2016-02-19_21-03-05.png

          Then I calculated the day 1 , 3, 7, 30, and 90 views.

           

          Day 1 Views

          IF [Metric Date] = [Day 1] THEN [Views] END

           

          etc.

          2016-02-19_21-12-18.png

          These are based on LODs so you can strip the detail if you want

          2016-02-19_21-13-30.png

          You change the aggregation from SUM to AVG.  I wasn't sure if you want them all in one view or maybe a 2nd table below so you had a row of totals and a row of averages.

          2016-02-19_21-16-39.png

          And I've now just read the 2nd issue...Ooops.  Maybe you can work that video date logic into what I've done above.

           

          9.1 workbook attached.

          1 of 1 people found this helpful
          • 2. Re: Aggregate over a group with a Condition
            Adam Crahen

            Hi Gerry-

             

            Give this version a shot.  I change the LODs to be calculated for each video.  I made day 1 equal to the video date and then added the days for the next date buckets.

             

            2016-02-19_23-50-30.png

            1 of 1 people found this helpful
            • 3. Re: Aggregate over a group with a Condition
              Gerry Zakharian

              Hi Adam,

               

              This is great - Thank you very much!!

               

              This shows activity for individual days. How would I go about also getting the cumulative totals.

              Meaning - Day 3 would equal the sum of the views for Day 1, 2, and 3.

               

              Thanks again!

              • 4. Re: Aggregate over a group with a Condition
                Adam Crahen

                Now that we have those days identified, it's just a matter of putting <= instead of just equal in the formula.

                 

                2016-02-22_19-47-37.png

                2016-02-22_19-49-06.png

                9.1 workbook attached.

                • 5. Re: Aggregate over a group with a Condition
                  Gerry Zakharian

                  Great! That makes sense.

                   

                  I'm now running into a different problem...

                  So, the main purpose of this exercise is to get views by series. The hierarchy is as follows:

                  Vertical -> Channel -> Series: Day 1 views, Day 3 Views, etc.

                   

                  Apologies for my lack of clarity in my original post - Almost all of the information is pulled from the YouTube API and stored in a SQL database. The only information that is not being automatically pulled is the series. I manually map the series by video in Excel and update it monthly. I placed the series name in column A and the Youtube Video ID (which would be the constant to identify the link between the two data sources) and connected both data sets to Tableau.
                  I've been able to run all of the calculations right up until I add in the series. Everything goes grey and it will not update the table.

                   

                  Thoughts?

                  Am I doing something incorrectly with the series mapping?

                   

                  Thank you again for all of your help on this!

                  • 6. Re: Aggregate over a group with a Condition
                    Adam Crahen

                    If you are manually mapping the titles to a series in Excel, why not just manually group them in Tableau?

                     

                    Right click on video title and select create->group

                    2016-02-23_19-55-16.png

                    If you check the include other box, new titles will fall into Other until you map them to a specific group.

                     

                    2016-02-23_19-58-15.png

                    • 7. Re: Aggregate over a group with a Condition
                      Gerry Zakharian

                      Thank you for the quick reply.

                       

                      I looked into manually grouping a bit, but I think it would be too labor intensive. We have a large historical library of ~60,000 videos. We also plan on ramping up production and expect to be uploading ~700 videos per month this year between our O&O and Partner Channels.

                       

                      Manually mapping in Excel is somewhat easy because I can apply multiple filters and map a large number of videos in one shot. That is why I was hoping to just update the series mapping data file once per month and have it recalculate.

                       

                      Is there any way to use the two data sources?

                      • 8. Re: Aggregate over a group with a Condition
                        Adam Crahen

                        Makes sense not to manually group 60,000 records!

                         

                        Any chance you might be able to post a sample workbook with the two data sources?  Hard to tell from your description what is going on.  Blending adds some complexity, which source is primary, what are the relationships, etc. 

                         

                        I think you'll get a better answer if we can see what the problem is.  Thanks.

                        • 9. Re: Aggregate over a group with a Condition
                          Gerry Zakharian

                          Hi Adam,

                           

                          (attached)So this is weird - I reconstructed the two separate data sets in excel and then recreated the view splits and calculated fields. In this file, everything works.

                          In my file, where I'm accessing the SQL data base and mapping the series with an Excel file, it does not work.

                           

                          Thoughts?

                           

                          Thanks again!

                          • 10. Re: Aggregate over a group with a Condition
                            Adam Crahen

                            Try extracting the SQL and Excel data connections instead of using a live connection.

                            1 of 1 people found this helpful
                            • 11. Re: Aggregate over a group with a Condition
                              Gerry Zakharian

                              It worked!!

                               

                              Thank you so much for all of your help on this!

                              • 12. Re: Aggregate over a group with a Condition
                                Gerry Zakharian

                                Hi Adam,

                                 

                                Back at it again...

                                 

                                I'm having a different issue now. Here is a copy/paste from my original post above...

                                Aside from just being able to pull those metrics 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.

                                 

                                So, the production team who originally created the calculated "Video Date" did it incorrectly and only some of the videos have a true publish date. I would feel more comfortable if I was able to generate a calculated field in Tableau that determines the publish date.

                                 

                                Would you be able to help me with a formula to determine this? I tried several different different approaches and none worked.

                                 

                                Recap: I cannot rely on "Video Date" as a true publish date. I need a formula to determine the first day that each video exceeds 500 views. This would establish a "Published Date" and would enable me to calculate true averages. I believe this can be calculated using the "Metric Date" field.

                                 

                                Thoughts?

                                Thank you in advance for your time, Adam Crahen !

                                • 13. Re: Aggregate over a group with a Condition
                                  Adam Crahen

                                  Hi Gerry-

                                   

                                  Try this calc for Day 1.  If you update the Day 1 formula everything else should update.

                                   

                                  Day1

                                  {FIXED [Video Title] : MIN(IF [Views] > 499 THEN [Metric Date] END)}

                                  • 14. Re: Aggregate over a group with a Condition
                                    Gerry Zakharian

                                    So sorry - I thought I had replied to this.

                                     

                                    It worked!

                                    Thanks again for all of your help!