Select Data from Two Date Periods On One Card
Objective
To replicate a business funnel in Domo for the Head of Retail.
- 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.
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?
