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 will get familiar with the case of visualizing ads data from Facebook.

With so many 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 clearly and make the right decisions at a glance:

scenario-diagram

Here is the checklist for all the steps you need to take as your solution takes shape:

  1. Prepare to acquire the data

    1. Create a Managed ClickHouse® cluster

    2. Create a ClickHouse® database

  2. Transfer the data

    1. Prepare your Facebook Ads environment

    2. Create a source endpoint

    3. Create a target endpoint

    4. Create and activate a transfer

  3. Visualize the data

    1. Create a workbook

    2. Create a connection

    3. Create a dataset

    4. Create a dashboard

    5. Add charts to your dashboard

  4. See also

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:

  1. Create a Managed ClickHouse® cluster

  2. Create a ClickHouse® database

  3. Configure your Managed ClickHouse® cluster's allow list

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.

  1. Go to the console.

  2. Select Clusters from the list of services on the left.

  3. 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 s1-c2-m8 preset for CPU and RAM capacity. Our scenario won't need a lot of computing capacity.

      • 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 two replicas, making sure replication on your cluster works from the start. For more information, see Replication.

      • Select two 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 NetworkingVPC, specify in which DoubleCloud VPC to locate your cluster. Use the default value in the previously selected region if you don't need to create this cluster in a specific network.

    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

The DoubleCloud service creates the superuser admin and its password automatically. You can find both the User and the Password in the Overview tab on the cluster information page.

To create users for other roles, see Manage ClickHouse® users

Create a ClickHouse® database

This step is easier to accomplish from your Linux terminal.

Please note - 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 Linux terminal:

  1. Open your Linux terminal.

  2. Connect to the ClickHouse® official DEB repository from your Linux system:

    sudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr && \
    sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 && \
    echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
     /etc/apt/sources.list.d/clickhouse.list
    
  3. Refresh the package list and install the software:

    sudo apt update && sudo apt install -y clickhouse-client
    

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

  1. Go to the console.

  2. Select Clusters from the list of services on the left.

  3. Select the name of your cluster to open its information page. By default, you will see the Overview tab.

  4. Under Connection strings, find the Native interface string and click Copy.

  5. Paste the string into your Linux terminal and execute it.

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 AdsMetrics:

  1. Type the following command in your Linux terminal:

    CREATE DATABASE IF NOT EXISTS AdsMetrics
    
  2. Test if the database was created successfully. Type SHOW DATABASES. You will see AdsMetrics in the list of databases on your cluster.

Configure your Managed ClickHouse® cluster's allow list

By default, the allow list 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:

  1. Select Clusters from the list of services on the left.

  2. Select your new cluster from the list.

  3. Select the Allow list tab.

  4. Under System options, click Allow on the right of DoubleCloud Visualization service. Your cluster will acquire the Updating state.

    Please wait until the cluster acquires the Alive state before taking the next step.

  5. Under System options, click Allow on the right of DoubleCloud Transfer service. Your cluster will acquire the Updating state.

    Please wait until the cluster acquires the Alive state before proceeding.

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 AdsMetrics ClickHouse® database.

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

  1. Prepare your Facebook Ads environment

    Prepare all the necessary assets and information to transfer advertising metrics from Facebook.

  2. 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.

  3. Create a target endpoint.

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

  4. 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.

    Please follow the official Meta step-by-step instructions to find your Facebook Account ID.

    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 APITools.
    • 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

  1. Under Basic settings select Facebook Marketing from the Source type drop-down menu.

  2. Give a Name to your endpoint. Let's call it facebook-ads-source. You can also write a Description if you want.

  3. 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.

  4. Provide your Account ID.

  5. 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.

  6. Provide the Access token.

  7. Check the Include deleted box if you want to include the data from your deleted campaigns, ads, and AdSets.

  8. 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 AdsMetrics database.

    The fields on your endpoint creation page should look like this:

  9. 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

  1. In the list of services, select Transfer.

  2. Select Endpoints tab, click Create endpoint and select Target.

  3. Select ClickHouse® in the Target type.

  4. 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.

  5. 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 - AdsMetrics in our case.

  6. 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

  1. In the list of services, select Transfer.

  2. Select the Transfer tab.

  3. Click Create transfer.

  4. Under Endpoints:

    1. Select facebook-ads-source in Source.

    2. Select facebook-ads-target in Target.

  5. Under Basic settings:

    1. Enter the transfer Name: facebook-ads-transfer.

    2. (optional) Enter the transfer Description if you want.

  6. Click Submit.

    When the source endpoint is ready to operate, you'll see this line in your Transfers tab:

  7. Click on the name of your new transfer, then click Activate.

  8. 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:

    For more information, see Facebook Insights API documentation .

  9. Check if all the data was transferred correctly:

    1. Open your Linux Terminal.

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

    SHOW TABLES FROM AdsMetrics
    

    If the data is transferred correctly, you should see the following readout:

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

Visualize the data

After successfully 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:

  1. Create a workbook. In Visualization it works as a container for all other visualization elements.

  2. Create a connection. This tool connects to your Managed ClickHouse® cluster to fetch and update the data.

  3. Create datasets based on the data from your connection and prepare them for visualization.

  4. Add a dashboard. This tool provides a canvas on which to place and organize charts.

  5. Create tabs to organize related data into groups.

  6. Create charts to visualize your data.

Create a workbook

Workbooks are visualization environments for data in your clusters.

  1. Go to the console.

  2. Select Visualization from the list of services on the left.

  3. Click Create a workbook in the upper-right corner of the page.

  4. Name your workbook Facebook Ads and 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.

  1. Click CreateConnection in the upper-right corner of your workbook page.

  2. Select ClickHouse®.

  3. Specify the connection parameters:

    • Connection name. Let's call it fb_transfer_connection.

    • 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:

  4. 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.

  5. Click Create in the upper-right corner of the page.

    If your connection is created correctly, 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:

  1. Click CreateDataset in the upper-right corner of your workbook page.

  2. 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.

  3. Drag and drop the table to the workspace on the right.

  4. You can see the preview of the dataset in the Preview section below.

  5. Click Save in the upper-right corner of your dataset page.

  6. Enter the name of your new dataset. You can use Ads Metrics - <table name> for consistency.

  7. Return to your workbook page by clicking Facebook Ads in the breadcrumbs section in the top left corner.

  8. 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.

  1. Open your Workbook.

  2. 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.

  3. Click + Add button in the top right to create a calculated field.

  4. Under Field settings, specify the name for the field: Activity time.

  5. Use the SPLIT function in the formula. Write it in the code field on the right:

    SPLIT([event_time], '+', 1)
    
  6. 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.

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

  8. 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.

  1. Click CreateDashboard in the upper-right corner of your workbook page.

  2. Call your dashboard Facebook Ads and click Create.

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

Create charts

In this scenario, we will create three charts with the most important information to give you the feel of how to visualize your data:

  1. Open Facebook Ads Dashboard.

  2. Select the tab you want to place the chart on.

  3. Click AddChart:

    1. Select the Campaigns-Sets dataset from the list. You will 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 will 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 will 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:

  4. Click Save in the upper-right corner of the page.

  5. Give your chart a name and click Done.

  6. Choose the right location for your chart by dragging it around.

  7. 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!

See also:

Managed Service for ClickHouse®:

Transfer:

Visualization: