First step: To have a metric

One of the most important metrics in these days to measure the efficiency of a E-commerce web site is the conversion rate, which is a number between 0 and 1 representing the percentage of people who closed a transaction in a e-commerce site, related to all the people who visited the site during a period of time.

We don’t need an A/B test to know the conversion rate, we can obtain it, by simply count all the visitors to the site during a period of time and the quantity of sells we made, then we can do the next math operation:

Conversion Rate = Number of Sells done / Number of visitor received

Let’s suppose during a day we had 1000 visitors to our site, and we closed 120 transactions. Then our conversion rate is 120/1000 = 0.12 (0.12 means 12%)

Can I trust in Conversion Rate?

Hummm, yes you can. But we need to know what a conversion rate is, in order to understand the real significance of this metric. Conversion rate is the result of an estimation (or a guess), if we try to estimate it again, we will likely get other different value (perhaps very close to the previous one). This is the reason why all estimators should came along with an error level measure. And based on that error level we define a range instead of a single value. A range where the estimator (the conversion rate in our case) is with a high level of confidence. For example we can say:

The conversion rate of the site is IN the range [11% , 13%] with a 95% of confidence.

However in order to simplify, the statement, many people would say: the conversion rate is 12% (which is the medium value in the range). This simplification can be the root of a wrong site evaluation and wrong decisions.

What are A/B Tests for ?

A/B Tests are a compare tool or technique to determine if a change to the site improves the conversion rate or not, however you don’t fall on the simple compare of conversion rate single values. You must extract conclusion by comparing both conversion rate ranges.

In the previous image we can conclude that Site Variation B is better because the range for conversion rate is higher ever. Even in the worst case (23%) of Variation B we have a value of conversion rate higher than any value in Variation A range. In this example we say we don’t have overlapping of ranges.

Let’s see a case with overlapping:

Is the Variation B better or not ? Well, seems to be better, however we cannot be sure, What about if “Variation A site” has a conversion rate of 21% and “Variation B site” has a conversion rate of 20%?. In this case A is better than B. Anyway, we can conclude that we need to continue testing.

Sample table: e-commerce A/B Test data

The following table includes date of 6 A/B tests done on a e-commerce site. There are 7 sets of test data: one set of data corresponds to the test of the original site and the other 6 data sets were obtained by testing the site after each of 6 improvements done. Each data set is composed by 5 tests (or records) done with 100 users each. The column “test_code” represents the name of the improvement, while the column “test_rate” represents the conversion rate obtained on this test.

How to obtain the range for conversion rate

In order to obtain the range for the conversion rate, we need to use two aggregation functions. First we will use the average (avg) function to obtain the media or average value of all the conversion rates. The media will be the central point of the range. Then, the second function we need to use is the standard deviation. With the result of the standard deviation function we will define the start and end points of the range. Let’s see how to calculate both points

In order to have a confident of 95%, the start of the range will be the point:

average — 2 * standard deviation

and the end of the range will be the point:

average + 2 * standard deviation

Let’s go now to TeamSQL, in the charts window, we can use the aggregation functions. In this case we will use the average function to show the central point of the conversion rate range as we can see in the following print screen.

The chart is the following:

However, the previous chart only shows the average value or central point in the conversion rate range. We would like to see the range of each test in the chart. Then we will add two more lines: one line (black) at the bottom representing the start of the range, and one line on top (green) showing the end of the range. Let’s see the new chart

In order to understand the previous chart we need to isolate and identify each conversion range. For example in the following image we can see the conversion range for the test NEW UX. The range for this test is 14% to 15.7%, this means that conversion rate is in the range [14% , 15.7%] with a confidence of 95%

The next step is to put all the pieces together and understand how to compare the range of Variation A with the range of variation B, Let’s see an example:

In the previous image we can see that Variation A range is 8%-11%, while variation B range is 14%-15.7%. In this case, it is clear that Variation B is better than A, because the worst case on Variation B (14%) is better than the best case on variation A (11.8%). We leave to the reader the analysis of range when there are range overlapping.

Conclusion

In this article we covered an important metric called conversion rate metric, the A/B Tests technique and how to create a chart in TeamSQL to evaluate and analyze results of A/B Tests. Charts in TeamSQL are very flexible, you can use aggregation functions (like sum, average, standard deviation) to calculate new metrics on which base the charts.. You can apply this technique to any change to your e-commerce web site. Let’s try to adapt to your database!

Analyze A/B Tests in TeamSQL was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

In this post, we’ll demonstrate TeamSQL Charts using this sample MySQL database, which contains employee and salary records for a fictional company. The database contains a table for employees (employees) as well as their departments (departments), salaries (salaries), titles (titles), and department managers (dept_manager).

Charting the Data

We’ll demonstrate the chart feature by creating two charts: one that shows a range of employee salaries based on the employee’s title, and one that shows how employee salaries have changed over time.

Chart 1: Employee Salaries by Title

The salaries table contains a list of salaries for each employee. Likewise, the titles table lists out each employee’s title. We’ll join these two tables to match each salary with a title based on the employee’s unique ID.

After the results appear, click the “Charts” button (next to the “Execute” button) to open the TeamSQL Charts window.

In the center of the window is the query we just ran along with a preview of the result set. To the left is a list of chart types we can choose from. Below the result set is a set of controls for configuring the chart’s data sources, filters, and settings.

In this example, we’ll create a bar chart. Click on the bar chart icon on the left-hand side of the page, then scroll down to the section called “Aggregations”. This is where we will set the chart’s data sources, otherwise referred to as its measures and dimensions.

Measures are numeric values used to populate the y-axis. Each measure consists of a:

• Source: the database field used to populate the measure’s values
• Label: the name to display in the legend
• Aggregation: the calculation performed on the values such as the minimum, maximum, average, or standard deviation
• Sort: arranges the values in ascending or descending order

We’ll add two measures: one for the lowest salary per title, and one for the highest salary per title. We’ll sort our titles by the highest salary in ascending order. Even though we’re only sorting by our highest salary measure, the chart automatically reorders the entire chart to fit the sort.

Click the “+” button to open the Select Measure dialog and configure each measure as shown below.

Next, we’ll set a dimension. Dimensions are used to populate the x-axis. Unlike measures, which are strictly numeric, dimensions can be anything from numerical values to text. In this case, we’ll use the title column as our dimension. What this means is that for each title, we’ll display the corresponding values from our two measures.

Click “+” to open the Select Dimension dialog and configure the title dimension as shown below.

Finally, we’ll create the chart by clicking Apply. TeamSQL automatically creates the chart with two bars per title: one for the lowest salary, and another for the highest.

To make this chart more readable, we’ll move the lowest salary bar next to the highest salary bar. Click “Chart options” to open the options dialog. This is where you can customize the appearance of the chart, such as the title and subtitle, font size, orientation, axis labels, and legend values. In the General tab is a series of checkboxes that toggle various features of the chart. Uncheck “Stacked” to display the two measures as two separate bars. Click “Apply” to save the changes.

Now we have a much clearer view of our employees’ salaries. With “Show Tooltips” enabled, you can hover the mouse over any one of the bars to see the actual values. You can also display the values within the chart itself by clicking the “Series” tab and checking “Value labels”.

Chart 2: Employee Salaries Over Time

The last chart only shows us salary ranges as of today’s date. Suppose we want to see how employee pay has changed over time. To do this, we’ll create a new chart that plots employee salaries for each year since the start of the organization.

We’ll start by modifying our existing query to include dates. Each record in the salaries table contains the date range that the salary was valid from (the from_date and to_date fields). We’ll use the to_date field in our query, since that indicates the last day that the salary was valid.

Next, we’ll create the chart. This chart is identical to the previous chart except for two key differences:

1. We’ll replace the Title dimension with a date dimension using the to_date field.
2. We’ll sort the chart by date instead of by the highest salary.

Add the two salary measures like before, but instead of sorting by the highest salaries in ascending order, keep both measures unsorted. Next, add a new dimension and select “to_date” as the source. Since “to_date” is a date field, TeamSQL provides a field called Time Bucket, which allows you to aggregate the field by time period. For example, you can group dates by day, week, month, year, or by time of day. We’ll group by year and sort in ascending order.

Before we create the chart, there’s one minor issue with the data set that we need to address. Salaries that are still current contain “1/1/9999” in the to_date field. This causes the chart to display “9999” as one of the years. To prevent this, you can either modify your SQL query to replace instances of “1/1/9999” with today’s date, or create a chart filter that only accepts dates earlier than 1/1/9999.

Click “Apply” to create the chart.

Conclusion

The charts feature is a powerful tool for visualizing your SQL data. You saw how easy it is to create and customize charts with just a few clicks. You can access a variety of different chart types (including pie charts, scatter charts, funnel charts, bullet graphs, and heat maps), change the appearance of your charts, and export your charts as images.

The charts feature is free to use during the Release Candidate period and you can download TeamSQL from here.

Charting Your Data with TeamSQL was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

You can easily create bar, area, line, pie, scatter, funnel, and bullet charts, as well as heat maps, using the data you return from your database. You don’t need to export your data to a separate data visualization tool.

A hardware shop table to populate our charts

In the rest of the article, all the query examples and charts will be based on a simple hardware shop table called SALES. The SALES table has one record per sale done in a hardware shop. If we execute in TeamSQL a “select * from SALES” SQL query we can see the following image:

The examples in this article are all executed against a PostgreSQL database, however as TeamSQL is database agnostic, you can create charts based on data stored in many database managers like MySQL, Microsoft SQL, Redshift or Redis among others.

Feeding a chart with a SELECT SQL query

In TeamSQL we can create a chart directly with the query output data. Then, the first building block for a chart is a query. Once we executed a query in the SQL panel, and having selected the “Results” window, we can press the orange button “Charts” at the right of the “Execute” button and the following window will open:

The black boxes on the right side of the window are buttons to select the type of chart. TeamSQL supports several chart types like: bar, line, pie, scatter or area charts among others, We will go back later to chart types, now we will choose the default chart type, which is “bar”, and we will continue working with other parameters of the chart in the same window. We will scroll down and we see this part of the window:

The bar chart to be created will be based on two set of values. Every set of values must be associated with a column in the SELECT query we used as the origin of the chart.

The first set of values is called the “Measure” and should be a numeric value, because we need a measurable value. We need to press “+” sign and select which column of the query will act as a measure in the bar chart we are creating. We will talk later in detail about the measure set of values, but now let’s go to the next parameter in the chart.

The second set of values is called the “Dimension” and can be any data type, because this value will be shown as a title or legend in the chart. Again we need to press “+” sign and select which column of the query will act as a dimension.

Let’s see how to configure the measure and dimension for our bar chart.

In the previous image we selected the column quantity_sold as the measure we want to base our chart on. Is important to note, we also selected in the aggregation parameter, the value “Total sum of”. Finally we press “apply” button at the button and the following window is displayed:

Then we go to dimension parameter, press “+” and select them as we can see in the following image:

as we see in the previous image we selected “date of sale” column as the dimension column and then we press “Apply” button. Finally we can see the following window, where the chart is almost defined.

The last thing to do is to press “Apply” (this time at chart level), and see how our chart is built. The resulting chart is in the following image:

In the previous image we can see our first chart!. The dimensions are all the days when we sold something. And the measure is the sum of units sold in each day. A proof of that is sep 23, when the chart shows 8 units sold, and we have two sales: one sale is for 2 hammers and 6 paint units.

The TeamSQL Chart feature is really powerful and flexible and allow us to can make many changes to the chart. If we try to cover all the options, this article will become in a bored Reference Manual, however in order to show how flexible TeamSQL is, let’s change something.

Suppose we want now “one bar per month”, instead of “one bar per day”. Let’s change the dimension. We first need to remove the current dimension by pressing “-” at the right of current dimension.

Then we will add a new dimension using the same column “quantity_sold” but in the parameter “time bucket” we will choose a different value. We will select “month of date_of_sale” instead of “day of date_of_sale”. Then we press “Apply” twice and we can see the new chart, now oriented to month instead to day. Powerfull !

Chart types: too many options to choose one

Let’s talk now about chart types. TeamSQL Chart offers many different chart types. Depending of the chart type, we will need different set of values of feed the chart. For example heat map needs three set of values, while many others types (like bar in our previous example) needs two set of values. On other hand some types of charts are better to show different concepts. For example if we want to show how the market is shared for any specific product, the best type of chart is “pie”, while to showing trends or sales increases over the time, a line chart can be better.

Let’s create a line chart with a new query, we can see in the following image our new query on the chart creation window:

We added as a new column in the query the “sale_amount” value, using the expression in the select: quantity_sold * unit_price. Let’s try to create a line chart based on the amount sold every day. If we choose “Total sum of sale_amount” for measure and “Day of date_of_sale” for dimension we will obtain the following chart:

Other interesting feature of the chart, is that we can move the mouse over an interesting graph area, to know values behind the graph. Let’s suppose we want to know the reason behind the sales increase on march 21, we can put the mouse over the graph in march 21 and we will see the following image:

Let’s now suppose we want to know about the distribution of revenue based on articles. As we mentioned, for this kind of analysis, a pie chart is the best option, so choose pie chart, then select “Total sum of sale_amount” as measure and “product_id” as dimension and finally we can see the following chart:

Conclusion

By creating several charts using a simple base table, we showed how flexible and powerful is the TeamSQL Charts feature. We explained here: line, pie and bar charts, however there are more chart types to be covered. One of them is the interesting heat map, which we plan to cover in a next article. Let’s continue learning how to extract the best juice from TeamSQL, it is worth!

Visualize your data with charts in TeamSQL was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

Today, we will take a look at one of the extensions currently available in the Extensions Store: Google Maps.

What does the Google Maps Extension do?

So let’s say that your data set returns a set of coordinate points. You can highlight the data points, right-click to display the options menu, and select Show in Google Maps.

This launches a new window with a Google map showing you the location indicated by the coordinate points.

How does this help me?

Location related data is commonly stored and used, and by installing this extension, you can get additional information from a set of coordinate points you’ve stored in your database without storing any additional information. You can leverage the power of Google Maps while working in TeamSQL.

Getting Started

If you don’t already have a copy of TeamSQL, download your free trial today!

TeamSQL Extensions: Google Maps was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

Rather than going through the traditional practice of exporting your query, saving it somewhere, and then forwarding the access directions to your colleagues, you can let TeamSQL do the heavy lifting.

Simply provide information about who you’d like to share with and the recipients’ email addresses, and TeamSQL will take care of the access-related steps (no more exporting queries!).

Here’s how to do this (notice how few steps are required).

How to Share Your TeamSQL Folders

To get started, navigate to the Saved Queries area of TeamSQL. You’ll see a list of all the queries you saved, as well as the folders you’ve created to store them.

Select the folder you want to share. You’ll see the options associated with that folder appear in the toolbar at the top. Click Share to proceed.

The Share Folder dialogue appears. Choose Individuals to share with a single person. You’ll need to provide the user’s email address.

Choose the Teams tab if you want to share with an existing TeamSQL team (which is essentially a group of individuals whose emails you’ve already provided).

Click Save to proceed.

At this point, the users to whom you’ve shared will receive an email that you’ve shared a TeamSQL folder with them. They will be provided an access link, as well as a link to download TeamSQL if they do not already have a local copy.

Happy sharing!

Sharing Your TeamSQL Folders was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

The UPSERT statement

The upsert statement is used when we have two tables. One is the destination table, where we will do updates or inserts (hence the name upsert) and the other table is the source table, where the information to be inserted or updated is stored. Let’s see an example.

Example tables : employee and employee_news

Let’s suppose we have a table called employee as we can see in the first image, which is the destination table and the employee_news table as the source table. Both tables have the same schema. To understand how both tables are related, we can think employee_news as a table that we receive once a month with the changes to apply to the employee table.

employee_news table (source)

UPSERT resulting table

To understand what we expect from the upsert execution, we will show the resulting employee table expected. The upsert statement only modify the destination table by: inserting non existing records (based on emplid value) and updating previously existing records. In the following image the new records are showed in green, and the fields with changes in blue.

Implementing UPSERT

The implementation of upsert we are covering here is based on the combination of the following two SQL statements:

• UPDATE with RETURNING *
• INSERT of a result set produced with a SELECT

In the next paragraphs we will explain both SQL statements

UPSERT: UPDATE THE MATCHING RECORDS

Lets see first the SQL update to modify the employee table with the records on employee_news table:

In the previous image we can see how to update a table (employee) based on other table with similar schema (employee_news). As we can see in the last line of the update, we affected or changed only records matching on emplid value.

The next point to explain is the RETURNING * sub-clause, which is a really powerful sub-clause that can be used in the insert, delete and update sql statement. It allows us to return as a result set all the records affected by an insert, delete or update. Then, in our upsert implementation, we will add to the previous update the RETURNING * to return all the update affected rows as they were a regular result set of a select. This result set (implemented as the upsert temporary table in our example) will be used in the insert as we will see in the next paragraph.

UPSERT: INSERTING THE NON MATCHING RECORDS

The next image shows the insert of the non matching records(records existing in employee_news and not existing in employee). The key to exclude the non matching records is the WHERE NOT EXISTS clause,.

The idea behind the NOT EXISTS clause is: if the record in employee_news doesn’t exist in the upsert temporary table, then this record must be inserted into employee. It is simple !

LET’S PUT ALL THE PIECES TOGETHER

Now we can see the complete upsert implementation. It is easy to see each part: first, the update part, and second the insert part.

CONCLUSION

We covered a technique to emulate the upsert statement, by combining an update and an insert. You can apply this technique to any pair of tables with identical schemas. Let’s try to adapt to your database!

Emulating UPSERT in PostgreSQL was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

Let’s say that we need to look at the correlation between height (in centimeters) and weight (in kilograms). Our returned data looks like this:

As always, once we return data, we can launch the TeamSQL charts wizard by clicking on Charts. Select the Scatter option, set your dimensions and measures, and click Create to proceed.

TeamSQL will now generate your chart.

As always, you can save your chart by exporting it as a PDF file or saving it to your clipboard for use in another application

Happy charting!

TeamSQL Charts: Scatter Plots was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

App Simplification

It might seem counterintuitive that you can simplify your tool set by adding another product (in this case, TeamSQL), but if TeamSQL works with some (or even all) the environments in which you’re working, you can streamline your workflow. Rather than wasting time switching between dedicated management clients and tools, you can manage everything in a centralized location.

In addition to allowing you to easily manage most database environments, you can easily keep tabs on multiple Redis servers simultaneously.

To get up and running with Redis, you have to download and compile the Redis package. The package includes redis-cli, which is used for server access. However, after you’ve connected, you can only run commands against the specific database to which you’ve connected. This is different from TeamSQL, where you can connect to multiple servers and open multiple tabs, each of which is running commands against a different server.

IDE-Like Feature Set

When working with Redis, you might find yourself running similar command repeatedly. In such instances, the features you’d find in IDEs or data clients that mimic the functionality of IDEs would be helpful in minimizing the amount of typing you’re doing. With TeamSQL, you get query history (all queries you run are automatically saved, so you don’t even need to save manually), as well as the ability to create your own custom snippets.

Furthermore, TeamSQL’s search functionality means that it’s easy to find work you’ve done. We’ve already mentioned that all queries you run are automatically saved, so you don’t need to manually save, but you can also save in-progress queries as well. It’s also worth mentioning that you can search for Redis keys easily

Extensibility

TeamSQL’s extensibility features mean that you can export, copy, and save your returned results sets, as well as use them in things like Extensions. Basically, you’re returning your data and immediately working with the results.

Summary

There’s nothing wrong with relying on CLI-based tools for managing Redis, but there are definitely upsides to using TeamSQL with your Redis instances. Try out a demo of TeamSQL today to see how useful it is when it comes to managing Redis.

Why You Should Use TeamSQL Instead of the Command Line for Redis was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

In today’s example, we’ll be looking at the market share each of the major browsers held in the period from January to May of 2015. Here’s what our data looks like returned from the database.

We click on Charts to bring up the chart creation wizard.

Here’s what the chart for the settings shown in the screenshot above look like.

As always, I can save my chart as a PNG file or copy it to my clipboard for later use.

Happy charting!

TeamSQL Charts: Pie Charts was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.

What are snippets?

From our snippets post:

Snippets are templates that you can use to help you build longer queries. Because TeamSQL knows which database you’re working in, you’ll get the right snippet at the right time (if you work in multiple types of databases, you’ll know how easy it is to get similar-but-different syntaxes mixed up). Now, you’re less likely to run a query that works for MySQL against SQL Server (or vice versa).

If you can’t remember the specific syntax, Intellisense can help you out. You can “scroll” through the list of snippets using your arrow keys, and when you’ve found the one you’re interested in, press tab to populate your query window with the snippet. Additionally, if you don’t recall what a specific snippet should do, the drop-down list of snippets includes a brief definition.

What are custom snippets?

Rather than limiting you to snippets we think you’ll find useful, we’re allowing you to create your own snippets. For example, do you find yourself running a specific query early and often? Turn it into a snippet instead of copying and pasting from an external file.

How to Create Custom Snippets

There are currently two ways for you to create custom snippets:

1. Using the Create Snippets option available in the main toolbar
2. Creating snippets on the fly by using the options menu

We’ll cover how to use both options.

Use the Create Snippets Option in the Toolbar

In the main toolbar, go to Snippets > Create Snippets.

This opens up the window you can use to create your snippet. You’ll be prompted for a name, description, and which database environment you want associated with your snippet (you can certainly create one that TeamSQL lets you use in any environment). Finally, you’ll provide your Snippet Text.

When you’re done making changes, click Save.

Your snippet is now ready to use.

Creating Snippets on the Fly Using the Options Menu

If you’re working in the TeamSQL editor and realize that you’re typing the same thing over and over again, you can create a snippet right then and there.

Highlight the text you’re working with, and right-click anywhere in the editor window. When the options menu appears, select Create Custom Snippet.

This launches the snippet creation wizard, but notice that your Snippet Text area has been populated with your highlighted selection. This means that you only have to provide a name, description, and which database environment you want associated with your snippet (you can certainly create one that TeamSQL lets you use in any environment).

When you’re done making changes, click Save.

Viewing Your Snippets

You can view the snippets that you’ve created at any time using the Manage Snippets area.

In addition to seeing a full list of your snippets, you can manage them. You can:

• Edit your snippets
• Clone your snippets
• Delete your snippets

There’s also an option for you to create a new snippet from scratch at the top right.

Summary

TeamSQL now supports the creation and use of custom snippets, which is helpful when you find yourself writing the same query multiple times.

Custom Snippets was originally published in TeamSQL on Medium, where people are continuing the conversation by highlighting and responding to this story.