How to use PPC and DM together in one multi-step strategy.

PPC Multi and DM Multi are templates to create up to 15 separate plans all with different variables including pricing, timing, and costs. Multis calculate individual plan P&Ls and also one combined P&L. But DM Multi and PPC Multi advertising inputs are different. So, when you want to plan a campaign using both PPC and DM together, how do you do that?

Why joining multiple tactics is needed.

The simplest sale is called a one-shot. You make an offer. The prospect buys it or does not. Ecommerce has many one-shot offers, like Mother’s Day Chocolate for example. Buyers search and either buy from a site or they do not. But gift chocolate sellers use follow-up chain offers if they can capture the name and contact information, like email. Then they can later make additional offers by email to try and close the sale. Special offers like discounts or added value are common.

Ecommerce marketers also sell “considered purchase” products that buyers shop for over time and multiple visits taking time to decide. Products like swimming pools, outdoor fencing, furniture, jewelry, are examples. These products require complex selling strategies like special added offers and information to make the sale.

Almost every ecommerce marketer has multi-step programs to close sales.

Combing PPC and DM together

Marketing Profit Plans makes it easy to create a plan that combines PPC and DM strategies into one plan. To accomplish this, the DM Multi template is used for both the PPC inquiry starts and the following DM tactics of direct mail, email, and phone.

To do this, you put the first plan PPC data in the first DM plan position. After Plan 1, the remaining plans 2 through 15 can be direct, email, phone call or any combination.

DM Multi does not have PPC inputs for Cost per Click Maximum and Time Period Spend Maximum, the key drivers of PPC initial response. So, a work around is used to calculate the inputs and arrive at the correct P&L results.

Step 1 The Setup Calculations

Assume the following are the PPC input numbers for the first step.

            Cost per Click Maximum = $2.50

            Time Period Spend Maximum = $25.0M

            Use hand division to calculate the number of responses: 10,000.

Step 2 Place the data in DM Multi position 1

First, place the Time Period Spend Maximum amount ($25M.) in the Advertising Fixed Exp $ M’s field.

Next, find the combination of Advertising Circulation M’s multiplied by Response Rate % that will yield the 10,000 responses. It’s always two variables. In this example, we use 100,000 circulation, and then divide 100,000 circulation by 10,000 responses. This is a response rate of 10%.

Place 100,000 in Advertising Circulation and 10% in the Response Rate% field.

Plan 1 now calculates the correct number of responses and the Advertising expense as Fixed Adverting expense with no Sales.

Step 3 Enter the 10,000 responses as circulation in Plan 2. (Entered as 10.0)

The balance of the DM plan inputs and calculations are normal for Product/Service Data, Transaction Cost, Timing Data, Advertising Data, etc.

Note. If Plan 1 has the option to make a sale and the ability to create Inquiry responses, change the Plan 1 setup as follows.

1. Complete all the Product/Service Data, Transaction Cost, Timing Data so a sales can be calculated.
2. Input the percentage that will make a purchase in the field Convert To Sale Rate%.
3. Instead of 10,000 responses carrying over to Circulation in Plan 2, reduce that by the number of Convert To Sale respondents that will start Plan 2.

Now Plan 1 will show the correct Sales, Expenses and Income.

Seven Step Plan Example

An example of a step plan follow. The product is a $2,000 swimming pool kit for DIY home installation. Each step has strong Call to Action copy and offers.

Step    Description

• Pay Per Click Lead Generation
• Inquiry Pack Mailed Out
• Email with Happy Customer Stories
• Email Benefits how life improves with this pool.
• Direct Mail special offer – Time Dated CTA
• Email repeats offer and stresses offer close datye
• Direct Mail Last Chance

Quick Start Guide

Overview

Marketing Profit Plans (MPP) is a versatile toolkit of Excel templates to build marketing plans focused on bottom line profits.

• Plans are created for two types of marketing:
  • Web Pay Per Click known as PPC
  • Direct Marketing: Direct mail, space advertising or special event programs.
• Plans can be setup three ways for both DM and PPC:
  • A single advertisement
  • A series or combination of up to 15 single plans
  • A plan of up to 199 segments with product and operational parameters all the same but each segment having unique parameters like DM response rates or Pay Per Click budgets.

To Install MPP

MPP runs on top of Microsoft Excel. Before starting MPP, be sure the Excel Options are set to allow macros to run. You do this at File /Options /Trust Center / Trust Center Settings/ Macro Settings/

Click the bottom choice: “Enable VBA macros (not recommended; potentially dangerous code can run)”

Before Starting

Create a new folder to contain MPP.xlsm

MPP plans can be printed or can be saved as new files or PDF’s. We recommend you place the MPP program in a new folder where you can add other new folders below it to store plans.

Create a few folders under the MPP folder to contain workbooks and PDF’s that MPP creates. A hierarchy of folder names based on how you manage campaigns or ads. Folder examples are Google PPC, Customer DM, Outside List DM, Publications, Dates, Tests, etc.

Start Up

Place MPP in the folder for it. Double click on MPP. It will open to a beginning tab. The second tab is DM Plan. Click that and you are off and running,

Once MPP is opened, if you open or create another Excel Workbook, it will not have all the controls on the ribbon and there will be no headings.

To use another Workbook side by side with MPP, open the other Workbook first.

Using MPP

The workbook sheet tabs and plan types MPP creates are:

DM Plan – Direct Marketing – a double page with plan and analytics based on response rates.

DM Plan Multi – Similar to DM Plan without analytics and up to 15 separate plans totaled. Note Multi is a large sheet and to help navigation and fitting screens has a toggle button to turn zoom on and off.

DM Group – Similar to DM Plan but with 3 to 199 rows for segment source codes. Note, the example sheet in MPP and the data in the sheet are for example only. To create a DM Group sheet, use the worksheet controls to choose how many rows (3 to 199) to set up a new external worksheet. The new worksheet name will end with a dash (–) so you can change the name. The process will also ask you to select the folder to hold the new worksheet. (That is why a folder organization plan will be helpful.)

PPC Plan – Web based Pay Per Click – single page.

PPC Plan Multi – Similar to DM Multi Plan but focused on PPC parameters.

PPC Group – The same style as DM Group but focused on PPC parameters.

On every plan, the first field is for a plan number. Because MMP is such a good what-if tool, you might use a prefix to the number like WF (for what-if) and FP (for final plan) as a prefix to a number. After you make a few plans and get used to the software you might want to make a hierarchy to plans organized.

Important: None of the plans are saved or stored internally when you close MPP. You can save Single and Multi plans as  PDF’s or print the sheets. Group sheets are set up as new Excel workbooks, so they are savable. But every time you start MPP, the templates are in a blank state.

Starting a new plan

On every plan, the first field to fill in is a plan number. Because MPP is such a good what-if tool, you might use a prefix to the number like P (for real, final plan) and WF 9 for a what-if. After you make a few plans and get used to the software you might want to come back to this topic to keep your work organized.

If a cost field is not appropriate for a plan, the field can be left blank or a zero can be inputted.

The basic structure of the plans is the input variables are all above the horizontal dotted line midway down the sheet. Everything below that double line are calculations that result in an Income Statement and other information.

The open input fields are white. All other fields are protected and light grey. The entire workbook is protected so the formulas and features like printing or creating a PDF cannot become corrupted. The Excel ribbon features are also disabled in MPP.

You close MPP by clicking the upper right corner X. There is no “File Save As.”

The Glossary

The Glossary is a sheet in the workbook and explains all the input field numbers and calculations. A separate PDF copy is included with the software zip file. This is for reference as you fill in the fields.

A word about accuracy.

The MPP Income Statement accuracy is completely dependent on the input numbers. You can use ball park estimates when you first start planning, but please start researching the actual cost and expense numbers to input in the data fields. We recommend starting that research as soon as possible. But before investing actual advertising money based on the MPP Income Statement calculations, be aware of the accuracy of the inputs.

To get a feel for how MPP works and how easy it is to create a plan, for your first plan we suggest you use ball park numbers.

First Plan I – Input Overview

The best template to start your first plan is with either the DM Single or PPC Single templates, depending which type of advertising channel you will be using. DM covers direct response campaigns, space advertising, and specialized promotions like events. (We will cover those separately.) PPC covers Pay Per Click internet advertising.

This post explains the different sections.

Reference Data

The top left section for Plan Number and other descriptive information is for storing and retrieving plans.

MPP plans can be printed or can be saved as new files or PDF’s. We recommend you place the MPP program in a new folder where you can add other new folders below it to store plans.

Create a few folders under the MPP folder to contain workbooks and PDF’s that MPP creates. A hierarchy of folder names based on how you manage campaigns or ads. Folder examples are Google PPC, Customer DM, Outside List DM, Publications, Dates, Tests, etc.

Product / Service Data

This section contains input relevant to the product, packaging and operational costs of fulfilling sales orders. See the detailed explanations in the Glossary. These costs are not included in General & Administrative Expense for several reasons. If these operations are outsourced, the vendors charges are based on the specifics of the individual products. If all fulfillment is done internally, tracking the fulfillment costs on each unit sold allows the pricing decision to be based on actual costs.

Transaction Costs

This section contains Credit Card Discount % – Credit Card Use Rate % – Sellers Fees % charged by web commerce platforms used for advertising, like Amazon, Etsy, eBay, and shopify – and G & A Expense %. G & A is your company General and Administrative, which is things like rent, utilities, insurance, and some manpower. The manpower (and rent – overhead) costs of packaging the product, warehouse packing and shipping, distribution, order entry & customer service are not included in G&A because they are calculated separately in the Product/Service Data section.

Sellers Fees % that are related to product warehousing, packing, shipping and returns should be included in Product/Service Data sections. The Sellers Fees % is the commission amount the seller’s distribution channel charges for using their platform.

Timing Data

This is the number of days to calculate the outflow and inflow dates in the Cash Flow Data section. The timing starts from Promotion Date input.

Direct mail Promotion Date should be the estimated date the mail will be received in the mail box.  This time span is longer for advertising class mail than first class mail. When entering the Response Flow In Days use judgement and keep a record of the basis the establish the input. Direct mail campaign responses usually come in over a span of many days, not all at one time. Therefore, most direct mail planners use what is known as the “Half-life” of campaign response as the midpoint for number of days when responses are 50% in as the Promotion Date. Space advertising is the date the magazine or newspaper is delivered or on the newsstand.

PPC internet Promotion Date starts when the campaign is turned on. Convert Process Days is when actual sales start. Keep in mind the Cash Flow Data dates are estimates or indications. All dates are calculated forward, meaning each flow period starts after the Promotion Date. In some cases, inventory must be paid for before the Promotion date. Some advertising purchases can be paid after an ad run. Postage for direct mail campaigns is paid when the mailing enters the postal facility.

Qualify and Order Ship Rate.

This shows you will create 200 responses.

Promotion date is explained above. The other inputs are explained in the Glossary.

Forecast Data

The strength of Marketing Profit Plan is that every plan has a forecast result based on specific parameters. Direct Marketing plans are intended to get positive results. That’s why the creative almost always has “Calls to Action.” The DM Plan has the key cost variables in the Advertising Data section because these variable can be manipulated to control or improve the profitability of results. If you have no response history upon which to estimate Forecast Data, start with industry averages and what if back and forth to change parameters like costs and other factors to arrive at a profitable forecast.

Special promotions like event participations use the DM Plan template to achieve a forecast the following way. If you cannot forecast any result coming from the promotion expense, enter in the dollar cost in the Advertising Fixed Expense input. This will calculate a loss in the Income Statement for the same amount. But let’s forecast the result of the event the following way. Input 2.0 in Advertising Circulation,  $2.9 in Advertising Fixed Cost, 10.0% in Response Rate, and 100%) at a loss of $2,900.   This is a good time to use this promotion event to introduce the DM Multi template.

PPC forecast responses are determined in the Advertising Data section. The two key drivers are Cost Per Click Maximum (Bid) and Time Period Spend Maximum. The Responses Received are the calculation of Spend Maximum divided by Cost Per Click Maximum.

Group Templates

Both DM and PPC Templates for Multi and Group use the same basic inputs, except Groups do not use Timing Data and calculate cash flows.

After the basic campaign strategy is calculated using a Single template, Group is to breakdown the campaign into individual key codes. For example, a DM campaign might consist of 10 different outside mailing lists – or 10 different segments of the house customer list. Group allows each to vary the response rate, mailing cost, circulations, and other parameters to show the detailed view. A PPC campaign uses the same format to allow for different keywords with different Cost Per Click Maximums and Time Period Spend Maximums.

Multi Templates

Multi templates are all-purpose templates. Multis create from 1 up to 15 single plans on one worksheet with each plan in one column. Multi calculates each plan’s results separately and combines them in a total. The calculations for each plan are the same as Single. All plans are combined in the Income Statement and cash flow table.

Multi is for planning comparisons and multi part strategies:
• multi-step sales funnels
• Inquiry to sale conversion programs
• Time periods like month, quarter or year
• Comparing results of different channel types like email, PPC, direct mail, customer vs outside lists

Multi can also display Customer Lifetime Value scenarios and plan retention strategies.