Select Data from Two Date Periods On One Card

danoffer
danoffer Member
in Archive

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? 

Categories