MadKudu can give you direct access to your data through a direct SQL connection to Amazon Redshift.
1. Use cases
With MadKudu SQL, you can access your data directly in Redshift. Now you can run custom SQL queries, export large amount of data, or even build your own machine learning models.
The export include:
from all your existing integrations, as well as:
company and person information
- the results of MadKudu models
2. Getting started
MadKudu SQL is turned on demand. To get started, send us an email at email@example.com.
Once enabled, your access URL and username will be available on the MadKudu SQL integrations page.
When setting up MadKudu SQL, we will create and maintain a Redshift cluster for you. No need to have an AWS account or your own Redshift cluster.
If you would like the data to be hosted on your own cluster, we can do that as well!
If you prepare to load the data yourself, we can also unload the data to your S3 bucket and let you load it yourself at your convenience.
3. Compatible clients
For Mac users, Postico works out of the box without having to install any extra driver.
Amazon recommends to use SQL Workbench/J to run SQL queries on the database. Please see the “Getting started” section above for more details.
Follow these instructions to set SQL Workbench/J for MadKudu SQL access.
Note: Make sure to start by installing the Redshift JDBC driver.
Excel can be a great way to run some analyzes and creates reports. To connect to Redshift from Excel, follow these instructions.
Python is a very popular way to analyze data. To connect to your Redshift instance from Python, you can use a library like psycopg2.
To make it work on a mac:
Install psycopg2 by running
pip install psycopg2in a terminal.
Use a python script to connect to the data (see an example here).
You now have access to all the tables documented below.
R is a great free open source software environment for statistical computing and graphics. You can use a library like this one to pull data sets directly into R.
MadKudu SQL is compatible with Tableau. You will first need to install the Tableau Redshift Driver.
Mode Analytics / Metabase
When connecting to MadKudu SQL you will have access to multiple tables, one for each type of data.
These tables are a combination of standard tables and tables that are specific to your data.
3.1. List of standard tables
Here are some of the standard tables you will see:
These tables will always have the same standard columns. In addition, accounts and contacts will have a column for each of your custom properties.
The events table contains all the events recorded for your account.
It contains the following columns:
The eventsraw table has one column, for each of your event properties, prefixed with `a`.
For example, if one of your events has a property
category in your analytics platform, then the events table will have this information in the
(Note: it’s a bit confusing, but this prefixing is necessary to avoid collision with SQL reserved keywords)
3.2. Sample queries
Joining events to contacts
SELECT * FROM events AS e INNER JOIN contacts AS c ON e.contact_id = c.contact_id LIMIT 1000;
Find the email address associated to an event
SELECT e.event_id, e.event_timestamp, e.event, c.contact_id, c.email FROM events AS e LEFT JOIN contacts AS c ON e.contact_id = c.contact_id LIMIT 1000;
Find the company info associated to a contact
SELECT c.contact_id, c.email, af.* FROM contacts AS c INNER JOIN contacts_to_accounts AS c2a ON c.contact_id = c2a.contact_id INNER JOIN accounts_firmographics AS af ON c2a.account_id = af.account_id;
Find the likelihood to convert for users in their trial
SELECT t2.email, t1.likelihood_to_convert_segment, t1.logit FROM analysis_scoring_conversion_free_trial t1 INNER JOIN contacts t2 on t1.contact_id = t2.contact_id LIMIT 100;
4. Frequently asked questions
How frequently is the data refreshed?
Data is refreshed once a day.
How much does it cost?
Cost is based on the amount of data stored in Redshift. Please drop us a line at firstname.lastname@example.org to get a price.
How secure is it?
MadKudu enforce a strict security policy to make sure your data is safe. SSL connection to the database is mandatory. Whitelist of your IP address is highly recommended.