You can connect Facebook Lead Ads Connector with your Facebook Page or Ad Account to import Leads data into Treasure Data.

This topic includes:

Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt

  • A Facebook Page/Ad account with Leads retrieval permission

  • Authorized Treasure Data account access

Use Command Line to Create a Connection

Install ‘td’ Command

Install the newest TD Toolbelt.

Create Seed Config File (seed.yml)

enable_guess_schema enabled: 

in:
  type: facebook_leads
  app_secret: <app_secret>
  access_token: <long-lived-access_token>
  id: 33056800448180
  time_created: '2020-01-28T15:46:25+0000'
  incremental: false
  enable_guess_schema: true
out:
  mode: append

enable_guess_schema disabled:

in:
  type: facebook_leads
  app_secret: <app_secret>
  access_token: <long-lived-access_token>
  id: 33056800448180
  time_created: '2020-01-28T15:46:25+0000'
  incremental: false
  enable_guess_schema: false
  form_fields:
	- {name: ocupation, type: string}
	- {name: last_name, type: string}
	- {name: cell_Phone, type: string}
	- {name: email, type: string}
	- {name: name, type: string}
	- {name: opt_in_marketing, type: boolean}

out:
  mode: append

Configuration keys and descriptions are as follows:

Config key

Type

Required

Description

type

string

yes

Connector type "facebook_leads"

app_secret

string

no

Facebook App Secret

access_token

string 

yes

Facebook long-lived Access token, see instruction here

ad_account_id

string

no

Facebook Ad Account ID

id

string

yes

Leads data can be imported by Ad Id or Form ID. See Appendix for details how to get Ad ID and Form ID

time_created

string

no

Import Leads data submitted since this time until the current time. The field accepts ISO 8601 date-time format. E.g. 2020-01-01T00:00:00+0700

incremental

boolean

no

Only import new data each time. See How Incremental works

 

form_fields

array

no

Required when enable_guess_schema=false Facebook Lead Form fields name and its data type

  • name

string

yes

Form field name. See Appendix for How to fine Lead’s Form Fields

  • type

string

yes

Field data type

  • format

string

no

Timestamp format. E.g. %Y-%m-%dT%H:%M:%S%z

  • skip_invalid_records

boolean

no

Skip invalid Leads data and continue to import others. If unselected, job will fail if invalid data is encountered

For more details on available out modes, see Appendix

Guess Fields (Generate load.yml)

Use connector:guess. This command automatically reads the target data, and intelligently guesses the data format.

$ td connector:guess seed.yml -o load.yml

If you open the load.yml file,  you see guessed file format definitions including column names, data type and format.

---
in:
  type: facebook_leads
  app_secret: <app_secret>
  access_token: <long-lived-access_token>
  id: 514618966066498
  time_created: '2019-12-01T15:46:25+0000'
  incremental: false
  columns:
  - {name: id, type: long}
  - {name: created_time, type: timestamp, format: "%Y-%m-%dT%H:%M:%S%z"}
  - {name: ad_id, type: string}
  - {name: ad_name, type: string}
  - {name: adset_id, type: string}
  - {name: adset_name, type: string}
  - {name: campaign_id, type: string}
  - {name: campaign_name, type: string}
  - {name: form_id, type: long}
  - {name: platform, type: string}
  - {name: is_organic, type: boolean}
  - {name: name, type: string}
  - {name: surname, type: string}
  - {name: email, type: string}
out: {mode: append}
exec: {}
filters:
- from_value: {mode: upload_time}
  to_column: {name: time}
  type: add_time

Then you can preview the data by using preview command.

$ td connector:preview load.yml

Example of Preview Data

+---------------+----------------------------+----------------+-------------------+------------------+--------------------+---------------------+------------------------+-----------------+-----------------+-------------------+----------------------+--------------------+-------------------+------------------------------+---------------+--------------------------+-------------------------------+
| id:string | created_time:string | ad_id:string | ad_name:string | adset_id:string | adset_name:string | campaign_id:string | campaign_name:string | form_id:string | platform:string | is_organic:string | ocupation:string | last_name:string | cell_phone:string | email:string | name:string | opt_in_marketing:boolean | time:timestamp |
+---------------+----------------------------+----------------+-------------------+------------------+--------------------+---------------------+------------------------+-----------------+-----------------+-------------------+----------------------+--------------------+-------------------+------------------------------+---------------+--------------------------+-------------------------------+
| "4045014073" | "2020-12-09T02:47:03+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Woodworker" | "Metinez" | "+593-12232704" | "wiparra1984@hotmail.com" | "wer" | true | "2020-12-11 16:46:19.171 UTC" |
| "5082987500" | "2020-12-08T21:15:00+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Other" | "Eeinal" | "+593-122326034" | "esl_darwin@yahoo.es" | "Din" | true | "2020-12-11 16:46:19.171 UTC" |
| "7101311962" | "2020-12-08T20:03:24+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Mechanic" | "Eeéves" | "+593-12232181" | "Wirem78@gmail.com" | "Won" | true | "2020-12-11 16:46:19.171 UTC" |
| "8393190935" | "2020-12-08T19:56:21+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Woodworker" | "Jela" | "+593-12232060" | "jujoselito@gmail.com" | "Jlito" | true | "2020-12-11 16:46:19.171 UTC" |
| "1310180253" | "2020-12-08T19:30:48+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "ig" | "false" | "Other" | "Eeara" | "+593-12232703" | "rulejandroendara@gmail.com" | "Rn" | true | "2020-12-11 16:46:19.171 UTC" |
| "1450462603" | "2020-12-08T19:22:49+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Electrician" | "Meales Salgado" | "+593-12232361" | "mos12_nelson@hotmail.com" | "Non Gerardo" | true | "2020-12-11 16:46:19.171 UTC" |
| "3902488656" | "2020-12-08T19:10:36+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "General_Contractor" | "Ze" | "+593-12232164" | "frzeab@hotmail.com" | "Fdy" | true | "2020-12-11 16:46:19.171 UTC" |
| "4082451769" | "2020-12-08T19:09:23+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Woodworker" | "Qespe Palacios" | "+593-12232464" | "wiquispe55@yahoo.es" | "Wer Quispe" | true | "2020-12-11 16:46:19.171 UTC" |
| "35426318220" | "2020-12-08T18:53:04+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Metalworker" | "Gevara" | "+593-12232122" | "wagtonsanchez20@yahoo.es" | "Erdo" | true | "2020-12-11 16:46:19.171 UTC" |
| "7637760944" | "2020-12-08T18:31:37+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "ig" | "false" | "Woodworker" | "Aearado" | "+593-12232684" | "eromirt@yahoo.es" | "Esto Mirt" | true | "2020-12-11 16:46:19.171 UTC" |
| "28014314104" | "2020-12-08T18:30:44+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Metalworker" | "Herera" | "+593-12232103" | "dopitojoselyn@gmail.com" | "Rigo" | true | "2020-12-11 16:46:19.171 UTC" |
| "7438330695" | "2020-12-08T18:20:04+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Woodworker" | "Zebrano Fienco" | "+593-122323579" | "jzanof@hotmail.com" | "Jre Tito" | true | "2020-12-11 16:46:19.171 UTC" |
| "11064095432" | "2020-12-08T18:09:14+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "General_Contractor" | "Qerumbay Ch" | "+593-12232856" | "gqmbay@hotmail.con" | "Ganny" | true | "2020-12-11 16:46:19.171 UTC" |
| "6792003594" | "2020-12-08T17:59:20+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Mechanic" | "Vera" | "+593-12232085" | "ra9@hotmail.com" | "Rrto" | true | "2020-12-11 16:46:19.171 UTC" |
| "7877391352" | "2020-12-08T17:49:47+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Woodworker" | "veores" | "+593-12232140" | "leitores03@gmail.com" | "mael" | true | "2020-12-11 16:46:19.171 UTC" |
| "1495469598" | "2020-12-08T17:48:15+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Other" | "Vearezo" | "+593-12232069" | "fe@hotmail.com" | "Fxiximo" | true | "2020-12-11 16:46:19.171 UTC" |
| "3998692212" | "2020-12-08T17:48:08+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Woodworker" | "Peez" | "+593-12232161" | "aroperezmito@gmail.com" | "Ando" | true | "2020-12-11 16:46:19.171 UTC" |
| "3880364891" | "2020-12-08T17:34:52+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "General_Contractor" | "Ceza Flores" | "+593-12232824" | "alo.caiza72@hotmail.com" | "J Alfredo" | true | "2020-12-11 16:46:19.171 UTC" |
| "2154481135" | "2020-12-08T17:26:23+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "General_Contractor" | "Ieas" | "+593-12232569" | "wapg213@hotmail.con" | "Is" | true | "2020-12-11 16:46:19.171 UTC" |
| "2970973350" | "2020-12-08T17:22:01+0000" | "238461972541" | "Promos_INTERE01" | "23846197254590" | "SubscribeV1_Prom" | "23846000000664251" | "SAMPLE_CP_DIC20_Sub1" | "3545308056298" | "fb" | "false" | "Other" | "Peheco" | "98739847347" | "huacheco21@hotmail.com" | "Aio" | true | "2020-12-11 16:46:19.171 UTC" |
+---------------+----------------------------+----------------+-------------------+------------------+--------------------+---------------------+------------------------+-----------------+-----------------+-------------------+----------------------+--------------------+-------------------+------------------------------+---------------+--------------------------+-------------------------------+


If the system detects your column type unexpectedly, modify the  load.yml directly and preview again.

The data connector supports parsing of "boolean", "long", "double", "string", and "timestamp" types.

Execute Load Job

Submit the load job. It may take a couple of hours depending on the data size. Users need to specify the database and table where their data is stored.

$ td connector:issue load.yml --database td_sample_db --table td_sample_table

The preceding command assumes that you have already created database(td_sample_db) and table(td_sample_table). If the database or the table do not exist in TD this command will not succeed, so create the database and table manually or use 

  • -auto-create-table

 option with td connector:issue command to automatically create the database and table:

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column created_at --auto-create-table 

You can assign Time Format column to the "Partitioning Key" by "--time-column" option.

Scheduled Execution

You can schedule periodic data connector execution for periodic Leads import. We carefully configure our scheduler to ensure high availability. By using this feature, you no longer need a cron daemon on your local data center.

Create the Schedule

A new schedule can be created by using the td connector:create command. The name of the schedule, cron-style schedule, the database and table where their data is stored, and the data connector configuration file are required.

$ td connector:create \
    daily_leads_import \
    "10 0 * * *" \
    td_sample_db \
    td_sample_table \
    load.yml 

The `cron` parameter also accepts three special options: `@hourly`, `@daily` and `@monthly`.

Incremental Scheduling

You can load records incrementally by setting true for the `incremental` option.

in:
 type: facebook_leads
 app_secret: <app_secret>
 access_token: <long-lived-access_token>
 id: 33056800448180
 time_created: '2020-01-28T15:46:25+0000'
 incremental: true
out:
 mode: append

If you’re using scheduled execution, the connector automatically saves the last import time time_created value and holds it internally. Then it is used at the next scheduled execution.

in:
  type: facebook_leads
  ...
out:
  ...

Config Diff
---
in:
  time_created: '2020-02-02T15:46:25Z'


List the Schedules

You can see the list of scheduled entries by td connector:list.

$ td connector:list
+---------------------------+--------------+----------+-------+--------------+-----------------+---------------------------------+
| Name                      | Cron         | Timezone | Delay | Database     | Table           | Config                          |
+---------------------------+--------------+----------+-------+--------------+-----------------+---------------------------------+
|daily_leads_import         | 10 0 * * *   | UTC      | 0     | td_sample_db | td_sample_table | {"type"=>"facebook_leads",... } |
+---------------------------+--------------+----------+-------+--------------+-----------------+---------------------------------+

Show the Setting and History of Schedules

td connector:show shows the execution setting of a schedule entry.

$ td connector:show daily_leads_import
Name     :daily_leads_import
Cron     : 10 0 * * *
Timezone : UTC
Delay    : 0
Database : td_sample_db
Table    : td_sample_table
Config
---
// Displayed load.yml configuration.

td connector:history shows the execution history of a schedule entry. To investigate the results of each individual execution, use td job <jobid>.

Delete the Schedule

td connector:delete removes the schedule.

$ td connector:delete daily_leads_import 

 

FAQ for Import from Facebook Lead Ads 

What Facebook App scopes or permissions are required for this Connector?

  • Following permissions are required:

    • email

    • public_profile
    • leads_retrieval

    • pages_manage_ads,pages_manage_metadata,pages_read_engagement,pages_read_user_content (if you want to import by Form ID)

    • ads_management (if you want to import by Ad ID)

Appendix

Import Modes for the Out Section 

You can specify file import mode in the out section of the load.yml file.

The out: section controls how data is imported into a Treasure Data table.
For example, you may choose to append data or replace data in an existing table in Treasure Data.

Mode

Description

Examples

Append

Records are appended to the target table.

in:
  ...
out:
  mode: append

Always Replace

Replaces data in the target table. Any manual schema changes made to the target table remain intact.

in:
  ...
out:
  mode: replace

Replace on new data

Replaces data in the target table only when there is new data to import.

in:
  ...
out:
  mode: replace_on_new_data

How Incremental Loading Works

If  incremental: true is set, this connector loads all records created since the time_created, if the time_created is set, or import all available data until the current time if time_created is not set. The next job execution will only import records created since the last job execution. This mode is useful when you want to fetch just the Leads created since the previously scheduled run.

For example the first job execution you specified the config as: 

in:
 ...
 time_created: '2020-01-28T15:46:25+0000'
 incremental: true

When bulk data loading finishes successfully, it outputs time_created: parameter. E.g. '2020-02-02T15:46:25+0000' as config-diff so that next execution uses it.
At the next execution, when time_created: is also set, this plugin uses the time_created from config-diff and ignores the original value and the new job config runs as 

in:
 ...
 time_created: '2020-02-02T15:46:25+0000'
 incremental: true

That way, each time the job runs, it only imports new records.

For Ad Account level Leads import (by setting the ad_account_id), a list of Lead Ad IDs and its latest time_created are stored as config-diff for the next job execution, e.g.

in:
  ...
  incremental: true
  list_time_created: {
	'23845900031': '2020-11-01T02:46:45Z',
	'23845899651': '2020-11-02T21:25:33Z',
	'23846121121': '2020-11-30T05:21:03Z',
	'23845899651': '2020-11-01T12:39:53Z',
	'23845900031': '2020-11-02T04:13:19Z',
	'23845899651': '2020-11-04T01:39:58Z'
}


  • No labels