or How to retrieve your YouTube Data of your Tableau User Groups events

Tableau Public Viz

I wanted to list all our past Analytics Tableau User Group events, when I realised that additionally know who were our speakers and which topics we talked about, I would also like to have some Youtube data as the number of views and likes.

Of course, I could go to Youtube and copy paste those stats, but the idea is to have something refreshing automatically. I do not know how to use APIs and I could find any site explaining the step by step. But I found a free google sheet extension that I could use.

The one I used is called “API connector” https://mixedanalytics.com/api-connector and you can choose multiple application to connect. In my case, I choose “Youtube Public Data”. I used this extension 2 times :

To retrieve the Video ID for a playlist
To retrieve the Youtube Stats from the video ID list

Retrieve automatically the Video Ids

To retrieve all the videos from a playlist, you need to add as Endpoint “/playlistItems” and add the name of your playlistId ( required field). Then you only have to specify destination sheet, in my example I create a sheet called “Playlist2023”.

This request will give you a row for every videos on your playlist and several columns, amongst them the one that interested me : VideoId.

You can schedule a refresh of your data quite easily automatically, but that is not included with the free version, I have. But as I intend to refresh the dashboard monthly after each TUG, I could hit the “run” button and my dashboard will automatically refresh. The free version included 3 saved requests and 250 monthly request (see below).

Retrieve YouTube Data

The second request is also quite easy, as I have to specify the following :

Endpoint : “/videos”
id : all my videoId with comma separated
part : snippet and statistics

This query will give me for every Video I have on my playlist, the title, description and the latest views and like count.

The dashboard

I have added the information that is relevant for me as the speakers name and the topic categories to be able to build the viz.

I ended up creating a new datasource with all my speakers and I have also add a column with the image Url of each of them. You need to be sure that the url end up with something like .jpg or .png, otherwise the “image role” will not work. For instance, I couldn’t grab the URL for Linkedin profile but I could for twitter. As an example here is Christina’s photo link : “https://pbs.twimg.com/profile_images/1535742424076656641/0tyOgij2_400x400.jpg“

I usually used the Tableau Public profile url, but for some of my speakers, the link was not working or the picture was missing, so I searched the internet for a new one ! In very few occasion, I couldn’t find one, as for Samantha Lin for instance ( see below).

The dashboard used then some parameter actions to highlight the speakers speaking on a selected event, or to highlight the event in which a selected speaker spoke. I also used the parameter actions to reset the selection, so the user can also sort the events by YouTube Likes or Views.

In Tableau Server, I usually have a similar behaviour than in Tableau Desktop, so I was surprised that I couldn’t sort by clicking on the top of the my #Views column. Of course it is working if I clicked at the bottom, but that wasn’t the kind of behavior I wanted. I could also change the display, but like I said I really wanted to display it like this.

So I created a quick parameter to allow it from the top. If you click on Likes, the sort will be according to the likes counts and “↓” will disappear from #Views and reappears as “#Likes ↓”

I wrote a calculation that create 2 columns called selector for sorting MONTH([Date])%2, that gives 2 values 0 and 1( this method is called internal densification). I can then rename those values to number of views and likes.

But to go further, I want to give an indication to the user of what is sorting, so I created another calculation used on the label

if [selector for sorting]=0 then “# Views “+[sorting for YT view] else “# Likes “+[sorting for YT likes] end

The field [sorting for YT likes] for instance is only here to put a descending arrow in case the #likes is selected, it is calculated like this : if [Sorting by]=1 then “↓” ELSE “” end

How does it work?

I created a parameter called [Sorting by] that can take only 2 values 0 or 1, depending if you click on #views or #likes
I added a parameter action called “Change the sort”

The label for sorting calculation displays the arrow according to the selection
Last I added a calculation on the rows of the TUG List to sort according to the selection

if [Sorting by]=0
then RANK_UNIQUE(SUM([View Count]),’desc’)
ELSE RANK_UNIQUE(SUM([Like Count]),’desc’)
END