Hi all!
We are beginning several new data analysis initiatives here and we’re curious about best/recommended practices.
We’ve identified several goals for acquisition, re-acquisition and retention and now need to do some trend analysis and segmentation of certain groups. However, we’re unsure of the best way to pull this information. Do most of you use SQL queries, or Lists and/or Extractions, any other methods?
Second, what kind of verification process do you have when doing SQL queries? We have one person who can do SQL queries, but we’d like to develop checks and balances to verify the data is accurate to the best of our ability.
Any information you can provide about your process is appreciated!
Thank you!
Marley
Hi Marley,
The process you use will probably depend on what you want to do. If what you're trying to do is pull out certain people that you want to identify as prospects for retention or re-acquisition, then what you're talking about is some sort of extraction and mailing (either DM or eDM), I would assume.
If, however, you want stats or numbers or just information to view about people, then you want some sort of report or dashboard.
With regards to SQL, my two cents' worth would be that SQL is what you use to maintain the database and build reports with. So if there is information that you want on a regular basis, you want to get your SQL person to turn it into a report (if there's not a report that you already want). You don't want it just to exist as something that only a database person can get for you (unless it really is a one-off query). Tessitura should become a useful self-serve resource for many people, not just a technical thing that can only be operated by one specialist.
So turn SQL into reports if it's regular, use lists and extractions if you're after promoting to people.
Finally, with regards to checking SQL, even though the language used is something that only a programmer would understand, the logic behind the query they should be able to explain in English. (e.g. they should be able to tell you that they've arrived at the final sales number by adding up all tickets to a performance for a particular date, except for exchanges and comp seats.) Ask them what they included, what did they not include. If you've got a good DBA, they should be able to explain it in detail.
Depending on what the information is, you may be able to check it against other sources you have. For instance, if you were used to using a spreadsheet for calculating something, does the query or report in Tessitura come in around ballpark?
But the main thing is, do not let the SQL part fool you into letting things get too technical. Ask the questions till you get a plain English explanation of what's in each report. Look at some of the existing Tessitura reports and their documentation to see good examples of how to do this.
Cheers,
Matt
Thank you so much for this information, it's been most helpful!
We are also in the process of developing and standardizing some analysis procedures. I just wanted to echo some of Matt's comments and add a couple insights of my own!
Firstly, I agree with Matt that your SQL person should probably not be the end user of the report. Not only do you run the risk or overloading your programmer, but it also gets frustrating for end users having to wait for information. If you have a large amount of users who do not use Tessitura, the report server can be used to schedule report deliveries by email (although I would encourage them to start using Tess, especially if you give them some pretty dashboards to look at!).
Another option I use for the middle-range user (who regularly wants more in-depth reports) is to build a set of T-Stats reports and share it with them. The ability to quickly retrieve their numbers has quickly overcome the reluctance to use the program, and they have even started modifying criteria on their own! For anyone experienced with Excel, T-Stats is pretty easy to pick up.
The most difficult part of the process for us has been creating a framework and common set of definitions for analysis. This is especially important if you want to start comparing measures over time: without a common framework you run the risk of apples and oranges.
Our solution has been to begin developing a set of metrics and a report suite of about 5 reports specifically designed for the executive and the board. We're about halfway through the development, but having a SSRS report that can be executed regularly has allowed us to take the framework and apply it to previous years for trending purposes. Having the suite use the same logic and metrics has also allowed us to focus on different areas, but also have all the reports relate to each other and measure things in such a way to support our business practice/objectives.
One final piece of advice: mockup your report first, before you give it to your SQL person to program. It will save a lot of headache for everyone if everyone is on the same page!
Hope that helps!
Court