Joining User Profiles

Each SaaS tool you use has its own way of identifying users with a unique primary key. And, you will find each of these different IDs across different collections of tables in your database. So, when you want to start matching Joe Smith who entered a ticket in Zendesk and also clicked through a campaign in Mailchimp, it starts to get tricky.

Expand image

For example, Stripe keeps track of users with a customer_id, Segment requires auser_id, and Marketo uses email to uniquely identify each person.

To effectively join across these sources, you need to understand how each id maps to each other. The best way to do this is to create a common identifier across tools.

When you install a new tool (or use Segment to install all of them at once), you need to choose what you will put in the ID field. There are lots of different options for this: emails, twitter handles, usernames, and more.

However, we suggest using the same ID you generate from your production database when you create a new user. Database IDs never change, so they are more reliable than emails and usernames that users can switch at their leisure. If you use this same database ID across as many tools as possible, it will be easier to join identities down the road. (In MongoDB, it would look something like this 507f191e810c19729de860ea.)

1
analytics.identify('1e810c197e', { // that's the user ID from the database
2
  name: 'Jane Kim',
3
  email: 'jane.kim@example.com'// also includes email
4
  });

Though we wish you could use a database ID for everything, some tools force you to identify users with an email. Therefore, you should make sure to send email along to all of your other tools, so you can join on that trait as a fallback.

Integrating as many tools as possible through Segment will make your joins down the road a little easier. When you use Segment to identify users, we'll send the same ID and traits out to all the destinations you turn on in our interface. (More about Segment destinations.

A few of our destination partners accept an external ID, where they will insert the same Segment user ID. Then you can join tables in one swoop. For example, Zendesk saves the Segment User ID as external_id, making a Segment-Zendesk join look like this:

1
SELECT zendesk.external_id, users.user_id
2
FROM zendesk.tickets zendesk
3
JOIN segment.usersusers
4
ON zendesk.tickets.external_id = segment.user_id

Here's a look at the Segment destinations that store the Segment User ID:

Whether you're using Segment or not, we suggest creating a master user identities table that maps IDs for each of your sources.

This table will cut down on the number of joins you have to do because some IDs may only exist in one out of many tables related to a source.

Here's sample query to create a master user identities table:

1
CREATE TABLE user_identities AS (
2
select
3
segment.id as segment_id,
4
segment.email as email,
5
zendesk.id as zendesk_id,
6
stripe.id as stripe_id,
7
salesforce.id as salesforce_id,
8
intercom.id as intercom_id
9

10
from segment.users segment
11

12
– Zendesk
13
leftjoin zendesk.users zendesk on
14
 ( zendesk.external_id = segment.id– if enabled through Segment
15
or zendesk.email = segment.email ) – fallback if not enabled through Segment
16

17
– Stripe
18
left join stripe.customers stripe on
19
 stripe.email = segment.email
20

21
– Salesforce
22
left join salesforce.leads salesforce on
23
 salesforce.email = segment.email
24

25
– Intercom
26
left join intercom.users intercom on
27
 ( intercom.user_id = segment.id– if enabled through Segment
28
or intercom.email = segment.email ) – fallback if not enabled through Segment
29

30
group by 1,2,3,4,5,6
31

32
)

You'll spit out a user table that looks something like this:

While creating this table in SQL is a good strategy, we'd be remiss not to point out a few drawbacks to this approach. First, you need to run this nightly or at some regular interval. And, if you have a large user base, it might take a while to run. That said, it's probably still worth it.

How to run a query with your joined data

So what can you do once you have all of your ID's mapped? Answer some pretty nifty questions that is. Here are just a few SQL examples addressing questions that incorporate more than one source of customer data.

Segment + Zendesk

1
-- Which referral source is sending us the most tickets?
2
SELECTsegment.referral_source,
3
COUNT(zendesk.ticket_id) AS count_of_tickets
4
FROM zendesk.tickets zendesk
5
LEFT JOIN segment.userssegment
6
ONusers.segment_id = segment.user_id
7
GROUP BY 1
8
ORDER BY 2 desc

Stripe + Zendesk

1
-- How many tickets do we receive across each pricing tier?
2

3
SELECT stripe.plan_name AS plan_name,
4
COUNT(zendesk.ticket_id) AS count_of_tickets
5

6
-- Start with Zendesk
7
FROM zendesk.tickets zendesk
8

9
-- Merge Users
10
LEFT JOIN user_identities users
11
ON zendesk.id = users.zendesk_id
12

13
-- Add Stripe
14
LEFT JOIN stripe.charges stripe
15
ON users.stripe_id = stripe.customer_id
16

17
-- Group by plan name, from most tickets to least
18
GROUPBY1
19
ORDERBY2desc

An alternative to the lookup user table in SQL would be writing a script to grab user IDs across your third-party tools and dump them into your database.

You'd have to ping the APIs of each tool with something like an email, and ask them to return the key or id for the corresponding user in their tool.

A sample script, to run on a nightly cron job, would look something like this:

1
var request = require('superagent'); // https://www.npmjs.com/package/superagent
2

3
var username = '<your-username>';
4
var password = '<your-password>';
5
var host = 'https://segment.zendesk.com/api/v2/';
6

7
/**
8
 * Gets the user object in Zendesk by email address.
9
 *
10
 * @param {String} email
11
 * @param {Function} fn
12
 */
13

14
functiongetUserIds(email, fn) {
15
 request
16
 .get(host + 'users/search.json?query=' + email)
17
 .auth(username, password)
18
 .end(fn);
19
}
20

21
/**
22
 * Get the first Zendesk user that matches 'kanye@kimye.com'
23
 */
24

25
getUserIds('kanye@kimye.com', function(err, res) {
26
if (err) return err;
27
// res.body.users will be an Array
28
// res.body.users[0].id will return the `id` of the first user
29
});

Here is the documentation for Zendesk's API for more information.