Marketing analytics end-to-end scenario

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:

Prepare to acquire the data

You first need to create a cluster and a database that will store the data from Facebook Ads for further processing and visualization.

You need to complete the following steps:

Create a Managed ClickHouse® cluster

Warning

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.

Go to the Clusters page in the console.
Click Create cluster in the upper-right corner of the page.
1. Select ClickHouse®.
2. Choose a provider and a region.
3. Under Resources:
  - Select the s2-c2-m8 preset for CPU and RAM capacity - it's more than enough for this scenario.
    Understand your ClickHouse® resource preset
    A resource preset has the following structure:
    
    <cpu-platform>-c<number-of-cpu-cores>-m<gigabytes-of-ram>
    
    There are three available CPU platforms:
    
    g: ARM Graviton
    
    i: Intel (x86)
    
    s: AMD (x86)
    
    For example, the i2-c2-m8 preset corresponds to an Intel-family machine with 2 virtual cores and 8 GB of RAM.
    
    Learn more about the availability of CPU platforms in areas and regions
  - 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.
4. 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.
5. 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.
6. 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.

To learn how to create users for other roles, refer to Manage ClickHouse® users

Create a ClickHouse® database

This step is easier to accomplish from your terminal.

Note that this scenario is based on a DEB-based Linux distribution . You can use a standalone system or a Windows Subsystem for Linux solution.

You need to install clickhouse-client to be able to talk to your new cluster via the terminal:

Install the ClickHouse® client.

The software is ready to go. Connect to your new cluster:

Go to the Clusters page in the console.
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 NOT EXISTS 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:

Go to the Clusters page in the console.
Select your new cluster from the list.
Select the Allowlist tab.
Under System options, click Allow on the right of DoubleCloud Visualization service. Your cluster state will change to Updating.

Wait until the cluster state changes to Alive before continuing to the next step.
Under System options, click Allow on the right of DoubleCloud Transfer service. Your cluster state will change to Updating.

Wait until the cluster state changes to Alive before continuing to the next step.

The preparations for your metrics data storage are complete.

Transfer the data

Now it's time to set up the tools to get the data from your Facebook Ads account and transfer it to your db_ads_metrics ClickHouse® database.

To accomplish this task, you need to take four steps:

Prepare your Facebook Ads environment

Prepare all the necessary assets and information to transfer advertising metrics from Facebook.
Create a Facebook source endpoint

This tool will reach your Facebook Ads account, take care of verification and preparation of the data for the transfer.
Create a target endpoint.

This tool will receive the data from the source endpoint and replicate it correctly to your ClickHouse® database.
Create and activate a transfer.

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:

Facebook Account ID.

To find your Facebook Account ID, follow the official Meta step-by-step instructions .

Copy it for further use.
Facebook App with Marketing API enabled. If you don't have a Facebook app, follow the steps below:
- Go to the Facebook Developers App hub .
- 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:
- Ads Management Standard Access
- ads_read
- ads_management
For instructions on requesting Advanced Access, see the Meta documentation on Access Levels .

You are now ready to set up the data transfer toolkit.

Create a Facebook source endpoint

Go to the Transfer page in the console.
Select Endpoints tab, click Create endpoint → Source.
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:
1. Click Test connection.
2. 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.
3. If you selected the dedicated runtime, select the network in the dropdown.
4. 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:

Create a target endpoint

Go to the Transfer page in the console.
Select Endpoints tab, click Create endpoint → Target.
Select ClickHouse® in the Target type.
Under Basic settings:
1. Enter the Name of the endpoint. For this scenario, let's call it facebook-ads-target.
2. (optional) Enter a Description of the endpoint.
Specify Endpoint settings:
1. Select Managed Cluster you want to connect to from the drop-down list - in our case, it's Facebook-Ads-Repository.
2. Specify User of the database and enter the Password.
  
  You can find both in the Overview tab on your cluster information page.
3. Set Database name in the cluster - db_ads_metrics in our case.
Click Submit.

When the target endpoint is ready to operate, you'll see this line in your Endpoints tab on the Transfer page:

Create and activate a transfer

Go to the Transfer page in the console.
Click Create transfer.
Under Endpoints:
1. Select facebook-ads-source in Source.
2. Select facebook-ads-target in Target.
Under Basic settings:
1. Enter the transfer Name: facebook-ads-transfer.
2. (optional) Enter the transfer Description if you want.
Click Submit.

When the source endpoint is ready to operate, you'll see this line in your Transfers tab:
Click on the name of your new transfer, then click Activate.
Wait until the transfer status indicator on the right of the transfer name changes to Done.
Supported tables
The DoubleCloud endpoint supports replication of the following tables from Facebook:
- Activities
- AdAccount
- AdCreatives
- AdSets
- Ads
- AdInsights
- Campaigns
- Images (if available)
- Videos (if available)
For more information, refer to Facebook Insights API documentation .

Check if all the data was transferred correctly:

Open your terminal.
Connect to your cluster with the Native interface string from your cluster's Overview tab and type the following command:

SHOW TABLES FROM db_ads_metrics

You should see the following readout:

┌─name──────────┐
│ activities    │
│ ad_account    │
│ ad_creatives  │
│ ad_sets       │
│ ads           │
│ campaigns     │
│ images        │
└───────────────┘

Visualize the data

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.
Create datasets based on the data from your connection and prepare them for visualization.
Add a dashboard. This tool provides a canvas on which to place and organize charts.
Add charts to your dashboard to visualize your data.

Create a workbook

Workbooks are visualization environments for data in your clusters.

Open the Visualization page in the console.
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:
1. Hover the cursor over event_time in the list of dataset fields.
2. 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
1. Select the Campaigns-Sets dataset from the list. You'll see all the data fields from this dataset below.
2. Click the chart type field on the right of the dataset section and select Normalized bar chart from the drop-down list.
3. 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:
1. Select the Campaigns-Sets dataset from the list. You'll see all the data fields from this dataset below.
2. Click the chart type field on the right of the dataset section and select Normalized line chart from the drop-down list.
3. 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:
1. Select the Campaigns-Sets dataset from the list. You'll see all the data fields from this dataset below.
2. Click the chart type field on the right of the dataset section and select Pivot table from the drop-down list.
3. 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!