When it comes to reporting, one of the most common requests we get in the marketing services business is to help the client visualize the current and historical status of their marketing and sales funnel.
At first glance, this seems an easy request and many an Information Technology team have taken on this challenge, only to be stymied with the reality of the issue and some hidden complexities.
When thinking of your technology environment, it is helpful to think of it implemented in layers. Think of the “bottom” layer as the layer visible to your users. These are the analysts, operations people, salespeople; anybody who wants some visibility into the marketing and sales operation. Above that is the data layer which holds the data structures the reporting layer needs. It will be a database (hopefully) and the data will be arranged in some logical, more or less normalized structure. It is this structure that needs to be carefully considered when exploring options for visualizing the funnel.
Above the data layer is the Extract, Transfer, Load (ETL) system. The ETL is the system that physically moves all your data into the database from the layer above, the operational layer. The people on the marketing and sales teams depend on applications like Marketing Automation Platforms, CRM systems, ERP, Web Analytics, etc. These are the tools your operational teams use, but more importantly, the systems that record customer interactions.
In this metaphor for your data architecture, the data flows from top to bottom, from operational environment to intelligence environment. That said, data can also flow back up and ideally it should. But this slightly oversimplified view begins to help us understand the problem of tying all your customers’ funnel activities together and getting a single view of that funnel.
The Funnel Spans Systems
The customer lifecycle spans many systems in most marketing and sales operations and that is the root of the problem. Think about what a comprehensive marketing and sales funnel comprises. Acquisition (of new contacts), inquiries from existing contacts, different levels of qualification (AQL, MQL, SQL etc.), opportunities and finally purchase and hopefully, multi-purchase. Those status values probably don’t always exist explicitly as a status code in each system AND they don’t all exist in any one of the systems.
Immediately you can begin to see the problem. To get a view of the entire marketing and sales funnel, you have to analyze data from three (or more) systems. Unfortunately, the three systems most likely will not store a “log” of a status change. There are examples of when they may, and if that is available, that is the simplest approach. For example, every time a lead status changes in Salesforce.com, there is a method for tracking that change in the Lead History object. That may not be the case in the Marketing Automation Platform and most likely is NOT the case in an ERP system.
The answer is to take a transactional approach when thinking about status changes and the marketing and sales funnel.
Transactions are the “facts” of the database world.
Ralph Kimball, one of the undisputed fathers of data modeling once described it like this: when you are in the grocery store checkout line, every single time the register goes “beep” that’s a fact, a transaction of one type or another that is of interest to the data architect. Look at your customer lifecycle the same way. When a customer passes a certain milestone, that’s a “beep” and we want to record that.
There are three things that need to be considered; Extract Method, Transformations and the Facts and Dimensions of the target database. The first two go beyond the scope of this blog post, but stay tuned for future advice on this. For now, I want to focus on the third item, as that is where the “transactional” nature of the solution shows itself.
We are going to build a transactional fact table. There are two golden rules of the fact table:
1. The transactions must be additive, that is, they add up, like your grocery receipt
2. They must tie to your conformed dimensions, in this case, most simply, the Customer, the Status and the Date. (See diagram)
This is where the dimensional modeling purist might take issue, but the pragmatist will appreciate the simplicity of this design. Status changes are NOT necessarily additive facts. Your report users will, however, want to see them as facts, and we will apply values that make them act, more or less, like additive, transactional facts.
In the ETL process you will need to standardize status changes across the three (plus) systems. Coming out of the ETL cycle, this will look something like…
- Customer Identifier
- Date of the Status Change
- The NEW status
- The OLD status (typical is CRM) but not required
This is the bare minimum you need to perform lookups on the three dimension tables, Customer, Status and Date. For every record in the table you will add some additional values.
- Nth Customer Status: This is a counter enforced within the confines of each customer, ordering that customer’s status records in order of Date. So, if customer Bob has had 5 statuses in 12 months, we can easily sort them by NTH_STATUS and see Bob’s complete customer journey. I am often asked by the reporting team “Can’t I just sort on date?” Yes of course, but if an analyst wants to see the number of days between status 1 and status 2, your report toll will have to calculate and determine which of Bob’s records are 1 and 2 and THEN calculate.
- Current Status Indicator: Find the MAX of the Nth values, and flag that record as the most recent or “current” status. This allows the reporting team to easily query the table for the current status values. Obviously, each customer will have only one current status.
- Status Lag Days: For each status record, add the number of days between that status and the subsequent status. For example, if Bob was at status “Acquired” (NTH = 1) on January 1st and then became status “MQL” on January 5th, the LAG_DAYS on his acquired status is 5 days. This value becomes additive. If I SUM all of Bob’s LAG_DAYS, they add up to the entire number of days he has been in the funnel. This is also known as “velocity.”
Ultimately, you end up with a transactional fact that looks something like this.
The table above shows you Bob’s entire customer journey, allowing you to easily sort his status values and determine his current status. If you add his LAG_DAYS, you will see his acquisition to purchase was 20 days. This provides the reporting team with a simple ‘one stop’ platform for both historical analysis and current status views of the funnel.
This article has provided you with some hands on advice on how to think about your marketing and sales data and some specifics on how to model it. In my next article, I’ll pull up to a much more strategic level and discuss the merits of building your own database specifically for marketing and sales and what ROI you can expect on that effort.