Modifying the Activity Stream

Special Activities

Special activity types can be used to modify existing data in the activity stream.

There are three types of special activities:

  • Alias
  • Cancelled
  • Refunded

Types of Special Activities

Special ActivityUsed forIndicated by
Alias Activity ScriptOverride and update a customer identifierExternalactivityid_source is set as customer in the activity SQL
Cancelled Activity ScriptRemove records from the activity streamAdd cancelled_prefix to the activity name
Refunded Activity ScriptAdjust revenue impact for some activitiesAddrefunded_ prefix to the activity name. Specify adjustment in the revenue column of the refund activity script.

Note: All special scripts should use the regular processing type to maintain the modifications.

Alias Activity Script

This script allows you to make one-time overrides to a customer’s identifier. Each separate customer_id is treated as a separate customer by Narrator, so the Alias Activity script allows you to consolidate two email addresses to associate them with the same customer. This type of script can in handy if your customers update their email address in your system.

To indicate that this script is a special activity, set external_activity_id_source = ‘customer’ in the SQL definition. Use the external_customer_id field to define the old customer identifier and use the customer field to define the new customer identifier. This script will replace all instances of the old customer identifier with the new identifier in your activity stream.

Use the regular processing type to ensure your alias is regularly applied historically and for future records.

Example:

SELECT
	 u.id  AS "external_activity_id"
	 , u.created_at  AS "ts"
	 , NULL  AS "external_customer_id_source"
	 , NULL  AS "external_customer_id"
	 , u.email  AS "customer"
	 , 'user_created'  AS "activity"
	 , NULL ::VARCHAR(255)  AS "feature_1"
	 , NULL ::VARCHAR(255)  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.users AS u

Cancelled Activity Script

This script allows you to remove specific records from the activity stream.

To indicate that this script is a special activity, add the prefix cancelled_ to the activity value in the script. The remainder of the activity name should match the name of the activity you want to delete. To delete records from an activity called “kitorders”, name the special script activity “cancelledkit_orders”.

The rest of the definition of the script should match the records you wish to remove. To process this type of script, Narrator finds all activity records that match the external_activity_id and activity name of the records in the cancelled activity script and remove them from the activity stream.

It is important to note that disabling this script will result in the deleted records being re-inserted into the activity stream.

Use the regular processing type to ensure the deleted records and continually kept out of the activity stream.

SELECT
	 u.id  AS "external_activity_id"
	 , u.created_at  AS "ts"
	 , NULL  AS "external_customer_id_source"
	 , NULL  AS "external_customer_id"
	 , u.email  AS "customer"
	 , 'cancelled_order_placed'  AS "activity"
	 , order_type ::VARCHAR(255)  AS "feature_1"
	 , NULL ::VARCHAR(255)  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue"
	 , NULL ::VARCHAR(255)  AS "link"
FROM warehouse.orders AS u

Refunded Activity Script

This script allows you to adjust revenue for specific activities in the activity stream.

To indicate that this script is a special activity, add the prefix refunded_ to the activity value in the script. The rest of the definition of the script should match the records you wish to modify, except for the revenue column. Any value in the revenue column will be added to the existing revenue column of matching activities in the activity stream. If you want to indicate a refund, use a negative revenue value that is equal to the original revenue value to subtract from the original amount.

It is important to note that disabling this script will undo the any modifications.

Use the regular processing type to ensure the deleted records and continually kept out of the activity stream.

Example:

SELECT
	 c.id  AS "external_activity_id"
	 , c.created  AS "ts"
	 , c.source_object  AS "external_customer_id_source"
	 , c.source_id  AS "external_customer_id"
	 , c.metadata_email  AS "customer"
	 , 'stripe_charged'  AS "activity"
	 , c.description  AS "feature_1"
	 , c.shipping_address_city  AS "feature_2"
	 , c.shipping_address_state ::VARCHAR ( 255 )  AS "feature_3"
	 , c.amount  AS "revenue_impact"
	 , 'https://your_company_here.stripe.com/charges/' || c.id  AS "link"
FROM stripe.charges AS c
JOIN stripe.refunds r
	on ( r.charge_id = c.id )
WHERE c.status = 'succeeded'