Apiway allows you to make free API integration with Google Sheets and Facebook Offline Conversions without coding in a few minutes
Start to ConnectHow integration works between Google Sheets and Facebook Offline Conversions?
New or Updated Spreadsheet Row
How to connect Google Sheets & Facebook Offline Conversions without coding?
Step 1. Sign up on Apiway
Step 2. Connect Google Sheets & Facebook Offline Conversions with Apiway Step 3. Select the trigger event that starts the data transfer Step 4. Select the action app where the data should be sent
Step 5. Map the data fields using automation builder
Automate Google Sheets and Facebook Offline Conversions workflow
Collaboration
CRM
Create Google Sheets and Facebook Offline Conversions free integration. Automate your workflow with other apps using Apiway
Orchestrate Google Sheets and Facebook Offline Conversions with these services
Use the Offline Conversions API to send offline conversion events and to see how many customers viewed or clicked Facebook ads before converting.
To use this API, you need:
1. Facebook Business Manager
If you don't have one, create one.
2. Facebook App ID
For access to Marketing API. To create an app:
- Visit Business Manager > Business Settings.
- Select Apps.
- Click Add New App and follow the instructions.
3. App Review and Permissions
Rules around app review and permissions depend on how you are implementing our API:
Direct Implementation | In this case, you are an advertiser looking to use offline conversions directly. In this case:
|
Partners Implementing As A Platform | In this case, you are a third-party partner offering offline conversions functionalities for advertisers that use your services. In this case:
|
4. Business Manager System User and Token
With system user access, your app can send data to Facebook via the API. To create it:
- Visit Business Manager > Business Settings.
- Select System Users.
- Click Add New System User.
- Select Admin System User as the role for the system user.
An access token provides access to Facebook data. To create system user access tokens:
- Visit Business Manager > Business Settings.
- Select System Users.
- Select the system user and click Generate New Token.
- Select your app > for scope, select ads_management.
5. Ad Account
You need an ad account to run ad campaigns on Facebook. To create one, see Marketing API or Advertiser Help Center, Set up People, Pages & Ad Accounts in Business Manager.
6. Grant System User Access to Ad Account
Grant your system user access to your ad account.
- Go to Business Manager > Business Settings.
- Select System Users.
- Select the system user and click Assign Assets.
- Select your ad account.
7. Offline Event Set
These are uploaded files with offline conversion data. When you create an ad, set tracking_spec to the offline event set ID to correctly attribute events. You can then create event sets, view stats on your imports, delete, and modify this data in Business Manager.
With older implementations, you could do offline event set CRUD operations at the Business Manager level to share event sets with other objects and entities.
You need specific access to create offline event sets, upload, or view data for an event set. You also need this access to assign these permissions to an ad account. You must be one of the following:
- Business Manager admin
- Admin system user who created the offline event set
- Admin on the ad_account connected to the offline event set
See Offline Conversion Event Set, Reference.
1. Create Offline Event Set
curl -F 'access_token=<SYSTEM_USER_ACCESS_TOKEN>' -F 'name=offline_event_set', -F 'description=conversion data used for superbowl campaign', //graph.facebook.com/<API_VERSION>/<BUSINESS_MANAGER_ID>/offline_conversion_data_sets
Make a HTTP POST:
Graph API Explorer
POST /<BUSINESS_MANAGER_ID>/offline_conversion_data_sets HTTP/1.1 Host: graph.facebook.comcurl -X POST \ -F 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<BUSINESS_MANAGER_ID>/offline_conversion_data_sets
The response includes an event set id:
{ "id": <OFFLINE_EVENT_SET_ID> }Parameters
name type: string | Event set name. Example: In store purchases, Lead registrations |
description type: string | Event set description. Example: In store purchases for the Superbowl campaign |
2. Assign Ad Account Permissions
To assign tracking and read permissions to an ad account:
Graph API Explorer
POST /<OFFLINE_EVENT_SET_ID>/adaccounts HTTP/1.1 Host: graph.facebook.comcurl -X POST \ -F 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<OFFLINE_EVENT_SET_ID>/adaccounts
Parameters
business type: integer | Assign ad account to this business ID. |
account_id type: integer | ID of ad account with offline tracking enabled. |
3. Set Ad Tracking
When you update tracking_spec, we overwrite it. Make sure to do GET first, then append the associated String for the offline event set to the existing tracking_spec. See Ads Management or use Ads Manager. For example, provide an appropriate tracking spec:
curl \ -F 'tracking_spec=[{action.type:"offline_conversion", dataset:["123"]}]' \ -F 'access_token=<SYSTEM_USER_ACCESS_TOKEN>' \ //graph.facebook.com/<API_VERSION>/<AD_ID>To update your ads's tracking spec:
Graph API Explorer
POST /<AD_ID>/?tracking_specs=[{"action.type":"offline_conversion","dataset": <OFFLINE_EVENT_SET_ID>}] HTTP/1.1 Host: graph.facebook.comcurl -X POST \ -F 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<AD_ID>/?tracking_specs=[{"action.type":"offline_conversion","dataset": <OFFLINE_EVENT_SET_ID>}]
Parameters
action.type type: string | Track this action for the ad group. Example: offline_conversion |
dataset type: integer | ID for offline event set. Example: 11111111111 |
4. Upload Offline Events
You should upload transaction within 62 days of the conversion. Upload the conversion data:
curl \ -F 'access_token=SYSTEM_USER_ACCESS_TOKEN' \ -F 'upload_tag=store_data' \ -F 'data=[ \ { match_keys: {"phone": ["HASH1","HASH2"], "email": ["HASH3","HASH4"]}, currency: "USD", value: 16, event_name: "Purchase", event_time: 1456870902, contents: [ {id: "A", quantity: 1}, {id: "B", quantity: 2}, {id: "C", quantity: 1} ] custom_data: { event_source: "in_store" }, }, { match_keys: {"lead_id": "12345"}, event_name: "Lead", event_time: 1446336000, contents: [ {id: "A", quantity: 1}, {id: "B", quantity: 2}, {id: "C", quantity: 1} ] custom_data: { event_source: "email", action_type: "sent_open_click", email_type: "email_type_code", email_provider: "gmail_yahoo_hotmail", } }, ]' //graph.facebook.com/VERSION/OFFLINE_EVENT_SET_ID/eventsTo send conversions, make a HTTP POST:
Graph API Explorer
POST /<OFFLINE_EVENT_SET_ID>/events HTTP/1.1 Host: graph.facebook.comcurl -X POST \ -F 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<OFFLINE_EVENT_SET_ID>/events
Parameters
upload_tag type: string | Required. Track your event uploads. Example: monthly, in-store uploads |
data type: json array | Required. Includes number of events being uploaded. Upload a conversion event per person up to 2,000 events per API call. Example: see example above |
namespace_id type: integer | Optional. Scope used to resolve extern_id or tpid. Can be another data set or data partner ID. Example: 12345 |
Use the same upload_tag for all event upload API calls in the same batch to group them. This helps you debug event uploads, and you should use this for any event uploads made in more than one API call.
Parameters within data include:
match_keys type: JSON dictionary | Required. The identifying information we use to match people on Facebook. Example: {"phone": ["{HASH}","{HASH}"], "email": ["{HASH}","{HASH}"], "fn": "{HASH}",}. |
event_time type: integer | Required. The UNIX timestamp of the conversion event. Example: 1456870055 |
event_name type: string | Required. Type of event. Example: ViewContent, Search, AddToCart, AddToWishlist, InitiateCheckout, AddPaymentInfo, Purchase, Lead, Other |
currency type: string | Required. Three-letter ISO currency code for this conversion event. Required for Purchase events. Example: USD |
value type: double | Required. Value of conversion event. Required for Purchase events. Example: 16.00 |
content_type type: string | Optional. Any valid Dynamic Ad content-type. Example: product |
contents type: JSON array | Optional. Required if you integrate your ads with Product Catalog. Example: Required: id, quantity Recommended: price, brandcategory Required: [ {id: "A", quantity: 1}, {id: "B", quantity: 2}, {id: "C", quantity: 1}] Recommended: [ {id: "A", quantity: 1, brand: "Brand_A", category: "", price: 10.0}] |
custom_data type: JSON dictionary | Optional. Information about this conversion event. Example: {category: 'ICECREAM'} |
order_id type: string | Optional. Unique identifier for each transaction or order in an offline event set. For example, for retail this can be a receipt ID. Example: ATN10001, 123456 |
item_number type: string | Optional. Unique identifier to distinguish events within the same order or transaction. Example: 1, a |
For example, you can upload information with the data field:
{ match_keys: MATCH_KEYS, event_time: EVENT_TIME, event_name: "Purchase", value: 400, currency: "USD", contents: [ { id: "A", quantity: 1, brand: "brand_of_A", category: "Apparel & Accessories | Clothing", price: 100, }, { id: "B", quantity: 2, brand: "brand_of_B", category: "Apparel & Accessories | Shoes", price: 50, }, { id: "C", quantity: 1, brand: "brand_of_C", category: "Apparel & Accessories | Jewelry | Watches", price: 200, } ], }Use the same upload_tag for all event upload API calls in the same batch to group them. This helps you debug event uploads, and you should use this for any event uploads made in more than one API call.
Match Keys
match_keys is a set of identifiers to match people for attribution. See Custom Audiences from CRM Data for normalizing and hashing your data. Only SHA256 is supported and we do not accept unhashed data.
Email Address(es) | YES | |
Phone Number(s) | phone | YES |
Gender | gen | YES |
Date of Birth. YYYY format | doby | YES |
Date of Birth. MM format | dobm | YES |
Date of Birth. DD format | dobd | YES |
Last Name | ln | YES |
First Name | fn | YES |
First Name Initial | fi | YES |
City | ct | YES |
US States | st | YES |
Zip codes | zip | YES |
Country | country | YES |
Apple Advertising Identifier | madid | YES |
Android Advertising ID | madid | YES |
Third-party user id | extern_id | Do NOT hash |
The lead id from Lead Ads | lead_id | Do NOT hash |
The response:
num_processed_entries | integer | Number of processed entries |
On errors you see an exception including invalid entries and the reason. Fix the errors or skip the data rows with errors and retry the API call.
View Upload Stats
A Business Manager admin or system user who created the offline event set can retrieve upload stats. Also any admin on the ad_account which is connected to the offline event set can read this data.
To view stats about offline event sets such as valid entries and matched entries:
Graph API Explorer
GET /<OFFLINE_EVENT_SET_ID>/uploads HTTP/1.1 Host: graph.facebook.comcurl -X GET -G \ -d 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<OFFLINE_EVENT_SET_ID>/uploads
View daily breakdowns of offline events in Offline Events Manager in Business Manager. For more precise breakdowns, make this call:
Graph API Explorer
GET /<OFFLINE_EVENT_SET_ID>/stats HTTP/1.1 Host: graph.facebook.comcurl -X GET -G \ -d 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<OFFLINE_EVENT_SET_ID>/stats
Parameters
start type: integer | Optional. The UNIX timestamp. Query events starting at this time. Example: 1456870055 |
end type: integer | Optional. The UNIX timestamp. Excludes events that occur this time onward. Example: 1456870056 |
fields type: string[] | Optional. List of strings. This endpoint provides all fields until v3.0, and after that it provides count, event and time by default. Additional fields are: matched_count, attributed_count, usd_amount, matched_usd_amount, attributed_usd_amount, or duplicate_count. |
summary type: boolean | Optional. Returns the average upload delay time in seconds when aggregation time is set to upload_time and this field is set to true. |
aggr_time type: string | Optional. Aggregate results based on this set time. Options are upload_time and event_time. Default is event_time. |
Offline Custom Conversions currently do not backfill. We do not attribute data from event uploads made before you created the custom conversion. You cannot use offline custom conversion data for ads delivery optimization. See Custom Conversion, Reference.
To create a custom conversion using your offline events, make a POST:
Graph API Explorer
POST /act_<ACCOUNT_ID>/customconversions HTTP/1.1 Host: graph.facebook.comcurl -X POST \ -F 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/act_<ACCOUNT_ID>/customconversions
Parameters:
name type: string | New custom conversion name. Example: Offline purchases over 100 dollars |
event_source_id type: integer | Offline event set ID to track. Example: 11111111111 |
custom_event_type type: string | One of nine Facebook Pixel standard events. Example: "ViewContent", "Search", "AddToCart", "AddToWishlist", "InitiateCheckout", "AddPaymentInfo", "Purchase", "Lead", "CompleteRegistration", "Other" |
rule type: JSON-encoded string | Operators and data for your conversion rule. See Custom Conversion, Reference. For example, purchases over USD 100. Example: {"and":[{"event_name":{"eq": "Purchase"}},{"value":{"gt":100}}]} |
The response, on success:
{ "id": <CUSTOM_CONVERSION_ID> }Measure with Custom Data
You can use the custom_data field to create rules that later determine if a conversion counts or not. This is similar to Offline Custom Audiences. The maximum number of custom conversions you can have per ad account is 40.
For example, include product category in uploads with custom_data:
data=[ { match_keys: {"phone": ["<HASH>","<HASH>"], "email": ["<HASH>","<HASH>"]}, currency: "USD", value: 16, event_name: "Purchase", event_time: 1456870902, custom_data: { product_category: "ICECREAM", }, }, ]Then use custom_data.{YOUR_CUSTOM_PARAM} to create a Custom Conversion rule:
curl \ -F 'name=Ice Cream Purchasers' \ -F 'custom_event_type=Purchase' \ -F 'event_source_id=<OFFLINE_EVENT_SET_ID>' \ -F 'rule={"and": [{"event_name":{"eq":"Purchase"}},{"custom_data.product_category":{"i_contains":"ICECREAM"}}]}' \ -F 'access_token=<ACCESS_TOKEN>' \ "//graph.facebook.com/<API_VERSION>/act_<ACCOUNT_ID>/customconversions"
To attribute offline conversion events to your client's ad, follow these steps. The API calls for most of these steps are same as when someone manages their own offline event set and campaign management.
- Partner - Create offline event set
- Partner - Share event set with client's Business Manager
- Client - Assign ad account offline tracking permission
- Client - Set offline tracking on ads
- Partner - Upload offline events, view stats
- Partner - Display ad insights for client
These steps vary depending on how your partner or agency permissions are set up with client ad accounts:
- You may own all ad accounts, event sets or any other assets of your client.
- You may have permission to your client's assets to perform certain actions.
To setup these permissions, see Business Manager Assets.
Partner - Share Event Set
Share the event set your client's Business Manager. Your client can then use the event set for ads tracking.
Graph API Explorer
GET /<OFFLINE_EVENT_SET_ID>/agencies HTTP/1.1 Host: graph.facebook.comcurl -X GET -G \ -d 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<OFFLINE_EVENT_SET_ID>/agencies
Parameters
business type: integer | ID of your client business manager |
Client - Assign Ad Account Offline Tracking Permission
Share offline event sets created by a partner with their client. You need to be the Business Manager admin or admin system user who created the offline event set to enable tracking for ads under and ad account. If you are an admin on the ad account connected to the offline event set, you can also can do this. To make this API call, the business in the call has to have access to the offline event set.
You can assign offline event tracking and viewing permissions to an ad account with this call:
Graph API Explorer
POST /<OFFLINE_EVENT_SET_ID>/adaccounts HTTP/1.1 Host: graph.facebook.comcurl -X POST \ -F 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/<OFFLINE_EVENT_SET_ID>/adaccounts
Parameters
business type: integer | Assign ad account to this business ID. |
account_id type: integer | Ad account ID with offline tracking enabled |
There may be scenarios where you provide your own external ID to represent a customer and match them to someone on Facebook. To do this, use extern_id following these guidelines.
Data partners who went through the match process can use partner ID as the namespace ID and extern_id as your tpid.
Provide only match_keys
We use match_keys to try to determine if the conversion data you shared can be matched to a Facebook user. If you provide match_keys, you cannot also provide a namespace_id parameter.
Provide both match_keys and extern_id
We use match_keys to try to find people on Facebook and forward mappings from {dataset_id, extern_id} to {facebook_user_id}. If you provide match_keys and extern_id, you cannot also provide namespace_id.
Provide only extern_id
If you already sent data with match_keys and extern_id, Facebook uses {dataset_id, extern_id} to retrieve a {facebook_user_id}.
Provide namespace_id
The namespace_id parameter applies to the entire API call. You can use it to refer to another offline event set that is accessible or owned by a business or a partner profile ID. If you already sent data with match_keys and extern_id, Facebook uses {namespace_id, extern_id} to retrieve a {facebook_user_id}. You should only provide one extern_id per row of data.
See offline events attributed to an ad someone viewed or clicked. We attribute offline conversions after more than 1 day. This means you need to set your attribution window to 28d_view, or action_attribution_windows=['28d_view']', otherwise you will not see any conversions in reports. See Insights API and Insights Guide.
Graph API Explorer
GET /act_<ADACCOUNT_ID>/insights HTTP/1.1 Host: graph.facebook.comcurl -X GET -G \ -d 'access_token=<ACCESS_TOKEN>' \ //graph.facebook.com/act_<ADACCOUNT_ID>/insights
Parameters
action_breakdowns type: string[] | Break down of impression, click, or conversion data. Grouped by type of action: offline, online, and so on. Example: ["action_type", "placement", "age", "gender", "country", "region"] |
fields type: string[] | The basic ad metrics. Example: ["impressions", "clicks", "actions",] |
level type: string | Aggregate or de-duplicate data at this level of reporting results. Example: ad or adset or campaign |
date_preset type: string | Relative timeframes to query metrics. Example: last_n_days (n = 7,14,28) or yesterday or today or last_month or lifetime |
Results look like this:
{ "data": [ { "date_start": "2015-12-01", "date_stop": "2015-12-01", "actions": [ { "action_type": "offline_conversion.purchase", "value": 1 }, { "action_type": "offsite_conversion.lead", "value": 3 }, ], ... } ] }For example, to view attribution:
curl -G \ -d 'access_token=<SYSTEM_USER_ACCESS_TOKEN>' \ -d 'fields=unique_actions,action_values' \ //graph.facebook.com/<API_VERSION>/act_<AD_ACCOUNT_ID>/insightsThe result looks like this:
{ "data": [ { "unique_actions": [ { "action_type": "link_click", "value": 94 }, { "action_type": "offline_conversion", "value": 1 }, { "action_type": "offline_conversion.purchase", "value": 1 }, { .... "value": 1 } ], "action_values": [ { "action_type": "offline_conversion.purchase", "value": 27.5 }, { "action_type": "offline_conversion", "value": 27.5 } ], "date_start": "2016-06-06", "date_stop": "2016-06-07" } ], "paging": { "cursors": { "before": "MAZDZD", "after": "MAZDZD" } } }
For these two APIs, implement data processing options by adding data_processing_options, data_processing_options_country, and data_processing_options_state inside each event within the data parameter of your events.
To explicitly not enable Limited Data Use (LDU) mode, specify an empty array for each event:
{ "data": [ { "event_name": "Purchase", "event_time": <EVENT_TIME>, "user_data": { "em": "<EMAIL>" }, "custom_data": { "currency": "<CURRENCY>", "value": "<VALUE>" }, "data_processing_options": [] } ] } To enable LDU and specify user geography: { "data": [ { "event_name": "Purchase", "event_time": <EVENT_TIME>, "user_data": { "em": "<EMAIL>" }, "custom_data": { "currency": "<CURRENCY>", "value": "<VALUE>" }, "data_processing_options": ["LDU"], "data_processing_options_country": 1, "data_processing_options_state": 1000 } ] } To enable LDU with geolocation (this works for Server-side API provided client IP address is provided): { "data": [ { "event_name": "Purchase", "event_time": <EVENT_TIME>, "user_data": { "em": "<EMAIL>", "client_ip_address": "256.256.256.256" }, "custom_data": { "currency": "<CURRENCY>", "value": "<VALUE>" }, "data_processing_options": ["LDU"], "data_processing_options_country": 0, "data_processing_options_state": 0 } ] } The Offline Conversions API offers the option to manually upload your events from a .csv file. In this case, add Data Processing Options, Data Processing Country, and Data Processing State as columns inside your file. More information about this can be found in the upload user interface.- See Processing Options for the Offline Conversion Automated Uploader.
Learn more about Data Processing Options.