Internal DB

Suggest Edits

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 about 2 years ago

What’s Next

Don't see what you're looking for? Browse the Activity Index for more examples...