BigQuery Datasets - Understanding Schema

BigQuery is a fully managed enterprise data warehouse. Currently, BigQuery integration with Pronto allows you to pull data from Accounts, Campaigns, Contacts, Leads, and Opportunities. This document helps to understand the data schema between BigQuery and Pronto.  
 

BigQuery Data 

In the BigQuery console, we need to create datasets under the project, and tables under datasets to store data. 

• Accounts
  • Create a dataset and for the table “Account”, users can upload a ‘.csv’ file, with a convenient name. 
  • The accounts fields that are mandatory while creating a new record in Pronto are ‘Name’ and ‘Source record Id’. 
  • The fields and the data type that can be added to the account table are: 
    • • Id → integer (Please contact Pronto support if you would like to use string ids) 
      • Account name → string
  • Other fields can be added as per requirement.

--------------------------------------------------------------------------------------------------------------------------

• Campaign
  • Create a dataset and for the table “Campaign”, users can upload a “.csv” file, with a convenient name.
  • The campaign fields that are mandatory while creating a new record in Pronto are ‘Name’, ‘type’, ‘stage’, ‘owner’, and ‘company’. 
  • For Campaigns, we need to add two tables, for campaign stages and campaign types. The fields in these tables should be ‘id’ and ‘name’. It is case sensitive and ‘id’ field accepts only integer values. 
  • The campaign table will have a stage_id field which will contain the stage_id(integer value), and the campaign_stage table will contain the id which will correspond to the stage name.
  • The fields and the data type that can be added to the campaign table are: 

• • • • id                        → integer 
      • Name                → string 
      • type_id            → integer 
      • Stage_id         → integer 
      • Owner_email → string 
      • Company        → string 
        
        
      • Campaign_stage: 
        • • id         → integer
          • Name → string
            
            
      • Campaign type:
        • • id → integer
          • Name → string 

• For modules like Campaign, we need to add type and stage tables, since they have separate mapping in Pronto.  
  
  
  

--------------------------------------------------------------------------------------------------------------------------

• Contacts: 
  • Contacts also have a similar process to that of campaigns.
  • Create a dataset and for the table “contact”, users can upload a “.csv” file, with a convenient name.
  • The contact fields that are mandatory while creating a new record in Pronto are ‘Last_Name’ and ‘Email'.
  • For contact, we can add two tables, for contact_source and contact_rating. The fields in these tables should be ‘id’ and ‘name’.
  • The contact table will have a source_id field which will contain the source_id(integer value), and the contact_source table will contain the id which will correspond to the source name.
  • The fields and the data type that can be added to the contact table are: 
    
    
    • • id                        → integer
      • First_name     → string
      • Last_Name    → string
      • Email                 → string
      • account _id     → integer
      • source_id        → integer
      • rating_id          → integer
      • Owner_email → string

Other fields can also be added as per requirement. 

• • • Contact_rating:    
      • id → integer
      • Name → string  

• • • Contact_source:
      • id → integer
      • Name → string 
        
        
        

--------------------------------------------------------------------------------------------------------------------------

• Leads
  
  • Leads have a similar process to that of campaigns and contacts.  
  • Create a dataset and for the table “lead”, users can upload a “.csv” file, with a convenient name. 
  • The lead fields that are mandatory while creating a new record in Pronto are ‘Last_Name’, ‘Company', 'Email’, ‘Lead status’. 
  • For leads, we can add three tables, for lead_rating, lead_source and lead_status. The lead_status table is mandatory. The fields in these tables should be ‘id’ and ‘name’. 
  • The lead table will have a source_id, rating_id, status_id field which will contain the id values, and the lead_source, lead_rating, lead_status table will contain the id values which will correspond to the source name, rating, and status, respectively.  
  • The fields and the data type that can be added to the contact table are: 

• • • • id                         → integer 
      • First_name      → string 
      • Last_Name      → string 
      • Email                  → string 
      • account _id     → integer 
      • source_id        → integer 
      • rating_id          → integer 
      • Status_id         → integer 
      • Owner_email → string 

Other fields can also be added as per requirement. 

• • • Lead_rating:
      • id         → integer
      • Name → string  

• • • Lead_status:
      • id          → integer
      • Name → string  

• • • Lead_source:
      • id          → integer
      • Name  → string 
        
        
        

--------------------------------------------------------------------------------------------------------------------------

• Opportunity:
  • Opportunity has a similar process to that of campaigns, contacts, leads.  
  • Create a dataset and for the table “opportunity”, users can upload a “.csv” file, with a convenient name. 
  • The opportunity fields that are mandatory while creating a new record in Pronto are ‘Name’, ‘opportunity stage’,‘deal_size’, ‘account_name’,‘Deal close date’, ‘opportunity owners’, ‘owner_email’.  
  • For opportunity, we can add two tables, for opportunity_stage, opportunity_type. The ‘opportunity_stage’ table is mandatory. The fields in these tables should be ‘id’ and ‘name’. 
  • The opportunity table will have a stage_id, type_id field which will contain the id values, and the opportunity_stage, opportunity_type table will contain the id values which will correspond to the stage and type, respectively.  
  • The fields and the data type that can be added to the contact table are:
     
    
    • id                                     → integer
    • Opportunity_name   → string
    • close_date                   → Date
    • account _id                 → integer
    • type_id                         → integer
    • stage_id                       → integer
    • Owner_email              → string

Other fields can also be added as per requirement. 

• • • Deal_type :
      
      • id        → integer
      • Name → string  

• • • Deal_stage :
      
      • id        → integer
      • Name → string