Creating Custom Tables

Our table visualization is one of the most used data representations within our tool, and it offers a lot of flexibility built right into the system. The most important part is, that beside the QQL query, a table metric heavily relies on the additional config, holding information like column names, column widths, and the column type. Our guide on using the QQL Console is recommended before moving on.

The data fetching and visualization within the table can be handled completely separate. First we will focus on how to load the data.

Loading data to visualize within a table

This is something that is fairly simple for tables, as the result of a SQL query is in a table format anyways, returning a set of rows holding information for a set of columns. When you start writing a query for a table, just make sure the query output (which can be verified looking at the “Query Result” tab) matches exactly what you would like to put into the table.

A simple example could be a table of fasn counts, showing the name of each profile and the total fans of each within the selected time frame. The query might look as follows:

SELECT name, fans FROM facebook INNER JOIN profiles ON facebook.profileId = profiles.id

Building the table itself - the config

First we need to set the visualization type to “table”. Then, click on ‘edit config’.

The config is very important when it comes to creating tables. This is where you add and specify the metadata.

Metadata is defined as data that describes other data. Essentially, the metadata specifies how we want the data (in this case, the QQL we just wrote) to be visualized. If you are creating your metric from scratch or are editing an existing metric that is not a table, there will not already be metadata in the config.

How to add metadata

In the config, you will see the QQL Code you just wrote in quotes “ “. After the end quote, you’ll want to add a comma, followed by

"metaData": {
     "columns": [
     {
        "id": "name",
        "title": "Name",
        "width": 0.6
     },
     {
        "id": "fans",
        "title": "Fans",
        "width": 0.4,
        "type": "integer"
     }
     ],
     "serverSide": false,
     "paginate": false,
     "sortBy": "fans",
     "sortDir": "DESC"
      }

Each id, title, width, type (when included) and hideOnDashboard (when included) corresponds to a single column you want in your table. The example above will have 2 columns: The first will be titled “Name” and the second will be titled “Fans”. Because the fans count is an integer, we need to specify this in type. A list of all possible types for each network is listed at the bottom of this article.

id

This id has to match one of the columns specified by the QQL query. This includes renames or ‘aliases’. For our example, the ids here are just ‘name’ and ‘fans’. However, if in the QQL code we had written ‘fans AS fanCount’ then we would put ‘fanCount’ as the id for the Fans column.  Note: id is case-sensitive.

title

This is how you want the title of the column to be displayed in your visualization. Unlike the aliases/ids, the title can have spaces in it. For example, the title for the Fans column could also have been written as ‘Fans Count’.

width

“width” is where you specify how wide you want the column to be. The sum of all the widths must equal 1.0 (100%). In the above example, the Name column is 0.6, meaning it will take up 60% of the table. Fans is 0.4 (40%), meaning it will take up the other 40% of the table. If the sum of the widths do not equal 100%, the config will still save. However, when you try to run the query, you will get an error message. For example, if Name was 0.6 and Fans was 0.5, this error would appear:

Screen Shot 2016-09-26 at 11.54.08 AM.png

Simply go back to ‘Edit config’ and adjust the widths so the sum is 100%.

hideOnDashboard

If “hideOnDashboard” is not specified for a column, it is assumed to be false. If this is false, then the column will be displayed when you add this custom metric to your dashboard. If this is true, then the column will not be displayed for this custom metric on the dashboard. However, in the Query Result, Visualization Preview, the full-page view, and the exports for the metric will all show the column.

This setting is really helpful because the main idea is that dashboard contexts usually need to handle smaller widths per metric as there are multi-column layouts. Therefore, this option helps to show only certain columns of a table in the dashboard context.

type

To get a good sense of the different types and what they actually mean, it would be very useful to go into the QQL Console of some of our default metrics and open up the config. This way you are able to see what types we use for different columns and what sort of representation they result in.

Basic Types

integer:
The column must return a number. If the number is an integer, that integer will show. If the number is a decimal, all values after the decimal point will be cut off (Note: This will not round e.g. If the column would return 2.7 but type is set to integer, then simply ‘2’ will be displayed.)

Example integer columns: facebook.fans, instagramOwnPosts.likes, twitter.OwnTweets

decimal:
The column must return a number. If the number is an integer, it will still be displayed as an integer (e.g. 2 would displays as 2, not 2.0).

percent:
The columns must return a number. This simply adds a percentage sign (%) at the end of the value. So if the number is a decimal and you select the type in the metadata to be a percent, the system does not convert the decimal into a percentage. E.g. 0.21 would display as 0.21%, not 21%.

Example percent columns: interactionRate, responseRate

string:
The columns must return text.

Example string columns: userName, type

boolean:
The columns must return 1 of 2 options e.g. Yes/No, True/False, 1/0

Example boolean columns: isQuestion, isRetweet, isPotentiallyPaid, isCollaboratorBoard

country:
The countryCode columns must have type country in the metadata.

responseTime:
The following columns require a responseTime type in the metadata:

responseTime0To2h
responseTime2To8h
responseTime8To24h
responseTimeLongerThan24h
responseTimeNotResponded
timeToRespond

Facebook Types

facebookOwnPost:
Use this with the ‘createTime’ id. This combines the pageName (name), createTime (time), message, and link into 1 column. This is the first column you see in our default Facebook Own Posts Table.

facebookOwnPostWithAttachment:
Use this with the ‘createTime’ id. This combines the pageName (name), createTime (time), message, link,attachmentDescription, attachmentHref, attachmentCaption, and attachmentPreviewPicture

facebookUserPost:
Use this with the ‘createTime’ id. This combines the pageName (name), createTime (time), userName, message, and link into 1 column. This is the first column you see in our default Facebook User Posts Table.

facebookQuestion:
Use this with the ‘createTime’ id. This combines the pageName (name), createTime (time), userName, message, and link into 1 column. This is the first column you see in our default Facebook Questions Table.

isSponsoredPost:
Use this with the ‘isPotentiallyPaid’ column.

Instagram Types

instagramPost:
Use this with the ‘createTime’ id. This combines the pageName (name), createTime (time), message, and link into 1 column. This is the first column you see in our default Instagram Posts Table.

Twitter Types

twitterTweet:
Use this with the ‘createTime’ id. This combines the pageName (‘@’ || platformUsername), createTime (time), message, and link into 1 column. This is the first column you see in our default Twitter Own and Retweeted Tweets Table.

twitterMention:
Use this with the ‘createTime’ id. This combines the pageName (‘@’ || platformUsername), createTime (time), userName (‘@’ || userName), message, and link into 1 column. This is the first column you see in our default Twitter Mentions Table.

twitterQuestion:
Use this with the ‘createTime’ id. This combines the pageName (‘@’ || platformUsername), createTime (time), userName (‘@’ || userName), message, and link into 1 column. This is the first column you see in our default Twitter Questions Table.

Pinterest Types

pinterestPin:
Use this with the ‘createTime’ id. This combines the pageName (name), createTime (time), message, and pinLink into 1 column. This is the first column you see in our default Pinterest Pins Table.

pinterestBoard:
Use this with the ‘createTime’ id. This combines the pageName (profiles.name), createTime (time), boardName (pinterestBoards.name),  message, and pinLink into 1 column. This is the first column you see in our default Pinterest Boards Table.

LinkedIn Types

linkedInStatusUpdate:
Use this with the ‘createTime’ id. This combines the pageName, createTime, message, and link into 1 column. This is the first column you see in our default LinkedIn Updates Table.

YouTube Types

youtubeVideo:
Use this with the ‘publishTime’ id. This combines the pageName (name), title, publishTime, and link into 1 column. This is the first column you see in our default YouTube Videos Table.

youtubePlaylist:
Use this with the ‘publishTime’ id. This combines the pageName (name), title, publishTime, and link into 1 column. This is the first column you see in our default YouTube Playlists Table.

Google+ Types

googlePlusPost:
Use this with the ‘createTime id. This combines the pageName (name), createTime (time), message, and link into 1 column. This is the first column you see in our default Google+ Posts Table.

serverSide & paginate

pagination:
When this is ‘true’, it activates the "paging" over pages of 25 items each. So entries are cut after 25, and the next posts will be on the following page. This has no relation to the serverSide setting. When it is ‘false’, all results will show on the same page.

serverSide:
This specifies if follow-up requests are sent to the server-side, or if these are handled within the browser (in JavaScript) instead. serverSide=false only makes sense if also the full data set was sent initially.

If serverSide=true, all follow-up requests (this includes when clicking a column header for sorting, or when clicking PREV or NEXT for paging) will be sent to the server-side instead. This is mostly a necessity for all metrics having bigger data sets, as these cannot be handled locally. You'll see that all posts or comments tables have serverSide:true.

On the other hand, metrics like Key Metrics Table, with a low number of rows guaranteed (it’s bound to the number of profiles which is never above a few hundreds), will usually have serverSide: false.

To sum it up, tables you will have a large number of rows for are best to have paginate and serverSide both true. However, if you need to be able to sort by different columns or you are sure you won’t have a lot of results, then serverSide=false would be okay. paginate=true will result in a page for every 25 results. paginate=false will display all results on one page.

Note: if paginate is false, serverSide must be false.

sortBy & sortDir

sortBy is where you specify which id you would like to sort the table by, and sortDir is where you specify in which direction you want it sorted: either ascending (ASC) or descending (DESC). In the example above we sort by ‘fans’ DESC. This means the table will list the profiles and their fan count from largest fan count to smallest.

Advanced Metadata - Summary Line

You have the option to add a summary line to the bottom of your table showing the sum or average value of each column. ∑ represents a sum while x̅ represents an average. 

Note: Currently, the summary line does not work for tables with "paginate:  true" 

Default Summary Line

The simplest way to activate the summary line is as follows: 

"summaryLine": {
            "enabled": true
},

This will apply to all columns and will sum or average the values depending on the column type. Integers and Booleans will be summed, while the other types (percent, decimal, etc.) will be averaged. 

Customized Summary Line

You can also add more detail to how you want the summary line, like which columns to apply it to and whether you want sum or average for each column.

"summaryLine": {
        "enabled": true,
        "columns": [
                 {
                         "id": "totalFans",
                         "type": "sum"
                 },
                 {
                         "id": "fanIncrease",
                         "type": "average"
                }
         ]
},

In this example, only the column showing the Fans count and Own Posts count will have a value in the summary line. Fans will show a sum, adding up all the Fan values from each row. Own Posts will show the average, averaging all the Own Posts values from each row. 

Tips

If you get an Invalid Configuration error when trying to save the config, don’t fret. It is often a small mistake causing this error. A misplaced or missing comma is usually the case.

Always double check that the ‘id’ you put in the config matches how you wrote the id in the QQL Console. Remember, this is case-sensitive. 

Especially when you’re just starting out creating your own custom metrics, we recommend editing an already existing metric rather than starting from scratch. This way, there is already metadata in the config and you can just change which columns you want to include and add/remove others. To edit an already existing metric, simply click on the question mark (?) at the top right corner of the metric, then scroll down to the QQL code and click on ‘Open in console'. 

Let’s edit a couple of popularly used tables to make them our own.

Example 1: The Facebook Own Posts Table

Here we are going to edit the Facebook Own Posts Table in 3 ways: 1. Add a column that shows the total interactions for each post including reactions (likes, comments, shares, haha, wow, angry, sad, & love), 2. Edit the interaction rate column so it shows the value of interaction rate calculated to include reactions, and 3. Remove the ‘Sponsored?’ column.

Writing the QQL

This is the QQL we see when we first open the Facebook Own Posts Table in the QQL Console:

SELECT name AS pageName, time AS createTime, message, link, likes, comments, shares, PRINTFNULL("%.4f",interactionRate) AS interactionRate, type, isPotentiallyPaid FROM facebookOwnPosts INNER JOIN profiles ON facebookOwnPosts.profileId=profiles.id

1. Adding a ‘Total Interactions’ column

While you can insert this calculation anywhere within the SELECT statement, I am going to put it after ‘shares’ because that’s where I’ll want the column located later on and it helps me organize the information better. To tell the system we want to load this data, we will write the following:

(likes+comments+shares+love+haha+wow+sad+angry) AS interactions

2. Editing the ‘Interaction Rate’ column

First, we remove ‘interactionRate’ from the PRINTFNULL command. It is then replaced by:

(likes+comments+shares+love+haha+wow+sad+angry*1.0)/fans*100

Here, we are taking the total interactions and first multiplying it by 1.0 (this is just so we get a decimal point included in the result). Then, divide by the fans count. We then multiple by 100 because we are displaying this value as a percent. If you wish to display it as a decimal, leave out the *100, but be sure to change the ‘type’ for the Interaction Rate column in the config from ‘percent’ to ‘decimal’.

Because we have included Facebook Reactions, your new interaction rate should be greater than or equal to the previously calculated interaction rate.

3. Removing the ‘Sponsored?’ column

Removing a column in the QQL Console is easy: simply deleted the corresponding code. In this case, we would delete:

isPontentiallyPaid

The new QQL Code

This is what should now be written in our QQL Console:

SELECT name AS pageName, time AS createTime, message, link, likes, comments, shares AS shares, (likes+comments+shares+love+haha+wow+sad+angry) AS interactions, PRINTFNULL("%.4f",(likes+comments+shares+love+haha+wow+sad+angry)/fans*100) AS interactionRate, type,  FROM facebookOwnPosts INNER JOIN profiles ON facebookOwnPosts.profileId=profiles.id

Editing the config

Now that we have written the QQL Code for our customized table, it’s time to edit the configuration.

EditingTheConfig.gif

As you can see, we added another column for the Total Interactions. Nothing needed to be changed for the I-Rate column because although we changed the formula, the id name and data type remained the same. However, I did change hideOnDashboard to false as I want to see the I-Rate on the dashboard. This is completely optional. Note you can also just remove hideOnDashboard from the config to make sure that column is visible on the dashboard. Lastly, we removed the column metadata for the Sponsored? column. Because the total number of columns remained unchanged (added 1, removed 1) we did not have to edit the widths to get the sum of 100%.

Once you successfully save the config edits, it’s now time to see if the visualization looks how you want. Click on ‘ Run QQL’. Depending on the type and amount of data, it may take awhile to run. When you’re all done, the visualization preview should display your new table. Now click on ‘Save As’ and give a name and description to your brand new custom metric!

Example 2: Creating a Filtered Instagram Posts Table + Content

Here we are going to edit the Instagram Posts Table in 2 ways: 1. Filter the posts to only include posts that have the hashtag #regram or the word ‘repost’ 2. Display the content attachment with the post. Below is an example of what this customized table will look like:

zQ9qzBUXDf.gif

Writing the QQL

This is the QQL we see when we first open the Instagram Posts Table in the QQL Console:

SELECT name AS pageName, time AS createTime, message, link, likes, comments, PRINTFNULL("%.4f",interactionRate) AS interactionRate, type FROM instagramOwnPosts INNER JOIN profiles ON instagramOwnPosts.profileId=profiles.id

1. Adding the content attachment to the post

While you can insert this anywhere in the SELECT clause, I am going to put it at the end. All we need to add is:

attachmentPreviewPicture

Note that other networks may have different or multiple attachment data, but in Instagram’s case, we just have this preview picture.

2. Filtering the posts for the hashtag and word

After the FROM clause, we are going to add another clause: WHERE. The WHERE clause is where we’ll be able to specify what parameters the data must meet in order to be displayed. It will look as follows:

WHERE message LIKE ‘%#regram%’ OR message LIKE ‘%repost%’

Notice how I had to write message LIKE for each hashtag/word I wanted. Do not forget to include the % symbols. Without them, only results where #regram or repost were the entire message would appear. I chose to use ‘OR’ meaning the post has to have either the hashtag #regram, the word ‘repost’, or both. However, if I replaced OR with AND, only posts that included both the hashtag #regram and the word ‘repost’ would be shown.

The new QQL Code

This is what should now be written in our QQL Console:

SELECT name AS pageName, time AS createTime, message, link, likes, comments, (likes+comments) AS totalInteractions, clicks, PRINTFNULL('%.4f', interactionRate) AS interactionRate, type, attachmentPreviewPicture FROM instagramOwnPosts INNER JOIN profiles ON instagramOwnPosts.profileId=profiles.id WHERE message LIKE '%#regram%' OR message LIKE '%repost%'

Editing the config

Now that we have written the QQL Code for our customized table, it’s time to edit the configuration.

75rcgiOfNG.gif

There is little to change this time around. All we need to do is change the ‘type’ of the first column from “instagramPost” to “instagramPostWithAttachment”, and we’re done!

Once you successfully save the config edits, it’s now time to see if the visualization looks how you want. Click on ‘Run QQL’. Depending on the type and amount of data, it may take awhile to run. When you’re all done, the visualization preview should display your new table. Now click on ‘Save As’ and give a name and description to your brand new custom metric!

If you have any questions about this article or QQL in general, please don't hesitate to reach out to us :)

Still need help? Contact Us Contact Us