BigQuery Integration

In this article, you will learn how to connect BigQuery to Forwrd and how to use it as a source when creating a scoring model.

Introduction to this integration

Adding BigQuery to Forwrd makes it easy to analyze and score your leads and customers by matching historical data (i.e., engagement, firmographics, demographics) against a business objective, to ultimately predict who will convert and why.

Here are some examples of use cases:

• Predict which leads will become MQLs
• Predict which MQLs will become Opportunities
• Predict which Opportunities will become Paying customer
• Predict which Customers will Expand
• Predict which Customers will Churn

In addition to BigQuery, you are encouraged to add more data sources to develop a holistic, unbiased scoring model that considers ALL relevant user touchpoints.

What you need to get started:

• A Forwrd.ai account
• A BigQuery account

Setting up the integration

1. The first step will be to give us permission to view the data in your BigQuery

Go into your Google Cloud account and into the 'IAM' tab. Under 'PERMISSIONS' choose 'VIEW BY PRINCIPALS' and then press 'GRANT ACCESS'.

A window will pop up on the right side of the screen. In the 'Add Principals' section, Under 'New Principals' write: [e-mail]

In the 'Assign Roles' section:

Under 'Role' #1 write: 'BigQuery Data Viewer'

Under 'Role' #2 write: 'BigQuery User'

Press SAVE. 

2. Add BigQuery as a source in Forwrd

Enter the Forwrd app, and navigate to the 'sources' layer.

Click 'New Source' and select BigQuery.

Give your source a name that anyone accessing Forwrd can easily recognize.

From there, log in to BigQuery like you normally would.

Once done, BigQuery will appear as a source in your 'Sources' panel in Forwrd.

Click the 'three dots' icon to see more functions you can perform with this source. For example, you can share this source with another team member who uses Forwrd and test the connection to this source.

3. Define a decision base (microdata warehouse).

Next, you must define a ‘decision base’, a micro data warehouse that Forwrd can analyze to generate predictions.

A decision base can combine data from multiple sources. For instance, you can combine data from BigQuery, HubSpot, and Mixpanel. The types of sources you would combine would depend on your use case.

Click 'Create New', name your decision base, and select the data sources and the respective objects you'd like to join together. Alternatively, you can also write your own SQL query that retrieves the objects and columns you require. However, when using this method, it is not possible to join the BigQuery data with other data sources.

Once the decision base is created, you can see its size, date range, and when it was last synced – and you can even set it to sync to ensure your data is always fresh and up-to-date.

After creating your decision base, you can apply filters to hone in on specific segments, user groups, etc.

4. Define a metric (business objective).

Next, you should define your Metric, which stands for the business objective and business logic, to guide your prediction.

Click 'Create New' and name your metric. Next, add the decision base you have just created (in step 2) and define an expression to teach Forwrd what a successful conversion looks like.

Next, you will teach Forwrd what an ‘open’ record looks like, so it will make predictions on these open leads.

Lastly, to generate the most accurate predictions, you must tell Forwrd how to recognize ‘lost’ leads that didn’t convert into opportunities.

5. Run an analysis.

At this point, we can run an analysis. Go to the 'Projects' tab, create a new project, and within it, create a new analysis that includes your ‘Decision base’ and ‘Metric’.

Once done, click 'Create'. This will run your analysis.

At this point, you can review your analysis – As you can see on the left side of the screen, Forwrd identified a number of factors that impact your business objective, and, by how much.

You can drill down to any of the factors that Forwrd detected to gain a deeper understanding of what drives conversions and what does more damage than good.

6. Build a scoring model.

Now you can build a model that will help you predict whether your open leads will convert or not. To do that, we’ll click ‘build model’.

Forwrd will display the result and classify your leads into four buckets based on their likelihood to convert.

You can hover over each of the records and see a clear explanation of WHY Forwrd decided to give the lead its score.

7. Push the results into BigQuery CRM.

Forwrd can write the score information back into BigQuery. However, it is important to configure the Target table to be different than the Source table. The way Forwrd operates is by deleting the Target table in every updated execution and re-writing all the data (therefore, don't update the Source table- it will be deleted).

A pre-requisite is to create a Target table in BigQuery and define its structure that will include the following fields: Score, Explain, Trend, and Scoring Date. Forwrd will later populate these fields.

And now, once we have this built, we can go ahead and activate this data and create an automation that pushes it into the tools your team uses daily – in our case BigQuery CRM.

Go to the 'Automations' tab and create a new automation by clicking the 'Plus' button.

You’ll see a new line created for this automation, press on the ‘Set’ button on the box that says: ‘DEFINE UPDATE SOURCE’:

In the drop menu that opens, select your BigQuery source name defined in step # 2  (‘Choose Update Source’): 

In the 'Scheme' field, choose the correct scheme.

In the ‘Object’ field, choose the Target table you would like to write to - The one defined before in the pre-requisite and in the ‘Record ID’ field, choose the record key you would like to use (for example, ‘BigQuery Lead Id’).

Choose the ID identifying each record in the 'Record Id' fields.

Now you can choose per the tabs: ‘SCORE’, ‘EXPLAIN, ‘TREND’ and 'SCORING DATE' at the bottom, the relevant fields you created (e.g. ‘Forwrd Score’, ‘Forwrd Explain’, ‘Forwrd Trend’, 'Fowrd Scoring Date'). 

At this point, you can choose to add another field by pressing the 'Plus' button. You can choose per the tabs: ‘SCORE’, ‘EXPLAIN’, ‘TREND’ and 'SCORING DATE' and add a condition of either once or always. For example, you can create a field for Forwrd’s first score by clicking 'Score' + 'Once' This way, you can view the progression of your score record.  

Once you are done, click on the Done button at the bottom. 

Now, choose the frequency of the updates.

Click on the sync box. You can now choose when you want your fields to update. Choose the time (in UTC) it should be running every day (it can also run weekly or monthly), but If you choose daily, you can pick the hour you wish to receive the update. You can add multiple times each day by pressing the Plus button.

Decide how far back you want to score the leads/customers.

This is defined at the first box in the line. Once you click it, you’ll see a menu that allows you to define the criteria. For instance, churn data was created in the last 14 days. It is important to choose the correct time metric related to the prediction metric created for this analysis.

Last but not least, enable the analysis- Slide the On/Off button on the right corner to the ‘ON’ position.

FYI- A common automation would be to score BigQuery customer churn and push them back into BigQuery CRM once a day.

Thanks for taking the time to review these instructions.

If you need further help setting things up, or if you’d like to see a personalized, in-depth demo of Forwrd –book a demo with us. We'd love to help!