Table of Contents
- Introduction
- Setting up an Amazon Redshift Datasource
- Querying Data From Your Datasource
- Analyzing and Visualizing Your Data
- Adding Drilldowns to Your Visualizations
- Querying Your Data Using Search-Based Analytics
- Summary
Introduction
Amazon Redshift is Amazon’s cloud-based relational database management system (RBDMS). Like most of Amazon’s offerings, Amazon Redshift is very popular, and for good reason: not only is it currently the fastest cloud data warehouse, but it gets faster every year.
Here at Knowi, we offer broad native integration to Amazon Redshift for analytics and reporting. This enables our users to leverage the speed and scalability of Redshift without any constraints, and to quickly analyze data from Redshift and form valuable insights. If you’re interested in learning how to use Knowi to analyze data from Amazon Redshift, you’ve come to the right place.
Setting up Your Amazon Redshift Datasource
After logging into your Knowi trial account, the first thing you’re going to do is connect to an Amazon Redshift Datasource and confirm that your connection is successful. This is how:
1. Find “Data sources” on the panel on the left side of your screen and click on it.
2. Head down to “Data Warehouses” and click on Amazon Redshift.
3. We don’t need to change any of the parameters here; Knowi automatically enters all of them for us. Just click “Test Connection” at the bottom of your screen.
4. Once you’ve confirmed that your connection was successful, click “Save.”
Your data source is now set up. Good work!
Querying Data From Your Datasource
Your datasource is now set up, which means it’s time to start querying your data. Here’s how to do this:
1. After saving your datasource, you should’ve received an alert at the top of your page that said “Datasource Added. Configure Queries.” Click on the word queries. (Otherwise, you can go back to the panel on the left side of your screen, go right below “Data Sources” and click on “Queries.” Then select “New Query +” from the top right.)
2. Give your report a name inside “Report Name*” on the very top left of your screen. We’re going to analyze an email campaign here, so let’s call this one “Email Campaign.”
3. In your Query Builder, click inside the “Tables” bar. Scroll down to “public.demo_sent” and click on that. This will automatically set up a Redshift query that returns the data within this table.
4. Head over to the bottom left hand of your screen and click on the blue “Preview” button in order to preview the data. You should see the results of an email campaign that includes various data such as the number of emails sent, opened, and clicked on, as well as the message type and the customer.
5. Once you’ve looked over your data, scroll to the bottom right corner of your screen and click the green “Save & Run Now” button.
As soon as your query was successfully completed, Knowi automatically saved the results of your query as a virtual dataset and then stored the results of that query as a dataset within its elastic data warehouse. Knowi does this every time you run a query.
Analyzing and Visualizing Your Data
Although you spent a little bit of time looking over your data, it’s unlikely that you learned anything from it in the format that it was in. There are a ton of things that we could figure out with our data, but let’s say we had to answer a burning question: do emails sent to certain customers have a higher conversion rate? Knowi allows us to efficiently answer this question and then visualize our results with the following steps:
1. Head to the top of the panel on the left side of your screen and click on “Dashboards.” Click the orange plus icon and name your dashboard. We’ll call this one “Email Visualization.”
2. Head back to the panel, just below “Dashboards,” and click on “Widgets.” Select the “Email Campaign” widget that you just created and drag it onto your dashboard.
3. Right now, the visualization that you see is just a data grid. We’re going to change this to something a little easier on the eyes, but first we have to add the metric that we’re looking for. In order to do this, scroll to the top right corner of your widget. Click on the 3 dot icon, then scroll down to “Analyze” and click on it.
4. Head to the top left corner of your screen and find “+Add Function.” The function that we’re looking to create is very simple: it’s called “conversion rate” and it’s calculated by dividing “conversions” by “sent.” In order to calculate this, click on “+Add Function,” then set “Name” as Conversion Rate and “Operation” as (conversions/sent)*100.
5. Right now, all we see is the conversion rate of each individual email campaign. What we want to see is the conversion rate of all email campaigns grouped by customer, and we also want our data sorted by conversion rate. First, we need to drag the “customer” bar from the left side of the screen over to the “Grouping/Dimensions:” box and let go.
6. Now we just need to drag our new “Conversion Rate” metric from “Fields/Metrics:” over to “Sort by:” and change the direction to descending in order to sort our data from the highest conversion rate to the lowest. As you can see, Facebook emails have a conversion rate of just over 1%, while Netflix emails have a conversion rate of less than 0.5%.
7. Now it’s time to visualize everything. Head back to the top of your screen and click on “Visualization.” Change your visualization type from “Data Grid” to “Column.”
8. Now you can see each customer ranked by the conversion rate of their emails. Head to the top right corner of your screen and click on the “Clone” icon which looks like two pieces of paper. Name this one “Email Campaign – Conversion Rates” and click the orange “Add to Dashboard” button.
Just like that, you’ve turned your raw data into a visualization that contains valuable information. The knowledge that Facebook’s conversion rate is about two and a half times higher than Netflix’s may be factored into future decision making.
Adding Drilldowns to Your Visualization
The next step to improving our visualization is to make it more interactive and navigable by adding drilldowns. Drilldowns are a powerful feature within Knowi that allow the user to take a deeper dive into a filtered section of the raw data with just one click. Here’s how we’ll add a drilldown to our widget:
1. Click on the 3 dot icon in the top right corner of your new widget, scroll down to “Drilldowns” and click on it.
2. Set your Drilldown type as “Widget,” set it to drill into “Email Campaign” when “Customer” is clicked, and set customer = customer in your optional drilldown filters. Click the orange “Save” button at the bottom right corner of the Drilldowns popup.
3. Test it out by clicking on Facebook, the customer whose conversion rate is the highest. As you can see, this returns every campaign where Facebook was the customer. Then get back to your original visualization, head back to the top right corner of your widget and click on the left arrow icon in the middle of that corner.
Querying Your Data with Search-Based Analytics
Your dashboard is set up, which means you’re fully prepared to query your data using search-based analytics. This means you’re ready to share your dashboard and your data with anybody who speaks English, even if they’re unfamiliar with Knowi. Here’s how to query your data using search-based analytics:
1. Head to the top right corner of your original “Email Campaign” widget and click on the 3 dot icon. Scroll down and click on “Analyze.”
2. Let’s say you want to monitor email activity by month in order to see if things looked any different in different months. In order to do this, head to the search bar at the top of your screen and type “total sent, total opened, total clicks, total conversions by month” and then hit enter. Knowi’s natural language processing will quickly provide you with what you’re looking for.
3. Now it’s time to visualize this data. Head back to “Visualization” and set the visualization type to “Area.” This will show us the total number of emails sent, and the total number of conversions per month. The conversions are so low that it’s hard to see any movement in the number with our naked eye, but that’s okay.
4. Head back to the top right and click on the “clone” icon once again. Name this widget “Sent and Conversions – Area,” clone it, and then add it to your dashboard.
5. Last, head back to your dashboard. Drag your new “Email Campaign – Area Visualization” widget to the top of your dashboard, which will bring the original “Email Campaign” widget to the bottom.
This data conveys another valuable piece of insight: these email campaigns receive a low number of opens and clicks and an extremely low number of conversions for every email that they send. While these numbers remain consistently low every month, they do seem to increase alongside the number of total emails sent.
It’s also important to remember that we didn’t need extensive coding knowledge or experience with Knowi to do what we just did. The low barriers to usage here makes Knowi’s dashboards accessible to any curious English speaker.
Summary
In summary, we connected to an Amazon Redshift Datasource and made a query on our new datasource. This stored the results of our query in Knowi’s elastic data warehouse. We then analyzed and visualized our data, and added drilldowns to our visualization that allow the user to drill in on a filtered section of the raw data that they’d like to learn more about. Lastly, we used search-based analytics to answer another question and visualize our answer.
