Select Data from Two Date Periods On One Card

danoffer

Objective

To replicate a business funnel in Domo for the Head of Retail.

 

https://ibb.co/hA15w7

 

 

• From Google Analytics

o   Come from Google Analytics

• From the website (SQL data)

o   Valuations

o   Appointments

o   No Shows come from the website

o   Shows

o   Purchases

 

Where I’m Up To

I’ve grabbed the data in Domo and written beast calculations to extract the data in a sandbox.

Challenges

The problem with comparing this data as ratios and as counts is that the date range field is actually different for the different data types.

 

https://ibb.co/bDgG3n

 

There are a number of different dates that are available for each record

• User Added on Time
• Valuation Added on Time
• Appointment Status Change Time
• Valuation Status Change Time
• Appointment Date

To illustrate the issue, let’s assume the following for the month of:

• 100 Valuations (January), 150 Valuations (February)
• 40 Appointments (January), 80 Appointments (February)
• 20 Purchases (January), 40 Purchases (February)

So for January, Purchases/Valuations should be 20% and Purchases/Purchases should be 50% in this example, but I can’t select two different date fields in Domo to create these ratios.

Let’s illustrate this issue with two examples.

• Adam created a valuation on the 29th January, his appointment is for the 2nd February and his vehicle was purchased on the 5th February.
• Ben created a valuation on the 1st January and his appointment was for the 5th January and his vehicle was purchased on the 10th January.
• Cara created a valuation on the 29th January, her appointment was for the 31st but the buyer updated the appointment on the 1st February to report a purchase

When we analyse this on a monthly view, Adam’s and Cara’s data falls into difficulty.

If I select for the card Valuation_Added_On_Time the month of January

• Valuations

o   Come from Valuation Added On Time

• Appointments

o   Come from Appointment Date ideally

• Purchases, No-Shows, Shows

o   Come from Appointment change time ideally

The problem comes in when you start looking at percentage performance.

Problems

• If we select valuation added on date, we end up with an inaccurate purchase count and appointment records
• If we select appointment change date, we end up with an inaccurate valuation count

Solution

• To get the right data, I’ve used different cards to select data from the same time range, but off different date range fields.

Continued Problem

• To get the ratios right (see left/right on the funnel image), I need to select data from two  different data range fields on the same card
  
  
  What do you propose?