This article outlines a series of steps to do the following:
- Create Dataset on BigQuery for
- Accounts
- Campaigns
- Opportunities
- Contacts
- Leads
- Create Tables on BigQuery for
- Accounts
- Account list
- Account list
- Campaigns
- Marketing Campaigns
- Campaign stages
- Campaign types
- Opportunities
- Opportunities list
- Opportunity stages
- Opportunity types
- Contacts
- Contact list
- Contact source
- Contact rating
- Leads
- Leads list
- Lead status
- Lead source
- Lead rating
- Accounts
Pre-requisite
- Create a new Project
- Create a new Service account
- Create Role and Add permissions
- Click on the Menu Button, BigQuery option, them the {SQL workspace} selection.
- On the node name, click the options button and then select [Create dataset]
Note: The nodes name will differ from every instance, in this example the node name is randomly named my-project-abhishek-345708 this will be different for every user.
Create Opportunity dataset
- The Create dataset dialog box will appear, in this example we will be adding "Opportunity" under the Dataset ID text box, then click [CREATE DATASET] to continue.
- Click on the Opportunity dataset option button, click [Create table].
- The Create table dialog box will pop up.
Under SOURCE section, for the {Create table from} choose Upload and for {Select file} choose a CSV file to upload. In this example the file name "Opportunity.csv" is selected.
Note: The file needs to be a CSV and can not be an Excel .xls or ,xlsx format.
Under the Destination section, for the {Table} choose a name that best represents the data. For this example the name of the table was called "Opportunities"
Under the Schema section, put a check in the {Auto detect] box. Then click the [CREATE TABLE] button. - By clicking on the table on the left sub-navigation menu and clicking Preview, you should now see rows data entered into the table you created imported from the .csv file.
- After uploading the Opportunities table, we need to upload the Opportunity type and the Opportunity stage tables. Follow the same procedure we did for creating table for Opportunities.
- Similarly for the Opportunity stage, create the table.
Create Accounts dataset
- As every Opportunity is associated to an Account, the Accounts Dataset and the table has to be created so that the Accounts details can be populated on Pronto under Customer-Accounts
- Steps to create a dataset for Accounts on GCP - BigQuery
- Steps to create table to import Accounts data.
- Steps to create a dataset for Accounts on GCP - BigQuery
Create Campaigns dataset
- Once the Opportunity and Accounts dataset and tables are created, let us go ahead and create the Dataset for Campaigns.
- Steps to create a Dataset for Campaigns on GCP - BigQuery
- Steps to create table to import the Campaign data.
- By clicking on the Campaigns tab and clicking Preview, you should be able to see the data you imported from the .csv file.
- Steps to create a Dataset for Campaigns on GCP - BigQuery
- Under the Campaigns dataset, we need to create two tables.
- Campaign type
- Campaign stage
Follow the same steps used earlier to create tables for Opportunity type and stage by importing the .csv file for Campaign type. Once the file upload is successful, you should be able to see the Campaign type tables created as shown in the screenshot below. By clicking on Preview, you should be able to see the data that is imported.
Similarly for the Campaign stage, once the table is created, by clicking the Preview button, you should be able to see the stages as shown in the screenshot below.
Create Leads dataset
- Next, create the Dataset and for Leads.
Under the Leads dataset, we need to create 4 tables
a. Leads
b. Lead Source
c. Lead Status
d. Lead Rating
- Create a Dataset for Leads, as we did before for Opportunity and Campaigns.
- Under the Leads dataset, follow the same steps used earlier to create tables for Opportunity type and stage by importing the .csv file for Leads, Lead source Lead rating & Lead Status. Once the files upload are successful, you should be able to see the Leads, Lead Source Lead rating & Lead Status tables created as shown in the screenshot below. By clicking on Preview, you should be able to see the data that is imported.
- Create a Dataset for Leads, as we did before for Opportunity and Campaigns.
Create Contacts dataset
- Next create the dataset for Contacts.
Under the Contacts dataset, we need to add three tables.
a. Contacts
b. Contact Source
c. Contact Rating
Follow the same steps used earlier to create dataset and tables for Opportunity type and stage by importing the .csv file for Contacts. Once the file upload is successful, you should be able to see the Contacts. Contact source and the Contact rating table created as shown in the screenshot below. By clicking on Preview, you should be able to see the data that is imported. - Here you will find the sample .csv files for all the Dataset and tables that you need to create. If you are having trouble uploading data with your existing .csv files, you may download and use these files to add and upload data.
For any questions, you can contact Pronto support on support@gopronto.io.
Comments
0 comments
Please sign in to leave a comment.