What is Looker

Looker is a Third Party data visualization tool that enables us to analyze our backend data in ways that are not available in the dashboard. It’s very useful when trying to find deeper insight; we use this tool both early on in the client relationship when we’re trying to prove validity and robustness to skeptical clients as well as when a client has been with us for a while and we are trying to deliver novel perspectives on their data. This section will review some of the reports we use in both scenarios.

Clicktrails

Proving Data Validity and Robustness

One of the very first demonstrations of our technology that clients often see is a sample of a clicktrail. A clicktrail is the sequence of all the measurable things (on the website or otherwise) that a customer did while shopping. In essence, it’s a visual representation of actions taken on the path to purchase. This is a powerful visualization of what data Clarivoy collects that the dealership did not have access to previously. The CRM typically provides 2 points or less of a click trail, it is not rare for us to find 20 or more touch points.

Why do we share raw clicktrails?

● They are helpful to reinforce the validity of what we do, by giving a sample of the individual data stories behind each purchase. It can demonstrate how we string together data the customer already knows (CRM leads or phone calls) with anonymous behavior in an event string. We tell the customer we do this, but clicktrails can bring the idea to life much better. We call it "the data behind the screen."

● We can reassure the customer by showing specific sources (e.g., Lotlinx or Facebook) in the context of an overall trail or two, giving the customer a "reason to believe" in both us and the source.

● There are sometimes patterns to illustrate like 3PA pageviews show up early in a clicktrail (anonymous research) and then 3PA leads show up late, just before purchase, when the consumer is finally ready to self-identify.

● Ultimately, it's a wow thing, showing information that the customer cannot get otherwise.

Clicktrails are not for comprehensive analysis. We don't want either the customer or the CSL to go through a complete set and try to develop insights. That's what the dashboard is for! At some point, YES we do want a data scientist to mine clicktrails for more patterns and actionable insights, but right now our goal is to scale and deliver insights faster, so we can serve more customers.

Clicktrails are not for validation of our matching. It would be possible to identify individuals from clicktrail leads with CRM touches, and then validate those names in the client's CRM. Some customers would like to do that, but it would violate the privacy expectations of their buyers for their anonymous clicks, so we do not provide enough information for them to match to PII.

Clicktrails should enhance our credibility, not detract from it. That's one of the reasons why we don't include trails with fewer than 3 touchpoints, because these very short ones usually just contain CRM data. If we shared all the trails with 1 or 2 touches, there are often many of them, and some customers may feel these are so short and incomplete they're hard to believe.

Clicktrails Files

Clicktrails files are automatically generated when properties are pushed to PROD (inserted into the sales.production_ready table).

These files can be found at:

https://drive.google.com/drive/u/0/folders/1HCxoOYjbSTb9XMMaIX3zFedfpbKuOtyc

To regenerate/refresh a ClickTrails file:

1. Update the created_ts field in the sales.production_ready table for each row needed (each row is 1 property for 1 month). Make the created_ts today's date.

2. The nightly DAG will automatically pick that row up and regenerate the ClickTrails Excel file, overwriting the existing one.

How to Prepare Clicktrails Manually

The process is relatively simple:

Step 1:

Open the Clicktrails Samples Looker board (Boards → Customer Success → Clicktrails → Clicktrail Samples)

Step 2:

a. Select the client and property

b. Make sure to increase the clicktrail count to a number high enough where all click trails will be downloaded. When gathering data for internal use it is ok to leave the minimum number of clicks at 0. However, when gathering data to show to a client, select a minimum of 2 clicks.

c. Select the date range

d. Click Update to load the data

e. When you have all your settings loaded click the 3 dots in the top right corner

f. Click Download

i. Format = CSV

ii. Download

Step 3:

Open the CSV file that you’ve downloaded. Highlight the entire workbook (you can click on the top left box to the left of the A and above the 1 to do this automatically), and double click on the grey line at the top of a column that represents the end of a cell in order to size the cells so that all the text is visible.

Step 4:

When showing this data to a client it is crucial that you clean up the data. Due to our data privacy agreements, we can not reveal information that the dealer might be able to use to trace a sale back to an individual. For this reason, we must delete column A with Year information in it.

Consider color-highlighting certain types of touches (like CRM vs. phone vs. 3PA) and/or sources (Facebook,, LotLinx, etc.), depending on what will interest your customer.

Look at the trails beforehand and pick out a few to highlight interesting or typical behavior. Let the customer know this is a sample of clicktrails for illustrative purposes, not a comprehensive set to analyze.

● Do not volunteer that we have filtered out shorter clicktrails.

● Do not volunteer that we have removed the Year.

If the customer asks for the names associated, let them know that we need to protect the privacy of their customers' anonymous online behavior.

The file is now ready to present to a client.

Presenting Click Trail Data

There are a few different ways to present this data. The two most common ways we use this data is to 1) Visualize the significant increase in prepurchase information that Clarivoy brings and 2) Discover how much overlap there is between vendors

3. Visualizing the Data The quickest way to make the data easy to read is to copy formatting from another Click Trail file, which will paste in the Conditional Formatting that selectively highlights different vendors, such as Third Party Auto. You may have reasons to highlight your customer’s data uniquely, so you can help them keep an eye on certain vendors. To do this, highlight the entire workbook, under home click Conditional Formatting, choose Text that Contains

In the field “Format cells that contain the text” type CRM and hit ok. I usually use the color red. Repeat the process above for the words email, and phone the entire time using the same color highlight.

Repeat this process one more time, except you’re going to highlight cells with the word web in a different color, I usually use the color green. The end should have every cell with an entry highlighted one of two colors, like the image below.

The contrast between what the dealer knew (in red) and what Clarivoy discovered is very apparent when shown this way and can provide the client with confidence that our data is far more robust than they could have ever achieved through other means. This helps make their budget decisions easier.

2. Discovering Vendor Overlap Similar to the process above, we want to use conditional formatting to highlight different texts in different colors. Say I had a client that wanted to know if people who used paid search were also on AutoTrader. I would highlight the text paid_search one color (in red below) and autotrader another color (green) so that I can see if there are a lot of red and green cells in the same rows. As you can see, in this example people who use paid search are not likely to have also have an attribution from autotrader.

Investigating Shopping Behavior Across Channels

This board has yet to be built in Looker - TBD. Below is the old Chartio information for reference in designing the new Looker board later.

The [STAGE] Clicktrail Overview report in chartio is a fantastic tool to use to investigate shopping behaviors. A snapshot of the report is below. When presenting this to a client there is sensitive information about some of the data sources we use at Clarivoy. Please screenshot your graphs so that the data sources (lr_anonymous_web, nr_anonymous_web, etc) are not revealed as I’ve done below.

The data shown above is an aggregation of all clients and all channels. The red bars represent the volume of activity that we track (leads, web traffic, calls, etc). As you can see the closer to the day of purchase the more activity there is with the most activity happening 1 day before sale. You can also see that on average from 1st touch point to sale, the path to purchase is 51.29 days long, with 10.39 touchpoints happening during that time. The interesting discoveries come when you look at specific clients, down to specific channels and vendors. I’ll cover some observations below.

Comparing Channels

This board has yet to be built in Looker - TBD. Below is the old Chartio information for reference in designing the new Looker board later.

In the screenshot above I have isolated out the direct mail channel. As you can see, there is no observable correlation between when a piece was mailed out and a sale. You can also see that 95 days average path to purchase is much higher than the 51 day average of all channels across all clients. In this particular instance I would feel safe in assuming Direct Mail has no significant impact on this dealers sales.

Taking the above method, you can compare and contrast different channels, as well as individual vendors. During your data discovery, you will likely notice that some channels have strange spikes in activity, on particular days. It can also be helpful to investigate what sources produced these spikes where it is 1 day or many days before purchase. You can narrow in on individual days by using the Touchpoints X Days from Sale tool at the bottom of the page, simply enter the day you want to focus in on and click update. When the data refreshes download the data as a CVS. I find it useful to create a pivot table with the export.