Custom Social Media Metrics With QQLRecently we introduced QQL, our own Social Media Analytics Query Language. Using QQL you can go beyond existing metrics and easily map your individual goals and KPIs into social media metrics. As we continue to develop and expand QQL continuously we made the next iteration step. It is now possible to create as many custom metrics as you like. These custom metrics can then simply be added to any of your custom dashboards and thus integrated into your reports and exports.
Today most companies are formulating different KPIs to evaluate success of their social media activities and campaigns. Therefore it becomes important to set up a metric set for your specific needs.

Custom Social Media Metrics With QQL By Example

For a better understanding of QQL and to develop your own ideas, I would like to present three individual custom social media metrics as an example.

Example 1: Facebook Total Post Likes

This example is a query for starters. It can be easily adapted to other social networks. The metric shows the total likes of all Facebook posts of a page or of a created group per day, week or month in a line chart or every chart type you would like to use. Of course, not every chart type makes sense here.
Custom Social Media Metrics - Facebook Total Post Likes
The related QQL query:

SELECT profileId AS dim1, time AS dim2, ownPostsLikes FROM facebook

As an example, I took a group of sports brands. The selected data period covers 6 months and shows the total likes for all posts a page published within a month. So, Puma generated over 131k likes in March 2014. The Global Facebook page for adidas has its highest number of post likes in the current month of July. This clearly can be linked with FIFA World Cup and the many national teams with adidas as sponsor.

Example 2: Own Tweets By Time

Next one is an intermediate query. This query plots the own tweets by time for a single Twitter profile or group. For this metric it is recommended to use the bubble chart as visualization type since the query is formatting time buckets per day and hour.
Custom Social Media Metrics - Own Tweets By Time
The related QQL query:

SELECT 'Own Tweets' AS dim1, STRFTIME('%H', time) AS dim2, STRFTIME('%w', time) AS dim3, SUM(ownTweets) AS dim4 FROM twitter GROUP BY dim1, dim2, dim3

For this query I chose the Twitter profile of Starbucks and considered all tweets over a data period of one year. Please note, my accounts time zone is Berlin/Europe, so it will look different for e.g. US/Central and others. With this metric you can see if a brand/profile tweets throughout the day or in specific time and day patterns.

Example 3: Facebook Response Time Distribution Development

The third query is something for experts.  Although the query itself looks not very complex, it is the metric config which is challenging. In the config you need to enable the post processors for the exporting and reporting formats like JSON, CSV, xlsx(Excel) and pptx (PowerPoint).  Since this metric is plotting the response time buckets of Facebook pages over time it makes sense to choose the stacked area percent chart. Furthermore, a monthly view is recommended in order to avoid scattering effects. Those effects can occur if there are no user questions for a certain day.
Custom Social Media Metrics - Facebook Response Time Distribution Development
The metric config:

{
    "config": {
        "qqlQuery": "SELECT time AS dim1, SUM(responseTime0To2h) AS '0-2 h', SUM(responseTime2To8h) AS '2-8 h', SUM(responseTime8To24h) AS '8-24 h', SUM(responseTimeLongerThan24h) AS '> 24 h', SUM(responseTimeNotResponded) AS 'Not responded' FROM facebook GROUP BY time",
        "postProcessors": {
            "json": [
                "createDim2FromColumns",
                "swapDim1AndDim2",
                "groupByDimensions",
                "highchartsLineChart"
            ],
            "csv": [
                "createDim2FromColumns",
                "groupByDimensions",
                "export2dTableArray",
                "formatDates",
                "formatNumbers",
                "CSV"
            ],
            "xlsx": [
                "createDim2FromColumns",
                "groupByDimensions",
                "export2dTableArray",
                "formatDates"
            ],
            "pptx": [
                "createDim2FromColumns",
                "groupByDimensions",
                "export2dTableArray",
                "formatDates"
            ]
        }
    }
}

The related QQL query:

SELECT time AS dim1, SUM(responseTime0To2h) AS '0-2 h', SUM(responseTime2To8h) AS '2-8 h', SUM(responseTime8To24h) AS '8-24 h', SUM(responseTimeLongerThan24h) AS '> 24 h', SUM(responseTimeNotResponded) AS 'Not responded' FROM facebook GROUP BY time

To show the power of this custom social media metric example, I’ve looked at the page of Mercedes for a period of one and a half years. This is really fascinating as you can exactly discover when the Mercedes Facebook page started to built up its social media support for user questions via Facebook.

QQL = Endless Capabilities To Map Your KPIs

These examples are illustrating what a powerful tool QQL can be for a meaningful social media analysis. If you need help with setting up your custom social media metrics just write us an email and we’re happy to help.
In fact, the possibilities of QQL are nearly endless. The current data sources are depending on our integrated social networks, but in the future we’re planning to integrate some external data sources to enable you to create even more sophisticated queries and social media success analyses.