Facebook has an incredibly valuable feature that allows you to match sales to your campaigns, ad sets, and ads. This feature will show you many sales are attributed to your Facebook Ads campaign, the total revenue generated by your ads, the return on ad spend, and a host of other insights. Here’s how you do it.
- Create a Facebook Ads account
- Create an Offline Event Set
- Assign an ad account to your offline event set
- Assign people and permissions
- Format your sales data
- Upload your formatted offline sales data
- View performance
Create a Facebook Ads account
If you don’t already have a Facebook Ads account, create one before proceeding.
Create an Offline Event Set
IMPORTANT: The offline event set should be created as soon as possible; preferably before you run any campaigns. Facebook will only match sales to ad activities that occur after the date that the offline event set is created. Suppose you started running your campaign on January 1 but did not create an offline event set. Then, on February 1 you want to upload all of the sales from January to see how many of those sales can be attributed to your Facebook campaign. Bad news. Facebook will never match any of your sales to any of your campaign activities from January. So, create your offline event set as possible.
Log in to the Facebook Business Manager.
Navigate to “Business Settings”.
Click “Data Sources” and then “Offline Event Sets”.
Click “Add”.
Name your offline event set. I recommend naming it the same as your Facebook Ads account along with the word “Sales” or “Purchases” or something whatever best reflects the activity.
Then click “Create”.
Assign an ad account to your offline event set
Turn off automatic assignment
I recommend turning off the feature for automatically assigning the offline event set to new ad accounts.
This feature would attach your “sales” events to every new ad account you create or gain access to. More than likely, though, you’re going to want to keep sales from one ad account separate from sales from all your other ad accounts. So, I recommend turning this off.
Select the desired ad account
Click “Next”. Then click “OK”.
Assign people and permissions
Click “Done”.
Format your sales data
To improve matches and simplify the process of uploading sales data, I recommend formatting your data before uploading.
For this walkthrough, we’re going to be using sample data from this Google Spreadsheet. Make a copy of the spreadsheet.
Notice there are two sheets: “sales_data” and “offline_events”. The “offline_events” sheet is where we’ll be adding our formulas.
Our goal is to prepare a CSV with the following fields:
Match keys
“Match keys” are the pieces of information that Facebook is able to use to match your sales data back to Facebook users. For example, Facebook might be able to identify a Facebook user if the email in your sales data matches an email address in a user’s Facebook profile.
Required parameters
Facebook requires the following parameters in order to be able to identify when the offline event occurred, what type of offline event we’re dealing with (e.g. “purchase”), and, in the case of purchases, the value and currency of the purchase.
Optional parameters
These optional parameters help Facebook to identify unique purchases and the items purchased. After the matching process has been completed, you’ll be able to view specifically which items sold were attributed to your Facebook Ads using the “Product ID” breakdown.
First name
In our sample data, we don’t have one field for first name and another for last name. Instead, we just have a single field called “CUSTOMERNAME”.
To get our “first name” field, we’re going to extract the first word from the customer name field.
On the “offline_events” sheet, in cell A1, enter “fn”. (“fn” is the name Facebook uses to identify the field for “first name”.)
Then, in cell A2, enter the following formula.
=IFERROR(FILTER(REGEXEXTRACT(sales_data!M2:M,"^[^\s]+"),sales_data!A2:A>0))Let’s break this formula down, starting from the inner-most function.
First, we use the REGEXEXTRACT function. The first part of the function is the range where we want to extract stuff. The second part of the function is a regular expression that basically says “starting from the beginning of the cell, extract one or more characters that aren’t whitespace characters”.
Working our way outward, the next function is FILTER. The FILTER function applies our REGEXEXTRACT function to all rows that have an “ORDERNUMBER” (sales_data!A2:A>0).
Finally, we’ve wrapped the whole thing in an IFERROR to get rid of any #N/A or other error indicators, should any arise. In the case of any errors, you’ll just get an empty cell.
Last name
Next we need to get the last name of each customer. We’re going to achieve this by extracting the last word from the customer name field.
On the “offline_events” sheet, in cell B1, enter “ln”. (“ln” is the name Facebook uses to identify the field for “last name”.)
Then, in cell A2, enter the following formula.
=IFERROR(FILTER(REGEXEXTRACT(sales_data!M2:M,"\s([^\s]+)$"),sales_data!A2:A>0))This formula is virtually identify to our previous formula that we used to extract the first name. The key difference here is the regular expression. This time, our regular expression looks for a whitespace following by one or more characters that don’t include whitespace, up to the end of the customer name.
City and state
On the “offline_events” sheet, in cell C1, enter “ct”. (“ct” is the name Facebook uses to identify the field for “city”.) In cell D1, enter “st”. (“st” is… well, you get the idea.)
No changes need to be made to the city or state fields. So, we can just take them as they are without having to modify them.
In cell C2, enter the following formula.
=IFERROR(FILTER(sales_data!R2:S,sales_data!A2:A))This formula grabs both the city column (column “R”) and the state column (column “S”) in one shot.
Zip code
In cell E1, enter “zip”.
Zip codes in the United States can be a little deceptive. In principle, 5-digit zip codes are numeric (i.e. they’re comprised only of numbers). However, they must be five digits long. The sneaky part is that there are zip codes that have leading zeros. If you give Google Sheets a zip code like 00501 as a number, Google Sheets will interpret it as a number, and will drop those leading zeros, because, well, numeric values don’t have leading zeros.
To overcome this quirk of the zip code system, we’re going to use the TEXT function. The TEXT function converts a number into text and allows you to specify a pattern in which to display the number. In our case, we’re going to use the pattern that forces zeros: “00000”.
In cell E2, enter:
=IFERROR(FILTER(TEXT(sales_data!T2:T,"00000"),sales_data!A2:A))Our email address field contains only email address, and only one email address per cell. So, we can just grab the whole column without having to worry about validating or modifying anything in the cells.
In cell F1, enter “email”. Then, in cell F2, enter the following formula.
=IFERROR(FILTER(sales_data!N2:N,sales_data!A2:A>0))Phone
With phone numbers there are a few things to take into account.
First, Facebook needs to be able to identify the country to which the phone number belongs. So, you’ll need to add the country code to each phone number if it isn’t already included. The country code for the United States is “+1”. We’re going to concatenate our country code with our phone numbers using the ampersand (&) method.
The second thing we need to consider is the possibility of non-numeric characters (e.g. hyphens, parentheses, spaces, periods, etc.). To avoid potential issues, we want our phone number to be comprised of nothing but our country code and the numbers that make up each phone number (i.e. without any of the extraneous characters that are really just there for human readability). To achieve this, we’re going to use REGEXREPLACE to replace all non-numeric characters with nothing, which has the effect of removing all non-numeric characters.
Now, the trick with the regular expression functions like REGEXREPLACE is that they require that the input be text (i.e. a “string”). If you try to give it a value that’s of a numeric type, the function will fail. So, we’ll make sure all our phone numbers are “strings” rather than numbers using the TO_TEXT function. If a phone number happens to have nothing but numeric digits, TO_TEXT will convert it into a string of numeric digits. If the phone number is already a string (e.g. if it has non-numeric characters), TO_TEXT just gives you back what you put in, i.e. the original phone number.
In cell G1, enter “phone”. Then, in cell G2, enter the following formula.
=IFERROR(FILTER("+1"®EXREPLACE(TO_TEXT(sales_data!O2:O),"[^0-9]+",""),sales_data!A2:A>0))Event time
As the name suggests, “event_time” represents the date and time that the “event”, i.e. the purchase, took place. Although Facebook supports a few different date/time formats, I recommend the standard ISO 8601 complete datetime format with hours, minutes, and seconds. This format starts with the 4-digit year, following by a hyphen, followed by the 2-digit month, followed by a hyphen, followed by the 2-digit day, followed by the uppercase letter “T”, followed by the hour (in 24-hour format), minutes, and seconds (each two digits, separated by colons). I recommend also including the time zone difference (e.g. +/-hh:mm).
The following example shows the date of November 22, 2020, the time of 5:09PM (and 31 seconds), in the time zone of
2020-11-22T17:09:31-06:00I’ve included a sheet named “datetime” in your spreadsheet which will show the current timestamp in your area using the format above. Select your time zone to show the correct UTC offset. If you’re currently on daylight saving time, check the daylight saving time box, as well.
Now that we got all that stuff out of the way, let’s get to the formula for converting our date/time to the right format.
In column H1, enter “event_time”. Then, in cell H2, enter the following formula.
=IFERROR(FILTER(TEXT(sales_data!F2:F,"yyyy-mm-ddTHH:mm:ss-06:00"),sales_data!A2:A>0))As you can see, we’re once again using the TEXT function to format our datetime. You’ll want to change the last part of the format to suit your time zone. You can see the list of time zones and their UTC offsets on the “timezones” sheet in your spreadsheet.
Event name
Event name represents the specific type of “event” or activity that we’re uploading to Facebook. There are a whole slew of events you can upload (AddToCart, CompleteRegistration, Subscribe, etc.). In our case, we’re dealing exclusively with sales. So, the event_name will always be “Purchase”.
In column I1, enter “event_name”. Then, in cell I2, enter the following formula.
=IFERROR(FILTER(IF(sales_data!A2:A>0,"Purchase",""),sales_data!A2:A>0))Value
In the case of purchases, the value field represents the value of the sale. We’ll use the total sale amount of each transaction.
The value field must contain a decimal number greater than or equal to zero. It can’t include letters, special characters, currency symbols or commas.
So, just to be on the safe side, we’ll do the following:
- Convert the value to a string using the TEXT function so that we can…
- Use REGEXREPLACE to remove any non-numeric characters
- Use the FIXED function to specify the number of decimal places and suppress the thousands separator
In cell J1, enter “value”. Then, in cell J2, enter the following formula.
=IFERROR(FILTER(FIXED(REGEXREPLACE(TO_TEXT(sales_data!E2:E),"[^0-9]*",""),2,TRUE),sales_data!A2:A>0))Currency
In our example, all of the transactions occurred in US dollars. So, we’ll just repeat “USD” in each row.
In cell K1, enter “currency”. Then, in cell K2, enter the following formula.
=IFERROR(FILTER(IF(sales_data!A2:A>0,"USD",""),sales_data!A2:A>0))Order ID
Order IDs are unique identifiers for each of your transactions. Using order IDs helps deduplicate offline events more accurately, improve attribution and surface order based statistics.
In our example sales data, our “order ID” is represented by the “ORDERNUMBER” column.
In cell L1, enter “order_id”. Then, in cell L2, enter the following formula.
=IFERROR(FILTER(sales_data!A2:A,sales_data!A2:A>0))Item number
The last of our fields for this example is the “item_number” field. This field represents the ID of the item purchased. In our example data, the field we’re looking for is the “PRODUCTCODE” field.
In cell M1, enter “item_number”. Then, in cell M2, enter the following formula.
=IFERROR(FILTER(sales_data!L2:L,sales_data!A2:A>0))Upload your formatted offline sales data
Whew! Glad we got all that formatting stuff out of the way. What a pain the butt. But trust me, having your data properly formatted will not only save you from massive headaches in this upload phase but also improve your match rate.
Download your formatted data as a CSV
In Google Sheets, go to “File” > “Download” > “Comma-separated values”.
Open Facebook Events Manager
In Facebook Business Manager, navigate to the Events Manager.
Select your offline event set
You can distinguish offline event sets from other event sets such as pixels by the little storefront icon.
Click “Upload Events”
Upload your CSV of formatted event data
You can either select your CSV using the button or just drag and drop your CSV into the gray upload area.
Map your data
Click the “Next: Map Data” button.
Because we’ve used Facebook’s header names for our fields (e.g. “fn”, “ln”, “ct”, etc.), most of the mapping should happen automatically. There’s just one little adjustment we have to make. We need to confirm that our “phone” field already includes the country code.
Click the header of the phone number field. Select “Yes”. Then click “OK”.
Review your uploaded results
Click the “Next: Review” button.
Facebook will give you feedback on your uploaded sales data. For example, they’ll let you know if there are any recommendations or warnings. (Warnings aren’t typically going to be problematic. But if you have errors, you’ll want to address those.) They’ll show you the percentage of rows from the sample that will be accepted. And they’ll show you the estimated match rate. The estimated match rate tells you approximately how many of the customers from your sales data can be matched to Facebook users. Note, this is not telling you how many sales can be attributed to your ads; just the estimated percentage of matching Facebook users.
Start the upload
Click the “Start Upload” button.
And you’re done.
View performance
After uploading your sales data, wait a few minutes. It can take up to 15 minutes, if I recall. Then make your way over to Ads Manager.
Click the “Columns” menu and select “Offline Conversions”.
| Amount Spent | Offline Purchases | Offline Purchases Conversion Value | Cost per Purchase |
|---|---|---|---|
| $1,000.00 | 20 | $100,000.00 | $50.00 |