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 Instances page:

   Go to instances

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

3. Go to the Studio page.

4. Click Hub.

5. In the search bar, enter Salesforce.

6. Click Salesforce plugins and then click Deploy.

7. In the Salesforce plugins deploy window, 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 - batch as the type of your data pipeline.

10. In the Source menu, and click Salesforce.

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

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

13. In the Connection section, click the Use connection toggle.

14. Click Browse connections. The Browse connections window opens.

15. Click Add connection and select Salesforce.

16. In the Create a Salesforce connection window, click the Configuration tab and do the following:

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

    2. In the Credentials section, 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_connection and 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 query section, 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 Studio page.

2. In the Transform menu, and click Wrangler.

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

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

5. Ensure that the Input schema is populated.

6. Click Wrangle.

7. In the Connections pane, 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 Studio page.

2. In the Sink menu, and click BigQuery.

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

4. In the Basic section, in the Reference name field, enter a name to identify this sink. For example, Leads_generated.

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

6. In the Table field, 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 BigQuery page:

   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.