Last updated Nov 11, 2024

Snowflake

Automatically integrate customer conversion data with Rokt to optimize campaign performance.

Note: Currently in Public Beta

Data Pre-requisites

The Rokt Conversions API native application requires access to an up-to-date view of Conversions data.

All of the following column names are required to be present in the view; for any columns that you would not like to integrate with Rokt, fill in the column with the value "ROKT_FILLER_VALUE". Integrating additional data fields with Rokt enables better campaign optimization.

Note: Bolded columns are required for the integration to function. Furthermore, one of either email, emailsha256, or passbackconversiontrackingid is needed in order provide meaningful signal to Rokt.

Column Name	Column Type
eventtime	TIMESTAMP
clienteventid	VARCHAR
email	VARCHAR
emailsha256	VARCHAR
passbackconversiontrackingid	VARCHAR
amount	VARCHAR
conversiontype	VARCHAR
firstname	VARCHAR
firstnamesha256	VARCHAR
lastname	VARCHAR
lastnamesha256	VARCHAR
mobile	VARCHAR
mobilesha256	VARCHAR
ipaddress	VARCHAR
ipaddresssha256	VARCHAR
address1	VARCHAR
address2	VARCHAR
zipcode	VARCHAR
city	VARCHAR
stateName	VARCHAR
country	VARCHAR

Detailed definitions for each column can be found here.

Install

Install the app through the Snowflake Marketplace.

Configure

1) Grant the application read-only access to the data view that contains conversion events.

a) In the left hand panel of the Snowflake UI, click Data Products >> Apps.

b) Select the "Rokt Conversions API" app.

c) In the top-right corner, click the security shield icon.

d) Under the "Privileges" tab, update the "Privileges to objects" field to point to the data view you use for maintaining conversions data.

2) Within the same "Security" view, select the "Connections" tab.

a) When in this tab, please click the "Review" button for the "Connections bubble".

b) Accept that the application will send data to Rokt's api.rokt.com URL, and configure your authentication credentials.

For Username, enter the rpub token. For Password, enter the rsec token. You can get these values from your Rokt account manager.

3) Open a workbook within the ROKT_CONVERSION_API database, CORE schema that has been deployed to Snowflake.

a) Insert your Rokt account id into settings. Directions for getting your accountid.

INSERT INTO app_settings(id, roktAccountID)
    VALUES
    (1, '${MY_ACCOUNT_ID}');    

b) Updating the function defined by the application. Part 2) must be completed first or this step will fail.

ALTER FUNCTION core.sync_data_func(
    VARCHAR,
    VARCHAR,
    TIMESTAMP,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR,
    VARCHAR
) SET
    EXTERNAL_ACCESS_INTEGRATIONS = (reference('rokt_external_access'))
    SECRETS = ('rpub_rsec' = reference('rokt_auth_secret'));

c) Create the task that executes the user defined function every hour.

CREATE OR REPLACE TASK core.sync_data_task
    SCHEDULE = '60 MINUTE'
    SUSPEND_TASK_AFTER_NUM_FAILURES = 0  -- 0 = not suspending the task in case of an error
    TASK_AUTO_RETRY_ATTEMPTS = 1
    AS
    CALL sync_data_proc();

Integrating Conversion Data

The application will now, on an hourly cadence, read all rows in the conversion view that are new within the previous hour, and integrate that data with Rokt's Event API.