Bring scattered data together

Get one answer from data spread across several systems, by finding what the tables have in common and joining them once.

Before you start

• Permissions: anyone with access to the tables can look at them in the Library. Joining in a project adds steps, so you need to be able to edit the project.
• Data you need: tables from two systems that describe the same customers, orders or products. The example uses Curio’s Shopify customers and Salesforce contacts. See Explore the sample company.
• Credits this uses: browsing costs nothing. Each question in a project or to the Librarian uses a credit, and a view the Librarian builds uses one more.

Steps

Say you want to know which of Curio’s online shoppers also buy for a business. Shopify knows what they bought, and Salesforce knows who they work for.

1. Decide what one row should be

Before you join anything, decide what a row in the result stands for: a customer, an order or a product. Join customers to their orders and you get a row per order, with the customer’s details repeated on each one. Add up a customer-level number after that, and it’s counted once per order.

2. Find a key the tables share

What do the two tables have in common? You’re looking for a key: a column that means the same thing in both, like a customer ID, an order number or an email address. Open each table in the Library and read its columns.

Curio’s tables show what you’ll meet in your own data:

• Shopify customers and Salesforce contacts both have email. Some contacts use a work address in Salesforce, though, so email alone misses them.
• Many of Square’s loyalty customers share a phone number with a Shopify customer, but some of their names are typed or formatted differently.
• Salesforce accounts and NetSuite have no ID in common, and the account names drift between the two.

3. See what Querri already found

When data arrives, Querri works out which tables share a key. On the Visualize canvas, green lines join those tables, and at Close zoom, 150% and up, each line names its keys.

The nightly pass looks further. With Profile data and joins on in Nightly insights, it looks for joins between sources and for the same customer living in two systems, and brings what it finds to your Inbox. Under Evidence, Where the numbers come from lists each join, with the key it uses and the share of rows that match. Nothing changes until someone accepts the card.

4. Make the keys match

Keys that look the same can still fail to match. Watch for:

• spaces and capitals: “Sam@Example.com ” and “sam@example.com”
• leading zeros: “00123” and “123”
• phone numbers with and without dashes or a country code
• one key under two names: CustID and customer_id

Say how to line them up in the same message as the join.

5. Join them in a project

Ask in project chat, and name the tables, the key and how to match it. For example:

Join shopify_customers to salesforce_contacts on email, trimmed and lowercased. Keep every Shopify customer, and tell me how many matched a Salesforce contact.

Querri adds a step, usually a Multi-Table Query. In the Data Flow, dashed lines run into it from each table it joined.

Tables that hold the same kind of rows, like orders from Shopify and Square, don’t need a key. Ask to combine them into one table, and say how to label each source’s rows, for example a channel column that reads “Shopify” or “Square”.

6. Keep the join in a view

Once the join is right, ask the Librarian for a view that uses the same key and rules:

Build a view of Shopify customers matched to Salesforce contacts on trimmed, lowercased email.

The view rebuilds after each sync, so new customers get matched without anyone asking again. Querri reads it for the questions it clearly answers. See Views.

Record how the tables match as a fact too, so later queries on those tables follow it. Clean up messy data shows how.

Check your work

• The row count is what you expected, like one row per customer.
• You know how many rows matched, and the number makes sense.
• Nobody appears twice unless they really have two matches.
• A few customers you know look right on both sides.

Troubleshooting

Nothing matched. The keys are formatted differently. Compare a few values from each side, then say how to clean them.

You have more rows than you started with. The key isn’t unique on one side, so one row matched several. Ask which key values appear more than once, and decide which match to keep.

Rows went missing. Rows without a match were dropped. Say which table should keep all its rows.

There’s no shared key. Curio’s Salesforce accounts and NetSuite are like this. Ask Querri to match on names instead, then check the matches yourself, because names drift. The lasting fix is to store one system’s ID in the other.

Next steps

• Clean up messy data: fix names, dates and duplicates before you join.
• Views: why a view is the place to keep a join.
• Visualize: read the green lines between your tables.