Viur Blog

Heroku launched what they call the “SQL database service for developers”. 

It relieves the burden of database management by providing a set of features and tools that save a lot of time and effort. 

These Heroku Postgres databases can accept outside connections so you are able to connect them to the business intelligence tool of your choice.

Let me show you how you can quickly connect a business intelligence tool to a Database.

On your Heroku Dashboard choose “Databases”

Then choose the Database you want. 

Click to get the connection details.

Now, we have to configure this database in a business intelligence tool. I’ll show you how to do it in Viur. Go to the “Datasource Configuration” page and click “Add Datasource” > “Add Database” and copy the connection details and test the connection. 

Make sure you have “use SSL” checked because Heroku only accepts connections with SSL.

Save the connection and follow the link to the “Widget Creator”.

The Database is loaded and good to go.

Let’s do a simple query using the “Code Editor” to see how it goes.

Looks good. 

All in all, it took me less than five minutes to get the database connection details from Heroku, connect it to my business intelligence tool (Viur) and create a simple chart using a SQL query. 

Everyday we produce huge amounts of data that can be used by apps and other services. Personal finances, fitness apps, home control & automation, work loggers… There is an app for pretty much everything we can think of.

Some applications that we use every day allow us to export data as a CSV file. This is great because it means we can explore data with a finer detail and eventually get better insights than those that most applications give by default.

All we need is a proper Data Visualization tool that can take these CSV files and lets us play a bit with the data. 

So, let me show you how Viur works with CSV data.

For this example I’ve chosen a dataset from SpritMonitor that contains my car’s mileage and my Gas’ expenses.

On the Datasouce Configuration page let’s choose “Files”

The first thing to notice is that we have to fill in a “CSV Database Name” and the “CSV Table Name”. This is related to how Viur handles CSV files: 

Viur uploads the data from the file into a private cloud database that only the user (and team) has access to. When uploading a second file you can put it into the same database and on a separate table, so when querying you can perform joins.  

While filling the form, pay attention to the column separator, and file encoding. Please note that files exported in Microsoft Excel may have the encoding set to Windows-1250.

On the second step change the data types for each column. This is crucial to perform relevant data analysis!

The last step is just a confirmation, go ahead and hit Finish!

We’re now ready to explore the data and create some widgets on the Widget Creator page. 

Choosing the datasource that we’ve just created and dragging the Consumption in the measures and applying an Average we’ll show us the total consumption average.

6.24 liters per Kilometer (about 45 miles per gallon in imperial units). Not a bad result for urban driving in such a hilly city.

We can go further and drag the Date. This is quite interesting because the season (cooling or heating) might have an impact on the consumption. I have to get weather data and cross these values with the temperature to see if there is a pattern. If you are asking yourselves what’s that huge spike in September 2012, let me just say that I decided to check the car’s top speed…

This was just a small tutorial on how to start analysing data from a CSV file. On a next blog post I’ll go deeper on data analysis, this time using multiple CSV files.

This week we introduced a new datasource connector for CSV and other text files. 

CSV files are the simplest way to share data between applications when there is no API available. Many companies rely on CSV files in their day-to-day workflow, so we took our time to make sure we could give a good user experience on the import and export of text files.

As of today you can upload CSV files and create a database with these files, establish relationships between them and perform data analysis over them, using our Widget Creator with Drag&Drop or using SQL queries. Then you can save your work in charts, metrics or tables and put them in one of your dashboards. You can also export your work into a new CSV file if you need to feed it into another application, or you just want to work your filtered data in Excel.

We hope this opens new doors and new usages for Viur as a tool that allows you to do data analysis quickly and easily.

In this small article I’ll cover some metrics that can give additional insights on your marketing efforts and how do you get to them on Google Analytics. Plus some example charts I use in Viur for visualizing them.

Traffic Sources

Traffic sources are the channels that you have open to the web. You want to keep them effective and constantly bringing new people to your site. You should pay particular attention to the organic ones (all but the paid search) and keep them growing week after week. You can achieve this by creating new (quality!) content and distributing it in a blog post, newsletter and social media. If people love your content they’ll share. also, if you are using paid advertising on search engines you can see how it’s going comparing against the other non-paid channels.

where to find on G.A.: Aquisition > Overview

Social Presence

This one is a bit of a subset of the Traffic Sources and allows you to see what social networks bring you more traffic. You should not be preoccupied on having an equal share on all networks since your target market might prefer one over another. People use more Facebook for personal matters  and Linkedin professionally, so for example, a B2C product might work better than a B2B on Facebook. You should focus on creating or adapting your content to each social network: hashtags might work great on Twitter, pictures on Facebook and infographics on Linkedin. Find what content works best for you in each channel.

where to find on G.A.: Aquisition > Social > Overview

Device Type

At first glance it might not strike as an important metric, but it’s absolutely crucial if you want to split the effort of improving the site usability for a particular device type. As you might know, since April 2015 Google has rolled out a new ranking algorithm that privileges mobile friendly sites. That means that if your site isn’t adapted to smaller screen resolutions, it will be penalized in terms of page rank. If most of your users come from a mobile device you should have a good mobile site and not just one that google ranks as good.

where to find on G.A.: Audience > Mobile > Overview

Languages

If your site doesn’t have an english page it should since it’s the global language of business. But if you know from where your visitors come from, you might create new business opportunities by translating your website to your visitors native language. Furthermore, by having your website translated in other languages it will perform better in search queries that people will do in their native tongue. There are services like Unbabel that can do that at affordable prices.

where to find on G.A.: Audience > Geo > Language

Device Type

At first glance it might not strike as an important metric, but it’s absolutely crucial if you want to split the effort of improving the site usability for a particular device type. As you might know, since April 2015 Google has rolled out a new ranking algorithm that privileges mobile friendly sites. That means that if your site isn’t adapted to smaller screen resolutions, it will be penalized in terms of page rank. If most of your users come from a mobile device you should have a good mobile site and not just one that google ranks as good.

where to find on G.A.: Audience > Mobile > Overview

Browsers

Not all browsers render pages the same way (Internet Explorer, I’m looking at you!). Developers have the tendency to not test every browser, so if you’re having a lot of hits from a particular browser, it’s good to know if your page works on that one, or if people are abandoning your site just because the experience is poor.

where to find on G.A.: Audience > Technology > Browser & OS

If you manage a Facebook page and you like to see how people are engaging with your content you’re probably a big fan of the Facebook Page Insights. It allows you, among other things to:

View metrics about your Page's performance 

Learn which posts have the most engagement

See data about when your audience is on Facebook 

One of the most useful metrics over there is the Engagement Rate that in a certain way allows you to see how popular or viral a post was.

According to their help center the Engagement Rate is:

The number of people your post reached who then liked, commented, shared or clicked on your post.

If you do a quick google search you’ll find different ways to calculate Engagement Rate being the most common one, and the one thats sticks more with the Facebook definition:

    fig. 1 - engagement rate formula

But if you go to your Page Insights and apply that formula to the data you retrieve from there your results don’t match the Engagement Rate.

For example, take a look at one of our post data:

    fig. 2 - 57 Post Clicks + 28 Likes, comments and shares

    fig. 3 - 9% engagement rate

Putting these values into the formula you get:

    fig. 4 - putting the values in the formula

But 16.3% is quite different from the 9% they show on the page insights, so what’s wrong?

What we found is that Facebook calculates Engagement Rate using other variables that are available in their Graph API. The formula that we use at Viur, and that matches Facebook results is the following:

    fig. 5 - the real formula!

Engaged Users is “the number of people who clicked anywhere in your posts” (post_engaged_users in the Graph API ) Post Reach is the “The number of people who saw your Page post” ( post_impressions_unique  in the Graph API)

With this formula we can get to the same results that Facebook (except we use decimal places in our results) :)

    fig. 6 - the correct values!

We hope this post clarified your understanding about the Engagement Rate and it’s calculation. Shoot us an email if you have any questions, we always answer! 

Anyone working on projects or an organization surely have heard of KPIs - Key Performance Indicators. Some even contribute to their calculation and are evaluated by it.

So, what are Key Performance Indicators and why are they useful?

KPI is simply the name given to a metric that is used to measure certain factors that are crucial to the success of an organization. Each KPI is usually associated with a goal or objective to be achieved.

Regular determination of the value of KPI intends to measure the degree of progress towards objectives and their evolution over time: 

 Are we moving in the right direction?   Will we achieve the target if we continue?

The KPIs can differ from organization to organization, since the focus of activity is also different. For example, the government may be concerned about an unemployment rate KPI, while a technology company is more concerned with a KPI regarding liquid results or customer retention.

How to define a KPI for our organization?

A performance indicator should follow the SMART type criteria:

Specific - has to be specific and have a well-defined purpose for the organization.

Measurable - it has to be possible to collect metrics and obtain a value.

Assignable - indicates the result produced by one or more teams.

Realistic - associated targets should be realistic.

Time-related - bound to a limited period in time.

Let's look at a detailed example:

KPI: CCR - Customer Churn Rate

Purpose: We are able to retain our customers?

Calculation formula:

Frequency: generally calculated every quarter or every month.

Data collection method: through the number of contracts that are renewed or not.

Data collection cost: is generally low, since the organizations already have customer relationship management platform (CRM) that already provide this kind of data.

Scenario: On April 1 the organization had 100 customers. During this month, it won 20 new clients and lost 5. CCR in late April = 5 / (100 + 20-5) = 4.34%

More examples of KPIs that can be searched online:

Financial

EBITDA

Net profit margin

Return on Investment (ROI)

Total shareholder return (TSR)

Customer

Customer lifetime value

Customer satisfaction index

Customer complaints

Net promoter score (NPS)

Sales and Marketing

Market growth rate

Cost per lead

Social networking footprint

Brand equity

Operations

Capacity utilization rate

Earned value (EV)

Time to market

Rework level

Human Resources

Revenue per employee

Employee churn rate

Employee satisfaction index

Salary competitiveness ratio (SCR)

Social Responsibility

Carbon footprint

Waste reduction rate

Waste recycling rate

Savings levels due to conservation and improvement efforts

All of them (and some more) can be found in a book that I strongly recommend:

Key Performance Indicators: The 75 Measures every manager needs to know

by Bernard Marr

And you? Do you define KPIs for your projects?

This is a guest post by Hélder Ferreira

Hélder is the Head of Service and Product Delivery at ISA - Intelligent Sensing Anywhere.

You can find the original post, in Portuguese, here.

Small Data still has a major role in company's day-to-day activities and databases are still here to stay. Database engines are more powerful than ever and handle really well calculations, so why should we not take the most of what they have to offer?

fig 1 - a database schema with its tables and columns

Viur has a feature that let’s you to perform advanced calculations and/or apply functions over your data without having to know any SQL or scripting language. You can use your table columns to create new attributes that are called “Formulas”.

When composing a new Formula you can use mathematical operators:

+ : Addition

- : Subtraction

* : Multiplication

/ : Division

^ : Power

and functions:

ABS : Absolute

COUNT : Count

COUNTD : Count Distinct

MAX : Maximum

MIN : Minimum

AVG : Average

SUM : Sum

Take a closer look on how Viur displays your Tables and Columns:

fig 2 - The accordion on the left contains the tables and inside each table are it’s columns. 

You can make new formulas using these columns (they don’t need to be from the same table) simply by dragging them to the Measures or Dimensions area like you would normally do in order to build a chart.

So lets drag “Units in Stock” and “Units on Order” into the Measures area

fig 3 - the columns become green when they are selected in formula mode.

To create a new formula you simply type a formula like you would do in Excel

fig 4 - Dialog for composing a new formula

When you save the formula it will be available at any time for future reuse as if it were a new column on your database.

Note: This new column is virtual, it is not really created in the database!

fig 5 - A chart using our newly created formula

This feature will definitely allow you to create more and better insights and explore your data without coding any SQL!

In a global market where competition is tight you must make sure your customers are happy.

There are many ways to measure customer satisfaction, and we found one that is quick to implement, uses a platform that almost every site uses and is free. It even works with Net Promoter Score if you want, as we explain below.

In order to put this example in practice you must have Google Analytics in your site. You can learn here how to set it up.

Without further ado this is how we did it:

Where we wanted to know if the user liked something we placed some thumbs up and thumbs down buttons.

Whenever the user clicks one of the thumbs a javascript function sends the corresponding value (0 if the feedback is negative or 1 if positive).

<!-- html code --> <p>Was this information useful? <i onclick="giveFeedback(0)" class="btn-feedback fa fa-thumbs-o-down text-danger" ></i> <i onclick="giveFeedback(1)" class="btn-feedback fa fa-thumbs-o-up text-success" ></i> </p>

To have the thumb icons with colors you'll have to use a css toolkit like Font Awesome (used in this example) or Bootstrap and include this snippet on your css file:

/* css code */ .btn-feedback { cursor: pointer; } .text-danger { color: #e5603b; } .text-success { color: #56bc76; }

Google says events should be invoked like this:

ga('send', 'event', 'category', 'action', 'label', value); //if you are not using Universal analytics yet it's _gaq.push(['_trackEvent', 'category', 'action', 'label',value]);

So we can make our giveFeedback function like this:

function giveFeedback(value) { ga('send', 'event', 'button', 'feedback','feedbackPage.html', value); //if you are not using universal analytics use instead: //_gaq.push([‘_trackEvent’, 'button', 'feedback', 'feedbackPage.html', value]); }

As you see the value variable depends on the user’s input.

With this small piece of code set up you can test if everything is working by going to Google Analytics > Realtime > Events and see if the events appear like this as you click the thumbs:

If everything is ok, results will appear later in Google Analytics under this event’s “Avg. Value”.

If you want a prettier and more awesome way to track this, you can use Viur to create a widget for it:

To accomplish this just go to the Widget Creator, choose Google Analytics as a Datasource and from “Event Tracking” drag “Avg. Value” to the measures, and add “Event Action” to filters.

Protip: Viur has a search input under the datasources dropdown (see the picture below). Search for “event” for a faster lookup.

Click on the filter you’ve just dragged to open the Filters panel and choose “exactly matches” to “feedback”:

Save the widget and add it to the dashboard and you’re good to go.

In this example we just used a simple binary (0,1) feedback system. However, you can easily use the Net Promoter Score, just change the question a bit and pass values from 0 to 10 to Google Analytics.

Our vision is to create a product so simple that anyone can use, hiding all the complex processes in data analysis. This week we focused on improving the user onboarding. The biggest change is the “help” button that allows you to immediately authorize Google Analytics and add widgets to the dashboard in a heartbeat. This help page also has other topics regarding database widgets and data exploration. With time we will add more useful topics, and refine the existing.

Clicking the help will open a friendly modal:

Feel free to give a thumbs up!

Another big new feature is that you can now edit widgets from the dashboard. Editing the widget will open it in the “Widget Creator” where you can tweak and then update the widget or save as a new one.

The header menu also changed, we moved the dashboard name next  to the navigation so the dashboard could have more vertical space.

We also improved the performance in the Widget Creator so you can explore data more quickly.

Stay tuned to our latest news:

The big day has come, and we finally released the Beta version of Viur for a selected few. This post describes what tools we used and how we used them.

Since day one we have a subscription form on the landing page to capture early adopters. This allowed us to have a good user pool to start working as soon as we had something to show. For this we used Mailchimp, which has a generous free tier and allows you to set up email campaigns and track opens and clicks among other things.

In the main site we use two awesome tools:

Google Analytics to track specific metrics (like bounce, exit rate,etc...), goals and specific events we trigger (we will talk more about this in a future post).

Heap Analytics to know how users interact with our site. Heap allows us to see what each specific user clicks and visits from the moment he enters the site. Setting up Heap is dead simple since it’s all done visually.

To track what users do inside the app we also use the mandatory Google Analytics and Mixpanel. We chose Mixpanel because it has a Java library that we could easily use with our server code and, like Heap, it allows us to know what each user did. It’s a good tool to keep an eye on what's going on and to know who are your most and less active users, which allows you to engage them differently.

So, with all these analytic tools in place the worst part is to have a bunch of tabs open to control each one individually. That SaaS fragmentation problem is one of the pains we are solving: we allow people to explore, analyse and blend data in just one place. We are supporting Google Analytics and databases for now, but we are about to start integrating with other services like mixpanel and mailchimp. Then at the end of the day we only have to  look at a dashboard like this:

Using Viur to create useful KPIs

If you already use Viur you know that it connects to all kinds of data sources enabling you to unlock the value of stored data without needing to be a data scientist or a developer.

For those who don’t know us yet, we offer an analytical platfom that connects with your business data (cloud and on-premises) allowing you to visualize and explore that data.

So, with this in mind we would like to show you how to create some sales KPIs (key performance indicators) using our demo database. For the demo we chose to use the northwind database that exemplifies a typical relational model for a store with orders, products, clients, suppliers, employees etc.

Viur’s interface uses the data warehouse nomenclature so as a quick refresh:

A Measure is a property on which calculations (e.g., sum, count, average, minimum, maximum) can be made.

Dimensions provide structured labeling information to otherwise unordered numeric measures. The dimension is a data set composed of individual, non-overlapping data elements. The primary functions of dimensions are threefold: to provide filtering, grouping and labelling.

~in wikipedia

So without further ado let’s get started building some KPIs just by dragging a few blocks and applying simple modifiers.

KPI 1 - Orders per country

To know where are your clients are.

Metrics: Count of Orders.”Order Id” sorted descending Dimensions: Orders.ShipCountry

KPI 2 - Order volume

See if you are getting more orders over time.

Metrics: Count of Invoices."Order Id" Dimensions: "Order Date" grouped by month

KPI 3 - Average order value

This is a useful KPI because it shows you if your customers are spending more.

Metrics: Average of Invoices."Extended Price" Dimensions: "Order Date" grouped by month

KPI 4 - Employee’s dispatched orders

Compare employee’s productivity

Metrics: Count of Orders."Order ID" Dimensions: "Order Date" grouped by year and Employees.Last Name

KPI 5 - Top products Find which are your top selling products

Metrics: Order Details.Quantity sort Descending Dimensions: Products."Product Name"

So that’s it for now, we hope this post was useful to show how to do business analytics KPIs by exploring a conventional database without any SQL code.

Don’t forget to follow us on social networks to view more content like this.

Some weeks ago we decided to know a little better our customers’ preferences by doing a small survey. Since at the time most of our subscribers were from Portugal we decided to focus on the portuguese users. The results were very useful and they had impact on the baseline we set to the beta version of Viur. Because we like to share here goes the results.

User typology

From these two charts we can see our main target are the managers and developers from Small and Medium Businesses. It came as a surprise the high percentage of developers, but that can be explained because they are more prone to be early adopters.

Databases and Cloud Services

In the database and cloud services usage charts there are also some interesting facts. PostgreSQL were one of our top priorities since it’s one of the most powerful database engines and its free! On the other side SQL Server is the most popular and unsurprisingly and confirming our guess, there are still a lot of people using Excel as a database. On the other side, Google Analytics won but a large percentage of users not using any cloud service at all.   

BI tools

It seems Excel is still the king of BI, followed by the BI capabilities that some ERPs give. Still there are a large amount of people not using BI tools or rolling their own.

Most wanted features

In this chapter it was very balanced but the dashboards won followed closely by Real Time and Data Exploration.

Also, 46% of the responders said it was very important to have a mobile support and 58% wanted to have access to the platform outside the company.

Conclusions

We reaffirmed that our target users are the SMB managers, but we can’t forget the developers.

With the database and cloud results we decided to include MySQL support in the beta version while leaving Mailchimp for now. So for the beta we will support SQL Server, MySQL, PostgreSQL and Google Analytics.

The BI tools results confirmed our theory that there are a lot of people out there still using Excel to do Business Intelligence. For these people (and many others, of course!) Viur will be great since it will allow them to save a huge amount of time - and money!

Featurewise, we are covering the user preferences so thats good!

Sunday - day 0

The flight was early so we had to catch the 2am bus to the Lisbon Airport.

2500 kilometers later we arrived at Berlin and as it was early we did a short city tour before we could even get to check in at the apartment we booked. We took our time and enjoyed some local attractions, including bratwurst and beer, and then at the apartment the rest of the day was spent preparing the next day’s pitch.

Monday - day 1

The first day of seedcamp is for the teams to know each other and to practice the pitch and get some feedback. Some good questions were asked during our presentation so we noted them down to later work on them. Guess what, the rest of the day (and night) was spent upgrading and preparing the pitch.

Tuesday - day 2

The second day is the seedcamp investors day, still it is a very informal presentation which is good, since it lets you be more comfortable. You are supposed to pitch (3 minutes) and then some Q&A. As we were one of the first teams in the morning we wrapped up before noon and got to wander around Berlim some more. As in the next day things would likely to be even more serious, with a bigger and different crowd, changes were made to the pitch and then some more pitch practice for a couple of hours.

Wednesday - day 3

The third and final day was to present the pitch to all the mentors and then get some feedback from the ones assigned to us. It was very interesting to have our ideas tested against the VCs point of view, and also some marketeers and founders. It made us think about our solution and how we have to present it to different audiences. We took a lot of notes and we will have all those thoughts and advices in consideration. This kind of feedback isn’t something that you can get everyday so it was really insightful.

After the mentoring session it was time to finally decompress and to eat and drink @ the factory, a cool looking startup incubator.

Final notes:

Seedcamp Week is intense but it is extremely valuable. We were not selected for their upcoming 2014 class but for the amount of feedback and guide we received in 3 days it has worth it.  

This weeks we’ve been focusing on having a demo ready as soon as possible. We spent some time updating the layout and building new widgets. So, without further ado, we introduce you the new widgets:

Funnel charts are very popular so we had to have one. We chose to start from  smilli’s implementation.

We needed a more flexible gauge so we tweaked Jake Trent’s Gauge.

 Our new polar / radar chart was built from alangrafu’s radar chart.

 From the nvd3 library we added the line with focus

and the stacked.

 besides these charts where we bootstrapped to the max, we also build one from the ground up like the note:

  We changed the widget’s font weight to be more thin, which gave them a more modern look and we also tweaked the widgets have less icons, so we opted to tidy things up in a single option icon button:

  So, that’s it for now. Expect more good news soon. :)

Let's pass forward the hello world and start this blog with a post about what led us here.

We are from Portugal, a country drowned in debt that is struggling to show the world that it is the "good student" and it will overcome all this crisis with pride and distinction. While from an outsider point of view all seems going in the right direction, internally that's very arguable. Fortunately not everything is bad, we still have good schools, will power and "desenrascanço" (you can google it). Along with these traits, in the last years it is spreading the belief that the salvation to crisis and ever-growing unemployment is the self-employment / entrepreneurship, so naturally everyday we are seeing more and more ideas being launched, more entrepreneurship conferences, lectures, contests and programs.

Before embarking the Viur project both of us had our jobs, with their goods and bads, so there was no actual need to give upon that stability, but there was a growing will of doing things our way and try something new before this opportunity passed us by.

So we did it. We joined an entrepreneurship program that allowed us to start and to do things by the book. We did the market analysis, SWOT, business model canvas, worked in the technical aspects of the solution, did some pitches, etc. All these steps allowed us to think and rethink some aspects of the solution we could have missed if we had started the coding right away. Since we begun to code things have changed from the original idea and from the documentation we produced, but it sure helped us get us where we are. After all the point is to be an agile company. So I think that more than ever things are getting clear and we are going on track now. When we get off track again maybe it's time to iterate, starting with the "bureaucracy" - because like Rust Cohle from True Detective says: "linear time is an illusion. I've walked before and I'll walk by again. Time is a flat circle".