This table records referral events, where existing customers introduce new customers to the brand. The person who makes the referral is known as a referrer, while the individual who receives the referral invite is called a referee. This fact table captures all the information surrounding the referral event, such as the date and time of the referral, store associated with the referral, referrer and referee identifiers, and the campaign associated with the referral.
Databricks Table Name: referrals
Referrals Fact - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Referrals auto_update_time: bigint context_id: bigint dim_campaign_id: bigint dim_event_date_id: bigint dim_event_time_id: bigint dim_event_user_id: bigint dim_event_zone_till_id: bigint dim_latest_updated_date_id: bigint dim_latest_updated_time_id: bigint dim_referred_to_user_id: bigint event_type: string PK referee_id: bigint year: int Date LK date_id: int day_of_month: int week_of_year: int month: string year: int quarter: string week_number: int week_start_date: string week_end_date: string day_of_week: string month_no: int month_no_of_year: int month_of_year: string day_of_week_no: int quarter_no: int yearly_quarter_no: int date: string Date Time LK time_id: bigint time: string hour_of_day: int minute_of_day: int day_shift: string day_shift_no: int hour_range: string Time Zone_tills store_country: string external_id_1: string store: string auto_update_till_parent: bigint area: string store_name: string store_city: string store_state: string till: string auto_update_till_store_relation: bigint is_ffc_enabled: string LK till_id: bigint type: string till_description: string auto_update_till: bigint store_channel: string external_id: string is_billable: string store_id: bigint is_active: string auto_update_store: bigint store_description: string latitude: string timezone: string external_id_2: string till_name: string zone_name: string Zone Tills users auto_update_merged_customer: bigint auto_update_loyalty: bigint fraud_status: string test_control_bucket: string is_merged_customer: string subscription_status_email_bulk: string first_name: string slab_name: string subscription_status_wechat_bulk: string email: string last_name: string merged_user_id: bigint LK user_id: bigint subscription_status_wechat_trans: string subscription_status_sms_bulk: string ndnc_status: string subscription_status_email_trans: string test_control_status: string auto_update_fraud_user: bigint is_inactive: string source: string slab_number: int auto_update_users: bigint registered_till_id: bigint auto_update_customer_enrollment: bigint slab_expiry_date: string customer_external_id: string subscription_status_sms_trans: string mobile: string loyalty_type: string auto_update_ndnc_status: bigint Users Campaigns is_recurring: string campaign_end_date: string is_migrated: string auto_update_campaign: bigint LK campaign_id: bigint campaign_type: string roi_type: string campaign_start_date: string name: string Campaigns Referrals Legend
PK Primary Key
LK Linking Key
Referrals Fact Table
Column Name
Data Type
Description
Linked Table auto_update_time
bigint
Date and time when the corresponding table available at the source was last updated. It is in the Unix timestamp format.
_
context_id
bigint
Identifier for the context in which the action occurred, such as a specific campaign or promotion context.
_
dim_campaign_id
bigint
Identifier for the campaign associated with the referral action.
campaign
dim_event_date_id
bigint
Date when the referral action occurred.
date
dim_event_time_id
bigint
Time when the referral action occurred.
time
dim_event_user_id
bigint
Identifier for the customer associated with the referral action.
users
dim_event_zone_till_id
bigint
Identifier assigned to the point-of-sale (POS) terminal within a store, where the referral occurred. It distinguishes one checkout location from another within the same store.
zone till
dim_latest_updated_date_id
bigint
Date when the data corresponding to this event/row is changed in the source table.
date
dim_latest_updated_time_id
bigint
Time when the data corresponding to this event/row is changed in the source table.
time
dim_referred_to_user_id
bigint
Identifier for the customer who was referred (referee).
users
event_type
string
The type of event, such as "Referral Registration" (when a referee registers as a result of a referral) or "Referral Transaction" (when a referee makes a transaction as a result of a referral).
_
referee_id
bigint
Unique identifier for the referee (the customer who was referred). It is the primary key of the table.
_
year
int
Year of the referral.
_
