Understanding your ad campaign metrics is a must if you take your advertising seriously. The better you know how every part of your ad campaign is doing, the more you can learn from its performance.
DoubleCloud provides a complete toolkit to replicate, process, efficiently store, and visualize data from Meta (Facebook Ads), Google, LinkedIn, and other advertising platforms.
For this scenario, you'll get familiar with the case of visualizing ads data from Facebook.
With a wide variety of different metrics available, it's sometimes hard to make sense of this vast array of numbers, status message, and other readouts. This amount of unprocessed data may even feel overwhelming.
This guide will help you apply the power of DoubleCloud data management toolkit to create a visual control center for your Facebook Ads campaign. It will allow you to see the stats and make the right decisions at a glance:
Here is the checklist for all the steps you need to take as your solution takes shape:
During the trial period, you can create clusters with up to 8 cores, 32 GB RAM, and 400 GB storage. If you need to raise the quotas, don't hesitate to contact our support.
Set 64 GB for storage - the data we want to replicate from Facebook Ads is text, so this amount of storage space will be plenty.
Choose a number of replicas. For our scenario, select 2 replicas, making sure replication on your cluster works from the start. For more information, see Replication.
Select 2 shards to improve query performance - a sharded cluster can execute queries to the same table in parallel. This reduces request competition for resources and improves processing time. For more information, see Sharding.
Under Basic settings:
Enter the cluster Name: Facebook-Ads-Repository.
From the Version drop-down list, select the ClickHouse® version the Managed ClickHouse® cluster will use. For most clusters, we recommend using the latest version.
Under Networking → VPC, select the network where you want to create the cluster.
If you don’t need to place the cluster in a specific network, leave the preselected default option.
Click Submit.
Your cluster will appear with the Creating status on the Clusters page in the console. Setting everything up may take some time. When the cluster is ready, it changes its state to Alive.
You'll see this line in your Clusters list:
Tip
DoubleCloud creates the admin superuser and its password automatically.
You can the username and password under Credentials in the Overview tab on the cluster page.
Select your cluster from the list. By default, you'll see the Overview tab.
Under Connection strings, find the Native interface string, and click Copy.
Use this string to execute the following command:
Docker
Native clickhouse-client
docker run --network host --rm -it clickhouse/<Native interface connection string>
The complete Docker command structure
docker run --network host --rm -it \
clickhouse/clickhouse-client \
--host <FQDN of your cluster> \
--secure \
--user <cluster user name> \
--password <cluster user password> \
--port 9440
<Native interface connection string>
You are now connected to your cluster via the clickhouse-client. It's time to create the database for you Facebook Ads metrics data. Let's call it db_ads_metrics:
Type the following command in your terminal:
CREATE DATABASE IF NOTEXISTS db_ads_metrics
Type SHOW DATABASES to test if the database was created successfully. You'll see db_ads_metrics in the list of databases on your cluster.
Configure your Managed ClickHouse® cluster's allowlist
By default, the allowlist settings block Transfer and Visualization services from accessing the data on your cluster. We need both of these services to make progress with our scenario, so let's grant them access:
This tool will perform the transfer of the data from Facebook Ads to your ClickHouse® database and make sure nothing gets lost or corrupted in the process.
Let's get to work.
Prepare your Facebook Ads environment
Address these four issues to prepare for your Facebook Ads data replication:
Click Create App and select Manage Business Integrations as the app's purpose.
In the following fields, fill out all the necessary information to create the app.
Follow to the Enable the Marketing API for your app section.
Enable the Marketing API on your app's Dashboard page.
API Access Token.
On your app's Dashboard page on the left sidebar, click Marketing API → Tools.
Highlight the ads_management, ads_read, read_insights, business_management permissions and click Get token.
You will see a long string of characters - this is your Access Token.
Copy it for further use.
Send a limit increase request. To avoid throttling your API token by Facebook Apps, request an upgrade to Advanced Access for your app on the following permissions:
Under Basic settings select Facebook Marketing from the Source type drop-down menu.
Give a Name to your endpoint. Let's call it facebook-ads-source. You can also write a description if you want.
Under Endpoint settings, specify the Start date from which you want to replicate the data for all incremental streams.
Note that you need to provide the date and time in the ISO 8601 format: YYYY-MM-DDT00:00:00Z.
Provide your Account ID.
Define the End date from which you want to replicate the data for all incremental streams in the YYYY-MM-DDT00:00:00Z format.
Note
If you don't specify the End date, the service will always sync the latest data.
Provide the Access token.
Check the Include deleted box if you want to include the data from your deleted campaigns, ads, and AdSets.
Check the Fetch Thumbnail Images box to fetch thumbnail URLs for each Ad Creative and store them in the thumbnail_data_url table in your db_ads_metrics database.
The fields on your endpoint creation page should look like this:
Optionally, you can test your source endpoint:
Click Test connection.
Select the runtime type you want Transfer to use for connecting to the database.
Dedicated:
Transfer connects to the database using a specified
internal or
external network.
Serverless:
Transfer connects to the database available from the internet using an automatically chosen network.
Runtime compatibility warning
Don't use endpoints with different runtime types
in the same transfer — this will cause the transfer to fail.
If you selected the dedicated runtime,
select the network in the dropdown.
Click Test connection.
Testing the connection may take a few minutes.
Click Submit.
When the source endpoint is ready to operate, you'll see this line in your Endpoints tab on the Transfer page:
After migrating your data from the Facebook Ads to the Managed ClickHouse® cluster, it's time to start building our data displays. This process consists of the following steps:
Create a workbook. In Visualization it works as a container for all other visualization elements.
Create a connection. This tool connects to your Managed ClickHouse® cluster to fetch and update the data.
Click Create → Create workbook in the upper-right corner of the page. You'll see the following dialog:
Under Name, type Facebook Ads.
Click Create.
You will see the following line on your list of workbooks:
Create a connection
Connections access hosts in your cluster to fetch data for processing and visualization.
Click Create → Connection in the upper-right corner of your workbook page.
Select ClickHouse®.
Specify the connection parameters:
Hostname. Copy the host's name from the Hosts tab on the information page of your cluster.
HTTP Interface Port (by default, 8443).
Username and Password for your cluster. You can find them in the Overview tab on the information page of your cluster.
Under Raw SQL level keep the default setting - Forbid. It allows the DoubleCloud service to handle all SQL queries automatically.
Check the HTTPS box to enable this security layer.
Your connection parameters should look like this:
Click Check connection.
If your connection is operational, you will see a green icon. If there is a problem, a red icon will appear, and a popup message will appear with information about the error.
Click Create connection in the upper-right corner of the page.
In the dialog window, name the connection fb_transfer_connection and click Create.
You'll see this line under Connections on your workbook page:
Create datasets
Datasets process data from your connections and prepare them for visualization.
Based on your scenario, create one or more datasets:
Click Create → Dataset in the upper-right corner of your workbook page.
In the Connections section, click + Add and select fb_transfer_connection. You will see the list of tables that the Visualization service can visualize in the Tables section.
Drag and drop the table to the workspace on the right.
You can see the preview of the dataset in the Preview section below.
Click Save in the upper-right corner of your dataset page.
Enter the name of your new dataset. You can use Ads Metrics - <table name> for consistency.
Return to your workbook page by clicking Facebook Ads in the breadcrumbs section in the top left corner.
Based on your personal scenario, create the tables you need.
The completed Datasets section in your dashboard may look like this:
Prepare datasets for visualization
Due to the timezone shift currently unsupported, some columns containing date and time in your new datasets haven't been processed correctly. Use calculated fields to extract correct date and time values from this data.
Let's use Ads Metrics - Activities dataset as an example.
Open your Workbook.
Click on the Ads Metrics - Activities dataset.
As you can see, the event_time column has the incompatible ISO 8601 timestamp. We need to convert this data into a compatible format.
Click + Add button in the top right to create a calculated field.
Under Field settings, specify the name for the field: Activity time.
Use the SPLIT function in the formula. Write it in the code field on the right:
SPLIT([event_time], '+', 1)
Click Create in the lower-right corner.
You will see your new calculated field on the top of the dataset field list, and it will have icon in the Field source column.
Set the correct data type for the field: click String in the Type column to open the drop-down menu and select Date and Time.
The new field with correct timestamps is ready:
Hide the column with incorrect data not to use it by mistake:
Hover the cursor over event_time in the list of dataset fields.
Click the icon.
The field is now hidden and will be unavailable for visualization. To check that you've done these steps correctly, look if the field disappeared from the Preview section of the dataset.
Click Save in the upper-right corner of the dataset page.
Repeat this sequence of steps for each dataset if necessary.
Add a dashboard
A dashboard is a customizable data display that allows you to create and arrange charts and selectors.
Click Create → Dashboard in the upper-right corner of your workbook page.
Call your dashboard Facebook Ads and click Create.
Your dashboard will open in edit mode. You're ready to start populating it with charts:
Add charts to your dashboard
In this scenario, we'll create three charts with the most important information to give you the feel of how to visualize your data:
Open Facebook Ads Dashboard.
Select the tab on which you want to place the chart.
Click Add → Chart:
Normalized bar chart
Line chart
Pivot table
Select the Campaigns-Sets dataset from the list. You'll see all the data fields from this dataset below.
Click the chart type field on the right of the dataset section and select Normalized bar chart from the drop-down list.
Drag and drop Measures and Dimensions to the appropriate sections of the chart:
Campaign name to Y,
Impressions, Reach and Link clicks to X,
Measure names will automatically apply to Colors.
Your chart fields configuration should look like this:
Select the Campaigns-Sets dataset from the list. You'll see all the data fields from this dataset below.
Click the chart type field on the right of the dataset section and select Normalized line chart from the drop-down list.
Drag and drop Measures and Dimensions to the appropriate sections of the chart:
Campaign name to X,
And set budget and Results to Y,
Measure names will automatically apply to Colors.
Your chart fields configuration should look like this:
Select the Campaigns-Sets dataset from the list. You'll see all the data fields from this dataset below.
Click the chart type field on the right of the dataset section and select Pivot table from the drop-down list.
Drag and drop Measures and Dimensions to the appropriate sections of the chart:
Campaign name to Rows,
And set budget, Impressions, Link clicks, Reach and Results to Measures,
Measure names will automatically apply to Columns.
Your chart fields configuration should look like this:
Click Save in the upper-right corner of the page.
Give your chart a name and click Done.
Choose the right location for your chart by dragging it around.
Click Save in the upper-right corner of the page.
Below you can see a tab with sample data and layout:
This step concludes the scenario. There's a lot more you can do with DoubleCloud. Keep exploring!