Since questions written in English can interpreted in different ways, VQL is a more formal way of asking questions that specify what data should be retrieved.
Note: VQL is used by our Vidbeo Insights service. If you do not see an option labelled ‘Insights’ in the Analytics menu within your admin panel, it may not be enabled for your account. Please contact us.
Let’s start with the simplest query you could write: how many views have all my videos had? In VQL this is:
SELECT total FROM VideosAnalytics
Taking each part of this query in turn:
SELECT: you want to retrieve data, and so if you are familiar with querying databases, this done with a request to ‘SELECT’ it
total: you want the total number of views
FROM VideosAnalytics: we store the analytics for your videos in this database, so this is where the value needs to be retrieved from.
The result of running that query is a single number: that is the total number of views all your videos have had over all time, since no limitations have been applied in the query (such as by id, date, city and so on):
Let’s now make the query a bit more specific.
What if we want to know how many views a particular video has had this month? All videos are identified with a unique id which you will see in its URL. So we can narrow down the number we want back by asking for the total number of views there have been this month for the video with the id vabcdefgh like this:
SELECT total FROM VideosAnalytics THIS-MONTH WHERE id = vabcdefgh
You will see VQL supports a special option ‘THIS-MONTH’ which it spots and replaces with the dates of this month.
You will also see we have used another option: ‘WHERE’. ‘WHERE’ is common to query languages. We are saying we are only interested in results WHERE a condition matches. In this case, our condition is that the id (of the video) is equal to vabcdefgh.
The result of running that query is a single number: the total number of views this month that the video with id vabcdefgh has had:
Next, what if we want to know whether we are getting more views, or fewer (perhaps meaning we need to promote our videos more)? We can do a comparison within a single query using the ‘COMPARED-TO’ operator, like this:
SELECT total FROM VideosAnalytics THIS-MONTH COMPARED-TO LAST-MONTH
The result of running that query is two numbers: a primary, larger figure which is the result of the first query we are running: how many views have our videos had so far this month? However since we have then introduced a comparison, underneath that figure is a secondary, smaller figure. That shows the difference - in this case, how that figure differs compared to last month. If the figure is green, it has gone up by that amount. If the figure is red, it has gone down by that amount. Or if it is grey, it hasn’t changed:
However what if we want more than one number back? We might want to see how many views our videos have had per city, rather than in total. We can do that using the BY operator. Let’s do that by adding ‘BY city’ to our query:
SELECT total BY city FROM VideosAnalytics THIS-MONTH
The result of running that query is a table, since we have requested that the totals are returned by city. So our table has two columns, with each row containing the name of a city and the total views there have been from that city this month:
Note: Due to the output being a table, not a number, the COMPARED-TO operator is not supported when using the BY operator.
But what is we want to limit the result further? Let’s say we are only interested in cities in Great Britain. The international code for Great Britain is GB, so we need to add that restriction to our query:
SELECT total BY city FROM VideosAnalytics THIS-MONTH WHERE country = GB
The result of running that query is again a table since we have requested our totals are returned by the city they came from. However now the cities are only those that are in Great Britain:
Next, what if we are only interested in finding out the top 2 cities in Great Britain that our videos have been viewed from? We can limit the query so it only selects the top 2 cities:
SELECT TOP 2 total BY city FROM VideosAnalytics THIS-MONTH WHERE country = GB
The result of running that query is a table: again, we have requested our totals are returned by the city they came from. However now there are (at most) 2 rows. The report automatically sorts the views (descending) so this results in a list of the top 2 cities that video has been viewed on within Great Britain:
In our example queries, you will have seen we used THIS-MONTH. This is simply a shortcut for the start and end dates we want to run the query for.
You can use any of the following to specify a date range:
|THIS-WEEK||This week (Monday - Today)|
|LAST-WEEK||Last week (Monday to Sunday)|
|THIS-MONTH||This month (1st - Today)|
|01-05-2015:08-05-2015||1st May 2015 - 8th May 2015|
In our example queries, you will have seen we test id, city and country. You can use these in your query, for example “city = London”:
|deviceType||desktop / mobile / tablet|
If you have a more specific question you wish to ask, VQL may not yet support it. We are currently expanding its capabilities. If so - or if any of the information above is not clear - please do email us as firstname.lastname@example.org. We might even be able to retrieve the data you need for you.