Use Salesforce batch source plugin to analyze leads data in BigQuery

Configure the Cloud Data Fusion Salesforce batch source plugin

1. Go to the Cloud Data Fusion Instancespage:

   Go to instances

2. For your instance, click View instance. The Cloud Data Fusion web interface opens.

3. Go to the Studiopage.

4. Click Hub.

5. In the search bar, enter Salesforce .

6. Click Salesforce pluginsand then click Deploy.

7. In the Salesforce plugins deploywindow, click Finish.

   When the deployment completes, a dialog is displayed with success message.

8. In that dialog, click Create a pipeline.

   The Cloud Data Fusion Studio page is displayed.

9. Select Data pipeline - batchas the type of your data pipeline.

10. In the Sourcemenu, and click Salesforce.

11. Go to the Salesforcenode and click Properties. This opens the Salesforce plugin properties page.

12. In the Reference namefield, enter a name for your source. For example, Leads_generated .

13. In the Connectionsection, click the Use connectiontoggle.

14. Click Browse connections. The Browse connectionswindow opens.

15. Click Add connectionand select Salesforce.

16. In the Create a Salesforce connectionwindow, click the Configurationtab and do the following:

    1. In the Namefield, enter a name to identify your connection—for example, Salesforce_connection .

    2. In the Credentialssection, enter the following details of your Salesforce account:

       • User name
       • Password
       • Consumer key
       • Consumer secret
       • Security token

    3. Click Test connection. If the entered details are correct, the test succeeds with a message "Successfully connected".

    4. Click Create.

    5. Select Salesforce_connectionand go back to Salesforce plugin properties page.

Extract data from the Salesforce batch source plugin

1. On the Salesforce plugin properties page, in the SOQL querysection, enter the following query:

     Select 
     
    LastName 
    , 
    FirstName 
    , 
    Company 
    , 
    Email 
    , 
    Phone 
    , 
    LeadSource 
    , 
    Industry 
    , 
    OwnerId 
    , 
    CreatedDate 
    , 
    LastModifiedDate 
    , 
    LastActivityDate 
     
    from 
     
    Lead 
     
    where 
     
    Status 
     
    like 
     
    '%Open%' 
    
   

   This query fetches the details of a potential lead required to run a campaign from the sObject Lead .

2. To determine the validity of the object schema, click Get schema.

3. To filter the records for a specific date or time for running the campaign, use the following fields:

   • Last modified after
   • Last modified before
   • Duration
   • Offset

Transform data using Wrangler plugin

Use the Wrangler plugin in Cloud Data Fusion to clean and enrich your data:

1. Go back to the Studiopage.

2. In the Transformmenu, and click Wrangler.

3. Connect the Wrangler to the Salesforce batch source plugin.

4. Go to the Wranglerplugin, and click Properties. This opens the Wrangler plugin properties page.

5. Ensure that the Input schemais populated.

6. Click Wrangle.

7. In the Connectionspane, select a valid connection.

8. Select the sObject you want to transform—for example, Lead .

9. Transform the data with the required directives:

    keep :LastName,:FirstName,:Company,:Phone,:Email,:LeadSource,:OwnerId,
   :CreatedDate,:LastModifiedDate,:LastActivityDatemerge :FirstName :LastName :Name ' '
   fill-null-or-empty :Email 'no email found'
   mask-number :Phone ########xxxxxxxx
   format-date :LastActivityDate yyyy-MM-dd HH:mm:ss
   drop :LastName,:FirstName 
   

   

Load data into BigQuery

1. Go back to the Studiopage.

2. In the Sinkmenu, and click BigQuery.

3. Go to the BigQuerynode, and click Properties. This opens the BigQuery plugin properties page.

4. In the Basicsection, in the Reference namefield, enter a name to identify this sink. For example, Leads_generated .

5. In the Datasetfield, enter the dataset the table belongs to. For example, Salesforce_Leads .

6. In the Tablefield, enter the table in which the extracted records need to be stored. For example, Incoming_Open_Leads .

7. To validate the plugin, click Validate.

Deploy, schedule, and run the pipeline

1. To deploy the pipeline, click Deploy.

2. To set up an appropriate refresh schedule using schedulers, follow these steps:

   1. Click Schedule.

   2. Enter the following details:

      • Pipeline run repeats
      • Repeats every
      • Starting at
      • Max concurrent runs
      • Compute profiles

   3. Click Save and start schedule.

   

3. To run the pipeline, click Run.

Verify the data extraction and ingestion

1. In the Google Cloud console, go to the BigQuerypage:

   Go to BigQuery

2. Search for the dataset Salesforce_Leads and the table name Incoming_Open_Leads to view the extracted records.

3. To run the query, click Query.

Analyse the leads data to understand your audience better and to deliver tailored campaigns at scale.