Help for dmAdmin BTR/Customers
BTR/CustomersAndServices/Analysis Spreadsheet
Download the Customers and Services template spreadsheet
What's this page for?
The Customers and Services report page can help you analyse:
- Who your customers are, and how they were acquired
- What services your customers are using
- Which customers (and which types of service) are making you the
most money, through signup/Gold fees, usage orders or renewal orders
- Which customers are generating the most traffic (measured in minutes, up to the most recent month-end)
- Which customers are due to renew their rental soon
Click on the links below for more information on how to use this page.
Help contents for this page
How to use the Customers and Services report
How to use the report selection fields
Quick start instructions
How to use the template analysis spreadsheet
Guide to column names and values
Where the data comes from
What range of data is included in the report
Known limitations and inaccuracies in the data
How to use the Customers and Services report
Select the report you want using the drop-down report selection fields. When you've configured the report to produce the information you want, click "Get Customers Report".
How to use the report selection fields
- Report Type
- Use this to choose what kind of report you'd like to see:
- Customers: Shows customer details only, with a summary of the VA generated by each customer from gold and setup fees, and initial, renewal and usage orders.
- Services: Shows one row for each service account owned by a customer, together with that customer's details.
Customers Filter
Use this to choose whether you want to see:
-
All customers: Shows all customers
- Recently acquired: Shows only customers who've been acquired in the last 6 months
- Current: Shows only customers who are currently active
- High-value: Shows only customers generating total VA over x100
- Max Rows
- Choose the maximum number of rows to be returned and displayed or added to the CSV download file. Choose 100, 1,000 or all available rows.
- Delivery
- Choose whether to see the results on this page, or download them as a .zip file containing Excel-readable CSV data. (If you want to analyse the data, we recommend downloading it.)
- Order By
- Choose whether to order the results by:
- DMID (ascending alphabetical order)
- Total VA (descending order, highest first)
- Created Date (descending order, most recent first)
- Next Renewal Date (ascending order, soonest first)
Quick start instructions
1. Select report type=Customer, customers filter=Recently acquired, max rows=100, delivery=Show table and order by=Total VA.
2. Click "Get Customers Report" to see an on-screen report on the top 100 (in terms of VA generated) customers who have signed up in the past six months.
3. Change delivery to Download CSV to get the report in a zip file containing the data in CSV format.
-
How to use the template analysis spreadsheet
-
To help you get started with analysing your data, we've created a downloadable template spreadsheet. To use this, follow these instructions.
1. Download the Customers and Services template spreadsheet (2.8mb) and open it in Excel.
2. On this page of Analysis Reports, choose the "Services" report type, and download it as a CSV file.
3. Open the CSV file, copy the data, and use "'Edit/Paste Special/Values Only" function in Excel to paste it into the second row of the 'Raw Data' worksheet in your Excel template.
4. You will then be able to refresh the data in the pre-defined report tables and charts so that it reflects the report you have just downloaded.
Guide to column names and values
Column names are abbreviated in the online reports in order to narrow the table columns so the report is easier to read. The full column name is shown when you hover the mouse over the column header, or any value in that column.
Common naming conventions for columns
- "Q" at the start of a column name is an abbreviation for "Quantity". It means that the column says how many of something there are.
- "GMT" at the end of a column name indicates that the column contains GMT date/time values.
- "VA" at the end of a column name indicates that the column contains an amount of "Value Add" in UK pounds ex VAT.
|
Column Name |
Column Type |
Example Value |
Description |
|
DMID |
VARCHAR(50) |
dominic.fox@digitalmailcom |
Customer's unique ID |
|
ServiceAddress |
VARCHAR(20) |
442070602000 |
The address (phone number) of the service |
|
ServiceType |
VARCHAR(20) |
PNX |
The type of the service |
|
CurrentOwner |
CHAR(1) |
Y |
Does the customer currently own this number? |
|
CurrentStatus |
VARCHAR(10) |
ACTIVE |
The current status of the service |
|
OriginalDealer |
CHAR(1) |
Y |
Am I the dealer who originally signed up the customer? |
|
CreateGMT |
DATE |
2009-03-04 |
When the customer was first signed up |
|
ServiceSignupGMT |
DATE |
2009-07-08 |
When the customer first signed up this service |
|
NextRenewalDueGMT |
DATE |
2010-01-01 |
When the next renewal is due on this service |
|
TotalServiceVA |
NUMERIC(10, 4) |
12.3456 |
How much VA this customer/service has generated |
|
TotalInitialOrderVA |
NUMERIC(10, 4) |
9.6712 |
How much VA the initial order for this customer/service generated |
|
TotUsageVA |
NUMERIC(10, 4) |
13 |
How much VA the usage orders for this customer/service generated |
|
TotalRenewalRentalVA |
NUMERIC(10, 4) |
5.7766 |
How much VA renewal rental orders for this customer/service have generated |
|
TotalGoldAndSetupVA |
NUMERIC(10, 4) |
2.3333 |
How much VA the gold and setup fees for this service generated |
|
NumberGoldValue |
NUMERIC(10, 4) |
1.9876 |
The Gold value of this number |
|
PurchasedTariff |
VARCHAR(25) |
Public.EC-UK-GEO-ALL-Bas |
The tariff the customer initially purchased this service on |
|
PurchasedTariffGen |
CHAR(3) |
T2 |
he generation of tariff to which the purchased tariff belongs |
|
PurchasedService |
VARCHAR(25) |
dmConnect |
The service the customer initially purchased |
|
PurchasedPlan |
CHAR(3) |
Bas |
The price plan the customer originally purchased |
|
LatestTariff |
VARCHAR(25) |
Public.EC-UK-GEO-ALL-Pro |
The tariff of the customer's most recent purchase |
|
LatestTariffGen |
CHAR(3) |
T2 |
The tariff generation of the customer's most recent purchase |
|
LatestService |
VARCHAR(25) |
dmConnect |
The service of the customer's most recent purchase |
|
LatestPlan |
CHAR(3) |
Pro |
The price plan of the customer's most recent purchase |
|
NumberRegion |
CHAR(2) |
UK |
The two-character region code for the region to which the number belongs |
|
NumberClass |
CHAR(3) |
PER |
The class to which the number belongs |
|
NumberType |
VARCHAR(7) |
PER-PN2 |
The type of the number |
|
LifetimeMinutes |
INTEGER |
1234 |
The total number of minutes' usage over the lifetime of the service so far |
|
Month0Minutes |
INTEGER |
12 |
The number of minutes' usage so far this month |
|
Month[1...6]Minutes |
INTEGER |
10 |
The number of minutes' usage over each of the past six months (Month6 oldest, Month1 newest) |
|
Month[1...6]UsageVA |
NUMERIC(10, 4) |
12.3456 |
The VA generated by usage orders from each of the past six months. Note that not all dealers are eligible for commission on repeat rental VA. These figures start with the most recent complete month for which a month-end has been calculated ("Month1"), and go back six months (to "Month6"). |
|
FirstOrderRef |
VARCHAR(25) |
ORD.2010.03.12345 |
The unique order reference of the customer's first order for this service |
|
Source |
VARCHAR(25) |
Clbhs |
SMACT codes for the web session in whcih this service was initially purchased. These tags give more detail about how the customer arrived at your site when they originally signed up for the service. In order to get this information, you need to add SMACT tags to your campaign URLs (such as the click-through URL you provide for a pay per click campaign) when you set up the campaign. For more information on SMACT tagging, see the dmABS Affiliate and Adapted setup guides, available from our Downloads page. f f |
|
Medium |
VARCHAR(25) |
- |
|
|
AdCode |
VARCHAR(25) |
Csla |
|
|
CampaignCode |
VARCHAR(25) |
_r24s1 |
|
|
SearchTerm |
VARCHAR(255) |
Free Numbers |
|
| CmsnDealerId | DealerID (4 or 5 digits) | 6731 | The ID of the dealer earning commission on this line (should be you!) |
Data scope and exclusions
Where the data comes from
The data in this report is primarily based on:
- dmClubs records of orders placed by customers
- Our customer account records, which include each customers registration details
- Our service account records, which show the current ownership of numbers.
- Our records of monthly usage of numbers, calculated at the end of each month.
What range of data is included in the report
The range of the Customers and Services report is limited by the primary data on which the report is based. In particular:
- The order records go back to 2002-06-01.
- The monthly usage records go back to 1998, but we exclude any usage prior to 2002-06-01 as it is necessary to correlate the usage records with the order records to know who owned a number during any given month.
Accordingly, the report summarises customer usage and VA generated starting from 2002-06-01, and excluding any activity prior to that date.
Known limitations and inaccuracies in the data
- Usage figures are calculated only up to most recent month-end
- This report excludes customers who no longer have accounts with dmClub
- Financial totals shown on the page are for that page only, not for the entire data set
- If the NextRenewalDueGMT date is in the past, it shows when the customers last rental period on the number expired.