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 |
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.