メインコンテンツまでスキップ

Snowflake

Snowflake Logo

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 NameColumn Type
eventtimeTIMESTAMP
clienteventidVARCHAR
emailVARCHAR
emailsha256VARCHAR
passbackconversiontrackingidVARCHAR
amountVARCHAR
conversiontypeVARCHAR
firstnameVARCHAR
firstnamesha256VARCHAR
lastnameVARCHAR
lastnamesha256VARCHAR
mobileVARCHAR
mobilesha256VARCHAR
ipaddressVARCHAR
ipaddresssha256VARCHAR
address1VARCHAR
address2VARCHAR
zipcodeVARCHAR
cityVARCHAR
stateNameVARCHAR
countryVARCHAR

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.

configure-database-view

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.

configure-external-access

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

configure-rpub-rsec

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.

この記事は役に立ちましたか?