Pivot Table Compatibility Hint

Honestly, we love Excel '07 (Excel version 12). There's just one problem... compatibility.

First let me say the upgrades in '07 are amazing. Once you get used to the navigation changes (ribbon versus drop down menus) you will never go back. Unfortunately, 99% of our report users are still on Excel '03 (version 10) and compatibility is a big issue. We end up using version 12 to hold and maintain the data (the table functionality is fantastic and with the increased data capacity, we can load a whole lotta data in one workbook) and we use '03 (version 10) for reports. Took alot of research to find a solution so I thought I would share how we overcome our primary compatibility problem.

Pivot Tables: the basis of all our reports

If you create a pivot table in version 12, and even if you save as an "Excel 97-2003 Workbook", the pivot table functionality will not work in Excel 03 (version 10).

Here's how we overcome this:

1. Save data set in '07/version 12. (Recommend tabling the data set)
2. Open a new workbook
3. Save As "Excel 97-2003 Workbook"
4. Close the "Excel 97-2003 Workbook" you just created
5. Re-open the "Excel 97-2003 Workbook"
> You should now see "Compatibility Mode" in the workbook title bar.
6. Create your pivot table in the "Excel 97-2003 Workbook"
> You can use the data set or table in the '07(version 12) workbook.

By doing this, you are ensuring the original pivot table is created in the earlier version and in compatibility mode from the start, therefore enabling pivot table functionality in the '03 (version 10) file.

To help, we have a machine set up with Office '03 to test the reports before we send them out.

-Analytics

The Analytics team wants this...

...tattoo!

All about our traffic

Have you ever wondered what percentage of DMA's it takes to produce majority of the traffics to our site?

Well wonder no more!

For the month of June our top DMA's produced 80% of the traffic to the AM.com site. Of those top DMA's only 1/3 of our DMA's gave us 80% of the traffic to our site!

Well who cares? YOU SHOULD and here why...

Think about this way. You have 100 people working for a business and only 30 people come to work for a week. Did those 30 people have to work really hard to get the job done or did they just work smarter?

In the 33% of our DMA's that produced 80% of the total traffic to our site majority of those areas also had a print presence. Think of it this way. Consumers going to stores seeing our magazine in breeze ways, dealers advertising in the magazine, and a good local presence equal more opportunities for consumers to know who we are and proactively find more about what we offer. (Heard of the law of supply and demand?)
Well at equilibrium price and quantity of a commodity you get to a point where quantity supplied equals quantity demanded[1]. So we supply the publication in the same markets that gives us the most searches. It seems we are in a great place of equilibrium. ALL these things and many more give us the end result of 80% of our searches coming from markets were the magazine is sold.

You have millions of searches being produced at a greater rate in areas where there are magazines. That says....Magazine = More Presence in Areas = More Consumers coming to our site searching for a vehicle = Increased searches = More Leads = MORE $$$$$$.

SO if you don' think it’s important to have a magazine in areas which gives consumers a local presence of our business, if you don't think the publications aids in leading traffic to the site, and you don' think local presence is one of the best things in advertising.

I think you might want to re-evaluate your thoughts.


Until next time…


[1] http://en.wikipedia.org/wiki/Supply_and_demand

News about the Monthly Summary Report

This report contains vital information to aid in all areas of our business. I will explain sections I like the most. So the first section "Search by Type" which breaks down all searches done on our site. It provides the percentage from the overall total of searches. You get the knowledge and sales ammunition to know how (i.e. new car, fuel efficient) searches are impacting our business via searches on the site.

Lets take another section "Advanced Search Fuel Efficient" this section is vital because we are in a day in time where every penny counts. Saving at the gas pump with a fuel efficient car for our car buyers is a very important. We have this information by area which is a even greater tool!

The last section I'll speak about is "Search Combo" this section explains how our users are coming to the site and the percentage of users who just want to search for a make only, make model, or all makes/all models searches. This information is invaluable.

Please take the time to review this reprot not only for the month but for the top 25 areas.


You can find this report posted on the Intranet:http://intranet.automart.com/
KB Browse
Dept: (our company name)
Area: .com Reports
Topic: Searches on AM.com
Group: Monthly Search Summaries

Until next time!

Wanted: AX Reports

Lots and lots of questions regarding ax reports lately. With the org changes, there is now more interest outside the typical ax report users. We LOVE this. Unfortunately, we don't have everything set up for ax quite like am yet. Getting very close though. We are building similar data sets for ax and have trended all the monthly dealer lead data since Jan 07. Until we have this trended data is a nice compact usable format, feel free to contact the team for your city/area.

Go ahead, comment - we will respond. What report (or reports) is (are) critical to your market? How are you using it/them?

-Melissa

DMA vs. MSA

This has been a recent big topic amongst the IBD and sales teams. One of the first changes I made when taking this role (about 4 months ago) was moving away from reporting on MSA/PMSA to DMA. This is important for one reason, when using MSA/PMSA, we potentiall aren't reporting on every zip used in a search on the 2 sites. Only with DMA are all the zips in the US accounted for.

DMAs (Designated Market Area) are composed of counties and updated annually. DMAs are often larger than a MSA or PMSA. DMA codes are useful for targeting market segments and MSA codes are useful for targeting a population.

Here is a visual. (Click the arrrow on the bottom left corner to see slide 2.)



The pictures speak for themselves.

A very important thing to note is we can customize the DMA's to better fit the sales markets. For example, 2 of our sales offices in Washington are N. Pugent Sound and Seattle. Based on the proper (or original) DMA set up, all the zip codes for those 2 markets are included in the Seattle DMA. With a little help from the sales team, we can create a new DMA for N. Pugent Sound and apply the necessary zips.

AutoMart.com on Rachael Ray

Sweeter 'n Yoo-hoo...

http://www.rachaelrayshow.com/show/segments/view/cool-websites-car-shopping-medical-info/

Airs Friday, July 4, 2008.

Welcome to the fun world of analytics!

Sometimes it's tough getting the word out regarding what we do and how exactly it applies to our business. Simply put, we work with numbers. Said differently, we take data that is in its raw format, scrub it to ensure accuracy, import it into our database and create a multitude of reports that can be used by our sales teams. What we (and any analyst) do is no small thing and most reports take significant effort, time and major attention to detail.

The idea behind this blog is to give the analytics team a place to discuss what we have learned or built and hopefully share some insight with anyone who wants to know more. Feel free to post comments, questions, and/or suggestions with your real name or anonomously.

We build reports for 2 websites, automart.com and autoextra.com and right now they don't share customer information which means we have no way of automatically figuring out if any one customer is advertising on both sites. We are in the process of figuring it out so some of the posts will be about that project as well as everything else. We will rarely, if ever, provide actual reports for download here but will reference where they are located on the Intranet (password required). To date, we use Excel for everything. It's an amazing tool for data manipulation and with the upgrades available in Excel 07, we can create some pretty nice reports. We provide all reports in both Excel 07 and 03 (we realize not everyone has upgraded yet) and many are saved as PDF.

We hope you find this valuable (and interesting).

Ciao