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 Metric Builder 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 fans counts, showing the name of each profile and the total fans of each within the selected timeframe. The query, which would go in the QQL Console, 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, scroll down to the Config box. 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.

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’.

widthRatio

“widthRatio” 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 in the Results:

Screen_Shot_2017-09-03_at_4.25.24_PM.png

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

hidden

hidden 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. You can also open the setting for the widget and unhide the column without having to change any code.

type

To get a good sense of the different types and what they actually mean, it would be very useful to go into the Metric Builder of some of our default metrics and check out 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.

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.

serverSide & paginate

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.

paginate
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.

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
},

summary.png

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"
}
]
},

Did this answer your question?