Creating your First Script
To add activities to the activity stream, you'll need to define SQL logic to transform source data into the standardized activity stream format so it can be processed and added to the activity stream. This can be done with a script.
Learn More: Transformation Scripts
The process to create a script is simple:
- Define the SQL logic to transform your data
- Validate that it meets the requirements
- Set the metadata: Set the script name, description, and timing
- Configure the processing type and schedule
- Submit the script for review
All scripts are created and managed in the Data Management portal. Navigate to the Data Management section and select Activity Stream Scripts on the left nav.
All scripts that create activities in the activity stream are referred to as Activity Stream Scripts. Most companies have many activity stream scripts and only a few Enrichment and Customer Attribute scripts.
Use the + Create New Script button to start defining your first script.
Note: You'll need the correct level of permissions to create a script. If you don't have permissions, contact your account admin for access.
In this section you can access data in your warehouse to transform it into an activity so it can be added to the activity stream. Recall, each activity relates to a concept in your business. For example, it can represent each time a customer placed an order or each time the customer requested a refund.
For this example, we will create an activity to represent when a customer started a session on the website.
Each concept needs to be transformed into the format of an activity. The SQL editor shows a partial SQL query to get you started. You can use the panel to the left of the SQL editor to see the data available to you in your data warehouse.
Each query requires the following fields to be defined, some of which can remain null if they’re not relevant.
|Column Name||Data Type||Required||Description|
|acivity_id||VARCHAR(255)||Required||Identifier of the activity used by the source. Ex. ‘event_223334’|
|ts||TIMESTAMP||Required||Timestamp of the activity.|
|souce||VARCHAR(255)||Optional (required if source_id is not null)||Type of the customer identifier, if available. Used for user mapping. Ex. ‘phone’|
|source_id||VARCHAR(255)||Optional||Customer identifier used by the data source, if available. Used for user mapping. Ex. ‘cust_22342’|
|customer||VARCHAR(255)||Optional||Value of the customer identifier that is used company-wide, if available. Can be email address, slug, or address.|
|activity||VARCHAR(255)||Required||Name of the activity. Ex. ‘upgrade_license’|
|feature_1||VARCHAR(255)||Optional||Custom field for metadata related to the activity.|
|feature_2||VARCHAR(255)||Optional||Custom field for metadata related to the activity.|
|feature_3||VARCHAR(255)||Optional||Custom field for metadata related to the activity.|
|revenue_impact||FLOAT||Optional||Amount of revenue associated with the activity, if available. Simplifies common revenue calculations.|
|link||VARCHAR(255)||Optional||Url associated with the activity, if available. Often associated with interactions on a website.|
The best way to get familiar with this format is to look through some example templates to get a sense of how they’re built.
Example SQL for our script:
SELECT p.session_id AS activity_id, p.session_ts AS ts, ‘segment’ AS source, p.user_id AS source_id, p.user_email customer, 'started_a_session' AS activity, p.utm_source::varchar(255) AS feature_1, p.referring_url::varchar(255) AS feature_2, NULL ::VARCHAR(255) feature_3, NULL ::FLOAT revenue, p.landing_page_url::varchar(255) AS link FROM segment.sessions AS p WHERE p.domain like ‘www.mycompany.com’
Tip: Preview the query output by pressing the Run Query button on the bottom right of the SQL editor. The output will show up to 1,000 rows of your query output, and you can inspect the data from here to ensure it meets your needs.
The Stream Table Columns panel to the left of the Dataset preview can be used to rename the otherwise ambiguous "Feature" columns to something more human-interpretable. The renamed columns will appear in the Narrator UI so users can understand what is in each column.
This step is optional, but highly recommended.
Note: These columns cannot be renamed directly in the SQL query because feature1, feature2, feature3, revenueimpact, and link are standard column names for the activity stream. These column names need to remain consistent in order to append (UNION) the new activities to the existing activity stream. For this reason, Narrator provides the option to store meta-data about each activity so that human-interpretable names can still be available.
All scripts must be validated to ensure they meet the standards necessary to add them to the activity stream. Narrator runs a series of validations to help you transform your data and SQL into the right format. Once you feel good about the activity logic you've created, press Validate and Continue to validate it.
Script validation is a two step process:
- Ensure the SQL can run
- Check script requirements and configure activities
Validation Step 1: Validate SQL
This step ensures the SQL can be executed. This step is similar to the validation completed by any SQL client.
Validation Step 2: Check Requirements and Configure Activities
The second step checks that the script meets the requirements to be added to the activity stream.
It checks the following:
Necessary columns are present:
- A timestamp (
ts) is available for every activity
If the validation criteria not met, the script cannot be created and you should go back to modify the logic until this criteria is met. We're here to help if you're still having trouble, feel free to use the Ask Ahmed chat or email email@example.com/docs to get quick support.
Confirm Activities Generated
The last part of the validation confirms the activities that will be generated from the script and provides the option to disable any of them. For your first script, you should keep the activities enabled.
Now that the script has been validated, the next step is to name and configure it.
It is recommended to give a descriptive name like "Web Sessions" instead of "Script 1" so that others can quickly reference it from list of scripts.
If you would like one script to run after another, then you can select the yes checkbox and use the dropdown to specify the script that should be run before this script.
Select the processing method you would like to use to update your script by selecting one of the four options provided. Most scripts use the Regular processing type. If your data is generated chronologically and isn't mission-critial to the business (ie. web sessions are not mission critical to most business operations), it is recommended to use the Regular processing type.
Each processing type has additional detail on the selection, but if you want to know more about how they are run you can read it here.
The last step is to review the details of your script.
If you haven't already added names for the columns headers, you can edit them here. The review step let's you see the validations conducted and the activities that will be generated from your script.
If there's anything you want to change, you can move back to a previous step by using the back link at the upper right.
Finally, if you are happy with your script, then you can Submit for Approval. This will send your script to the Narrator team for review. Usually the review process takes a few hours, but always less than 24 hours.
You can see all scripts awaiting review in the Pending Approval section of the Scripts section. If at any point you wish to edit an script before it has been approved, you can do it from this section.