Once your DB is connected, you can start writing SQL queries.
Your DB schema is on the left to make process much easier.
Please note, that SQL editor at Statsbot is specific and operates with cubes. So you don't need to write a whole query in traditional way, but first just to select underlying table you will operate with. Thus, you would be able to create a very flexible and powerful analytics tool comparing to the traditional approach, so the whole team could digest data easy and fast.
Take a look at the precise example below on how to work with SQL editor.
First thing is to write your query in “SQL query” window and press “Run Query” button.
In my example it is a table containing all messages received by bot. If everything is correct you will see metrics, dimensions, segments and date filter appeared on the right.
Next step, we are going to name the prospective metric and define the way it should be calculated. Let’s keep things simple and name it “Messages”, select “id” column and “count” function followed by pressing on “Save” button.
Lastly, I have to apply date filter. Since Statsbot is going to provide information about how many messages we received during certain period of times, date filter is a mandatory field. In this particular example it is “created_at” column which is a time stamp saved when each message was received by bot.
Once done, we are actually able to try pulling that metric straight away from the report builder below or by sending a command to Statsbot in Slack. Please take a look on how it could be done below.
Let’s imagine, we now would like to find out how many messages were recognized out of that number. In order to do that, we will add a dimension that has name “recognized” and choose column from our DB which has the same name.
Let’s check how it looks now below in report builder and in Slack
We are using keyword “by” when sending a command to Statsbot to apply “recognized” dimension.
Lastly, let’s imagine that we would like to find out how many messages sent by one particular team was recognized by bot in the last 7 days. We simply add a segment with team name, which is “Demobot” and write expression “bot_id = 21172” since Demobot team has this ID in our DB.
Let’s take a look how report looks like now