Prepare a CSV for lead conversions

A lead conversion file tells the platform what happened to the leads its ads produced. The platform already knows a form was submitted. This file tells it which of those leads became qualified, and which bought, so it can optimize for the outcome rather than for form fills.

There is a template per platform, because each one keys on its own lead identifier. Most of this article covers Meta, which has the widest set of requirements. The other four are at the end.

Before you start

Meta places five conditions on this integration. All five are on your side, and the integration will not work usefully without them.

The Meta lead ID is stored against the lead in your CRM. It has to be captured when the lead arrives, and stored in a text field so a long number is not truncated.
At least three stages are reported back, so Meta has a funnel rather than a single point.
At least one percent of leads reach the final stage. On 1,000 leads from an instant form, that means at least ten conversions.
A stage called New Lead is included and sent for every lead. This is required for lead coverage and is not optional.
A CRM pixel exists and is connected in Studio.

Required columns

Column	What goes in it
facebook_leads_id	The Meta lead ID. A 15 or 16 digit number from the `leadgen_id` field on the lead generation webhook. Sent in plain text.
event_name	The stage the lead reached, using your own CRM’s name for it. For example Interested, Qualified or Purchase.
event_time	A UNIX timestamp.
last_modified_date	The time the lead status was updated.
lead_event_source	The name of the system the outcome was recorded in, for example HubSpot, Dynamics or the name of your in-house CRM.
action_source	One of `phone_call`, `chat`, `physical_store`, `system_generated` or `other`.
email_1_hashed	Hashed email address. Required.
One phone column	At least one of the six phone columns, hashed.
currency and value	Required on Purchase rows only. Leave both empty on every other stage.

Columns worth adding

The lead ID is what identifies the person, so these do not decide whether the conversion is attributed. They give Meta more to work with and are worth sending where your CRM holds them.

Column	Notes
first_name_hashed, city_hashed, state_hashed, country_hashed	Recommended.
last_name_hashed, middle_name_hashed, zipcode_hashed, gender_hashed	Optional. Names follow the same rule as the others: letters a to z, trimmed and lowercased, then hashed.
date_of_birth_whole_hashed	Optional. DD/MM/YYYY, then hashed.
date_of_birth_year_hashed, date_of_birth_month_hashed, date_of_birth_date_hashed	The same date split across three columns: YYYY, MM from 01 to 12, and DD from 01 to 31. Use these or the combined column, not both.
email_2_hashed, email_3_hashed	A second and third address, one per column.
external_id	Your own identifier for the person. Sent in plain text.
state, zipcode, country	The same location fields in plain text, for where an unhashed value is wanted. Two-letter state code for the United States, the full region name elsewhere.

Finding the Meta lead ID

The lead ID is a 15 or 16 digit number issued by Meta for every lead. It arrives in the leadgen_id field on the lead generation webhook and appears under the user data parameter. Where it ends up after that depends on how the lead was downloaded or synced into your CRM, so check the field yours actually stores it in rather than assuming.

How each value has to be formatted

Email address. Trim and lowercase, then hash. One address per column.
Phone number. E.164, so a leading plus sign then the country code and the number, up to fifteen digits. Then hash.
Names and city. Letters a to z only, trimmed and lowercased, then hashed.
Date of birth. DD/MM/YYYY, then hashed.
The lead ID, the event fields, the action source, external_id and the plain location columns are not hashed.

For the hashing step itself, Apply SHA-256 hashing to PII has snippets for Google Sheets, SQL and the common languages, and test vectors to check your export against.

Never hash an empty cell. A hashed blank matches nobody and looks like a real value.

Naming the file and how often to send

The file name has to carry the date on which it is uploaded. Add a numeric suffix for a second file on the same date.
Daily is the recommendation and three files a week is the practical minimum.
Historic data can be backfilled up to seven days.

The file itself

A comma-separated .csv file.
A header row naming the columns. You only need the columns you are actually sending, so a file with fewer columns than the template is fine.
There is no limit on the number of rows or on the size of the file.

A header mismatch fails quietly. A column whose header does not match the template is skipped rather than reported, and the file processes normally without it, so a renamed or misspelled header does not produce an error. It produces events with that field missing. If a match rate is lower than you expected on a file that processed cleanly, compare the header row against the template first, including case and underscores.

Check before you send

Every row carries a lead ID, and none has been truncated.
New Lead rows are present.
Purchase rows carry currency and value, and no other row does.
Phone numbers are in E.164 before hashing.
The stage names are the ones you agreed to report, spelled identically every time. A stage that differs by a capital letter is a different stage to Meta.

The other four platforms

Five fields appear in every template: event_name, event_time, last_modified_date, currency and value. The rules on those are the same everywhere, including currency and value being needed on Purchase rows only. Everything else differs.

Platform	Lead identifier	Identifier requirement
Meta	facebook_leads_id	Hashed email address required, plus at least one of six hashed phone columns. lead_event_source and action_source also required.
Google	None. Matching is on the identifiers themselves.	Either a hashed email address or a hashed phone number.
TikTok	tiktok_lead_id	Email address and phone number both optional. lead_event_source required.
Snapchat	snapchat_lead_id	Hashed email address required, plus at least one of six hashed phone columns.
LinkedIn	linkedin_lead_id	Hashed email address required. There is no other identifier.

Google

Google is the exception with no lead identifier column. The match is made on a hashed email address or a hashed phone number, so at least one of the two is required, with the click identifiers gclid, gbraid or wbraid alongside where you have them.

The name and address fields work as a set rather than individually. If you map any of first name, last name, street address, country, state, city or postcode, send all seven. The first three are hashed and the last four go in plain text.

TikTok

Keyed on tiktok_lead_id, and lead_event_source is required as it is on Meta. The email address and phone number are both optional here, in single columns named email_hashed and phone_hashed rather than the numbered sets used elsewhere.

It is the only lead template that carries product columns: product_id, product_type, product_name, product_category, brand_name, number_items, shop_id and transaction_id.

Snapchat

The widest of the four at 30 columns, and the closest to the Meta template. A hashed email address is required, as is at least one of the six phone columns. First name, last name, city and state are recommended, and the location fields are hashed rather than plain. It also accepts the client user agent, the IP address and the Snapchat click ID.

The narrowest at seven columns: the lead ID, a hashed email address, and the five common fields. The email address is required, because it is the only identifier LinkedIn matches on.