Assignment: A Database System to Support a Self-Service Email Marketing System

Learning Outcomes of this Assessment

The learning outcomes covered by this assignment are:

• Provide a broad overview of the general field of 'database systems' and to develop specialised knowledge in areas that demonstrate the interaction and synergy between ongoing research and practical deployment of this field of study.

Key Skills to be Assessed

This assignment assesses your skills in:

• Designing and implementing a database system

• Conveying your knowledge of database systems

• Use of T-SQL

Task

Imagine you are employed as a database developer consultant for a digital marketing company (the client). The client is currently in the process of developing a self-service email marketing system called insightMail with their in-house software development team. insightMail is a simple and low-cost web base self-service software solution for those who are new to email marketing.

Introduction to insightMail

With insightMail you will be able to create, test and send email campaigns within minutes with little or no training. Whether it's a newsletter, a promotional campaign or a transactional message, you can do it all with insightMail solutions.

Setting Up an insightMail Account

Start with insightMail free account by creating a username and a secure password. insightMail does not let you start with a paid account right away as it wants to make sure the account is correctly set up and not used for spam. The system takes the spam concerns seriously, so you must activate your account by clicking on the link in confirmation email. insightMail also requires a valid company name, address, phone number and Website URL, if you do not have those details, you can still setup an account by calling insightMail sales team.

Multi-user account

insightMail provides multi-user account facility for premium customers. A multiple account enables multiple users to login to the same account in order to do the job according to their permission level. This means that it is possible to maintain a single account for your entire marketing team. The account owner can add as many users as he/she wants and limit what permissions each of them has. You can choose role

for each user: account manager, marketing executive, marketing assistant, etc... or create a customized permission.

Email Marketing Packages

insightMail has a very attractive pricing strategy. Many small businesses will pick up insightMail because it provides professional email marketing services for free. The free plan lets you send 300 emails per month to less than 100 subscribers. This works if you have a fairly small subscriber list. If you email your subscribers more frequently, then you will outgrow this plan. The monthly paid plans let you send unlimited emails and the pricing varies by the number of subscribers. the lowest subscription plan begins at £20 per month for up to 500 subscribers. Pricing then rises to £30 per month for up to 1,000 subscribers and £40 per month for up to 2000 subscribers. You can enter the number of estimated subscribers on the handy calculator available on the website to get an exact price.

Creating a Subscriber List

Upload email contacts by using a Microsoft Excel or CSV file, or by manually adding them one at a time. If you have a very large subscriber list, insightMail technical support team can help uploading those contacts to the system through a special backend tool and this service will be free for the lists contain more than 20,000 contacts. All data that is uploaded is vetted and duplicates are automatically removed. Once your data is uploaded insightMail give you a detailed report of any problems with the data. There is no limit to the amount of data you can upload.

Setting up a Campaign

When you first log in, you will be prompted to create your first campaign. The insightMail self-service email builder includes over a dozen of predesigned templates to choose; you can customize them with your own images, links, and texts. You can drag and drop elements such as images, spacers, texts, videos, and social buttons, and customize the colour and style of each. Also can easily delete an unwanted element by clicking on it, which reveals a trash icon. You can adjust font style, size, and colour too. Also can preview your email on both desktop and mobile, so you can get a sense of how it looks like on different platforms and devices.

Once you have finished building, you can then preview the campaign. Finally, you need to select your email list(subscriber list) to which you would like to send your email. Before you send the campaign out, you can send a test email to make sure there are no errors. Specify when you want to send your mailing. You have the following three options: immediately (Now), in the future (Later), or when you want to send it (Scheduled Mailing).

The Scheduled Recurring Mailings feature allows you to send a mailing more than once. This is useful if you have a static, but highly personalized, content template that you want to send to recipients on a regular basis. From the Scheduling Options dialog box you can set up your recurring mailing by specifying the following options:

title, start date, time of day, recurrence pattern (daily, weekly, monthly, or yearly), and end date.

When you hit Send button, you will see a pop-up message with an overview of the campaign, with the number of contacts, subject, From name, and From email, so you can do one last check. When your campaign is delivered to all the recipients, insightMail will send you a confirmation email.

Tracking a Campaign

E-mail tracking is a method for monitoring e-mail delivery to intended recipients. insightMail has a special email tracking mechanism, so you can easily see subscriber activity. To track emails, you must enable email tracking before the email is sent. Enabling email tracking will not allow you to track email messages already sent.

Reporting

Detailed campaign reports allow you to measure the success of your campaigns using standard metrics such as bounces, opens and clicks. See in detail which email client used to read your email, social sharing and more. A hotspot of your email shows the areas that recipients found most interesting and, using geo mapping, you can see the location of recipients when they opened your email. You can use this to create targeted geographical segments without any address information.

In-depth analytics data is available for you as soon as you send out your email campaign. The campaign status report page shows the below information about your campaign .

• Recipients - You can view the total number of recipients for your email campaigns

• Opened - You can view the number of contacts who have opened the campaign

• Unopened - You can view the number of contacts who are yet to open the campaign

• Clicked - You can view the number of contacts who clicked the links in the campaign

• Unsubscribed - You can view the number of contacts who have unsubscribed from your mailing list

• Marked spam - You can view the number of contacts who have marked your campaign as spam

• Forwards - You can view the number of contacts who have forwarded your campaign

• Bounced - Bounce is an e-mail that is returned to the sender because it was rejected by the receiving mail server due to various reasons. A bounced messaged can be in the form of a hard bounce, which represents a message that is undeliverable (such as a message to a nonexistent address), a soft bounce, which represents a message that is temporarily undeliverable (such as a message to a recipient whose mailbox is full), or an unparsable bounce, which represents a bounced message that cannot be categorized as either a soft or hard bounce

The client require your knowledge and expertise to design, implement and test a database system. You will provide a report detailing your suggested schema and advice. You will also provide your SQL statements with appropriate comments and a full backup of your database schema and example content.

In addition to the database schema the client also requires working T-SQL statements, with comments to achieve the following:

• Create all the tables using SQL.

• Inserting, editing, activating and deactivating main user accounts.

• Inserting, editing and removing sub users.

• Inserting, editing and removing subscribers (email contacts). They would also like to be able to have a search facility in the subscribers section .

• Inserting, editing and removing email template data.

• Inserting, editing and removing email campaign data. They would also like to be able to have a searchable history of previous campaign data.

• Show standard information about the campaign including recipients, opens, unopened, clicks, unsubscribed, forwards, bounce and marked as spam on dashboard.

You should demonstrate your knowledge of concurrency techniques in the statements to ensure consistency of the database when multiple users are simultaneously updating.

Extra features to be implemented

To get more than a "Satisfactory" mark, a number of extra features should be implemented. Features include, but are not limited to:

• Use of views

• Use of stored procedures

• Use of system functions and user defined functions

• Use of SSIS package to bring bulk email list into the system for premium customers.

• Use of SSRS Reporting tools to display user and campaign statistics for the client.

Report

A 4000-5000 word report that documents your database design.

Additional consultancy advice to the client will award marks above the "Satisfactory" grade. This could include but is not limited to:

• Advice on data integrity and concurrency

• Advice on database security

• Advice on database backup and restore

• Advice on introducing business intelligence techniques or data science to this product.