Now you can connect Facebook and Google Ads in BI Builder. This allows you to analyze account information and assess your advertising investments.
For example, connect a Facebook Ads account to see costs and find out which ads get more traffic. The information is automatically gathered into datasets for ad sources and expenses. You can use this data to create a dashboard to see how well your ads are working and if they're worth the cost.
This article explains how to connect an ad account and start working with advertising datasets.
Datasets:
Examples:
How to connect ad accounts
- Go to BI Builder.
- Open the Analytics hub tab.
- Switch to Connections.
- Click Create.
- Select an ad account and click Connect.
Here's how to connect a Facebook Ads account in three steps:
-
Log in to Facebook. Click Connect and log in to your Facebook Ads account, so BI Builder can access your ad campaign data.
-
Specify the source name. This will appear in the dataset. By default, it's named Facebook, but you can choose your own.
-
Add a UTM parameter. Set a value for the utm_source parameter to show where customers came from.
How to use datasets
To get data on advertising sources, use the following datasets:
tracking_source — ad data sources: This includes details about all connected ad sources, such as unique source IDs, names, and UTM parameters.
tracking_source
| Field | Description | Data type |
|---|---|---|
| ID | Source ID | Number |
| NAME | Source name | String |
| UTM_SOURCE_LIST | List of utm_source parameters | String array |
tracking_source_expenses — ad cost statistics: This provides daily ad cost statistics, including currency, number of clicks, impressions, and actions.
tracking_source_expenses
| Field | Description | Data type |
|---|---|---|
| SOURCE_ID | Source ID | Number |
| EXPENSES | Expenses per day | Fractional number |
| CURRENCY | Currency | String |
| DATE | Date | Date |
| CAMPAIGN_NAME | Advertising campaign name | String |
| CAMPAIGN_ID | Advertising campaign ID | String |
| CLICKS | Number of clicks | Number |
| IMPRESSIONS | Number of impressions | Number |
| ACTIONS | Number of actions | Number |
| CPM | Cost per thousand impressions | Fractional number |
| CPC | Cost per click | Fractional number |
How to create a chart
Add information from the dataset to charts to keep track of advertising expenses. For example, assess which ads get more traffic.
- Go to BI Builder and open the Charts tab.
- Click + Chart.
- Select the dataset from the list. If you don't see the one you need, click Add a dataset to create a new one.
How to create a dataset - Choose a format for showing the data: big number, table, line chart, or others.
- Click Create new chart.
How to blend datasets
Combine datasets to add information about advertising sources and expenses to the dashboard.
To blend datasets, do the following:
- Go to BI Builder > SQL > SQL Lab.
- Select the bitrix24 schema.
- Enter the SQL query and click Run.
The new set includes information from the fields that you specified in the SQL query. You can save it as a dataset to create a chart for the dashboard.
Create a chart based on a virtual dataset
If you're blending datasets for the first time, refer to this sample query.
Example of an SQL query
Here's an SQL query to extract data from two datasets: ad data sources (tracking_source) and ad cost statistics (tracking_source_expenses). This query combines data from these datasets using the source ID and selects information only for ad campaigns with IDs 19644720474 and 13541843024. You can copy it and see how it works. Just replace the campaign IDs before running the query.
SELECT tracking_source.ID AS "Source ID", tracking_source.NAME AS "Source name", tracking_source_expenses.CAMPAIGN_ID AS "Campaign ID", tracking_source_expenses.CAMPAIGN_NAME AS "Campaign name", tracking_source_expenses.DATE AS "Date", tracking_source_expenses.EXPENSES AS "Expenses", tracking_source_expenses.CLICKS AS "Number of clicks", tracking_source_expenses.CPC AS "Cost per click" FROM tracking_source INNER JOIN tracking_source_expenses ON tracking_source.ID = tracking_source_expenses.SOURCE_ID WHERE tracking_source_expenses.CAMPAIGN_ID IN ('19644720474', '13541843024');
SELECT: Chooses which data to extract. For example, tracking_source.ID AS "Source ID" selects the source ID from the tracking_source dataset, names it Source ID, and displays the result in a separate column.
FROM: Specifies the main table for the query, such as the tracking_source table with a list of ad sources.
INNER JOIN: Connects datasets and bends their information. The query tracking_source_expenses ON tracking_source.ID = tracking_source_expenses.SOURCE_ID combines ad source data with their expenses.
WHERE: Filters results to include data only from specific ad campaigns.
In brief
-
Now you can connect Facebook and Google Ads in BI Builder. This allows you to analyze account information and assess your advertising investments.
-
The information from ad accounts is automatically gathered into datasets: ad data sources (tracking_source) and ad cost statistics (tracking_source_expenses).
-
Add information from the dataset to charts to keep track of advertising expenses. For example, assess which ads get more traffic.
-
Combine datasets to add information about advertising sources and expenses to the dashboard. To do so, write an SQL query, save the result as a dataset, and create a chart.
Read also: