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.

This guide explains how you can replicate, efficiently store, and visualize data from the Facebook Marketing API. Once you complete this tutorial and learn about the functionality DoubleCloud provides, you can configure data replication from other advertising platforms, such as Google, LinkedIn, Instagram, or Hubspot. This enables you to process and analyze all your marketing data in one place using the power of ClickHouse® and DoubleCloud Visualization.

Step 1. 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.

Step 1.1. Create a Managed ClickHouse® cluster

Go to the Clusters page in the console.
Click Create cluster at the top right and select ClickHouse.

Tip

If you're just testing the integration now, you don't need to configure cluster settings. You get a fully functional ClickHouse® cluster suitable for testing and development if you proceed with the default settings.

To do that, enter a cluster name under Basic settings, click Submit at the bottom of the page, and continue to the next step.
In Provider and Region, select where you want to deploy your Managed ClickHouse® cluster. You can create clusters on AWS or Google Cloud in any of the available regions. By default, DoubleCloud preselects the region nearest to you.
Under Basic settings, enter a cluster name, such as clickhouse-dev.
Under Resources, select the s2-c2-m8 preset.

Two cores and 8 GB of RAM are enough for analyzing Facebook Ads data, but if you're planning to aggregate a lot of data and run complex queries, you may want to choose a preset with more resources.
In Replicas, select 2. Having several replicas of your data increase allows for faster data access, increases availability, and aids in the case of a disaster recovery. For more information, refer to Replication.
In Shards, select 2. Sharding improves query performance — a sharded cluster can execute queries on the same table in parallel. This reduces request competition for resources and improves processing time. For more information, refer to Sharding.
In Storage, select 64 GB. Facebook Ads data is - the data we want to replicate from Facebook Ads is text, so this amount of storage space will be plenty.
Click Submit.

Creating a cluster takes around five minutes depending on the provider, region, and settings. When the cluster is ready, its state changes to from Creating to Alive.

Step 1.2. Create a ClickHouse® database

To create a database in the new cluster:

Select the cluster you just created from the cluster list.
Click WebSQL at the top right:

WebSQL is a DoubleCloud service that allows you to connect to your Managed ClickHouse® clusters from your browser tab. It provides a full-fledged SQL editor that you can use to view databases and execute SQL queries. Learn more
In WebSQL, click on any database in the connection manager on the left to open the query editor.
Create a new database named facebook_ads using the following CREATE statement:
```
CREATE DATABASE IF NOT EXISTS facebook_ads ON CLUSTER default;
```

Make sure that the database has been created:

SHOW DATABASES

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ _system            │
│ default            │
│ facebook_ads       │  // your database
│ information_schema │
│ system             │
└────────────────────┘

Step 2. Transfer data

Step 2.1. Prepare your Facebook Ads environment

Before you can replicate data from Facebook Ads, make sure you have the following:

Facebook ad account ID.

To get your Facebook ad account ID, follow the official Meta step-by-step instructions .
Facebook App with the Marketing API enabled.
How to create a Facebook app
If you don't have a Facebook app, take the following steps to create one:
1. Go to the Facebook Developers App hub .
2. Click Create App and select Manage Business Integrations as the app's purpose.
3. Fill out information about your app.
4. Go to the Enable the Marketing API for your app section.
5. Enable the Marketing API on your app's Dashboard page.
API Access Token.
1. On your app's Dashboard page on the left sidebar, click Marketing API → Tools.
2. Enable the ads_management, ads_read, read_insights, business_management permissions and click Get token.
Copy the token 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, refer to Meta's documentation on Access Levels .

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

Step 2.2. Create a Facebook source endpoint

A source endpoint is a Transfer component that connects to the Facebook Ads API, fetches your analytics data, and passes it on to a transfer.

Go to the Transfer page in the console.
Click Create → Source endpoint.
In Source type, select Facebook Marketing.
Under Basic settings, enter an endpoint name, such as facebook-source-dev.
In Start date, enter the date and time from which you want to replicate the data for all incremental streams. Use the ISO 8601 YYYY-MM-DDT00:00:00Z format.
In Account ID, enter your Facebook ad account ID.
In End date, enter the date and time until which you want to replicate the data for all incremental streams. Use the ISO 8601 YYYY-MM-DDT00:00:00Z format.
In Access token, enter your Facebook Marketing API token.
If you're testing the integration, you can leave other settings as they are. If you want to know about all the available settings you can use for replicating your data, refer to Facebook Marketing connector.
(Optional) To make sure that Transfer can connect to Facebook with your credentials, you can test your 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.

Step 2.3. Create a target endpoint

Go to the Transfer page in the console.
Click Create → Target endpoint.
In Target type, select ClickHouse.
Under Basic settings, enter an endpoint name, such as facebook-ads-target.
In Connection type, select Managed Cluster.
In the dropdown, select Facebook-Ads-Repository — the cluster you created in previous steps.
In Authentication, select Default.
1. Specify User of the database and enter the Password.
  
  You can find both in the Overview tab on your cluster information page.
2. Set Database name in the cluster - facebook_ads in our case.
Click Submit.

Step 2.4. Create and activate a transfer

Go to the Transfer page in the console.
Click Create → Transfer.
Under Endpoints:
1. In Source, select facebook-ads-source .
2. In Target, select facebook-ads-target .
Under Basic settings, enter a transfer name, such as facebook-ads-transfer.
Click Submit.
Select the transfer in the list, and click Activate at the top right.
Wait until the transfer status changes to Done.

Step 2.5. Make sure that the data has been transferred

Go to the Clusters page in the console and select clickhouse-dev — the ClickHouse® you transferred data to.
Click WebSQL at the top right.
Select any database in the connection manager on the left to open the SQL editor.

To list all tables, run the following command:

SHOW TABLES FROM db_ads_metrics

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

Step 3. Visualize the data

Now that your data has been replicated from Facebook Ads to the Managed ClickHouse® cluster, you can start building a visualization dashboard.

Step 3.1. Create a workbook

A workbook is a visualization container. It can contain connections, datasets, dashboards, and charts.

To create a workbook:

Open the Visualization page in the console.
Click Create → Create workbook at the top right.
In the dialog, enter a workbook name, such as Facebook ads.
Click Create.

Step 3.2. Create a connection

A connection access hosts in your cluster to fetch data for processing and visualization.

To create a connection:

In your new workbook, click + Connection.
Select ClickHouse.
Specify the following connection parameters:
- Cluster deployment: Select DoubleCloud.
- Cluster: clickhouse-dev.
- In Username and Password, provide the credentials for accessing your cluster.
  
  Tip
  
  You can find the username and password under Credentials in the Overview tab on the cluster page.
Leave the default preselected values in other settings.

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 dialog, enter the connection name, such as Facebook Ads data and click Create.

You'll see this line under Connections on your workbook page:

Step 3.3. Create datasets

Datasets process data from your connections and prepare them for visualization.

To create a dataset:

On the workbook page, click + Create dataset.
In the Connections section, click + Add and select Facebook Ads data. You will see the list of tables that can be visualized 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 at the top right.
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:

Step 3.4. 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.

Step 3.5. Add a dashboard

A dashboard is a customizable data display that allows you to create and arrange charts and selectors.

To add a dashboard:

Click Create → Dashboard at the top right of your workbook page.
Enter a dashboard name, such as Facebook Ads and click Create.

Your dashboard will open in edit mode. You're ready to start populating it with charts:

Step 3.6. Add charts to the dashboard

In this section, you create three charts with the most important information, so that you can get acquainted with the visualization tool. After that, feel free to try adding other charts that may be a better fit for your data.

To create a chart:

Open your workbook.
Click Create → Wizard chart at the top right.
Click Select dataset at the top left and select the Campaigns-Sets dataset.
Click on the chart type field and select a chart type you want to add:

Screenshot here
Normalized bar chart

Line chart

Pivot table
1. Drag and drop Measures and Dimensions to the appropriate sections of the chart:
  - Drag Campaign name to Y,
  - Drag Impressions, Reach and Link clicks to X,
  - Measure names will automatically apply to Colors.
Your chart fields configuration should look like this:
1. Drag and drop items from the Measures and Dimensions sections to the...:
  1. Drag Campaign name to X
  2. Drag And set budget and Results to Y
  3. Measure names will automatically apply to Colors.
Your chart fields configuration should look like this:
1. Drag and drop Measures and Dimensions to the appropriate sections of the chart:
  1. Drag Campaign name to Rows
  2. Drag And set budget, Impressions, Link clicks, Reach and Results to Measures
  3. Measure names will automatically apply to Columns.
Your chart fields configuration should look like this:
Click Save at the top right.
Enter a chart name and click Save.
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!