MySQL as an External Data Source

You can connect KISSmetrics to your MySQL database. This will import all existing data and then continuously monitor your database for any new data and pass that into KISSmetrics. This is a powerful and easy way to integrate KISSmetrics with your app.

If you have a non-MySQL database (Oracle, Postgres, SQLite, Mongo), we have not yet built support to directly read from these database types. Let us know that you are interested in seeing such an integration! In the meantime, please consider converting your data into .csv files and using our recurring CSV import method.

It’s recommended you read People, Events and Properties before getting started here.

Setting up your database

The KISSmetrics SQL integration will only work with a read-only database. We enforce this for all users for our users peace of mind. Additionally we recommend that you create a separate MySQL user for KISSmetrics to use. The following SQL will accomplish this:

CREATE USER 'kissmetrics'@'%' IDENTIFIED BY 'some_password';
GRANT select ON your_database.* TO 'kissmetrics'@'%';
FLUSH PRIVILEGES;

Just make sure you note the username (kissmetrics in this example) and password (some_password in this example) because you’ll need those when you create the connection

Updating your firewall rules

KISSmetrics will connect to your MySQL server using one of the following IP addresses:

You’ll want to make sure that your firewall allows connections to the port you run MySQL on (which by default is 3306) from these IP addresses. If you have other security needs please contact support so that we can help you figure out the best solution for your situation.

If your database is hosted on Amazon, then we would be locating your databases using the internal Amazon network. In that case, KISSmetrics will connect to your MySQL server using one of the following internal Amazon IP addresses:

Creating the connection

To create the connection:

Creating queries

You will need to create one query for each event you want to track. When creating your SQL query you’ll need to make sure that the results contain the following fields:

Query Screenshot

See below for more details about each.

KM_PERSON

The value you select for KM_PERSON will be used as the identity for the person performing the event. As mentioned the best choice, when available, is the e-mail field form your table. To rename email as KM_PERSON you can use the SELECT...AS... syntax to name the field in the results (e.g. SELECT email AS KM_PERSON).

It’s possible that the identity field you want to use is in a different table. In this case you might need to use a SQL JOIN to join the two tables. For example if I have a payments table that I’m creating a query for I might do:

SELECT
  users.email AS KM_TIME...
FROM
  users, payments...
WHERE
  users.id = payments.user_id;

KM_TIME

The value you select for KM_TIME will be used as the date/time for the event you syncing. KISSmetrics will not sync without this field. The column type can be any valid date field, including just a plain INT field with a UNIX epoch-based timestamp. You can use the SELECT...AS... syntax to name the field in the results (e.g. SELECT created_at AS KM_TIME).

Please note that KISSmetrics will set the timezone to UTC/GMT (SET time_zone = "+0:00") for the connection before making any queries.

KM_LAST_RAN

When KISSmetrics syncs it will sync at most 10,000 rows every hour per query. When it runs the next hour it will need to make sure that it selects the next 10,000 rows - making sure not to duplicate or miss any rows.

This is where the KM_LAST_RAN field comes in. There are a few key things to keep in mind:

A full query might look like:

  SELECT
    id AS KM_LAST_RAN
    ...
  FROM
    users
  WHERE
    id > $KM_LAST_RAN_FOR_THIS_QUERY

Additional properties

Any additional fields you return in your SQL will be recorded as KISSmetrics properties for the person/event. So let’s say you have gender and country columns in your table that you want to pass along when a user signs up. You could do:

SELECT
  id AS KM_LAST_RAN,
  email AS KM_PERSON,
  created_at AS KM_TIME,
  gender,
  country
FROM
  users
WHERE
  id > $KM_LAST_RAN_FOR_THIS_QUERY

Whatever the name of the field is is what the property will be recorded as. If you want to use a different name you can use SELECT ... AS .... So if you had a field cpn that you wanted to record as Coupon Code you could use: SELECT cpn AS "Coupon Code".

Examples

Tracking when a user signs up

It’s quite easy to track all your signups:

SELECT
  users.login AS KM_PERSON,
  users.created_at AS KM_TIME,
  users.id AS KM_LAST_RAN
FROM
  users
WHERE
  users.id > $KM_LAST_RAN_FOR_THIS_QUERY

Imagine you also have a payment_plans table and you want to include the plan name when a user signs up, you can do:

SELECT
  users.login AS KM_PERSON,
  users.created_at AS KM_TIME,
  payment_plans.name AS 'Plan Name',
  users.id AS KM_LAST_RAN
FROM
  users, payment_plans
WHERE
  payment_plans.id = users.payment_plan_id AND
  users.id > $KM_LAST_RAN_FOR_THIS_QUERY

Tracking when a user pays you

SELECT
  users.login AS KM_PERSON,
  payment_transactions.created_at AS KM_TIME,
  payment_transactions.id AS KM_LAST_RAN,
  payment_transactions.amount
FROM
  payment_plans, users
WHERE
  users.id = payment_plans.user_id AND
  payment_plans.id > $KM_LAST_RAN_FOR_THIS_QUERY

Tracking when a user cancels their account

SELECT
  users.login AS KM_PERSON,
  users.canceled_at AS KM_TIME,
  users.canceled_at AS KM_LAST_RAN
FROM
  users
WHERE
  users.canceled_at IS NOT NULL AND
  users.canceled_at > $KM_LAST_RAN_FOR_THIS_QUERY

Filed under External Integrations

Search

Learn more about KISSmetrics

Get started now