The 10 Toughest Analytics and Data Challenges… And How We Solved Them
Since 1998, when WiseGuys Marketing was founded, we have watched marketing analytics move from postal processing, to email, then SEM and SEO, and of course, social. As you would expect, our data challenges over that time have increased in complexity. Here is a quick look at 2 categories:
- Customer Data Analytics & Modeling
- Data Preparation & Manipulation
If you have challenges in any of these areas, we should talk!
Customer Data Analytics & Modeling
- Challenge: Which marketing campaigns are effective?
Description: It is one thing to understand the old adage “Half of all advertising works. I just don’t know which half.” (John Wanamaker, circa 1950s). It’s another thing to write the algorithm to solve it.
The challenge is that a typical buyer is touched by multiple campaigns, but 100% of the credit for the sale usually goes to the last touch, which undervalues the contribution made by the earlier campaigns.
Solution: Our Matchback Analysis solves the attribution problem.
Detail:
WiseGuys has developed a process for Matchback Analysis that allows you to more accurately attribute a sale to all the marketing campaigns and stimuli that touched the customer, instead of giving all the credit to the last touch.
Our Matchback Analysis employs an exhaustive algorithm that considers 5 criteria when matching a customer response back to a marketing stimulus:
- Source code matching: does an incoming order response source code match an earlier, outbound promotional code on file?
- Response time window: does a response fall within a reasonable time frame after the promotion date?
- Overlapping message windows: does an email campaign coincide with your direct mail campaign, creating two choices for attribution?
- Household matching: does a customer name on the response match anyone in the household, either by last name or email address?
- A/B Testing for “Lift”: does the aggregate response from a promotion segment (the “A” group) exceed the control group (the “B” group not receiving the promotion)?
- Challenge: Which Google ads are most effective at attracting profitable, repeat customers?
Description: Clicks and conversions measure the success of Google ads in generating an initial sale. But what about the Lifetime Value (LTV) of the customer? One of our clients asked us if we could determine which keywords and which Google pay-per-click ads delivered customers with the highest Lifetime Value. We were able to factor Google Analytics and Google AdWords results into a Lifetime Value calculation.
Solution: “Moneyball for Marketers” – using Lifetime Value to inform pay-per-click bidding.
Detail:
One of our clients had a Google AdWords budget in the tens of thousands every month. They had already optimized their ad spend, but were wondering if they could do more. With the ability to see which keywords and which ads generated customers with the greatest Lifetime Value (LTV), they would be able to justify increasing their bids in those areas and attract even more high-value customers.
We partnered with their search engine marketing agency, the RKG Group, to import the relevant Google Analytics and Google AdWords data feeds, by keyword search. Our initial attempt was to establish which pay-per-click (PPC) keyword searches generated the higher response rates on the first sale, and subsequently, higher LTV. We soon discovered that keyword granularity was overkill; the better approach was to use keyword category. With keyword category data, embedded in RKG source data, we matched the order history back to the category. This facilitated not only LTV reporting but, later, Matchback Analysis as well.
- Challenge: Can we justify the expense of a marketing campaign, knowing that with the initial response we might break even (or take a small loss) but if enough customers become repeat buyers it would be profitable?
Description: One of our clients was planning a large mailing campaign, and wanted to know more about the prospective yield of their campaign before investing serious dollars. Would the prospects that they convert generate enough repeat business over the first year to justify the campaign expense?
Solution: Calculate the Near-Term Value of a customer.
Detail:
When you know the Lifetime Value (LTV) of a customer, you can invest marketing dollars more intelligently, with confidence in the return on your investment and long-term profitability. But the entire lifetime of a customer can be a long time to wait.
Enter the concept of Near-Term Value (NTV).
NTV calculation differs from LTV in that it counts sales only within the first year of the life of the customer.
As in LTV, the customer acquisition costs are backed out, as well as the Cost Of Goods Sold (COGS). The net value – the NTV – can then be used to assess which prospect segments from previous campaigns were projected to be profitable.
By calculating the NTV for our client, they were able to determine that they could justify the expense of the campaign, and were able to generate a significant profit.
- Challenge: When is a customer truly “lapsed”? One year? Two Years? Or is it product specific?
Description: By analyzing the length of buying cycles by product class, the client can answer the question “At what point should I consider a customer as lapsed?”
Solution: In 2010, WiseGuys developed a Buying Cycle algorithm, as part of a retention project for a WiseGuys CRM client. By establishing when a customer’s buying cycle is about to pass, marketing staff can intervene with just the right promotion to get that elusive second order – and succeed in keeping the customer active.
Data Preparation & Manipulation
- Challenge: One customer account may have multiple contact names. How can all the activity for that company be tracked in an intelligent way?
Description: Many order management systems use a flat file structure that is inadequate for B2B marketing. They store customer source and contact information in one file, even though B2B marketing needs a parent-child relational structure (one customer account to many contact names). This source information needs to be normalized in a relational structure, so that marketing queries and reporting yield valid results.
Solution: Convert flat file customer records into a relational B2B database
Detail:
With corporate accounts, you typically have multiple contacts in your customer database. If you treat each of them as individuals and consider them separately when you do analysis, your results will not accurately represent your full relationship with that business.
WiseGuys Marketing algorithms give you a way to solve that.
We create an artificial “Org” file that contains one company record for each unique company location. It is linked to a second individual contact file that contains many “Person” records. Orders are included in a third file for transactions. With this structure, complex customer queries and reports deliver more accurate results.
- Challenge: Merging two customer databases into one integrated database
Description: When two companies combine through merger or acquisition, you need to combine customer databases that might be in very different formats, with different fields and different values.
Solution: Use a table-driven approach to merging the databases.
Detail:
One of our clients acquired another business with significant potential for crossover sales. They needed to merge the customer databases, but ran into concerns about mixing apples with oranges.
WiseGuys employed a table-driven approach that standardized and converted the coding structure (source codes, etc.) from the acquired company to the host.
- Challenge: How do you handle individuals who are both a retail and a wholesale customer?
Description: If one of your customers purchases from you for their own use as well as for resale, how do you handle that in your database – without creating duplicate customer records?
Solution: We create a many-to-many relational structure for Divisions and Customers.
Detail:
When you are analyzing customer data, you want to be able to look at retail and wholesale customers separately. But what do you do if the same individual purchases for their own use, as well as for resale and wholesale or distribution?
WiseGuys solves this problem by creating a many-to-many relational structure for Divisions and Customers. With this structure, we create an “Or” condition for customer queries (in the arcane world of “and/or” Boolean logic). The “Or” condition allows queries to select a customer that is either retail OR wholesale. This is very convenient when all other query conditions are strictly “And” conditions (i.e. “Recency 12 months AND order frequency 2+).
- Challenge: How do you ensure that the B2B decision maker receives your marketing campaigns, instead of the purchasing manager?
Description: Your order management system credits the purchasing manager with all the sales for that company, but the purchasing manager simply executes the purchase and has no involvement in making the decision. How do you ensure that you are marketing to the right person, when that person’s contact record doesn’t have any sales attached to it?
Solution: B2B Contact Targeting
Detail:
Corporate purchases are credited to the purchasing manager in your database (with his rich spending history), but the purchasing manager has no involvement in making the purchasing decision. You want to market to the decision maker.
WiseGuys has a method for attributing the sales to the person who makes the decision, so you are targeting your campaigns to the correct individual. Our approach allows program logic to suppress bill-to contact names in favor of ship-to names or sold-to names (or titles).
- Challenge: Is it possible to combine prospect and customer records in the same database?
Description: When targeting marketing campaigns, you often want to market to both prospects and customers. But if you combine prospects and customers in the same database, your database becomes cluttered and it is harder to keep the data clean. How can you have customers and prospects reside side-by-side in your marketing database, without creating data hygiene issues?
Solution: Householding helps eliminate duplicate records.
Detail:
WiseGuys has developed an approach to combining customer and prospect records in one database that minimizes data hygiene issues and has worked successfully for a number of our clients. Our approach combines an import standardization module with a householding algorithm that assigns artificial, negative “customer numbers” to each prospect. This creates instant visibility of customers versus prospects. As a result, duplicates are considered a necessary evil, but their impact is minimized.
- Challenge: How can you keep customer data clean?
Description: Bad data hygiene is the leading cause of CRM failure. If your customer data is old, outdated, inaccurate, or doesn’t accurately represent your full relationship with that company or that individual, even the best marketing campaigns will deliver poor results. Experienced database pros take data hygiene very seriously. What are some tips for ensuring quality, clean customer data?
Solution: Start by inspecting your data regularly.
Details:
Here are four data hygiene strategies that can be accomplished with relatively little cost or effort, and which will contribute significantly to the success of your marketing campaigns.
- Browse your data – To ensure a complete understanding of your marketing database, you must routinely browse your customer data files. It’s an old fashioned approach, but necessary. Running queries can help too, but simply sorting, viewing, and occasionally running crosstab counts of your customer data will reveal many issues!
- Faulty data hygiene patterns – These include blank fields, inaccuracies, duplicate records, incomplete data entry, missing data, and outdated information. By observation, a pattern may reveal itself that contributes a great share of problems. One of the most common examples occurs during order entry: Many operators who take phone orders routinely key the same default source code for all orders, instead of finding the correct code to apply for each specific order.
- Database conversion patterns – When a new software program is implemented, in the rush to complete the conversion, a quality control process for the data is often overlooked. Old codes are not mapped properly to new fields. Old dates are converted badly. Contact names get jumbled with company names. The old saying applies – garbage in, garbage out.
- Missing data – Perhaps the trickiest data pattern to see is one that is not there: When the data you would expect to see in your database is missing completely. As marketing becomes more multi-channel, the data exchanges between each platform become more challenging. Here are a few examples of patterns you may recognize:
- Customer number ranges – Gaps in your customer numbering could mean you are missing an entire set of records from a period of time.
- Inconsistency between reporting systems – Experienced marketers know that you can’t expect your marketing reports to tie out exactly with accounting reports. For instance, there are always differences in order cutoff dates versus paid dates. However, you should look for large differences in customer or transaction counts, which may indicate serious problems with your data handoffs.
- Make certain that the results from your ETL (Export, Transfer, Load) utilities are checked periodically – ideally by a third party independent of the process itself.
- Bad full name parsing – Accounting system source data typically uses full names rather than first, last. Parsing is easier said than done, particularly when the source data entry process has no validation.
