Building An Automated PPC Spend Tracker In Google Sheets
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
You’ve asked for it, and here it is: The Total Guide To Product Listing Ads. We have a feeling this product will continue to expand and get cool, new features since it’s doing so well for our eCommerce accounts. But, there is so little information out there about how to set these up and make them work for you right now. We took it upon ourselves to put everything we know about PLAs into one, six part guide. We’ll be posting twice a week until all six parts are posted, so keep your eyes peeled!
Part 1: Setting Up Your Data Feed
Part 2: How to Set Up Your PLA Campaign
Part 5: Targeting Strategies
Data feed definition and formats
Google defines a data feed as “a file made up of a list of products which use groupings of attributes that define each one of a merchant’s products in a unique way”. By describing your products using these attributes, users can search and find your products more easily. One of the first important decisions is to choose the format of the data feed. The format needs to be selected according to your needs as a merchant as well as the ability to create the file. Google supports data feeds in two general formats, text (.txt) and XML (.xml). Smaller merchants are advised to use a .txt format, as it requires the least technical knowledge and can easily be created by using a spreadsheet editor, such as Excel or Google Doc.
The screenshot below shows the spreadsheet version of a product feed. Attribute names need to be included as headers in the first row, while the rows thereafter describe your items according to the attributes. When done adding all the products and corresponding attributes to the feed, the file needs to be saved as a tab-delimited file.
The advantage of this file format is that it is simple to create and edit as long as you have a spreadsheet program. It is also easier to view multiple items. However, one major disadvantage is that is is easier to make formatting mistakes.
For more technical advanced merchants, the data feed can also be submitted as an extended version of RSS 2.0 or Atom 1.0. These files can be created and edited by using any text editor. When using RSS 2.0, there are three pre-defined elements at the item level, namely title, link, and description that are required for product data feeds. In order to include additional required elements as well as more detailed information about each product, Google extended RSS 2.00 by adding the Google Merchant Center feed namespace. Below is an example of a basic RSS 2.0 document for a single item:
<title>The name of your data feed</title> <link>http://www.example.com</link> <description>A description of your content</description> <item> <title>Red wool sweater</title> <link> http://www.example.com/item1-info-page.html</link> <description>Comfortable and soft, this sweater will keep you warm on those cold winter nights.</description>
<g:image_link>http://www.example.com/image1.jpg</g:image_link> <g:price>25</g:price> <g:condition>new</g:condition>
Besides declaring the Google Merchant Center namespace (see yellow highlighting), a prefix within every attribute tag must be added to distinguish attributes from elements defined in RSS 2.0. Without the prefix, all the attributes and values would be ignored.
Compared to RSS 2.0, Atom 1.0 based data feeds require five pre-defined elements at the entry level – title, id, link, summary, and updated. To be able to include additional required elements as well as more detailed information on products, Google also extended Atom 1.0 and added the Google Merchant Center namespace. Here is an example of a basic Atom 1.0 document containing a single item:
As with RSS 2.0, the namespace declaration (see highlighting) as well as including a prefix within every attribute tag, are required or any attributes and values will be ignored.
Google also recommends running through a checklist to ensure that the file is properly formatted:
Product Feed Specification
The screenshot below shows an overview of required and recommended attributes that need to be included in the data feed before it can be uploaded to Google Merchant Center. As the screenshot shows, some attributes are required, while others are recommended. Items like clothing/apparel or media might also need additional attributes. For a detailed list of attribute requirements, please visit: http://support.google.com/merchants/bin/answer.py?hl=en&answer=188494#US.
By not including required attributes, the specific items will not show up in Google Shopping results. In contrast to that, not providing any of the recommended attributes will only lead to items showing up less frequently.
It is also important to understand that item refers to a single variation of a product as a product might come in multiple variations – different color, material, pattern, or size.
– id: Each item has to have a unique identifier and the id cannot be re-used between feeds.
– Title: This describes your item so include any characteristics such as color or brand to differentiate your item form others. The title can be up to 70 characters long.
– Description: Use up to 10,000 characters to include further information about your item. Be aware that this is displayed in the shopping search results.
– Product type: This field categorizes the category of the product submitted. You can either use Google’s category taxonomy or your own classification.
– Link: This is the URL that links directly to your product.
– Image link: This attribute will display an image of your item. The accepted image formats are GIF, JPG (or JPEG), PNG, BMP, and TIF.
– Condition: The condition attribute lets the buyer know if your item is ‘new’, ‘refurbished’ or ‘used’.
– Availability: This attribute declares whether your item is ‘in stock’, ‘available for order’, ‘out of stock’ or ‘preorder’.
– Price: The price attribute needs to contain the most prominent price on the landing page and must include a currency according to ISO 4217 Standard.
– Brand: This needs to be the brand name of the item, not the store name unless the product is manufactured by that store.
– Gtin: This is the global trade item number for the specific item.
– Mpn: The manufacturer part number of the item identifies the product to its manufacturer and specifies each item.
– Gender/age group/color/size: required for apparel products
– Tax: This attribute is only accepted in the US and can be used to override any merchant-level settings in your Google Merchant Center account.
– Shipping: Provide specific shipping estimates for your items or override the shipping settings you submitted in your Google Merchant Center account.
– Shipping weight: This attribute is required if you set up a shipping rule in the Merchant Center that is based on weight.
– Item group id: This is required for all variant apparel products in the US.
– Color: This defines the dominant color of an item but multiple colors can be added by using a ‘/’ in order of prominence.
– Material: This attribute describes the material or fabric that an item is made out of.
– Pattern: Pattern or graphic print featured on your item.
– Size: Specific size of your product.
– Google product category: This attribute is recommended as long as it is not required to further describe your item by category.
– Additional image link: Any images that show your product/item from a different angle, images of packaging or variants of the product.
– Sale price: This attribute shows the advertised sale price of the item and is recommended for items that are on sale.
– Sale price effective date: This is used in conjunction with the sale price attribute and indicates the date range that applies to the sale price.
– Adwords grouping: This attribute groups products in an arbitray way. It can only hold one value and can be used with CPC or CPA bidding.
– Adwords labels: This attribute only works with CPC bidding but can hold multiple values.
– Adwords redirect: This attribute allows advertisers to specify a separate URL that can be used to track traffic coming from Google Shopping.
– Gender/age/size: While these attributes are required for all apparel items, it might also make sense to include it for any other items to further describe your products.
– Color: Use this attribute to describe your items by color.
– Online only: This attributes clarifies whether an item is available for purchase only online.
– Excluded destination: By default your items appear in Google Commerce Search or Google Shopping.
– Expiration date: This attribute shows the date that an item listing will expire.
Uploading your data feed
Once you successfully created your data feed, it is time to upload it to your Merchant Center account. All you have to do is the following:
3. As a next step, you choose the country in which you want your products to display (if you want to target different countries, you have to submit a feed for each individual country as some countries have different attribute requirements).
4. Choose googlebase as the format and enter a name for your .txt file.
5. Save changes and go to “manual upload” and upload your feed.
Once you have uploaded your feed, Google will process and test your feed. Depending on the size of your file, it may take a while to process the feed. You can check back on the status of your data feed upload by clicking on the dashboard in the Google Merchant Center. A linked status will appear next to your file. Just click on this link, which will give you details about the feed validation. It will show any errors that will need to be fixed before you can upload your feed again. Be aware that it might take up to 24 hours for the products to start appearing in Google Shopping.
To review the quality of your data feed, go to the Products link in the left navigation bar within Merchant Center. This page displays the status of each product and will display up to 10,000 products randomly. If you have more than 10,000 products and want to see the status of specific products, you have to search for their status.
Feed Schedules and Upload Formats
Once you have uploaded the feed, it is scheduled to expire after 30 days. In order for your products to continue showing in Google Shopping results, you need to re-upload your feed. You can also either upload your feed on a daily basis or as frequently as your products or prices change.
While manual feed uploads are easy and usually don’t take a lot of time (processing the feed however may take longer depending on the size of your feed), you can also create a schedule to have your feed uploaded automatically without having to worry about it. Once you hit “create schedule” in your data feed overview, the following window will appear:
Just fill it out with the desired frequency, timing and location of your feed. (Important: The URL of your file location must begin with http://, https://, ftp://, or sftp://). Hit schedule to complete the set-up process.
This upload method requires the size of your file to be less than 1 gigabyte, and the user-agent “googlebot” cannot be blocked from the directory containing your feed. Moreover, the feed URL has to point directly to the data feed file in one of Google’s supported file formats.
Data feeds can also be submitted via Google Spreadsheets. For this option, either use the existing template that Google provides or submit a Google spreadsheet that you created yourself. When clicking on new data feed, just provide the target country, format and data feed file name and check the checkbox next to I want to use Google spreadsheet to store, edit and upload your feed. The next step is to either upload your data by generating a template feed or using an existing Google spreadsheet that contains all of your product information.
The spreadsheet must be viewable to everyone with a link. The advantage of this option is that it is simple to create and to update as content is uploaded directly from the information given in the spreadsheet. However, this format cannot be used to upload a large volume of data, ie files larger than 20MB.
FTP is an advanced method to submit data feeds to Google Merchant Center. This format has to be used for files larger than 20MB. The file also needs to be smaller than one gigabyte and has to be split in multiple smaller files if the original file is larger than this.
In your Merchant Center account, go to the Settings link, then click FTP
Enter a username and password for the FTP account and click Save Changes. It may take several hours before the account creation takes effect. Also, once the FTP account is created, the chosen username cannot be changed.
There are three methods for submitting your file via FTP.
– Option 1: FTP your data feed using a web browser:
– Option 2: FTP your data feed using an FTP program:
– Option 3: FTP your data feed using a DOS prompt
Google Cloud Storage
Developers can also use Google Cloud Storage to submit products to Merchant Center. However, some technical know-how and experience in programming applications is a must.
Google Cloud Storage is similar to an FTP server – you submit and access files with a file transfer client. Once you set up your Merchant Center account with Google Cloud Storage, Google automatically assigns a private bucket for your shopping data. The benefits of using this service are enhanced security, up to 10 gigabytes in size, and flexible infrastructure that can be used for other applications as well.
Submitting your product feed via Google Cloud Storage:
There you have it! All you need to know about setting up your data feed. Next time, in Part 2 of The Total Guide to Product Listing Ads, we’ll discuss: How to Set up Your PLA Campaign
Browse By Category
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
Learn 3 easy ways to quickly test out variants on low-traffic landing pages to improve performance.
Get a client services view on 10 traits of successful PPC account managers and see which are on your list!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Hero Conf Los Angeles is one short month away. And now we get to share the good stuff. Join us at Hero Conf Los Angeles, April 18-20, for 10 of the most fun and unique networking opportunities in the industry.
In light of the March Madness season it was thought that we could have some fun and review some of the previous PPC Hero articles on the best ways to utilize Excel in order to more efficiently and effectively manage PPC accounts.