A Marketing Agency Data Model

What goes into marketing a business? And what data model could a marketing agency use to run their organization? Read this article to find out!

Marketing, especially digital marketing, is everywhere. Digital is probably going to be the primary form of marketing in the future: businesses can collect an enormous amount of this data, easily transform it, and use it to reach potential customers. Plus, digital marketing is effective.

In today’s article, we’ll talk about a data model that can track marketing projects. We’ll also discuss some basic marketing facts. Ready? Let’s get started.

Six Questions About Marketing

If you’re not familiar with marketing, these questions will explain a little bit about it:

1. What is marketing?

   Marketing is the set of activities a company uses to advertise and sell products or services. It includes reaching existing customers with additional offers and attracting new customers. Marketing agencies utilize various strategies, from simple ads to offering discounts, to entice people to buy their goods.

2. Who’s who in marketing?

   Briefly, here’s what you need to know about marketing terms in the context of this article :

   • Marketers are people who run marketing campaigns and interpret the data.
   • Clients are businesses or other organizations that hire marketers to attract prospective buyers.
   • Customers are the prospective buyers that businesses or organizations are trying to attract. Note: We will not store customer data in this model.
   • Campaigns are the efforts made, usually over a predefined time period, to advertise or sell something.

3. Describe pre-Internet marketing.

   People have always marketed (i.e. tried to sell) their products and services. We do it almost daily – just think of all the times you’ve said “I could do this for you” or “I know someone who could do it”. This is similar to word-of-mouth marketing and it’s worked very well for centuries. People knew craftsmen who could “do that”, word spread, and the craftsmen’s businesses thrived. Think about the architects or explorers who served many different leaders just because of their skill. Someone probably said, “Oh, he’s the guy to find the Spice Islands for you”.

   Another very old (but still effective) form of marketing is signage. Craftsmen used tables to let people know where their shop was; barbers used and still use striped poles, and so on.

4. How did mass marketing get started?

   Mass marketing – where marketers broadcast their message to as many people as possible – really got started when media became cheap, easy to produce, and widely available. In my opinion, this began with newspapers. People loved to read them, and businesses quickly realized that this was a good place to advertise themselves. Nowadays, we can add radio, movies, TV, and the Internet to the list. Of course, modern marketers can target people much more efficiently than they did back then.

5. How has the Internet changed marketing?

   At first, the Internet was a relatively small place that was used to exchange “clever” stuff. As posting information on the Internet became cheaper, easier, and more accessible, the amount of “garbage” posted online started increasing dramatically. But we won’t talk about the “garbage” here; we’ll talk about the “clever” stuff. ☺

   The Internet offers so many ways to target customers that all other media seems like a medieval craftsman’s “table sign” in comparison. Marketers can segment customers based on their habits (purchasing and browsing data etc.), track their behavior on a website (it’s even easier if the customer is a registered user), and use customers’ activity on social media sites to find the most receptive prospects. Yes, it does sound a lot like something George Orwell would write about, but in some ways it’s not bad at all. If somebody is going to send you an ad, it’s better to get something you’re interested in, right?

6. How is the success of a marketing campaign determined?

   Before the Internet, marketing was relatively simple. Now, there are so many ways to reach customers (social media platforms, online ads, TV commercials, print ads, emails, etc.) that things get very complicated. So marketers have many ways to track a campaign and determine how successful it was. Among these, increases in revenue and profit are probably the most important metrics.

   When running multiple campaigns, each one should be tracked separately. Ideally, results would be measured at the customer level (Did the customer sign up for an e-newsletter? Did they purchase a product? When? At what price, and with what offer?)

   We won’t go into the details of campaign tracking here. But you should have it in mind if you design a similar model.

What Should our Data Model Contain?

Now let’s examine the most important functionalities in our model:

• What does our model cover?

  This model will primarily focus on campaign data. For each campaign, we’ll store any related clients and partners, what we have offered, what employees was in charge, the overall progress of the campaign, its related activities and output, and the campaign results. We’ll also store contracts between our agency, its partners, and its clients.

• What won’t we cover in this model?

  We won’t go into granular detail on the campaigns, such as tracking contacts with individual potential customers – i.e. calls, emails, meetings, and outcomes. This could be added to the model and would require a few more tables.

The Data Model

The data model consists of five subject areas:

• Agencies & clients
• Employees
• Campaigns
• Campaign statuses
• Partners

We’ll describe each subject area in the order listed.

Section 1: Agencies and Clients

The first subject area is Agencies & clients. It contains only three tables, which all describe the relationship between agencies and clients.

The agency table stores the name of our agency or agencies. Usually, we’ll run only one agency, but in some cases we could be the owner of several. If so, we’ll need a table to keep these agencies separate. The agency_name attribute is the only value (except for the primary key, id) and it can hold only UNIQUE values.

Now we know who will do the job, but not for whom. This information will be stored in the “

” table. Here we’ll find a list of every client any of our agencies have ever worked with. For each client, we’ll store:

• client_code – A UNIQUE value that internally denotes the client. Note: The code attribute will serve as an alternate key in a few other tables as well.
• client_name – The client’s full name.
• address, email, phone, mobile and contact_person – Contact details for the client. The first two are mandatory because we probably will never work with a client who doesn’t have an address or an email. The other three are not mandatory because they may not apply (e.g. the client has a mobile phone number but not a landline number, or there is no designated contact person).

Now we need to relate agencies with clients. When an agency starts working with a client, they’ll need to sign a contract defining all their obligations towards each other. For each contract, we’ll store:

• contract_code – A UNIQUE value that internally denotes a specific contract.
• document_location – The exact location of where the contract document, in digital format, is stored.
• details – All details related to that contract, stored in free textual format.
• agency_id – The agency related to that contract.
• client_id – The client related to that contract.
• date_signed – The date when the contract was signed.
• valid_from – The date when the contract becomes valid.
• valid_to – The date when the contract expires. If this value is NULL, then the contract is valid indefinitely (or until either party ends it).

Please notice that I haven’t used the agency_id – client_id pair as an alternate key. This is because an agency could have multiple contracts with the same client at the same time. For example, a client might need separate campaigns for different items (i.e. a snack company markets pretzels and cookies at the same time). We would have a separate contract for each of these two campaigns.

Section 2: Employees

The Employees subject area contains only one table, the employee table. The agency table, in this subject area, is just a copy of the original table. It’s been pasted here to simplify the model and reduce overlapping relations.

For each employee, we’ll store:

• employee_code – A code that UNIQUELY denotes each agency employee.
• agency_id – The agency that the employee currently works in. Together with “employee_code”, it forms the UNIQUE key of this table.
• first_name & last_name – The employee’s first and last name.

Of course, we could include many more details here. We could have dates denoting when the employee started and ended his engagement with the agency. We could have a separate table to store the employee’s history with the company. Or we could implement roles to define employee responsibilities and add another table to store role history.

Section 3: Campaigns

Campaigns are the central part of this model. This subject area will store details related to the services we provide to clients. We can expect that the client will ask for an all-in-one solution for his needs. For example, the client could ask that we produce a TV commercial and organize campaigns on a few different TV stations.

In the campaign table, we’ll store the list of all campaigns our agency has done. Each campaign is a set of actions or services we perform according to the contract we’ve signed with the client. We could have multiple campaigns for the same client at the same time or consecutively. On the other hand, a campaign is strictly related to only one client. For each campaign, we’ll store:

• campaign_code – The internal code that UNIQUELY designates each campaign.
• contract_id – Refers to a record in the contract table. This record contains the client_id and agency_id, so this is how we relate the campaign to the agency and the client.
• description – Describes the campaign in text format. We can use this attribute to store all information that doesn’t require a more structured format.
• current_status – References the status_catalog dictionary. This is where we’ll store the current campaign status. We’ll store status history elsewhere, and we’ll talk about statuses in the next subject area.
• employee_id – References the employee in charge of this campaign.

During campaigns, we’ll provide products and advertising services to clients. Products will be whatever we produce for clients, e.g. a TV commercial or a print ad. Advertising services will be any activities we do to promote the client’s business, such as a social media campaign. We’ll store almost the same data for both products and advertising services, but they will have separate tables in the database. Because of their similarity, I’ll describe the product and the advertising tables together here:

• campaign_id – The ID of the related campaign.
• product_type_id / advertising_type_id – The ID of the type of product or advertising used in that campaign.
• planned_start_time and planned_end_time – When we plan to start and end this activity. We’ll define these times in advance, probably in the planning phase.
• start_time and end_time – The actual start and end times. Both of these could be NULL because they will be updated when the activity actually starts and ends. We can expect that these times will differ from planned times.
• details – All the details we want to store in free textual format.
• funds_planned – The funds we initially allocate to cover the cost of this activity.
• actual_costs – The actual cost (to us) for this activity. I guess it’s pretty obvious that actual costs are usually higher than planned. ??
• employee_id – The employee in charge of this activity.
• product_link – A link to the final product, if any. We’ll store the product’s location at the moment it is available. This attribute is specific to the product table and is not present in the advertising table.

We have already mentioned that we’ll organize activities by type. Therefore, we’ll have one dictionary that is related to products (product_type) and one to advertising activity (advertising_type). Both have exactly the same structure. Except for the primary key , they contain only the UNIQUE type_name column. We can expect “TV commercial”, “banner”, and “print ad” as product types and “newspaper ads”, “TV campaign”, “online marketing”, and “social media campaign” as advertising types.

Section 4: Campaign Statuses

To modify and successfully complete campaigns, we need a way to track them. We also need access to historical campaign data to analyze the performance of previous campaigns. The two tables in this subject area accomplish this.

A list of all possible statuses that could be assigned to any campaign is stored in the status_catalog table. Each status will have a UNIQUE status_name. The is_completed value denotes if the relevant campaign is done. The is_ok value tells us if the campaign was successful.

Each time we assign a status to a campaign, we’ll store a new record in the status_history table. Each record will keep references to the campaign and its status, the status_time when this status was assigned, and any additional details we think are needed.

Section 5: Partners

We may hire partners to produce or part of a campaign for us. Maybe we simply don’t have enough resources to do a really big job, or maybe the client wants a product or service we do not provide. In this case, we’d hire a partner to do what we cannot. We will sign contracts with all our partners.

The partner catalog lists all partners we work with. For each partner, we’ll store a UNIQUE partner_code, a partner_name, and the contract_id referencing the relevant contract.

I’ve already mentioned that we could hire partners to help us with products or advertising. So, once more, we have two tables with almost the same structure. The advertising_partner table stores the UNIQUE advertising_id – partner_id pair, which together denote which partner is in charge of which advertising action. The product_partner table’s UNIQUE product_id – partner_id pair shows which partner is in charge of developing which product. We could work with several different partners in the course of one campaign.

The remaining two tables in our model, the advertising_contract table and the product_contract table, relate partner engagements with the contracts signed between them and our agency. Both of these tables contain only a primary key and references to related tables. These foreign key pairs form the UNIQUE/alternate keys for these tables.

What Do You Think?

Today, we’ve discussed a data model that could be used to track activities in a marketing agency. This model covers some of the basics, but there is plenty of room for improvement. Tell us what you think: What would you add to this model? Or what would you remove?