How to do email Marketing Effectively?

Email marketing is the most effective and direct method to connect with your leads, nurture them, and convert them into customers. The below image is for reference:

Image courtesy of Data Mentors

Email marketing can be done by anyone, but we will discuss the keys to do it effectively. Followings are some features that one should keep in mind while doing email marketing.

1. List Building: A successful email marketing campaign starts with an email list full of qualified leads that are interested in your offerings. According to the CAN-SPAM Act, we cannot buy an email list; we have to build it. So, it is necessary to convert website visitors into subscribers. For this, subscription form can be used on the home page, blog page, or anywhere else.
2. Strong Content: Your message has equal importance in marketing. So, make the content- images and text- inviting and attractive. Every reader should feel that it is designed for him/her. Say what is needed and provide links for more information. Make sure that the email builder is not just easy to use but helpful in creating mails with HTML tags and template based emails.
3. Timing and Automation: Successful email marketing requires you to send the right message to the right people and at the right time. So, keeping the information up-to-date to make sure that the subscribers receive email according to their interests, stage in the customer journey, and other factors like gender should be seamless with Email Marketing tool.
4. Analytics: When we run a campaign, it is important to evaluate the performance to make improvements. A few parameters are:

• Subscriber Data- New subscribers and unsubscribes
• Performance of your email- This can be tracked using Google analytics or if your email marketing tool has its own reporting system. I do this with Pabbly Email Marketing.
•  A/B testing : To determine the optimal form, placement, etc.
• Campaign Performance- How many clicks, how many opens, etc.

So, these are the main factors that one should look in the email service provider tool. I used Pabbly Email Marketing and satisfied with the features that are mentioned above. Other beautiful features that I would like to share are:

Pricing: The tool is the cheapest available tool in the market. See the price comparison of Pabbly with Aweber and other email service providers.

SMTP: Similar to MailChimp, Pabbly Email Marketing has its own in-built SMTP that can be used to send emails. In addition, it has a feature called “SMTP Routing” that enables the user to send promotional, relational and transactional emails using a range of SMTPs (50+). And, it doesn’t charge anything for connecting external SMTPs. Below is a few SMTPs that are supported by it

Sending Limit: There is no limit on the number of email sending.

Mobile Responsive: Making your email mobile-friendly is crucial as a large number of emails are opened on mobile devices. With Pabbly, it is easy to use email template that gets easily fixed on mobile screens.

Easy campaign creation and automation comprising templates and workflows.

Integration: Quite easy to integrate with software like Elementor, Calendly, Convertful, Leadpages, OptinMonster, Ninja Forms, etc.

Few Other Features:

Other features that can help to get better email delivery, engagement and conversions.

I would suggest using Pabbly Email Marketing. The reason being it comes for free for one month in which you can try it to the fullest as there is no restriction on features. And, if you upgrade it to other plans depending on your subscribers’ list it is quite affordable.

A Blog that can be read to know about the available cheap email marketing services in the market.

Excel CountIFs Function

Excel CountIfs is a built-in statistical function. It can also be used as a worksheet function in addition to other formulas in a cell of a worksheet.

Syntax

=countIfs( criteria_range_1, criteria_1, criteria_range_2, criteria_2, …..criteria_range_n, criteria_n)

Arguments

• Criteria_range_n. Range of cells that you want to apply against criteria_n
• Criteria_n. The criteria used to determine which cells to count.

Where n=1,2,3,4,5….

Purpose

To count a number of cells in a range that meets a single or multiple criteria

Return Type

Numeric

Remarks

• Each additional range must have the same number of rows and columns as in the first range
• Both contiguous and non contiguous ranges are allowed
• If the criteria is a reference to an empty cell, the countifs function treats it as zero

Excel CountBlank Function

Excel Count Blank is a built-in statistical function. It can be used as a worksheet function means as a part of formula in a cell of a worksheet. The function counts only the number of blank cells; cells containing text, numbers, errors, etc. are not counted.

Syntax

Countblank(range)

Arguments

Range: REQUIRED. Range of cells to count empty cells

Purpose

To count the empty cells in a range

Return Type

Numeric

Remarks

• Formulas that return empty text (“ ”)are considered blank and will be counted
•  Cells containing zero are considered non blank and thus will not be counted

Excel Count Function

A built-in excel function, Count Function counts the number of cells containing numbers as well as the number of arguments that contain numbers. It is categorized as a statistical function that can be used as a worksheet function.

Syntax

=count (value1, value2,…)

Arguments

• Value1: An Argument/ cell reference/ range
• Value2. Optional. An Argument/ cell reference/ range

Purpose

To count the number of cells containing numbers

Return Type

Numeric

Remarks

• It can handle upto 255 additional values
• For counting logical and text values, use CountA Function and CountIf to count on certain criteria
• Logical values are not counted, but with other statistical functions like ISNUM, MBER, ISTEXT, ISLOGICAL, ISFORMULA, etc., it is possible.
• Empty cells, errors and text are not counted
• Date and time are stored as numbers in Excel, so excel Count Function counts both

Excel CountIF Function

Excel CountIf Function is a built-in excel statistical function. It is a worksheet function that can be used as a part of formula in a cell of a worksheet.

Syntax

=countif(range, criteria)

Arguments

• Range. Required. The range of cells that you want to count based on certain criteria.
• Criteria. Required. Criteria to determine which cells to count

Purpose

To count the number of cells based on a single criterion

Return Type

Numeric

Remarks

• CountIf function supports logical operators - >,<,<>,= and wildcards *,? For partial matching.
• Criteria can also be based on a value from another cell using concatenation.
• CountIf is not case-sensitive.
• In criteria, do not forget to use double quotes for text, while numbers can be passed without quotes.
• When logical operator is used with a number, enclosed them with quotes.
• Value from another cell can also be used in criteria using concatenation ex”</>/=”&cell
• Wildcards * and ? can be used in criteria

Excel CountA Funtion

CountA - Count Anything except blanks

CountA is a built-in excel function that is categorized as a statistical function. It can also be used as a worksheet function. CountA function is used to count cells containing numbers, texts, logical values, error values, and empty text returned in formulas (“ ”).

Syntax

=CountA (value1, value2,….)

Arguments

• Value1: An Argument / Cell Reference/ Range
• Value2: Optional. An Argument/ Cell Reference/ Range

*Argument is a parameter that is not a cell or a range of cells.

Purpose

To count the number of non-blank cells.

Return Type

Numeric.

Remarks

• It does not count cells that are completely empty.
• If you forgot to mention the arguments in the range, with CountA Function, the arguments can be passed separately in the formula. Ex. =COUNTA(c1:c5, a,b). It will return the sum of non-empty cells between c1 & c5 and 2.
• The major difference between Count and CountA functions is, Count Function counts cells containing numbers but CountA counts all cells that are non-empty. If need arises to count empty cells, use CountBlank function that will count only empty cells.

Concept of Invisible Characters:

Keep in mind that CountA will count the empty cell that actually contains invisible characters (accidentally type a space in a cell) or an empty string returned by (“”) formula. To check this

• Select the range
• Press Ctrl+G 
• Click on SPECIAL one the left bottom
• Select BLANK and ok to check such cells

Excel EDATE Function

EDATE is a built-in excel Date/ Time function and it is used to add or subtract a specified number of months to a date and fetches the result as a serial date. It is a worksheet function means it can be used in combination with other functions. Excel Edate function is good to use to calculate the maturity dates or due dates that fall on the same day of the month as the date of issue.

Purpose

To shift date n months in future or past

Syntax

EDATE(start_date, months)

Arguments

• Start_date. REQUIRED. The start day from which future or past number of months will be counted.

Dates should be entered by using the DATE function or as results of other formulas. The function will return errors if dates are entered as text

• Months. REQUIRED.A number of months to add or subtract

Return Type

New date in future or past as excel serial number

Return Value

Date in serial number form

Remark.

• The Excel EDATE function returns a serial number representing the date. To force Excel to display it as a date, you should apply the Date format to the cells with your EDATE formulas
• If a decimal value is provided for months, the EDATE function will only add the integer portion to start_date
• If start_date is not a valid date, the EDATE function will return the #VALUE! error

Excel ROUNDDOWN Function

A built-in Math/ Trig function, ROUNDOWN function can be used as a worksheet function.

Purpose

To round a number down, toward zero

Syntax

=ROUNDDOWN(number, num_digits)

Arguments

• Number.REQUIRED.Any real number that you want to round down.
• Num_digits.REQUIRED. The number of digits to which you want to round down the number.

If Num_Digits is:

• Zero (0). The number is rounded down to the nearest integer.
• Greater Than Zero (>0). The number is rounded down to the specified number of places in the right of decimal point.
• Less than zero (<0). The number is rounded down to the specified number of places in left of the decimal point.

Return Type

Number

Remarks

ROUNDDOWN behaves like ROUND. The only different is ROUNDDOWN always rounds a number down.

Excel Round Function

Round function is a built in excel function categorized as Math/ Trig Function. It can be used as a worksheet function in excel and VBA.

Purpose

To return a number rounded to the specified number of places

Syntax

=ROUND(Number, Num_Digits)

Arguments

• Number.REQUIRED. The number that you want to round
• Num_Digits. REQUIRED. The number of digits to which you want to round the number argument

If Num_Digits is:

• Zero (0). The number is rounded to the nearest integer.
• Greater Than Zero (>0). The number is rounded to the specified number of places in the right of decimal point.
• Less than zero (<0). The number is rounded to the specified number of places in left of the decimal point.

Return Type

Number

Remarks

• To always round up (away from zero), use the ROUNDUP function
• To always round down (toward zero), use the ROUNDDOWN function
• If a number is already rounded to the given number of places, no rounding occurs

Excel INT Function

Excel INT Function

An excel function that returns the integer part of the decimal number by rounding down to the integer. It can be used as a worksheet function or a VBA function. The function is categorized as math and trig function in excel.

For example: INT(10.8) will return 10 and INT(-10.8) will return -11.

So, we can say, negative numbers become more negative as INT function rounds down.

Syntax

=INT(Number)

Argument

Number. REQUIRED. Any positive or negative decimal digit from which integer is required

Purpose

Get the integer part of a decimal number by rounding down

INT function returns the integer part of the decimal digit by rounding down. However, if you only want the exact the integer part of both negative and positive numbers, it is good to use TRUNC Function.

Remarks

Excel provides a number of functions for rounding:

• To round normally, use the ROUND function.
• To round to the nearest multiple, use the MROUND function.
• To round down to the nearest specified place, use the ROUNDDOWN function.
• To round down to the nearest specified multiple, use the FLOOR function.
• To round up to the nearest specified place, use the ROUNDUP function.
• To round up to the nearest specified multiple, use the CEILING function.
• To round down and return an integer only, use the INT function.
• To truncate decimal places, use the TRUNC function.

Source:

https://exceljet.net/excel-functions/excel-int-function

Excel TRUNCFunction

Excel TRUNC Function

Introduced in Excel 2007, TRUNC function is a built-in excel function that can be used as a worksheet function. It is categorized as Math and Trigonometry Function that does not round off the number. It is advised to use TRUNC function to obtain the integer value of the given decimal number.

For Example:=TRUNC(10.2) or =TRUNC(10.2,0) will return 10, =TRUNC(10.2,1) will return 10.2

Syntax

=TRUNC(Number, Num_Digits)

Arguments

• Number. REQUIRED. Any positive or negative number that is required to truncate.
• Num_Digit. Optional. Could be a positive, negative digit, or zero. If this parameter is omitted, trunk function will assume 0.

-A positive value that is greater than zero specifies the number of digits to the right of the decimal point.

-equal to zero (or left blank) specifies to truncate to the nearest integer.

- A negative value less than zero specifies digits to the left of the decimal point.

Purpose

To truncate a number to a given precision

Return Value

A truncated number

Remarks

• The function does not rounds off the number, just returns the output integer/ and non-integer depending upon the input.
• The difference between INT and TRUNC is that INT returns on integer value by rounding off to the nearest integer but Trunc does not rounds off the number and return the integer and non-integer output depending upon the input.
• TRUNC and INT are similar functions because they both return the integer part of a number. The difference is that TRUNC merely truncates a number, but INT rounds off a number. However, for positive numbers and when trunk uses 0 as the num_digit, both functions return the same value. With negative numbers, results will be different.

Source

• https://corporatefinanceinstitute.com/resources/excel/functions/trunc-function/
• https://support.office.com/en-us/article/trunc-function-8b86a64c-3127-43db-ba14-aa5ceb292721
• https://exceljet.net/excel-functions/excel-trunc-function

Excel Match Function

Excel Match function is categorized under excel lookup and reference functions. It is used to fetch the value in an array and returns the position of the value within the array. For example, we need to match the value of 5 in a range comprising(1, 5, 6, 8, 9). The function will return 2, as 5 is the 2^nd item in the given range.

It is a built-in excel function and can be used as a worksheet function.

Syntax

=match (lookup_value, lookup_array, [match_type])

Arguments

• Lookup_value. REQUIRED. Value that we want to look up. Numeric/ text/ logical value/ cell reference. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
• Lookup_array. REQUIRED. Data array to search in.
• Match_type. OPTIONAL. Can be set to 1, 0, and -1. By default, match function takes 1 as match type.

Match_type:

• 1 It returns the largest value that is less than or equals to the lookup value. It requires sorting of lookup array in ascending order, from smallest to largest; a to z.
• 0 Finds the first value in the array that exactly matches with the lookup value. No sorting is required.
• -1 returns the smallest value that is greater than or equals to the lookup value. Lookup array should be sorted in descending order; from largest to smallest; z to a.

Purpose

To find the relative position of lookup_value in a row, column, table or an array. For partial matches, wildcard characters are used.

Return Type

Integer

Return Value

Position of the lookup_value.

Remarks

• If match function does not find a match, it returns #N/A error.
• Match Function is not case-sensitive.
• If match_type = 0 and lookup_value is a text value, you can use wildcards in the lookup_value.

1. * is used to match any sequence of characters
2. ? is used to match any single character

• Use wildcards when match_type is set to 0.
• If lookup_value has several occurrences in the lookup_array, it returns the position of the first value.
• In financial analysis, match function is used in combination with other functions to lookup and return the values in the column. Commonly it is used with the INDEX function, generally called INDEX MATCH combination. Match returns a position, to fetch the value, learn using Index and Match together.

Sources:

• https://www.techonthenet.com/excel/formulas/match.php
• https://exceljet.net/excel-functions/excel-match-function
• https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

Excel Choose Function

It is a built-in lookup/ reference function that is used to get the value using a giving position or index. Also, it can be used as a worksheet function and a VBA function in excel.

Syntax

=CHOOSE(index_num, value1, [value2], …)

Arguments

• Index_num. REQUIRED. The position number in the list of values to return. A number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254. Limit of 29 in EXCEL 2003.
• Value, Value2… Value1 is required, subsequent values are optional. A value can be a number, a cell reference, a defined name, a formula/function, or a text value. 1 to 254 value arguments from which Choose selects the a value or an action to perform based on index_num.

Purpose

To get a value from a list based on position

Return Value

The value at the given position

Return Type

string, numeric, date, etc.

Remarks

• If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
• If index_num is a fraction, it is truncated to the lowest integer before being used.
• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
• If index_num is an array, every value is evaluated when CHOOSE is evaluated.
• The value arguments to CHOOSE can be range references as well as single values.

So, to get the value from the list using position, we use CHOOSE function. However, to get the position of a value in a row/ column/ table, use MATCH Function. Choose can handle up to 254 values but INDEX, or INDEX with MATCH are better approaches for large value sets.

Sources

• https://support.office.com/en-us/article/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc
• https://exceljet.net/excel-functions/excel-choose-function

Excel EOMONTH Function

A built-in excel time/date function, EOMONTH is used to calculate the last date of the month, n months in future of past. The result is a serial date value. It is worksheet function and can be entered as a part of other excel formulas. The function is good to fetch the due dates, expiration dates and other dates that need to land on the last date of the month.

Purpose

To find the last date of the month. Last date of the same month, previous months or future months

Syntax

=EOMONTH(start_date, month)

Arguments

• Start_date.REQUIRED. Starting date for reference
• Month. REQURED.Number of months to be added in the start date. Value could be 0, +ve or –ve.

Return Type

Date 

Return Value

Serial date value

Remarks

• If a decimal value is provided for months, the EOMONTH function will only add the integer portion to start_date.
• The EOMONTH function returns a serial date value, the way excel stores dates internally
• #NUM! error occurs when

1. Start_date is not a valid excel date
2. The supplied start_date plus the value of “months” argument is not a valid excel date.

• #VALUE error occurs when any of the supplied arguments is non-numeric.

Excel WORKDAY.INTL Function

Excel WORKDAY.INTL Function

A built-in excel function that is categorized as date/ time function, WORKDAY.INTL can be used as a worksheet function. It is introduced in MS Excel 2010 version. The function takes a date as input and returns a workday in future or past depending on the offset value you provide. The only different between Workday and Workday.INTL is that the Workday.INTL allows us to customize the weekend days. It is used to calculate completion dates, ship dates, etc. that take into account non-working days.

Purpose

To get N workday in past or future.

Return Value

Next or previous working date based on inputs.

Return Type

The WORKDAY.INTL function returns a serial date value representing the number of days since January 1, 1900

Syntax

Workday.INTL(start_date, days, weekend, holidays)

Arguments

• Start_date. date from which working days needs to count
• Workday function excludes the start date while fetching the workday.
• Days. +ve for future working date and –ve for past working date, a zero value yields the start_date. If it is supplied as a decimal number, it is truncated to the integer.
• Weekend. Optional. It can be specified which day or days are weekend days. It can either be a number or a string as

Number	Weekend days
1 or omitted	Saturday, Sunday
2	Sunday, Monday
3	Monday, Tuesday
4	Tuesday, Wednesday
5	Wednesday, Thursday
6	Thursday, Friday
7	Friday, Saturday
11	Sunday only
12	Monday only
13	Tuesday only
14	Wednesday only
15	Thursday only
16	Friday only
17	Saturday only

What is Weekend String Here?

It is a series of seven “0s” or seven “1s” that represent the seven days of the week, beginning with Monday. 1 represents a non-working day and 0 is used to represent a workday.

Example:0000011 – Saturday & Sunday are weekend days

1000001- Monday and Sunday are weekend days

Holiday. It is optional. A list of date or an array constant of the serial numbers representing the dates that should be considered as non-working days. Excel only cares about holiday dates, not holiday names.

Points to Keep in Mind:

·         If start-Date /a date in holiday list is out of the current database value, WORKDAY.INTL returns #NUM! error.

·         If start_date plus day-offset yields an invalid date, WORKDAY.INTL returns #NUM! error.

·         If weekend string is of invalid length or contains invalid characters, , WORKDAY.INTL returns #VALUE! error. (string length is 7 containing 0 and 1)

·         Make the list of holiday absolute reference so that it won’t change when you paste the formula to other cells. Example. If your holidays are stored in the range G3:G6, it is best to enter the range as $G$3:$G$6.

Types of Errors That Workday and Workday.INTL Returns:

·         #NUM! error occurs if either:

A combination of the start_date and days arguments results in an invalid date, or

Weekend argument in the WORKDAY.INTL function is invalid.

·         #VALUE! error occurs if either:

start_date or any value in holidays is not a valid date, or

Days argument is non-numeric.