• Best CPA Networks
  • Best Cost-Per-Sale Affiliate Networks
  • Best Pay-Per-Call Networks
  • mThink Digital
  • Thought Leadership
    • White Papers
  • About
    • Content Marketing
    • Content Strategy
      • Web Content
      • Social Media Strategy
      • Webinars & Video
      • Thought Leadership
    • Performance Marketing
    • Portfolio
      • Revenue Performance
      • Accenture
      • Microsoft
      • Java Detour
      • Our Process
    • Contact Us

mThink

Blue Book Logo

BlueBook Logo

The Trusted Name in Performance Marketing

ROS Leaderboard

  • Home
  • Blue Book
    • About Blue Book
    • Blue Ribbon Panel
    • Interviews
    • Research Methodology
    • Back Issues
    • Advertising
      • Website Creative Specifications
      • Newsletter Creative Specifications
  • Best CPA Networks
  • Best Cost-Per-Sale Affiliate Networks
  • Best Pay-Per-Call Networks
  • Best European CPA Networks
  • Best CPA Networks for Affiliates
  • Best CPA Networks for Advertisers

Setting the Data Table

April 1, 2004 by artkade

The last issue of Revenue gave an overview of databases and how they can be used. Let’s delve a little deeper into how you, as manager of an affiliate program, can use a database to improve your service and provide customized information for all of your affiliates.

When creating a database, the first step is to understand what information you want to record, and the important relationships among the data. Similar information is grouped into a table in the database. An affiliate will have a variety of contact information such as an email address, a postal address and perhaps even a separate payment address. All of this information could be placed into a single table. Let’s call this table affiliate_contact.

You may want to record certain accounting information about an affiliate, such as the date a sale was made, what item was sold, how much the affiliate earned and the total dollar amount generate by the sale. This information could be placed into the affiliate_contact table we already created, but we will place it instead into a new table called affiliate_sales. I’ll explain why later.

In database design, you want to create tables that group similar information and then link these tables together based on their relationships. This is where the term “relational” comes from when describing a database. Relational databases, such as Oracle, DB2, SQL Server and MySQL, provide very rich tools for extracting information based on these relationships.

Planning and mapping the information you have into tables is just the first, but perhaps most important, step in developing your database. You could change a database’s design once it is running, but if you have a lot of data, or a lot of code using the database, changes can be difficult and time consuming. So, it is worthwhile to take some time and care in planning your tables. In 10 years, my company, Epage, has gone through a few database redesigns, but there are some tables that have not changed structure since the first design.

Creating basic relationships between tables can be quite easy. Usually, it’s accomplished by having a common item such as a table column in related tables. If your affiliates all have a unique identifier, such as their contact email address, this can be used to link tables together. The affiliate_contact table and the affiliate_sales tables would both have an “email” column with the affiliate’s email address. If you want to retrieve information from both tables, like the affiliate’s first name and last sales date, you could query both tables using the same lookup key (the affiliate’s email address).

There are other ways to generate relationships among tables. We like to generate a unique number or string of characters to identify one of our users. This unique identifier is only used internally to form table relationships, and may never be seen by the user. This way, if a user needs to change their email address, it would only need to be changed in one table. In our example above, both tables, and perhaps many more, would need to be updated.

There are many reasons to break your information into multiple tables. Tables with many columns (email, address, phone, etc.) can be very difficult to manage. Database servers are designed to efficiently deliver results to your queries. But, they can get bogged down when you have a lot of columns that you might want to select from. For example, when you insert a new row, such as adding a new affiliate to the affiliate_contact table, the database must re-optimize the way it retrieves data from that table. The more columns that are in a table, the more work the database must perform.

Efficiency is another reason for multiple tables. Some tables may have only one row (entry) for each affiliate, such as the affiliate_contact table. Other tables, like the affiliate_sales table, may have many rows, one for each sale. If these two tables were combined, there would be a lot of wasted space for repeating the contact information for each sale.

Consider what unique information you want to record for each affiliate when planning your tables. You may want to know certain business information. For example, you may want to know whether the affiliate prefers to be paid by check or electronically. Or you may want to review the payment terms for certain affiliates, such as the percent of the purchase price they earn. A database can record these unique terms for each affiliate, allowing you to personalize how your program works. You might want to offer better terms to a desired affiliate or during a promotional period. When a sale is made, the percentage earned by the affiliate would be read from the database, and the result would be stored into the affiliate_sales table.

If you send multiple mailings to your affiliates, some might not want to receive all of the messages. You could store which type of messages they don’t want in the affiliate_contact table. Or, you might want to contact your top-performing affiliates. Each month you could query the affiliate_sales table to find those top performers.

Once you have the information recorded, how you use it is limited only by your imagination. You could send a special message on the anniversary of an affiliate’s signup. You could determine which affiliates had a big drop-off in month-to-month sales – perhaps they are having a problem you can solve. You could determine characteristics of your best affiliates – perhaps it’s their location – and target more like them.

Another good piece of information to record is how new affiliates found out about your program. If you use a tracking code in your advertising, you can record the code in the affiliate_contact table. Then you could determine not only how many affiliates were generated with a specific code, but how much revenue that advertising generated. One last idea to consider: If your users can refer new affiliates to you, then you could record who referred each affiliate. Offer an incentive to these users, such as a percentage of sales generated by the affiliate, and you have the potential for a huge force generating new affiliates for you, with almost no work on your part.

EDWARD ARENBERG, vice president and CTO of Epage, created one of the first fully dynamic Web sites. He manages and develops for EP.com, Epage.com and AdConnect.com.

Related posts:

  1. Less Is More Ask any guru in the affiliate universe what ValueClick's acquisition...
  2. Been There Done That: Q & A with Shawn Collins It's very difficult to find anyone in affiliate marketing better...
  3. Bad Guerrillas The biggest mistake of all is launching an affiliate marketing...
  4. Databasics 101 Most small business operators have dabbled with databases, but relatively...

Filed Under: Revenue Tagged With: 02 - Spring 2004, Data Management, Features, mtadmin, Software, Website Infrastructure

Search

ROS Col 2 Top

ROS Col 2 Mid

ROS Col 2 Low

Subscribe to our newsletter!

* indicates required

ROS Col 2 – 4 Misc

ROS Col 2 – 5 Misc

ROS Col 2 – 6 Misc

Recent Posts

  • 2023: Challenges, Opportunities & Predictions
  • Top 4 Best Practices For Consumer Finance Campaigns
  • Four Moments That Changed Performance Marketing Forever
  • New additions, improvements, and added perks from your favorite CPA network
  • Your Top 5 Affiliate Marketing Questions Answered
  • 5 strategies to create better content
  • FCC Says Ringless Voicemails Require Prior Consent
  • Top 5 Free Methods to Increase Affiliate Traffic
  •  3 Top Lead Generation Best Practices To Scale Your Business
  • Automation, Conversions, Profitability, Oh My – Breaking Affiliate Campaign Roadblocks
  • 5 tips for a better brand image
  • How Mac of All Trades Found Multi-Channel Success
  • Winning In A Changing World: An Interview with Taras Kiseliuk, CEO of ClickDealer
  • Survey: What Consumers Want from Financial Services Providers
  • The influencer marketing era: what does the future of web marketing hold?

About mThink

mThink is a specialist digital marketing company based in San Francisco. We focus on media buying, Facebook marketing, direct response, social and mobile. In addition mThink produces the annual Blue Book Rankings of major performance marketing networks. Read More »

Baseboard

Copyright ©2023 · mThink. All rights reserved.
3053 Fillmore Street, Suite 325 | (415) 787-0250
Disclaimer | Privacy Policy