Internal DB
Add all the main activities that you have in your internal database.
Some helpful tips:
- What are all the main tables that your db has and what customer activity does each represent?
- Look for timestamps and ask yourself why that timestamp exists, is it for the product or for understanding. If it is for understand then you may want to add it to the activity stream.
Signed Up
Customer signed up for the product or service
This transformation uses the first time a user was created in the database as the time of sign up.
Type: Activity Transformation
select
u.id as activity_id,
u.created_at as ts,
NULL as anonymous_customer_id,
u.email as customer,
'signed_up' as activity,
u.user_type as feature_1,
NULL as feature_2,
NULL as feature_3,
NULL as revenue_impact,
NULL as link
FROM internal_db.users u
select
u.id as activity_id,
u.created_at as ts,
NULL as anonymous_customer_id,
u.email as customer,
'signed_up' as activity,
u.user_type as feature_1,
NULL as feature_2,
NULL as feature_3,
NULL as revenue_impact,
NULL as link
FROM internal_db.users u
Customer Table
This transformation creates the customer table.
Type: Customer Transformation
select
u.email as customer
, u.geo_city as city
, u.geo_country as country
, u.geo_zip as zipcode
, u.geo_street_address as street_address
, u.phone as phone_number
, u.gender
, u.age
, u.dob as date_of_birth
from internal_db.users u
Added Product Review
A customer added a review on a product.
Type: Activity Transformation
SELECT
r.product_review_id as activity_id
, r.created_at as ts
, NULL as anonymous_customer_id
, u.email as customer
, 'added_review' as activity
, r.product_review_rating as feature_1
, r.product_review_text as feature_2
, p.sku as feature_3
, p.product_price as revenue_impact
, NULL as link
FROM internal_db.product_reviews r
JOIN internal_db.products p
on (p.product_id = r.product_id)
JOIN internal_db.users u
on (u.user_id = r.user_id)
Received Referral
A customer added a review on a product.
Type: Activity Transformation
SELECT
r.id AS "activity_id"
, r.created_at AS "ts"
, NULL AS "anonymous_customer_id"
, ru.email AS "customer"
, 'recieved_referral' AS "activity"
, r.text AS "feature_1" -- referral text
, r.type AS "feature_2" -- referral type
, u.email AS "feature_3" --referred person
, r.amount_recieved AS "revenue_impact" -- amount recieved
, NULL AS "link"
FROM internal_db.referral r
JOIN internal_db.user u
ON (u.id = r.referrer_id)
JOIN internal_db.user ru
ON (ru.id = r.referred_id)
Contribute to the Narrator Community
Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.
Have questions?
Our data team is here to help! Here are a couple ways to get in touch...
š¬ Chat us from within Narrator
š Email us at [email protected]
š Or schedule a 15 minute meeting with our data team
Updated over 2 years ago
Don't see what you're looking for? Browse the Activity Index for more examples...