How to View ShareASale Pipe-Delimited File in Excel Columns

“Pipe” symbol is a vertical bar as in | or ¦ but more frequently the former.

A “pipe-delimited file” is a file in which each line of represents a record, and separate fields are divided by the pipe. This symbol then serves as a delimiter (or an indication of “the beginning or end of a data item”).

The most common format in which delimiter-separated files are saved is CSV. It may seem a bit strange as “CSV” originally stood for “comma-separated values” but don’t let the file extension’s semantics confuse you here. It is very common that, instead of commas, colons or pipes are used as delimiters.

One of the main reasons why CSV files are widely-used online is because “plain-text makes the data easy to import into any Spreadsheet program or database regardless of what type of computer or software program you are using” [source].

“Easy to import” they say, huh?

However, the reality is that those of us who aren’t too technically savvy, may find the conversion of a CSV file into a digestible Excel format a challenging task… especially when you have to deal with a pipe-delimited original. Don’t despair! It is actually very easy to convert a pipe-delimited CSV into an Microsoft Excel file with columns. Excel will, actually, do all the “magic” for you!

For my example I will use an “Affiliate Timespan” ShareASale report.

Step 1: Generate Pipe-delimited File

If you already have the CSV file you wish to convert into Excel columns, just skip right to the next step.

If you’re doing this on ShareASale, log into your merchant interface, then go to Reports > Affiliate Timespan, and check the box next to “Create Downloadable Text File” (see the image below).

ShareASale Affiliate Timespan Report

Step 2: Open File in Excel

Launch Microsoft Excel, go to File > Open > change “All Excel Files” (to the right of the “File name”) to “All Files,” and find the pipe-delimited file you have just downloaded.

Excel Open All Files

Hit “Open” and Excel’s “Text Import Wizard” will launch.

Step 3: Help Excel to Convert It for You

Keep the 1st step of the Wizard as is (having “Delimited” checked as it is a delimited file that you are working with),  and click “Next.”

But in the 2nd step, pick “Other” and insert the pipe symbol into the blank box to the right of your choice (as shown below). As soon as you do so: Voilà! The preview will already show you the columns you are looking for.

Pipe-Delimited File to Excel Columns

Just click “Finish” and you are all done, and ready to save this file in the format you’ll be able to enjoy a bit better than the original CSV file.


The Importance of Affiliate Recruitment

When a company engages in digital marketing activities the main goal is to increase sales and revenue.  When managing an affiliate program it is no different. Growing revenue is not only good for the merchant’s bottom line, but it also helps increase the programs exposure to potential affiliate partners. The higher the EPC and AOV numbers are, the more attractive it will be and the easier it will be to recruit quality affiliates.

Whether you have an established affiliate program or just launched one, getting quality affiliates to join is paramount. Many merchants ask this question: Do I just wait for affiliates to apply to my program? You could, but that is doing a disservice to the program and hurting potential growth.  One of the main pieces to growing an affiliate program is affiliate recruitment. There are two types of affiliate recruitment strategies: passive and active.

Passive recruitment is when an affiliate discovers your affiliate program on their own. On the other hand, active recruitment is when an affiliate discovers your program because of your outreach efforts. When managing your affiliate program you do not want to rely solely on a passive recruitment strategy because you will most likely not get the results desired. If you do passive recruitment you are going to have predominately more coupon/deal affiliates than any other and relying on one type of affiliate is not going to provide the growth and incremental sales numbers you wish to see.

Diversifying your affiliate pool through affiliate recruitment will provide better opportunities for growth.  Engaging in active affiliate recruitment will aid in this facet. Having coupon/deal affiliates in the program is not a bad thing, but including other affiliate types like content, paid search, social media, and datafeed affiliates will help in the growth and sustainability of the program long term. Many of the previously mentioned affiliate types are not going to find you, so you must proactively find them and invite them to your program.

Recruiting the content, paid search, social media and datafeed affiliates is important because they all specialize in different areas of digital marketing and will target different segments of traffic for your brand. Practicing active affiliate recruitment is important for the short-term, but more importantly the long-term.

You may be asking how you find affiliates to join your program? Check back next week for my post where I will be discussing where to find quality affiliates. I will also touch on what each type of affiliate brings to the table in terms of skills and value.

Affiliate Text Links vs. Banners – The Great Debate

I am asked regularly by clients what types of links are best for their affiliate program and I give the same answer, it depends. What do I mean it depends? Every affiliate promotes a merchant differently. For example, some use text links embedded within content, while others prefer banner ads to help convert traffic from their site. I would never say go with one over the other, as both are essential to the growth of an affiliate program, but I would do an analysis of your current creative inventory and see what links and banners are being used more frequently and supply your affiliates accordingly. If you are thinking of starting an affiliate program then I would say have both text links and the most popular banner sizes available for affiliates.

Rakuten Marketing conducted a link performance report across the Rakuten Affiliate Network from October 2013 – June 2014 to see what types of links are most effective for merchants. Please note that this data is specific to Rakuten Affiliate Network and could vary depending on the network.

The report from Rakuten Marketing provided the percentage of sales by link type and the percentage of sales by banner size. They found that 51% of sales occurred using a text link and another 22% using banner links. Does this mean you should not offer any banners or even decrease the amount in your creative inventory? Absolutely not! Offering affiliates a selection of the most popular banners will do, but be prepared to create additional banners if an affiliate requests a size not currently available.

Sales by Link Type

According to Rakuten Marketing, over 56% of all sales from banner links resided from two sizes in particular. The 88×31 (28.1%) and 120×60 (28%) were by far the most popular for affiliates and also generated the most sales. Both the 88×31 and 120×60 banners are popular among coupon and deal oriented affiliates as are text links, but text links are also very popular among content affiliates.

Sales by Banner Size

When deciding how to stock your creative inventory it is wise to provide the most popular banner sizes (88×31, 120×60, 125×125, 300×250) and create text links that include your brand name and all coupons/deals and popular keywords associated with your brand. By equipping your affiliates with the most popular creatives it will save time creating banners that will never be used by affiliates.

Affiliate Coupon Landing Pages: Why They Are Important?

Creating a comprehensive coupon strategy is a great way to increase conversions and overall revenue for your affiliate program;. However, many times merchants ignore a major piece to their coupon strategy — landing pages.

You would be surprised on how many merchants do not employ this tactic (even clients of ours). It does require some additional coding and design work, but the benefits can be tremendous. Many merchants who create landing pages for their affiliate coupons have seen a spike in conversions and that equals more revenue.

Why are landing pages for each coupon so important? Besides providing higher conversions and revenue, it provides the customer all the details related to the promotion and allows them to redeem the coupon all in one place. With that being said, there are four (4) main components to an effective affiliate coupon landing page and they include:

1.       Emphasizes the specifics of the coupon/deal

If there are multiple pieces to the coupon (i.e. a percentage off and free shipping) then it must be spelled out here. Being transparent is important and explaining what the customer gets (in terms of a discount) and needs to do should be highlighted in this section.

2.       Informs the customer of any restrictions associated with the coupon/deal

This is one of the most important pieces to an effective coupon landing page. Providing all restrictions associated with the coupon (i.e. cannot be combined with other offers, not valid on certain brands, etc.) will limit the amount of customer service inquiries received and put the customers mind at ease knowing they are getting the discount on the products they are purchasing.

3.       Clearly illustrates how the customer can redeem the coupon/deal

This may seem like a common sense task, but it is necessary. Walk the customers hand through the process so that they feel comfortable. This part should tell the customer if they need a coupon code and where it should be input during the checkout process. If no code is needed then it should say that discount will be subtracted from total in cart upon checkout. Any piece of information that will be vital to a customer should be included.

4.       Contains a clear and concise call to action

Having the specifics of the coupon, restrictions, and instructions are all great and beneficial, but having a call to action should be considered just as important. Having a button to “Redeem Coupon” or “See Details” should be showcased prominently on the page so the customer can act immediately. There should be no confusion on what the customer should do once on the page.

Creating effective coupon landing pages are essential to any coupon strategy. As a merchant if you are not doing it, then you may be losing out on opportunities to enhance your affiliate program and getting quality affiliates to join too. Beat your competition to the punch and start building those landing pages.

6 Types of Coupons To Use In Your Affiliate Program

In my experience as an affiliate manager I see the value in providing coupons to affiliates. It gives your affiliates another way to convert visitors to their site in customers of yours. Do all affiliate programs need to have coupons? No, but if your products/services are set up for coupons then I highly recommend developing a well-rounded coupon strategy to introduce to all your affiliates (not just coupon affiliates).

During the set up process of clients affiliate programs (even merchants with established programs) I like to have them create a set of coupons to offer their affiliates from the outset. Affiliate coupons should not be intended only for coupon/deal oriented affiliates, but also content, media buying affiliates, social media affiliates. Each affiliate has a different way of promoting merchants products and the more tools given the better chance for success.
So the question you are now asking is; what coupons should be offered?

There are six common types of coupons for merchants to offer affiliates, but please note that not all will be applicable to every program. The six coupon types are: monthly coupons, coupons that are valid from the beginning of the year to the end (i.e. January 1 – December 31), holiday related coupons, short-term coupons, affiliate exclusive coupons, and deal of the day coupons.

1. Monthly coupons

These coupons should target different price points and should change from month to month. We recommend clients try and make the banners/links associated with the coupons dynamic so that the affiliate does not have to change creatives monthly and they can be assured it will update automatically. Having happy affiliates increases the chance of program success. Examples of monthly coupons include: $5 off $50 order and $15 off $100 orders. Each month the offer would change either the amount of the discount, the threshold, or both.

2. Coupons that are valid from the beginning of the year to the end

The main purpose of these coupons is to get the customers to spend more than the average customer would. For example, Free Shipping on orders over $50 or Save $10 on orders over $100. This gets the customer in the mindset that the more they spend the more they will save.

3. Holiday specific coupons

Customers love to save money, especially around the holidays, so create coupons that will excite the customers (an affiliates too). An item simply on sale does nothing, but offering a percentage off or dollar amount off gives the customer more motivation to buy, and it is free money to them. The holidays are right around the corner, so be prepared with coupons specific to your business and the holiday.

4. Short-term coupons

These coupons are valid for a weekend, a 2 – 3 day period during the week, or even a full week, but the purpose is to get the customer to act and spend immediately. If weekends are historically slow then offer a discount to create buzz and spur action (i.e. 20% + Free Shipping on all orders). Also, if you have a product that you want to move then a good strategy is to create a coupon that will aid in that (i.e. $100 off all widgets – this weekend only).

5. Affiliate exclusive coupons

These coupons are self-explanatory, they are meant for affiliates only. Affiliate exclusive coupons should be reserved for the affiliates that you value the most, but making sure they are aware you can do this is essential. Personally reaching out to affiliates that produce and add value with exclusive coupons can help cement a healthy relationship. Some affiliates do not want to promote coupons that the other affiliates are, so providing your top affiliates with their own coupons (with their own vanity codes) will help differentiate them from their competition.

6. Deal of the day coupons

These coupons are geared more towards retailers, but if you have a product that you want to promote, then creating a deal of the day coupon should be an option. Coupons like this should be as aggressive as possible in terms of a discount. If you are planning on implementing a deal of the day strategy be sure to create dynamic banners for affiliates. Affiliates are not going to want to update banners daily. The easier you make it for your affiliates the more they will want to promote your products/services.

A coupon strategy is another way to increase conversions for your affiliate program, but making sure they make financial sense (for the merchant) and are going to be used and accepted by affiliates is another piece to the puzzle. The six above mentioned coupon types gives you a foundation to create your strategy and solidify relationships with your current and future affiliates.

As always your comments are welcome. Please comment below or email me.