Connecting BigQuery to Churney Integration

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

We follow the Google standard for sharing data in a secure way, described in https://cloud.google.com/bigquery/docs/share-access-views

This guide covers these two steps:

Create dataset which will contain the hashed views
Assign permissions for Churney to access the views

‍

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 the hashed views

‍

To create a new dataset with the views, go on your project where the raw data is and click on the three dots to create a new dataset, for example, if your project is called churney_playground, click as below:

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.

‍

Example of creating a hashed view

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 `<your_project>.churney.sensitive_data_view` AS
WITH 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 `<your_project>.raw_data.sensitive`
)
SELECT
  TO_HEX(SHA256(email_normalized)) AS email,
  TO_HEX(SHA256(
    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,
  TO_HEX(SHA256(phone_digits)) AS phone,
  TO_HEX(SHA256(CONCAT('+', phone_digits))) AS google_phone,
  TO_HEX(SHA256(NULLIF(LOWER(TRIM(name)), ''))) AS name,
  TO_HEX(SHA256(FORMAT_DATE('%Y%m%d', birthday))) AS birthday,
  * EXCEPT(email, phone, name, birthday,
           email_normalized, email_local_part, email_domain, phone_digits)
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;

Expected output:

email_normalized          google_email_value   phone_value    google_phone_value
john.doe+promo@gmail.com  johndoe@gmail.com    442071838750   +442071838750
a.b@example.com           a.b@example.com      16505551212    +16505551212

Assign permissions for Churney

Churney will need permissions to read the hashed views. We base our guide on the best practices published on Google documentation https://cloud.google.com/bigquery/docs/share-access-view

‍

Churney will give you a service account and below you can find which permissions to assign to it:

1. Assign BigQuery User role to Churney service account https://cloud.google.com/bigquery/docs/share-access-views#assign_a_project-level_role_to_your_data_analysts

For this, you need to go to the IAM page of the project where you created the views for Churney and add BigQuery User to the service account. Note that this does not give Churney permission to access the data under your project.

2. Give Churney service account BigQuery Data Viewer permission to access the dataset with the views created previously https://cloud.google.com/bigquery/docs/share-access-views#assign_access_controls_to_the_dataset_containing_the_view

This requires that you go to the dataset and click on the three dots on the side to select Share.

‍

‍

Continue on Add Principal and add the Churney service account with the permission BigQuery Data Viewer

‍

‍

Authorize views

‍

Authorize the view to access the source data. This means that Churney, only through the view, would be able to read the data. https://cloud.google.com/bigquery/docs/share-access-views#authorize_the_view_to_access_the_source_dataset

For this step, go on the raw data dataset (in this example, that is called synthetic), and click on the dataset itself (not the 3 dots) and choose Sharing and then on Authorize Views

Then finally, type the name of the authorized view that you created above

Connecting BigQuery to Churney