Connecting Databricks GCP to Churney Integration

Here you can read our guide for connecting your Databricks GCP data warehouse with Churney.

What kind of data is required?

The short answer is as much as possible. The long answer is that Churney requires data about:

Payments
Trials (if applicable)
User demographic (if available)
User activity
Attribution Data: Source of truth for campaign performance (UTM/MMP/ad network)

Additionally, we need to know the location (region) of your data warehouse.

‍

Create hashed views

‍

To create the views, we would need to create queries to hash the PII columns.

For context, Facebook has a guide on how to hash contact information for their conversion api: https://developers.facebook.com/docs/marketing-api/conversions-api/parameters/customer-information-parameters. Google has a similar guide for enhanced conversions using their ads api https://developers.google.com/google-ads/api/docs/conversions/enhance-conversions. Basically, we want to hash columns that contain data which would allow one to determine the identity of a user: First name, last name, birth day, street address, phone number, email address etc.

Normalization Patterns

The specific contact information columns normalization patterns are as follows:

email (Meta) — lowercase, leading and trailing spaces removed → john.doe+promo@gmail.com
google_email (Google) — same, and for gmail.com / googlemail.com also remove every period and any + suffix before the @ → johndoe@gmail.com
phone (Meta) — digits only, country code kept, leading zeros removed, no + → 442071838750
google_phone (Google) — the same digits with a + prefix → +442071838750

Also, please be aware of the following requirements:

Phone numbers must include a country code. Meta and Google both match on the country code, and Google rejects a number without one instead of repairing it. Always store the country code, even when every customer is in one country. Churney cannot add it later, because a SHA-256 hash cannot be reversed.

Drop the national trunk prefix. A UK number written 020 7183 8750 is +44 20 7183 8750, not +44 020 7183 8750. The SQL below removes leading zeros and the 00 international prefix, but it cannot find a trunk zero in the middle of a number.

Do not hash an empty value. SHA256('') is a valid 64-character hash that every customer without a phone number would share. Churney rejects it. The SQL below returns NULL instead. Do the same for placeholder strings such as none or null.

Hashes must be lowercase hex, 64 characters, with no 0x prefix.

Two columns per identifier. Meta and Google need different normalization, so share both email and google_email, and both phone and google_phone.

‍

Create a dataset named churney .

If the raw data contains email, phone, birthday or other identifiers, the columns need to be excluded and hashed in the view.

Example, if the raw data lies under raw_data.sensitive and you would like to create a view churney.sensitive_data_view

‍

CREATE OR REPLACE VIEW test.churney_views.users AS
WITH normalized AS (
  SELECT
    *,
    NULLIF(LOWER(TRIM(email)), '') AS email_normalized,
    SUBSTRING_INDEX(NULLIF(LOWER(TRIM(email)), ''), '@', 1) AS email_local_part,
    SUBSTRING_INDEX(NULLIF(LOWER(TRIM(email)), ''), '@', -1) AS email_domain,
    NULLIF(REGEXP_REPLACE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^0+', ''), '') AS phone_digits
  FROM test.raw_data.users
)
SELECT
  user_id,
  SHA2(email_normalized, 256) AS email,
  SHA2(
    CASE
      WHEN email_domain IN ('gmail.com', 'googlemail.com')
           OR email_domain LIKE 'gmail.co.%'
           OR email_domain LIKE 'googlemail.co.%'
      THEN CONCAT(REPLACE(REGEXP_REPLACE(email_local_part, '\\+.*$', ''), '.', ''), '@', email_domain)
      ELSE email_normalized
    END, 256) AS google_email,
  SHA2(phone_digits, 256) AS phone,
  SHA2(CONCAT('+', phone_digits), 256) AS google_phone,
  SHA2(NULLIF(LOWER(TRIM(full_name)), ''), 256) AS full_name,
  SHA2(DATE_FORMAT(birthday, 'yyyyMMdd'), 256) AS birthday,
  country_code, signup_date, created_at
FROM normalized;

If you want to test it how the script looks with some data:

WITH sensitive AS (
  SELECT 1 AS id, 'abc' AS name, '  John.Doe+promo@Gmail.COM ' AS email,
         DATE '1990-04-05' AS birthday, '+44 20 7183 8750' AS phone
  UNION ALL SELECT 2, 'jane doe', 'a.b@example.com', DATE '1991-01-02', '00 1 (650) 555-1212'
),
normalized AS (
  SELECT
    *,
    NULLIF(LOWER(TRIM(email)), '') AS email_normalized,
    SPLIT(NULLIF(LOWER(TRIM(email)), ''), '@')[SAFE_OFFSET(0)] AS email_local_part,
    SPLIT(NULLIF(LOWER(TRIM(email)), ''), '@')[SAFE_OFFSET(1)] AS email_domain,
    NULLIF(REGEXP_REPLACE(REGEXP_REPLACE(phone, r'[^0-9]', ''), r'^0+', ''), '') AS phone_digits
  FROM sensitive
)
SELECT
  email_normalized,
  CASE
    WHEN email_domain IN ('gmail.com', 'googlemail.com')
         OR email_domain LIKE 'gmail.co.%'
         OR email_domain LIKE 'googlemail.co.%'
    THEN CONCAT(REPLACE(REGEXP_REPLACE(email_local_part, r'\+.*$', ''), '.', ''), '@', email_domain)
    ELSE email_normalized
  END AS google_email_value,
  phone_digits AS phone_value,
  CONCAT('+', phone_digits) AS google_phone_value
FROM normalized;

And the result should be:

Given this input row:
  full_name     = 'abc'
  email    = 'John.Doe+promo@Gmail.com'
  phone    = '+1 (650) 555-1212'
  birthday = 1990-04-05

The view returns:
  full_name          = ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
  email         = 80f9126efd0446cb338c54ea6fe3d4af8fa3fe93db0024b14150441550e632a2
  google_email  = 06a240d11cc201676da976f7b49341181fd180da37cbe40a77432c0a366c80c3
  phone  = e323ec626319ca94ee8bff2e4c87cf613be6ea19919ed1364124e16807ab3176
  google_phone  = 1e231c66011e7a2d867a9cfae267a6aff103cf4913640b6e71a99850fc0ffbc8
  birthday      = 01433d270632f5b4e3bff9da6f27d5c1fdf1f5eb45442adfa5f2f20bd6b6503b

These are the SHA-256 hashes of, in order:
  'abc', 'john.doe+promo@gmail.com', 'johndoe@gmail.com',
  '16505551212', '+16505551212', '19900405'

Create a service principal for Churney

Go to your account -> Settings, and then Identity and access -> Users and create the new service principal

Then go to the service principal to generate a token as below. The maximum lifetime is 730 days, so please use that. Store the secret somewhere safe for now.

Create a group for the churney service princial as below:

And add the service principal to the group:

Create a storage credential

Churney will give you the name of their gs bucket for this step.

Go to Catalog -> Storage Credentials -> Create credentials

‍

Note down the service account you will see and share it with Churney. This is the service account that will access Churney’s google storage bucket to unload the data.

‍

Go to the storage credential and grant the below permissions to yourself

And finally run

CREATE EXTERNAL LOCATION IF NOT EXISTS `churney_external`
URL 'gs://<bucket_name>/'
WITH (STORAGE CREDENTIAL `churney-storage-credential`);

Where the <bucket_name> contains the gcp bucket of Churney.

Then run

GRANT CREATE EXTERNAL TABLE ON EXTERNAL LOCATION `churney_external` TO `Churney`;
GRANT READ FILES ON EXTERNAL LOCATION `churney_external` TO `Churney`;

‍

Grant permissions on schema

‍

Let’s assume that you would like to share the views under churney_views with Churney

Churney will create external tables pointing at the gs bucket, so let’s create a schema for the churney_exports:

CREATE SCHEMA test.churney_exports;

Churney will maintain the exports here, so grant the permissions below

For the churney_views schema, Churney only needs Data reader, so grant those permissions as below

Grant permissions to the warehouse

Finally, go the your warehouse, and add the service principal as below:

What to share with Churney

The token generated for the user (in a safe way) and the id of the principal. You can find the id as below:

The region of your Databricks (e.g. europe-west1)
The name of the export dataset create for Churney (in this example churney_exports)
The service account generated from the storage credential setup
The catalog name (in this example test)
The connection details of the sql warehouse: server_hostname and http_path as below

‍

‍

Connecting Databricks GCP to Churney